Bug #20298 Specified key was too long; max key length is 1000 bytes
Submitted: 6 Jun 2006 16:27 Modified: 11 Jun 2006 4:40
Reporter: Bill So Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.22 OS:MacOS (Mac OS X)
Assigned to: CPU Architecture:Any

[6 Jun 2006 16:27] Bill So
Description:
This problem happens when the encoding of the table is UTF8.  It doesn't happen if the table is in iso8859 encoding.

SQL=Specified key was too long; max key length is 1000 bytes:
- - - - - - - - - -
# Table structure for table `mos_core_acl_aro`

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 `section_value_value_aro` (`section_value`,`value`),
  UNIQUE KEY `mos_gacl_section_value_value_aro` (`section_value`,`value`),
  KEY `hidden_aro` (`hidden`),
  KEY `mos_gacl_hidden_aro` (`hidden`)
) TYPE=MyISAM
= = = = = = = = = =

How to repeat:
CREATE DATABASE `test_db` DEFAULT CHARACTER SET utf8;

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 `section_value_value_aro` (`section_value`,`value`),
  UNIQUE KEY `mos_gacl_section_value_value_aro` (`section_value`,`value`),
  KEY `hidden_aro` (`hidden`),
  KEY `mos_gacl_hidden_aro` (`hidden`)
) TYPE=MyISAM;
[6 Jun 2006 18:33] Sveta Smirnova
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

You can find information about length of unicode strings here: http://dev.mysql.com/doc/refman/5.0/en/charset-unicode.html
[11 Jun 2006 4:40] Bill So
Hi, Sveta.

Thanks for your reply.  I read the documentation referring to Unicode and Character encoding in MySQL site before I submit the "bug".  And I don't notice any constraint about the character length for index in MySQL 5.0.

Actually, I'd like to see if my problem is similar to this: #13835?

Thanks,
Bill
[11 Jun 2006 5:28] Paul DuBois
Information about key lengths being measured in bytes
can be found here:

http://dev.mysql.com/doc/refman/5.0/en/create-index.html

Prefixes can be up to 1000 bytes long (767 bytes for InnoDB tables). 
Note that prefix limits are measured in bytes, whereas the prefix 
length in CREATE INDEX statements is interpreted as number of
characters for non-binary data types (CHAR, VARCHAR, TEXT). Take
this into account when specifying a prefix length for a column that
uses a multi-byte character set.