When you create an index on a large-sized field in ApsaraDB RDS for MySQL, the system may report the following error:
Error 1071: Specified key was too long; max key length is 767 bytes.
The maximum length of an indexed field 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.
For example, utf8mb4 is a four-byte character set. The default length of an indexed field 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 open source MySQL documentation.
Alibaba Cloud reminds you that:
- When you perform operations that have risks, such as modifying instance configurations or data, check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
- Before you modify the configurations and data of instances including but not limited to ECS and RDS instances, we recommend that you create snapshots or enable RDS log backup.
- If you have authorized or submitted sensitive information such as the logon account and password in the Alibaba Cloud Management Console, we recommend that you modify such information in a timely manner.
- Log on to the ApsaraDB RDS for MySQL console. Open the details page of the target instance. In the left-side navigation pane, click Parameters. Change the value of the innodb_large_prefix parameter to ON or 1. Click Apply Changes.
Note: After you change the value of the innodb_large_prefix parameter to ON or 1, the maximum length of an indexed field 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;If you have created a table, execute the following statements to change the row format of the table:
alter table idx_length_test_02 row_format=dynamic; alter table idx_length_test_02 row_format=compressed;
- ApsaraDB for RDS