Bug #19080 Sub_part indexes on utf8 varchar fields are broken for UPDATE

Description:
It seems that in some circumstances using sub_part indexes will make MySQL unable to find records in UPDATE queries. SELECT doesn't seem to be affected. We are using all-UTF8 tables, I don't know how that affects things.

One workaround is not to use sub_part indexes, but this is not a viable option performancewise for large UTF-8 fields.

This appears to work correctly in MySQL 5.0.19, but we cannot upgrade production to the 5.0 series because we are dependant on some other software not compatible with that :(

How to repeat:
-- 
-- Table structure for table `users`
-- 

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(32) collate utf8_unicode_ci NOT NULL default '',
  `password` varchar(32) collate utf8_unicode_ci NOT NULL default '',
  `sha256_password` varchar(64) collate utf8_unicode_ci NOT NULL default '',
  `email` varchar(128) collate utf8_unicode_ci NOT NULL default '',
  `verified` tinyint(3) unsigned NOT NULL default '0',
  `validation_id` varchar(32) collate utf8_unicode_ci NOT NULL default '',
  `pubstats` tinyint(3) unsigned NOT NULL default '0',
  `lastsub` datetime NOT NULL default '1500-01-01 00:00:00',
  `regtime` datetime NOT NULL default '1500-01-01 00:00:00',
  `playcount` int(10) unsigned NOT NULL default '0',
  `timecount` bigint(20) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=809 ;

Add some data...then try:

mysql> CREATE INDEX validation_id ON users(validation_id(10));
Query OK, 789 rows affected (2.18 sec)
Records: 789  Duplicates: 0  Warnings: 0

mysql> show indexes from users;
+-------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name      | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| users |          0 | PRIMARY       |            1 | id            | A         |         789 |     NULL | NULL   |      | BTREE      |         |
| users |          1 | name          |            1 | name          | A         |         789 |     NULL | NULL   |      | BTREE      |         |
| users |          1 | validation_id |            1 | validation_id | A         |           4 |       10 | NULL   |      | BTREE      |         |
+-------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)

mysql> UPDATE users SET verified=1 WHERE validation_id='3748bf1f5bc031fb777fb6bc8476ea5a';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0
              ^
              I
            "oops"

mysql> DROP INDEX validation_id ON users;
Query OK, 789 rows affected (0.11 sec)
Records: 789  Duplicates: 0  Warnings: 0

mysql> UPDATE users SET verified=1 WHERE validation_id='3748bf1f5bc031fb777fb6bc8476ea5a';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0
              ^
              I
          "now it's back"

mysql> CREATE INDEX validation_id ON users(validation_id);
Query OK, 789 rows affected (0.05 sec)
Records: 789  Duplicates: 0  Warnings: 0

mysql> UPDATE users SET verified=1 WHERE validation_id='3748bf1f5bc031fb777fb6bc8476ea5a';
Query OK, 0 rows affected (0.02 sec)
Rows matched: 1  Changed: 0  Warnings: 0

You can try the same with SELECT, and then it will work correctly.

Thank you for the bug report. Could you please provide a dump
file for insert data?

Thanks in advance.

I don't know if it's of any help, but the following may be relevant:

http://bugs.mysql.com/bug.php?id=14056
http://bugs.mysql.com/bug.php?id=4521

Some more, similar breakage:

songs is also an utf8 table with sub_part indexes

mysql> SELECT COUNT(*) FROM songs WHERE album IN ('S&M (Disc 1)');
+----------+
| COUNT(*) |
+----------+
|       12 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM songs WHERE album IN ('S&M (Disc 1)', 'MySQL ate My query');
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.03 sec)

After dropping the index on albums:

mysql> SELECT COUNT(*) FROM songs WHERE album IN ('S&M (Disc 1)', 'MySQL ate My query');
+----------+
| COUNT(*) |
+----------+
|       12 |
+----------+
1 row in set (0.47 sec)

Thank you for the feedback. With your original test I was unable to repeat
(validation_id='3748bf1f5bc031fb777fb6bc8476ea5a' not exists) then I
tried with another one:

miguel@hegel:~/dbs/4.1> bin/mysql -uroot db19
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 4.1.19-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show create table users\G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(32) collate utf8_unicode_ci NOT NULL default '',
  `password` varchar(32) collate utf8_unicode_ci NOT NULL default '',
  `sha256_password` varchar(64) collate utf8_unicode_ci NOT NULL default '',
  `email` varchar(128) collate utf8_unicode_ci NOT NULL default '',
  `verified` tinyint(3) unsigned NOT NULL default '0',
  `validation_id` varchar(32) collate utf8_unicode_ci NOT NULL default '',
  `pubstats` tinyint(3) unsigned NOT NULL default '0',
  `lastsub` datetime NOT NULL default '1500-01-01 00:00:00',
  `regtime` datetime NOT NULL default '1500-01-01 00:00:00',
  `playcount` int(10) unsigned NOT NULL default '0',
  `timecount` bigint(20) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `name` (`name`),
  KEY `validation_id` (`validation_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

mysql> select id from  users WHERE validation_id='f1861d224d31df52586103f701e1b462';
+----+
| id |
+----+
| 19 |
+----+
1 row in set (0.00 sec)

mysql> UPDATE users SET verified=1 WHERE validation_id='f1861d224d31df52586103f701e1b462';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> SELECT version();
+------------------+
| version()        |
+------------------+
| 4.1.19-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> 

Could you please give me an upadate failed using your dump data
for to test again.

Thanks inadvance.

Perform:

DROP INDEX validation_id ON users;
CREATE INDEX validation_id ON users(validation_id(10));

And try again. The problem does not exist when the index is created on the entire field.
(This is described in the original bug report, BTW).

Thank you for the feedback. How mentioned not affects 5.0.

mysql> DROP INDEX validation_id ON users;
Query OK, 790 rows affected (0.06 sec)
Records: 790  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX validation_id ON users(validation_id(10));
Query OK, 790 rows affected (0.06 sec)
Records: 790  Duplicates: 0  Warnings: 0

mysql> select id from  users WHERE validation_id='f1861d224d31df52586103f701e1b462';
+----+
| id |
+----+
| 19 |
+----+
1 row in set (0.00 sec)

mysql> UPDATE users SET verified=1 WHERE validation_id='f1861d224d31df52586103f701e1b462';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.1.19-debug-log |
+------------------+
1 row in set (0.01 sec)

mysql> 
------------------------------------------------------------------
mysql> DROP INDEX validation_id ON users;
Query OK, 790 rows affected (0.06 sec)
Records: 790  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX validation_id ON users(validation_id(10));
Query OK, 790 rows affected (0.07 sec)
Records: 790  Duplicates: 0  Warnings: 0

mysql> select id from  users WHERE validation_id='f1861d224d31df52586103f701e1b462';
+----+
| id |
+----+
| 19 | 
+----+
1 row in set (0.02 sec)

mysql> UPDATE users SET verified=1 WHERE validation_id='f1861d224d31df52586103f701e1b462';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.21-debug | 
+--------------+
1 row in set (0.00 sec)

mysql>

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

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

Please provide the three-part version number(s) for the fix.
Thanks.

The fix is pushed in 4.1.19 and 5.0.22

Noted in 4.1.19, 5.0.22 changelogs.

Index prefixes for <literal>utf8</literal>
<literal>VARCHAR</literal> columns did not work for
<literal>UPDATE</literal> statements. (Bug #19080)