All Products
Search
Document Center

ApsaraDB RDS:What do I do if the "Specified key was too long; max key length is 767 bytes" error message is displayed when I create indexes on an ApsaraDB RDS for MySQL instance?

Last Updated:Dec 12, 2023

Problem description

The following error messages appear when I create an index for a table on the RDS instance:

  • Error 1071: Specified key was too long; max key length is 767 bytes.
  • ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

Scenarios

  • The issue may occur on RDS instances that run MySQL 5.6, MySQL 5.7, and MySQL 8.0.

Causes

The maximum length of an indexed field that can be used 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, the four-byte character set utf8mb4 is used. 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 for a field of the varchar (255) or char (255) type, the index creation fails. For more information, see MySQL documentation.

Solutions

After you change the value of innodb_large_prefix to ON, the maximum length of an indexed field that can be used by InnoDB in the DYNAMIC or COMPRESSED row format increases to 3,072 bytes.

Note

innodb_large_prefix is deleted from MySQL 8.0. If your RDS instance runs MySQL 8.0, you can change the value of Row_format for the table. For more information, see Step 3.

  1. Log on to the ApsaraDB RDS console. In the top navigation bar, select the region in which your RDS instance resides and click the instance ID. In the left-side navigation pane of the page that appears, click Parameters. On the page that appears, change the value of innodb_large_prefix to ON, click OK, and then click Apply Changes.

  2. Log on to the database, execute the following SQL statement, and then confirm that innodb_large_prefix is set to ON.

    show variables like 'innodb_large_prefix';

    The following figure shows an sample output. 返回结果

  3. Configure the row format.

    Note
    • After you change the value of innodb_large_prefix to ON, the maximum length of an indexed field that can be used by InnoDB in the DYNAMIC or COMPRESSED row format increases to 3,072 bytes.

    • Only tables in the DYNAMIC and COMPRESSED row formats are supported. Tables in the compact row format are not supported.

    1. Specify the DYNAMIC or COMPRESSED row format for a table when you create the table. Example:

      create table [$Table_Name] (id int auto_increment primary key, name varchar(255)) ROW_FORMAT=DYNAMIC default charset utf8mb4;
      Note

      [$Table_Name] specifies the name of the table.

    2. Modify the row format of an existing table.

      Execute the following SQL statement to check the value of Row_format. If the value is not DYNAMIC or COMPRESSED, perform the subsequent steps to change the value:

      1. show table status like '[$Table_Name]'\G

        The following figure shows an sample output. 返回结果2

      2. Execute one of the following SQL statements to change the value of Row_format:

        alter table [$Table_Name] Row_format=dynamic;
        alter table [$Table_Name] Row_format=compressed;

  4. Use a prefix index.

alter table [$Table_Name] add key(column_name(prefix_len));
alter table [$Table_Name] add index idx_name(name(100));
-- create index index_name on [$Table_Name](column_name(prefix_len));