| Bug #17530 | Bug in non-UNIQUE index column truncation | ||
|---|---|---|---|
| Submitted: | 17 Feb 2006 14:50 | Modified: | 27 Feb 2006 20:53 |
| Reporter: | SINISA MILIVOJEVIC | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.0.18 | OS: | Any (Any) |
| Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
[21 Feb 2006 15:09]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/2979
[24 Feb 2006 13:02]
Evgeny Potemkin
When a too long field is used for a key, only a prefix part of the field is used. Length is reduced to the max key length allowed for storage. But if the field have a multibyte charset it is possible to break multibyte char sequence. This leads to the failed assertion in the innodb code and server crash when a record is inserted. Fixed in 5.0.19, cset 1.2063.1.3
[25 Feb 2006 19:17]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/3143
[25 Feb 2006 22:09]
Trudy Pelzer
Patch also added to 5.1.7.
[27 Feb 2006 20:53]
Mike Hillyer
Documented in 5.0.19 and 5.1.7 changelogs:
<listitem>
<para>
Data truncations on non-UNIQUE indexes could crash InnoDB when
using multi-byte character sets. (Bug #17530)
</para>
</listitem>
Description: This is a crashing bug. Crash happens due to the assertion in InnoDB storage engine. The assertion, however, is caused due to the silent specification change. Since 5.0.18, SQL layer truncates a long column in a non-UNIQUE index, causing the assertion. The truncation should be made at a multiple of mbmaxlen for the character set (3 for UTF-8). However, that is not how things function since 5.0.18. If SQL layer is the one that does the truncation, the first insert crashes. If truncation is done in the CREATE statement, no problems. A workaround is that if you need to declare an index on a long column, it is best to explicitly declare it on just 255 UTF-8 characters. Otherwise, you will get a crash. How to repeat: mysql> CREATE TABLE t(a VARCHAR(800) BINARY NOT NULL, KEY(a)) -> ENGINE = INNODB -> CHARACTER SET utf8 COLLATE utf8_general_ci; Query OK, 0 rows affected, 1 warning (0.03 sec) mysql> show create table t; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------+ | t | CREATE TABLE `t` ( `a` varchar(800) character set utf8 collate utf8_bin NOT NULL, KEY `a` (`a`(255)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.03 sec) mysql> insert into t values ('kjhjk'); ERROR 2013 (HY000): Lost connection to MySQL server during query mysql> exit Bye heikki@127:~/mysql-5.0.18/client$ ./mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.18-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> drop table t; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE `t` ( -> `a` varchar(800) character set utf8 collate utf8_bin NOT NULL, -> KEY `a` (`a`(255)) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.05 sec) mysql> insert into t values ('abc'); Query OK, 1 row affected (0.03 sec) mysql> ha_innodb.cc: if (DATA_BLOB == col_type || (key_part->length < field->pack_length() && field->type() != MYSQL_TYPE_VARCHAR) || (field->type() == MYSQL_TYPE_VARCHAR && key_part->length < field->pack_length() - ((Field_varstring*)field)->length_bytes)) { prefix_len = key_part->length; if (col_type == DATA_INT || col_type == DATA_FLOAT || col_type == DATA_DOUBLE || col_type == DATA_DECIMAL) { sql_print_error("MySQL is trying to create a column " "prefix index field, on an " "inappropriate data type. Table " "name %s, column name %s.", table_name, key_part->field->field_name); prefix_len = 0; } } else { prefix_len = 0; } Suggested fix: No fix, only workaround to specify shorter indices in create table itself.