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
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.7
• OS: Linux (Linux)
• CPU Architecture: Any

[20 Dec 2004 4:12] B Jones

Description:
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)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

Hi!

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.

cheers
bj