MySQL Bugs: #14056: Column prefix index on UTF-8 primary key column causes 'Can't find record..'

Bug #14056	Column prefix index on UTF-8 primary key column causes 'Can't find record..'
Submitted:	16 Oct 2005 9:08	Modified:	18 Jun 2010 23:14
Reporter:	Heikki Tuuri	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.1, 5.0	OS:	Any (All)
Assigned to:		CPU Architecture:	Any

Description:
Jan, 

work together with Sergey Petrunia to fix this bug.

ha_innobase::store_key_val_for_row() has forgotten UTF-8 column prefix indexes.

See Sergey's patch in:
http://bugs.mysql.com/bug.php?id=11704

Also: grep all places in InnoDB where column prefix indexes are handled, and submit a report to me where you explain why UTF-8 columns work ok there.

Regards,

Heikki

create table t2 (
  a int, b char(10), filler char(10), primary key(a, b(2)) 
) character set utf8 engine = innodb;

insert into t2 values (1,'abcdefg','one');
insert into t2 values (2,'ijkilmn','two');
insert into t2 values (3, 'qrstuvw','three');
update t2 set a=5, filler='booo' where a=1;
drop table t2;

mysql> create table t2 (
    ->   a int, b char(10), filler char(10), primary key(a, b(2))
    -> ) character set utf8 engine = innodb;
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> insert into t2 values (1,'abcdefg','one');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t2 values (2,'ijkilmn','two');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t2 values (3, 'qrstuvw','three');
Query OK, 1 row affected (0.00 sec)

mysql> update t2 set a=5, filler='booo' where a=1;
ERROR 1032 (HY000): Can't find record in 't2'
mysql> drop table t2;
Query OK, 0 rows affected (0.01 sec)

How to repeat:
See above.

Suggested fix:
Use function:

/**********************************************************************
This function is used to find the storage length in bytes of the first n
characters for prefix indexes using a multibyte character set. The function
finds charset information and returns length of prefix_len characters in the
index field in bytes.

NOTE: the prototype of this function is copied to data0type.c! If you change
this function, you MUST change also data0type.c! */

ulint
innobase_get_at_most_n_mbchars(
/*===========================*/
                                /* out: number of bytes occupied by the first
                                n characters */
        ulint charset_id,       /* in: character set id */
        ulint prefix_len,       /* in: prefix length in bytes of the index
                                (this has to be divided by mbmaxlen to get the
                                number of CHARACTERS n in the prefix) */
        ulint data_len,         /* in: length of the string in bytes */
        const char* str)        /* in: character string */

Similar bug when using varchar fields:

mysql> create table t3(a int not null, b varchar(10) not null, filler varchar(10), primary key (a, b(2))) engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t3 values (1,'abcdefg','one');
Query OK, 1 row affected (0.52 sec)

mysql> insert into t3 values (3, 'qrstuvw','three');
Query OK, 1 row affected (1.03 sec)

mysql> insert into t3 values (2,'ijkilmn','two');
Query OK, 1 row affected (0.14 sec)

mysql> select * from t3;
+---+---------+--------+
| a | b       | filler |
+---+---------+--------+
| 1 | abcdefg | one    |
| 2 | ijkilmn | two    |
| 3 | qrstuvw | three  |
+---+---------+--------+
3 rows in set (0.00 sec)

mysql> update t3 set a=5, filler='booo' where a=1;
ERROR 1032 (HY000): Can't find record in 't3'

This bug is also in ha_innobase::store_key_val_for_row() function.

Blobs seem to work correctly:

mysql> create table t4(a int not null, b blob not null, c char(10), primary key (a, b(2))) engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t4 values (1,'abcdefg','one');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t4 values (2,'ijkilmn','two');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t4 values (3, 'qrstuvw','three');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t4;
+---+---------+-------+
| a | b       | c     |
+---+---------+-------+
| 1 | abcdefg | one   |
| 2 | ijkilmn | two   |
| 3 | qrstuvw | three |
+---+---------+-------+
3 rows in set (0.00 sec)

mysql> update t4 set a=5, c='booo' where a=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t4;
+---+---------+-------+
| a | b       | c     |
+---+---------+-------+
| 2 | ijkilmn | two   |
| 3 | qrstuvw | three |
| 5 | abcdefg | booo  |
+---+---------+-------+
3 rows in set (0.00 sec)

All test were done with 5.0.15.

Regards
    Jan

Jan,

BLOB works correctly because it is a binary string and is not UTF-8 data.

TEXT should fail.

Regards,

Heikki

TEXT does fail:

mysql> create table t33(a int not null, b text(10) not null, filler varchar(10), primary key (a, b(2))) engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.18 sec)

mysql> insert into t33 values (1,'abcdefg','one'),(3, 'qrstuvw','three'),(2,'ijkilmn','two');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> update t33 set a=5, filler='booo' where a=1;
ERROR 1032 (HY000): Can't find record in 't33'

This bug is very similar to VARCHAR case.

Regards,
    Jan

Fixed on 5.0.15.

Noted in 5.0.15 changelog.

Jan, Elliot, Paul,

the fix is NOT in 5.0.15, since it was released before the bug was fixed!

Please find out in which version the fix is, and correct the documentation.

Regards,

Heikki

Ok, I found out that the fix is in 5.0.16, not in 5.0.15.
Regards,
Heikki

Jan,

the bug should be fixed also in 4.1.

Regards,

Heikki

Fix for 4.1 proposed.

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/1102

Fixed in 4.1.17

Moved report to 5.0.16, added 4.1.17 entry.

Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)