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 allowed maximum length for a single-column index in an InnoDB table cannot exceed 767 bytes, while that for a composite index cannot exceed 3072 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 3072 bytes, and that for a composite index is also 3072 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 1072 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. One 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.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.