iamhucong commented 4 months ago

Bug Report

Which version of ShardingSphere did you use?

master 3bb9a429

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

all

Expected behavior

Create table successfully.

Actual behavior

In MySQL:

java.sql.SQLSyntaxErrorException: Specified key was too long; max key length is 3072 bytes

Reason analyze (If you can)

27605 The generated encrypted column is too long, which leads to the inability to index.

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

-- Logic SQL:
CREATE TABLE t1 (
id int NOT NULL auto_increment PRIMARY KEY,
b int NOT NULL,
c datetime NOT NULL,
INDEX idx_b(b),
INDEX idx_c(c)
) ENGINE=InnoDB
-- Actual SQL:
CREATE TABLE t1 (
id int NOT NULL auto_increment PRIMARY KEY,
b_cipher VARCHAR(4000),
c_cipher VARCHAR(4000),
INDEX idx_b(b_cipher),
INDEX idx_c(c_cipher)
) ENGINE=InnoDB

RaigorJiang commented 3 months ago

Yes, it is indeed possible to cause the column too long. If we use text type, will it affect the calculation query of the cipher column?

iamhucong commented 3 months ago

Yes, it is indeed possible to cause the column too long. If we use text type, will it affect the calculation query of the cipher column?

If want to index on a TEXT field, must specify a fixed length to do that, will leads same problem. It seems that it cannot be fixed by this way.

RaigorJiang commented 3 months ago

If adding cipher columns cause the row to be too long, the table may need to be split.

zzyReal666 commented 3 months ago

I've had the same problem.My solution is to create the table manually, and all DDL statements are executed manually. If want to index on a varchar,max length is 768 (mysql)

github-actions[bot] commented 2 months ago

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.

github-actions[bot] commented 1 month ago