Bug #17041 InnoDB reports wrong error code when index length exceeded
Submitted: 2 Feb 2006 14:58 Modified: 2 Feb 2006 15:15
Reporter: Kristian Koehntopp Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.0.16 OS:Windows (Windows)
Assigned to: CPU Architecture:Any

[2 Feb 2006 14:58] Kristian Koehntopp
Description:
When creating an index that is too long for MySQL, MySQL must report error 1071 (SQLSTATE 42000), Specified key was too long.

For MySQL in general, this limit is 1024 bytes, but for InnoDB, this is 767 bytes. If you create an index with a length between 767 and 1024 bytes, you do not get error 1071, you get 139 ("too big row"), which is an error.

How to repeat:
root@localhost [test]> create table utf ( d varchar(4000) character set utf8 );
Query OK, 0 rows affected (0.34 sec)

root@localhost [test]> alter table utf add primary key (d(767));
ERROR 1071 (42000): Specified key was too long; max key length is 1024 bytes
root@localhost [test]> alter table utf add primary key (d(342));
ERROR 1071 (42000): Specified key was too long; max key length is 1024 bytes

root@localhost [test]> alter table utf add primary key (d(256));
ERROR 1005 (HY000): Can't create table '.\test\#sql-15f8_c.frm' (errno: 139)
root@localhost [test]> alter table utf add primary key (d(341));
ERROR 1005 (HY000): Can't create table '.\test\#sql-15f8_c.frm' (errno: 139)
root@localhost [test]> alter table utf add primary key (d(255));
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [test]> quit

You can do the same with latin1, multiply all prefix length with 3 then.

Suggested fix:
Check the index length of the engine, and use that limit instead of some hardcoded constant.
[2 Feb 2006 15:15] MySQL Verification Team
I was unable to repeat with currente 5.0.18 Windows server release:

c:\mysql\bin>mysql -uroot mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.18-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> delete from proc where name="FuncCountVariableInTableItemResults";
Query OK, 1 row affected (0.01 sec)

mysql> exit
Bye

c:\mysql\bin>mysqldump -uroot --routines vote2006 > vote2006.sql

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.18-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table utf ( d varchar(4000) character set utf8 ) engine=innodb;
Query OK, 0 rows affected (0.88 sec)

mysql> alter table utf add primary key (d(767));
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
mysql> alter table utf add primary key (d(342));
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
mysql> alter table utf add primary key (d(256));
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
mysql> alter table utf add primary key (d(341));
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
mysql> alter table utf add primary key (d(255));
Query OK, 0 rows affected (0.42 sec)
Records: 0  Duplicates: 0  Warnings: 0
[2 Feb 2006 15:21] MySQL Verification Team
Removing unrelated text :)

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.18-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table utf ( d varchar(4000) character set utf8 ) engine=innodb;
Query OK, 0 rows affected (0.88 sec)

mysql> alter table utf add primary key (d(767));
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
mysql> alter table utf add primary key (d(342));
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
mysql> alter table utf add primary key (d(256));
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
mysql> alter table utf add primary key (d(341));
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
mysql> alter table utf add primary key (d(255));
Query OK, 0 rows affected (0.42 sec)
Records: 0  Duplicates: 0  Warnings: 0