MKRD.info - Thorough articles on a variety of topics, and no ads!

MySQL Error Specified key was too long

When importing a UTF-8 formatted dump of a version 4 MySQL database into a version 5 database, you may receive an error saying:

ERROR 1071 (42000) at line *: Specified key was too long; max key length is 1000 bytes

Solution:

First determine which particular table(s) is giving you the problem. Either turn the --verbose option ON, examine the partial database import with a tool such as PHP MyAdmin, or turn the --force option ON. The table giving you the problem will the the one that was not imported (usually, the one that follows the last table shown in the incomplete imported database).

Then, open the dump file and find where the index of the table is specified. For example, in the following statement:

CREATE TABLE 'db_templates' (
'template_name' varchar(200) default NULL,
'content' text,
'create_date' datetime default NULL,
KEY 'module_name' ('module_name','template_name')
) TYPE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COLLATE=utf8_general_ci;

You will need to manually shorten the index key to fit into the UTF-8 1000-byte limit, by manually setting the following key limitations:

CREATE TABLE 'db_templates' (
'template_name' varchar(200) default NULL,
'content' text,
'create_date' datetime default NULL,
KEY 'module_name' ('module_name'(75),'template_name'(100))
) TYPE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COLLATE=utf8_general_ci;

Sources:

http://bugs.mysql.com/bug.php?id=6604

http://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes

http://stackoverflow.com/questions/7622253/how-to-skip-row-when-importing-bad-mysql-dump

http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html

Explanation: The maximum key size in MySQL is 1000 bytes.This is a known issue submitted to the MySQL bug tracker and has yet to be resolved. Currently the only solution is to limit indexing on UTF-8 encoded tables or use latin1 instead.

latin1 uses one (1) byte per character and UTF-8 usually allocates three (3) bytes per character, therefore some indexes will exceed the 1000 byte limit. This accounts for the generated error when converting tables with large or compound indexes from latin1 to UTF8 encoding.

Sources: Dallas Vogels, http://islandlinux.org/howto/mysql-error-specified-key-was-too-long-max-key-length-1000-bytes

˅˅˅ Additional valuable information is available at one of the links below: ˅˅˅