Bug #58187 Difference in maximum key lenght between 5.1.52 and 5.5.7rc

• Submitted: 14 Nov 2010 21:12
• Modified: 14 Nov 2010 21:28
• Reporter: Roel Van de Paar
• Status: Not a Bug
• Category: MySQL Server: DDL
• Severity: S1 (Critical)
• Version: 5.5.7rc
• OS: Any
• CPU Architecture: Any

[14 Nov 2010 21:12] Roel Van de Paar

Description:
5.1.52:

mysql> CREATE TABLE `t1` (pk VARCHAR(1024) CHARACTER SET latin1 not null PRIMARY KEY);
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

5.5.7rc:

mysql> CREATE TABLE `t1` (pk VARCHAR(1024) CHARACTER SET latin1 not null PRIMARY KEY);
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

Looks like a backwards incompatible change

How to repeat:
CREATE TABLE `t1` (pk VARCHAR(1024) CHARACTER SET latin1 not null PRIMARY KEY);

[14 Nov 2010 21:24] Roel Van de Paar

See bug #4541 [Note that the above is executed on the same system/os and both are x64 mysqld releases, so the difference is specifically between the versions]

[14 Nov 2010 21:28] Roel Van de Paar

Not a bug. 

The difference is actually between MyISAM and InnoDB;

mysql>  CREATE TABLE `t1` (pk VARCHAR(1024) CHARACTER SET latin1 not null PRIMARY KEY) ENGINE=InnoDB;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

mysql>  CREATE TABLE `t1` (pk VARCHAR(1024) CHARACTER SET latin1 not null PRIMARY KEY) ENGINE=MyISAM;
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes