Migrating tables from 7.3 to 7.5

Help
1

I have DB Schema which is in accordance with Singlestore 7.3.

Now, when I am trying to update my Singlestore to 7.5, certain tables are not getting created any more.

Some of the examples:
CREATE TABLE tag
(
id BIGINT UNSIGNED NOT NULL,
name CHAR(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
value CHAR(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
KEY (id, name, value) USING CLUSTERED COLUMNSTORE,
KEY (name) USING HASH,
KEY (value) USING HASH,
SHARD KEY (id) USING HASH
);

I am getting the following error:
Query 1 ERROR: Feature ‘shard key on columnstore table with additional BTREE/HASH option’ is not supported by SingleStore.

Looks like Shard key with hash is not supported anymore. Can you please explain the reasoning.

Another example:
CREATE REFERENCE table policy
(
id BIGINT NOT NULL AUTO_INCREMENT,
customer_id BIGINT NOT NULL,
policy_id BINARY(16) NOT NULL,
name CHAR(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
PRIMARY KEY (id) USING HASH,
UNIQUE (customer_id, policy_id)
);
Error: Query 1 ERROR: Feature ‘multiple UNIQUE indexes with at least one index containing multiple columns on columnstore table’ is not supported by SingleStore.

I understand that i am missing something basic which changed in 7.5, but I am not able to pin point exactly what it is.

2

Zhou from Singlestore here. Thanks for posting this.

  1. The behavior is intended. Shard key in columnstore were meant to be ‘logical’ key only. And in previous versions we accidentally allowed an undocumented ‘shard key (a) using hash’ syntax. It actually causes some other issues like the hash index can not be dropped, so we correctly locked them out in 7.5. I would suggest do ‘shard key(a), key(a) using hash’ instead so it is clear you have both an shard key and a secondary hash key.
  2. Yea unfortunately for now we don’t support other unique keys when there exists a multi column key. Our engineers are working on the feature and it will be supported in some later versions.
1 Like
3

Thanks zhou for your response.

1 Like