Problem description

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.

 

Causes

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.

 

Solutions

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.
  1. 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.
  2. 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;

 

Application scope

  • ApsaraDB for RDS