[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;

Click to enlarge

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.