| Bug #13253 | InnoDB causes error when a long sub-part index is created. | ||
|---|---|---|---|
| Submitted: | 16 Sep 2005 2:47 | Modified: | 19 Sep 2005 8:16 |
| Reporter: | Sadao Hiratsuka (Basic Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.0.10 | OS: | Linux (Linux) |
| Assigned to: | Osku Salerma | CPU Architecture: | Any |
[16 Sep 2005 4:07]
MySQL Verification Team
Most probably a feature request.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.14-rc-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create table test_i1ng (col1 varchar(2000), index (col1(768)))
-> character set = latin1 engine = innodb;
ERROR 1005 (HY000): Can't create table './test/test_i1ng.frm' (errno: 139)
mysql> create table test_m1ng (col1 varchar(2000), index (col1(1001)))
-> character set = latin1 engine = myisam;
Query OK, 0 rows affected, 1 warning (0.07 sec)
mysql> show warnings;
+---------+------+----------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 1000 bytes |
+---------+------+----------------------------------------------------------+
1 row in set (0.00 sec)
mysql> exit
Bye
miguel@hegel:~/dbs/5.0> bin/perror 139
MySQL error code 139: Too big row
miguel@hegel:~/dbs/5.0>
[16 Sep 2005 4:55]
Heikki Tuuri
Hi! Since InnoDB may store 'externally' tails of columns whose length is > 768 bytes, you cannot create a column prefix index on the first 769 bytes of a column, or a normal index on a column whose length is 769 bytes. Osku, please check that an error is returned by 5.0.14 in the above cases. Error 139 is 'Too long row'. We should fix this so that MySQL would return a more descriptive error. Is it enough to return a warning and truncate the index? In my opinion, no. An error is better noticed by the user. Regards, Heikki
[16 Sep 2005 11:13]
Osku Salerma
I've tested things a bit. Normal indexes in InnoDB have a maximum length of 1024 bytes, not 767. Prefix indexes have a maximum length of 767, not 768. At least these manual pages talk about a limit of 1000 for InnoDB which is wrong: http://dev.mysql.com/doc/mysql/en/indexes.html http://dev.mysql.com/doc/mysql/en/create-table.html
[19 Sep 2005 8:16]
Osku Salerma
The actual bug in this case is that index lengths 768-1024 are accepted at all, which has been split into its own bug #13315. A better error message could surely be used, but the same could be said for almost every error, and the exact mechanism by which detailed error messages can be created is still being hashed out (see bug #3443). So I'm closing this specific bug.
Description: InnoDB causes an error when a long sub-part index is created, although MyISAM doesn't cause any error but a warning. How to repeat: -- sample script create table test_i1ok (col1 varchar(2000), index (col1(767))) character set = latin1 engine = innodb; create table test_i1ng (col1 varchar(2000), index (col1(768))) character set = latin1 engine = innodb; create table test_i2ok (col1 varchar(2000), index (col1(383))) character set = cp932 engine = innodb; create table test_i2ng (col1 varchar(2000), index (col1(384))) character set = cp932 engine = innodb; create table test_i3ok (col1 varchar(2000), index (col1(255))) character set = utf8 engine = innodb; create table test_i3ng (col1 varchar(2000), index (col1(256))) character set = utf8 engine = innodb; create table test_m1ok (col1 varchar(2000), index (col1(1000))) character set = latin1 engine = myisam; create table test_m1ng (col1 varchar(2000), index (col1(1001))) character set = latin1 engine = myisam; create table test_m2ok (col1 varchar(2000), index (col1(500))) character set = cp932 engine = myisam; create table test_m2ng (col1 varchar(2000), index (col1(501))) character set = cp932 engine = myisam; create table test_m3ok (col1 varchar(2000), index (col1(333))) character set = utf8 engine = myisam; create table test_m3ng (col1 varchar(2000), index (col1(334))) character set = utf8 engine = myisam; -- error in InnoDB root:mi> create table test_i1ng (col1 varchar(2000), index (col1(768))) -> character set = latin1 engine = innodb; ERROR 1005 (HY000): Can't create table './mi/test_i1ng.frm' (errno: 139) -- warning in MyISAM root:mi> create table test_m1ng (col1 varchar(2000), index (col1(1001))) -> character set = latin1 engine = myisam; Query OK, 0 rows affected, 1 warning (0.00 sec) root:mi> show index from test_m1ng\G *************************** 1. row *************************** Table: test_m1ng Non_unique: 1 Key_name: col1 Seq_in_index: 1 Column_name: col1 Collation: A Cardinality: NULL Sub_part: 1000 *** the length of index is truncated *** Packed: NULL Null: YES Index_type: BTREE Comment: 1 row in set (0.00 sec) Suggested fix: Like MyISAM, InnoDB should truncate length of index and create one.