| 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: | |
| 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 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
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.