MySQL Bugs: #2130: Max key length problem

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:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.1 alpha	OS:	Linux (Linux)
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
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 (
  id int(5) NOT NULL AUTO_INCREMENT,
  textfield varchar(255) CHARACTER SET utf8,
  PRIMARY KEY (id),
  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.

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.

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
    http://www.mysql.com/doc/en/Installing_source_tree.html

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