MySQL Specified key was too long; max key length is 767 bytes

When restoring a MySQL database, I got the error “Specified key was too long; max key length is 767 bytes”.

MySQL version 5.6 and older versions have a limit of 767 bytes prefix limit.

https://dev.mysql.com/doc/refman/5.6/en/create-index.html

Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 767 bytes long for InnoDB tables or 3072 bytes if the innodb_large_prefix option is enabled. For MyISAM tables, the prefix length limit is 1000 bytes.

From the error message, the error was on line 745, when checking the SQL file, I found the following SQL statement.

To fix it, find

Replace with

This will limit the length of the issue column to 191 chars. Each character takes 4 bytes to store in utf8mb4 character set. The limit 191 is found by trying with default length for issue field 200, then reducing it until the SQL gets created properly. The calculation is 191 * 4 + length of id column < 767 The correct solution is to use newer MySQL versions like MySQL 5.7, which allows length up to 3072 bytes.

Need help with Linux Server or WordPress? We can help!

Leave a Reply

Your email address will not be published.