| 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: | |
| 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 20:47]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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)
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.