Bug #2130 Max key length problem
Submitted: 16 Dec 2003 9:55 Modified: 21 Feb 2004 11:37
Reporter: Gary Thornock Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.1 alpha OS:Linux (Linux)
Assigned to: Sergei Golubchik CPU Architecture:Any

[16 Dec 2003 9:55] Gary Thornock
MySQL 4.1.1 alpha reports this error message when importing tables from mysqldump files from MySQL 4.1.0 alpha:

  Specified key was too long. Max key length is 500

The error occurs on tables that have indexed varchar(255) fields with character set utf8.  We've been speculating about the cause, and our best guess is that it happened because 4.1.1 varchar field lengths are measured by number of characters and not bytes, but max key length is still in bytes.

How to repeat:
In MySQL 4.1.0, do this:

CREATE TABLE test_table (
  textfield varchar(255) CHARACTER SET utf8,
  INDEX (textfield)) TYPE = MyISAM;

Observe that it works just fine.

Now run the same create table statement in 4.1.1.  Observe that it reports an error:

  Specified key was too long. Max key length is 500

Suggested fix:
One possible fix would be to make max key length use characters instead of bytes.  Another useful possibility would be for max key length to be a variable that can be set in /etc/my.cnf or with set global, instead of having to be set at compile time.
[3 Feb 2004 17:55] Simon Greenhill
Same problem under windows XP. 

It may be interesting / useful to note that if a column is set to anything greater than 165 (VARCHAR) this error occurs, smaller than 165 & no problem.
[21 Feb 2004 11:37] Sergei Golubchik
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at

Additional info:

The problem is of course that key length in MyISAM table cannot exceed 500 bytes, and as one utf8 char can take up to three bytes, it limits key length to 165 utf8 characters.

I now doubled the max allowed key length in MyISAM tables in 4.1.2