Unable to DROP COLUMNSTORE HASH KEY/INDEX using ALTER TABLE

Off-Topic
1

Hi,

I have a table
CREATE TABLE recommendations (
_item_id bigint(20) unsigned NOT NULL,
_recommended_item_id bigint(20) unsigned NOT NULL,
_score float NOT NULL,
_algorithm_id tinyint(3) unsigned NOT NULL,
/*!90618 SHARD / KEY _recommended_item_id (_recommended_item_id),
KEY _item_id (_item_id,_algorithm_id,_recommended_item_id,_score) /!90619 USING CLUSTERED COLUMNSTORE / /!90621 WITH(COLUMNSTORE_SEGMENT_ROWS=10000) /,
KEY _recommended_item_id_2 (_recommended_item_id) USING HASH
) /!90623 AUTOSTATS_CARDINALITY_MODE=INCREMENTAL, AUTOSTATS_HISTOGRAM_MODE=CREATE, AUTOSTATS_SAMPLING=ON / /!90623 SQL_MODE=‘STRICT_ALL_TABLES’ */

memsql> SHOW INDEX FROM recommendations;
+-----------------+------------+------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+-----------------------+---------+---------------+
| Table           | Non_unique | Key_name               | Seq_in_index | Column_name          | Collation | Cardinality | Sub_part | Packed | Null | Index_type            | Comment | Index_comment |
+-----------------+------------+------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+-----------------------+---------+---------------+
| recommendations |          1 | _recommended_item_id   |            1 | _recommended_item_id | NULL      |        NULL |     NULL |   NULL |      | BTREE                 |         |               |
| recommendations |          0 | _item_id               |            1 | _item_id             | NULL      |        NULL |     NULL |   NULL |      | CLUSTERED_COLUMNSTORE |         |               |
| recommendations |          0 | _item_id               |            2 | _algorithm_id        | NULL      |        NULL |     NULL |   NULL |      | CLUSTERED_COLUMNSTORE |         |               |
| recommendations |          0 | _item_id               |            3 | _recommended_item_id | NULL      |        NULL |     NULL |   NULL |      | CLUSTERED_COLUMNSTORE |         |               |
| recommendations |          0 | _item_id               |            4 | _score               | NULL      |        NULL |     NULL |   NULL |      | CLUSTERED_COLUMNSTORE |         |               |
| recommendations |          1 | _recommended_item_id_2 |            1 | _recommended_item_id | NULL      |        NULL |     NULL |   NULL |      | COLUMNSTORE HASH      |         |               |
+-----------------+------------+------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+-----------------------+---------+---------------+
6 rows in set (0,00 sec)

memsql> ALTER TABLE recommendations DROP KEY _recommended_item_id_2;
ERROR 1091 (42000): Leaf Error (memsql-c04s01:3307): Can't DROP '_recommended_item_id_2'; check that column/key exists

In the memsql.log I see this:
ERROR: Thread 99726: ShardingAlterTableV6: Executing 'USING? ALTER TWO_PHASE 15376380536120774201:351 TABLE recommendations DROP KEY _recommended_item_id_2' on a leaf failed. The main query was 'ALTER TABLE recommendations DROP KEY _recommended_item_id_2'.

Can please someone explain this? Is it a bug?

2

Hi tomas,

Which version of MemSQL are you using?

3

Hi adam,

I am using 7.1.3

4

Thanks Tomas,

Something must have happened in the past that is causing issues on your cluster. I can drop that index from a table with the same schema as yours.

Can you send us a cluster report to bug-report@memsql.com? Here is how you generate one:
SingleStoreDB Cloud · SingleStore Documentation

5

Hi adam,

I am unable to do the report. I have 2 nodes and when I run memsql-report collect --all it immediately says:
Failed to collect report for host memsql-c04s02

After that it still runs untill my 20GB / partition is full. I tried to redirect the /tmp folder for the report to different much bigger partition with --remote-temp-dir and --output-path but it still generates the report to /tmp folder. Is there a way to redirect the /tmp dir for the report? Or should I run different partial report instead of memsql-report collect --all?

Thank you

6

Hi Tomas,

No need for the --all for now. Partial report is good enough.

-Adam

7

Hi adam,

I just sent it to bug-report@memsql.com with subject “cluster report”.

Thank you!

8

Thanks for the report tomas. We have figured out the bug. For folks following along here the issue is because an index was added (without an explicit name) on the same column as the shard key. We will patch this issue in a future memsql release.

repro steps:

memsql> create table b(a int, b int, shard key(a), key() using clustered columnstore);
Query OK, 0 rows affected (0.143 sec)
memsql> alter table b add key(a) using hash;
Query OK, 0 rows affected (0.446 sec)
memsql> alter table b drop key a_2;
ERROR 1091 (42000): Leaf Error (127.0.0.1:3306): Can't DROP 'a_2'; check that column/key exists

this only impacts columnstore tables (rowstores always create an index on the leaves on the same columns as the shard key. Columnstores don’t). The work around is to give the index an explicit name when adding it until we can patch the problem:

memsql> alter table b add key key_name(a) using hash;