RDS MySQL reports Error 1709: Index column size too large. The maximum column size is 767 bytes when creating an index - ApsaraDB RDS

Issue description

When you attempt to create an index on a large field in RDS MySQL, you might encounter the following error:

ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

Cause

The InnoDB engine in MySQL restricts the index field length to 767 bytes. For large fields with multi-byte character sets or combinations of multiple fields, creating an index will trigger this error if the limit is exceeded.

Note

For example, with the utf8mb4 character set, which is a 4-byte character set, the maximum supported index field length is 191 characters (767 bytes/4 bytes per character ≈ 191 characters). Therefore, creating an index on a field of type varchar(255) or char(255) will fail. For more information, see the MySQL documentation.

Solution

Log on to the RDS console, modify the innodb_large_prefix parameter to ON on the Parameters page, and click Submit Parameters.
Note
- After you enable innodb_large_prefix, the maximum index field length for InnoDB tables in Dynamic and Compressed formats can support up to 3072 bytes.
- After you modify this parameter in the primary instance, the system will automatically synchronize it to all read-only instances under the primary instance.

Adjust the row_format of the table.

When creating a new table, you can specify the row_format as Dynamic or Compressed. See the following example:

CREATE TABLE idx_length_test_02 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
) ROW_FORMAT=DYNAMIC DEFAULT CHARSET=utf8mb4;

For existing tables, you can modify the row_format using the following commands:

ALTER TABLE <table name> ROW_FORMAT=DYNAMIC;
ALTER TABLE <table name> ROW_FORMAT=COMPRESSED;

After completing these operations, try creating the index again to verify that the error no longer occurs.