[ERROR] 1071 Reported When an Index Creation Fails for RDS for MySQL
Scenario
The index failed to be created because it was too long. The following error was reported:
[ERROR] 1071 - Specified key was too long; max key length is 3072 bytes
This problem may occur in MySQL-8.0.20.5.
Fault Analysis
The InnoDB table engine has a length limit on index prefixes.
By default, an index prefix can contain a maximum of 767 bytes, but if innodb_large_prefix is set to ON, the index prefix length is increased to 3,072 bytes.
SHOW VARIABLES LIKE '%innodb_large_prefix%';
The length of an index prefix also depends on the InnoDB page size. If the parameter innodb_page_size is set to its default value 16 KB, the maximum index prefix length is 3,072 bytes, but if this parameter is set to 8 KB, the maximum index prefix length is 1,536 bytes. If this parameter is set to 4 KB, the maximum length of the index prefix is 768 bytes.
SHOW VARIABLES LIKE '%innodb_page_size%';
Check the structure of the problematic table and query all supported character sets and their byte usage:
SHOW CHARACTER SET;
When a problematic table uses the utf8mb4 character set, each character uses 4 bytes. This means that if the index prefix has 3072 bytes, it can only contain 768 (3072/4 = 768) characters. You just need to set the index prefix length in the CREATE TABLE statement to 768 or modify the index field to keep it less than 3072 bytes.
Solutions
Change the length of the index field.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot