[ERROR] 1071 Reported When an Index Fails to Be Created for RDS for MySQL

Scenario

The index failed to be created because its length exceeds the upper limit. 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 problem table and query all supported character sets and their byte usage:

SHOW CHARACTER SET;

Click to enlarge

When the problem table uses the utf8mb4 character set, each character occupies 4 bytes. This means that the index prefix only contains 768 (3072/4 = 768) characters if the index prefix has 3072 bytes. Therefore, you only 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.