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:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.18 OS:Any (Any)
Assigned to: Evgeny Potemkin

[17 Feb 2006 14:50] Sinisa Milivojevic
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.
[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>