The generated encrypted column is too long

[iamhucong]

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]

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]

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]

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

[zzyReal666]

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]]

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]]

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