MySQL 1071: Specified key too long.
MySQL 1071: Specified key too long.
When encountering the MySQL error 1071: Specified key was too long; max key length is 767 bytes, the immediate action is to check and potentially adjust the column data types or the specific index length to be within the limit. If using a character set like utf8mb4, where each character can take up to 4 bytes, you might exceed the limit with a relatively small number of characters.
- Identify the table and column that is causing the issue. This should be mentioned in the error message or the context where the error occurred.
- Check the current configuration of the table, focusing on the column definitions and index declarations. Use the following SQL command to get the table structure:
SHOW CREATE TABLE yourtablename;
- If the column causing the issue is using a character set that requires more bytes per character (e.g., utf8mb4), consider if it's possible to reduce the number of characters in the index. For instance, if you have a VARCHAR(255) with utf8mb4, it could potentially exceed the limit. You can adjust the column's size or specify a prefix length for the index.
To adjust the index length, modify the index declaration, specifying a prefix length that keeps the total size under 767 bytes. For a utf8mb4 column, this would look like:ALTER TABLE yourtablename MODIFY COLUMN yourcolumnname VARCHAR(191);
Or, for creating a new index with a specified key length:CREATE INDEX indexname ON yourtablename(yourcolumn_name(191));
If adjusting the column size or the index length is not feasible, consider changing the table's character set to utf8, which requires fewer bytes per character if internationalization concerns permit this change.
- If you have the permissions to change system variables and the above solutions are not viable, another approach (though requiring careful consideration and possibly not immediately actionable) involves changing the InnoDB file format to `Barracuda`, which supports larger prefix lengths when combined with the `DYNAMIC` or `COMPRESSED` row format. This approach allows you to have larger keys but requires changing global variables and possibly altering table settings.
Please note, steps involving changes to system variables or file formats should be approached with caution and ideally tested in a non-production environment first.
Master
MySQL
debugging in Minutes
— Grab the Ultimate Cheatsheet
(Perfect for DevOps & SREs)
MySQL
Cheatsheet
(Perfect for DevOps & SREs)
MORE ISSUES
Backed by
