Help Center/ Relational Database Service/ Troubleshooting/ RDS for MySQL/ SQL Issues/ Failed to Change the varchar Length Due to the Index Length Limit
Updated on 2023-03-06 GMT+08:00
View PDF
Share

Failed to Change the varchar Length Due to the Index Length Limit

Scenario

The alter table command failed to modify a table structure. The following error information was displayed:

Specified key was too long; max key length is 3072 bytes

Possible Causes

  • If innodb_large_prefix is set to OFF, the maximum length allowed for a single-column index in an InnoDB table is 767 bytes, while that for a composite index is 3,072 bytes, with each column in the composite index no more than 767 bytes.
  • If innodb_large_prefix is set to ON, the allowed maximum length for a single-column index is 3,072 bytes, and that for a composite index is also 3,072 bytes.
  • The index length is related to the character set. When the utf8 character set is used, a character occupies three bytes. If innodb_large_prefix is set to ON, the allowed maximum length for all columns in an index is 1,072 characters.

The table structure is as follows:

CREATE TABLE `xxxxx` (
......
`subscription_type` varchar(64) NOT NULL DEFAULT 'DEVICE_EXCEPTION' COMMENT 'Subscription type',
`auth_key` varchar(255) DEFAULT'' COMMENT 'Signature. A token is added to the API request header based on the value of this parameter',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update time',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `enterprise_id` (`subscription_type`,`enterprise_id`,`callback_url`) USING BTREE)
) ENGINE=InnoDB AUTO_INCREMENT=1039 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

This table uses the utf8 character set. Each character occupies three bytes. The composite index enterprise_id contains the callback_url column. If a DDL operation is performed and callback_url to is changed to varchar(1024), the maximum length of the composite index is exceeded. As a result, an error is reported.

Solution

Modify the index or column length.

Parent topic: SQL Issues