What do I do if my ApsaraDB RDS for MySQL instance reports Error 1709? - ApsaraDB RDS

Symptoms

When you create an index, the system may report the following error:

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

Causes

The maximum indexed field length allowed by the InnoDB storage engine is 767 bytes. If you create an index on a multi-byte field or multiple fields, this error may be reported.

Note For example, utf8mb4 is a four-byte character set. The default indexed field length allowed by the utf8mb4 character set is 191 characters based on the following formula: 767 bytes/4 bytes per character ≈ 191 characters. If you create an index on a field of the varchar (255) or char (255) type, the index creation fails. For more information, see the MySQL documentation at the official MySQL website.

Solutions

Log on to the ApsaraDB for RDS console and navigate to the Parameters page. On the Editable Parameters tab, find the innodb_large_prefix parameter, change its value to ON or 1, and click Apply Changes.

Note After you set the innodb_large_prefix parameter to ON or 1, the maximum indexed field length allowed by InnoDB in the DYNAMIC or COMPRESSED row format increases to 3,072 bytes.

Specify the DYNAMIC or COMPRESSED row format during table creation. Example:

create table idx_length_test_02
(
  id int auto_increment primary key,
  name varchar(255)
) 
ROW_FORMAT=DYNAMIC default charset utf8mb4;

Note For a created table, you can run the following commands to change its row format to DYNAMIC or COMPRESSED:

alter table <The name of the table> row_format=dynamic;
alter table <The name of the table> row_format=compressed;