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: ˅˅˅

 

Did you like the article? Let Google Search know by clicking this button: . Please link to content that you find useful on this website on your own website, forum or blog! You can also comment on this page below, or contact me to ask a question or suggest a topic for me to research. There is a user-editable Wiki available on my website, as well as a Forum that you can contribute to. Site Map.

Page last modified 31-Dec-12 22:07:48 EST
Comments on this page:


Add a comment to this page
Comment Title:
Your Name:
Your Email Address:
Notify me of new comments to this item:
Additional Comments:
This is a captcha-picture. It is used to prevent mass-access by robots. (see: www.captcha.net)