1071 – Specified key trouble with MySQL

Old mambers already knows about it, this is pain in the neck that occurs when you are in a hurry and if you completely forgot how did you resolve same problem few months ago.

It is a charset thing that happens if you try to copy UTF8 table to LATIN1 database. Ok… or something like that.

If you receive error message #1071 – Specified key was too long; max key length is 1000 bytes when you run a query like:

# Table "mos_core_acl_aro" DDL
CREATE TABLE `mos_core_acl_aro` (
`aro_id` int(11) NOT NULL auto_increment,
`section_value` varchar(240) NOT NULL default '0',
`value` varchar(240) NOT NULL default '',
`order_value` int(11) NOT NULL default '0',
`name` varchar(255) NOT NULL default '',
`hidden` int(11) NOT NULL default '0',
PRIMARY KEY (`aro_id`),
UNIQUE KEY `mos_gacl_section_value_value_aro` (`section_value`,`value`),
UNIQUE KEY `section_value_value_aro` (`section_value`,`value`),
KEY `hidden_aro` (`hidden`),
KEY `mos_gacl_hidden_aro` (`hidden`)
) TYPE=MyISAM;

Try to add DEFAULT CHARSET=latin1 at the very bottom:

# Table "mos_core_acl_aro" DDL
CREATE TABLE `mos_core_acl_aro` (
`aro_id` int(11) NOT NULL auto_increment,
...
KEY `mos_gacl_hidden_aro` (`hidden`)
) TYPE=MyISAM DEFAULT CHARSET=latin1;

MySQL doesn’t allow more then 1000 bytes as a key.

latin1 = 1 byte = 1 chararcter
uft8 = 3 byte = 1 chararcter

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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.