Bug #7420 Incorrect Error an index would be > 1024 chars (InnoDB), > 1000 chars (MyISAM)
Submitted: 20 Dec 2004 4:12 Modified: 20 Dec 2004 6:58
Reporter: B Jones Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.7 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[20 Dec 2004 4:12] B Jones
We are trying to create a unique index on the main columns in this table so we can use a REPLACE statement to update them.  Yet MySQL incorrectly claims that this column would have e.g. > 1024 characters.  We have tried char/varchar and unique/non-unique index, but all attempts get the same error message.  Without this being fixed, REPLACE will not work.

How to repeat:
CREATE TABLE test_addresses (
  address_id int(11) NOT NULL auto_increment,
  address_line_1 char(127) default NULL,
  address_line_2 char(127) default NULL,
  address_line_3 char(127) default NULL,
  town char(64) default NULL,
  postcode char(16) default NULL,
  state_id int(11) default NULL,
  country_code_ISO3166 char(2) default NULL,
  addresses_timestamp timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

  PRIMARY KEY  (address_id),
  KEY state_id (state_id),
  KEY country_code_ISO3166 (country_code_ISO3166),
  KEY postcode (postcode),
  KEY town (town),
  KEY addresses_stct (state_id,country_code_ISO3166),
  KEY addresses_stct2 (country_code_ISO3166,state_id),
  KEY country_code_ISO3166_2 (country_code_ISO3166,state_id,town,postcode,address_line_3)

ALTER TABLE `test_addresses` ADD INDEX ( `address_line_1` , `address_line_2` , `address_line_3` , `town` , `postcode` , `state_id` , `country_code_ISO3166` ) 

-- Refuses to create index; claims it is > 1024 chars

Suggested fix:
Create index
[20 Dec 2004 6:10] Heikki Tuuri

In UTF-8, every character can take up to 3 bytes, and the maximum key value length for a MySQL index is 1024 bytes.

The error message shuld be fixed if it talks about characters instead of bytes.
[20 Dec 2004 6:58] Hartmut Holzgraefe
the error message says bytes, not characters, just fine:

ERROR 1071 (42000): Specified key was too long; max key length is 1024 bytes
[20 Dec 2004 7:45] B Jones
My mistake. I had expected UTF8 to store characters in an 8-bit format, so hadn't factored that in.  Locally we're reverting to latin1 to reduce the storage overhead.  Heikki, thanks as always for your quick response.