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

[16 Oct 2005 9:08] Heikki Tuuri
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 */
[17 Oct 2005 7:19] Jan Lindström
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.
[17 Oct 2005 7:24] Jan Lindström
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
[17 Oct 2005 7:55] Heikki Tuuri
Jan,

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

TEXT should fail.

Regards,

Heikki
[17 Oct 2005 11:00] Jan Lindström
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
[8 Nov 2005 11:08] Jan Lindström
Fixed on 5.0.15.
[9 Nov 2005 3:28] Paul DuBois
Noted in 5.0.15 changelog.
[29 Nov 2005 16:29] Heikki Tuuri
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
[29 Nov 2005 16:44] Heikki Tuuri
Ok, I found out that the fix is in 5.0.16, not in 5.0.15.
Regards,
Heikki
[30 Nov 2005 11:20] Heikki Tuuri
Jan,

the bug should be fixed also in 4.1.

Regards,

Heikki
[1 Dec 2005 11:57] Jan Lindström
Fix for 4.1 proposed.
[15 Jan 2006 12:35] 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/1102
[17 Jan 2006 16:14] Alexander Ivanov
Fixed in 4.1.17
[17 Jan 2006 23:40] Mike Hillyer
Moved report to 5.0.16, added 4.1.17 entry.
[5 May 2010 15:10] Bugs System
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)
[6 May 2010 1:40] Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 6:01] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:30] Bugs System
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)
[28 May 2010 6:57] Bugs System
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)
[29 May 2010 15:27] Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[17 Jun 2010 12:04] Bugs System
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)
[17 Jun 2010 12:48] Bugs System
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)
[17 Jun 2010 13:31] Bugs System
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)