Bug #19080 Sub_part indexes on utf8 varchar fields are broken for UPDATE
Submitted: 13 Apr 2006 17:52 Modified: 8 May 2006 19:17
Reporter: Gian-Carlo Pascutto Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:4.1.18-0/4.1BK OS:Linux (Red Hat Enterprise 3 (x86))
Assigned to: Georgi Kodinov CPU Architecture:Any

[13 Apr 2006 17:52] Gian-Carlo Pascutto
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.
[13 Apr 2006 20:47] Miguel Solorzano
Thank you for the bug report. Could you please provide a dump
file for insert data?

Thanks in advance.
[13 Apr 2006 22:06] Gian-Carlo Pascutto
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
[14 Apr 2006 21:20] Gian-Carlo Pascutto
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)
[17 Apr 2006 14:37] Miguel Solorzano
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.
[17 Apr 2006 14:48] Gian-Carlo Pascutto
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).
[17 Apr 2006 15:08] Miguel Solorzano
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>
[26 Apr 2006 11:34] 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/5562
[27 Apr 2006 7:22] 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/5624
[4 May 2006 16:36] Paul Dubois
Please provide the three-part version number(s) for the fix.
Thanks.
[5 May 2006 9:48] Georgi Kodinov
The fix is pushed in 4.1.19 and 5.0.22
[8 May 2006 19:17] Paul Dubois
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)