| Bug #37439 | Incorrect output order from ORDER BY using 2 field index in MEMORY BTREE | ||
|---|---|---|---|
| Submitted: | 17 Jun 2008 2:00 | Modified: | 18 Jul 2008 16:10 |
| Reporter: | John Nagle | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.0.27-community-nt | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | enum, order | ||
[17 Jun 2008 3:23]
Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with a newer version, 5.0.51, and inform about the results.
[17 Jun 2008 4:06]
John Nagle
I've been able to reproduce the problem on MySQL "5.0.27" running on Red Hat Linux Fedora Core 5. This is a standard server configuration running in a server farm. The original bug was observed on "5.0.27-community-nt" on Windows 2000 running as a desktop system. So it's a cross-platform problem.
[18 Jun 2008 16:55]
John Nagle
Someone else was unable to reproduce the bug. I've been running with ALTER DATABASE testdb DEFAULT CHARACTER SET utf8; to force all text fields to UTF8, but I didn't mention that in the bug report. That changes record size, and might be significant. I'm asking some other MySQL users I know to run tests to try to reproduce this. (It may have been causing problems in our production system. Tests with small tables work fine, but we'd been seeing problems under heavy load. This MEMORY table is a work queue for a busy system, and returning records out of order can stall out the work queue. I can now force the error repeatedly on a desktop system, so we'll get it eventually.)
[18 Jun 2008 19:18]
John Nagle
Reproduced at another site on MySQL 5.0.45 on Linux 2.6.23.15-80.fc7, with
this change to make sure that the main text field is UTF8. (The original
bug report didn't have that, but the database and server were set up
for default UTF8.) So we're accumulating info that this is cross-platform
and cross-version.
CREATE TABLE ratingqueue
(
domain VARCHAR(255) CHARACTER SET utf8 NOT NULL UNIQUE PRIMARY KEY,
requestor_ip_hash INT,
rating_state ENUM ('running','starting','queued','hold')
NOT NULL DEFAULT 'queued',
server VARCHAR(63) NULL,
priority SMALLINT DEFAULT 0,
update_timestamp TIMESTAMP NOT NULL,
request_timestamp TIMESTAMP NOT NULL,
INDEX(requestor_ip_hash),
INDEX USING BTREE(rating_state, request_timestamp)
) ENGINE=MEMORY;
[22 Jun 2008 17:26]
John Nagle
We've been able to demonstrate this defect on three different operating systems (Windows 2000, Linux Fedora Core 5 and Linux Fedora Core 7) and two different versions of MySQL (5.0.27 and 5.0.45). However, it's hard to reproduce; the number of rows matters, and some other attempts to reproduce it (on FreeBSD, for example) have failed. To work on the problem, it's necessary to find a system for which it FAILS and work from there. It's easy to find systems on which the test case appears to work, but that doesn't mean the problem is solved. Debugging on a system on which the test does not fail is useless. It's failing on widely-deployed mainstream configurations, so there are probably operational systems out there getting wrong results from MySQL, but not noticing.
[17 Jul 2008 19:13]
Valeriy Kravchuk
Had you been able to repeat this on any machine with 5.0.51 or newer version?
[18 Jul 2008 2:48]
John Nagle
> Had you been able to repeat this on any machine with 5.0.51 or newer version? It's broken on MySQL as late as 5.0.45. What specific fix between 5.0.45 and 5.0.51 would have fixed the problem? Bear in mind that it's hard to get this to fail; it's sensitive to factors which include, at least, the number of records in the table, as documented above. But since we've seen it has failed on at least three rather different systems, it's real. We suggest you try to reproduce it on 5.0.45 and get a solid, repeatable failure to analyze. Thanks.
[18 Jul 2008 12:18]
Susanne Ebrecht
Many thanks for reporting a bug. I can't repeat this by using newest version of MySQL. Please also read: http://dev.mysql.com/doc/refman/5.0/en/enum.html
[18 Jul 2008 16:10]
John Nagle
It's difficult to repeat this bug and it may not fail on some MySQL configurations, although it's failed on three different systems and versions so far. That's enough to establish that it's real. We'll give the bug more publicity and ask others to try to repeat it. Status: vendor in denial.
Description: This is a situation in which a MEMORY engine table with BTREE indices on two fields, the first field being an ENUM, is sorted incorrectly. Key fields in the table definition are: rating_state ENUM ('running','starting','queued','hold') NOT NULL DEFAULT 'queued', request_timestamp TIMESTAMP NOT NULL, ... INDEX USING BTREE(rating_state, request_timestamp) ... ENGINE=MEMORY; When the request SELECT * FROM ratingqueue ORDER BY rating_state; is made, the returned values are not in order by rating state. The query is using the BTREE index. The entries appear to be in order by request_timestamp; it's as if the first field was ignored in building the two-field index. Without the BTREE index, EXPLAIN shows a filesort and the results are correct. This can be reproduced with a Python program, the MySQL query browser, and the command-line client, so it's not a client-side or connector problem. The number of records involved seems to matter. We've seen it fail with > 1000 records and with 100 records, but succeed at 50 and 25. So a 100-record test case is attached. Possibly related to Bug #7516 and/or #967, which reported other hard to reproduce problems related to ENUMs. How to repeat: CREATE TABLE ratingqueue ( domain VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY, requestor_ip_hash INT, rating_state ENUM ('running','starting','queued','hold') NOT NULL DEFAULT 'queued', server VARCHAR(63) NULL, priority SMALLINT DEFAULT 0, update_timestamp TIMESTAMP NOT NULL, request_timestamp TIMESTAMP NOT NULL, INDEX(requestor_ip_hash), INDEX USING BTREE(rating_state, request_timestamp) ) ENGINE=MEMORY; LOAD DATA INFILE 'testdata100' INTO TABLE ratingqueue; -- Data set "testdata100" START msn.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 google.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 youtube.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 baidu.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 orkut.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 qq.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 wikipedia.org 651586 queued \N 0 2008-06-16 12:33:27 2008-06-16 12:33:27 yahoo.co.jp 651586 queued \N 0 2008-06-16 12:33:25 2008-06-16 12:33:25 microsoft.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 sina.com.cn 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 megaupload.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 blogger.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 ebay.com 651586 queued \N 0 2008-06-16 12:33:25 2008-06-16 12:33:25 amazon.co.uk 651586 hold \N 0 2008-06-16 12:33:19 2008-06-16 12:33:19 rapidshare.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 facebook.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 sohu.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 fotolog.net 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 friendster.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 mail.ru 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 google.de 651586 queued \N 0 2008-06-16 12:33:26 2008-06-16 12:33:26 passport.net 651586 queued \N 0 2008-06-16 12:33:24 2008-06-16 12:33:24 google.com.br 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 google.fr 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 amazon.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 yahoo.com.cn 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 bbc.co.uk 651586 queued \N 0 2008-06-16 12:33:25 2008-06-16 12:33:25 www.samoandomains.ws 651586 hold \N 0 2008-06-16 12:33:22 2008-06-16 12:33:22 google.co.jp 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 google.es 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 wretch.cc 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 uol.com.br 651586 queued \N 0 2008-06-16 12:33:24 2008-06-16 12:33:24 yandex.ru 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 skyblog.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 google.pl 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 craigslist.org 651586 queued \N 0 2008-06-16 12:33:26 2008-06-16 12:33:26 google.cl 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 flickr.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 tom.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 google.cn 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 photobucket.com 651586 queued \N 0 2008-06-16 12:33:25 2008-06-16 12:33:25 onet.pl 651586 queued \N 0 2008-06-16 12:33:22 2008-06-16 12:33:22 imageshack.us 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 google.com.mx 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 fc2.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 aol.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 ebay.co.uk 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 mixi.jp 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 xunlei.com 651586 queued \N 0 2008-06-16 12:33:26 2008-06-16 12:33:26 rambler.ru 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 cnn.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 google.it 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 allegro.pl 651586 queued \N 0 2008-06-16 12:33:26 2008-06-16 12:33:26 google.co.in 651586 queued \N 0 2008-06-16 12:33:24 2008-06-16 12:33:24 ebay.de 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 google.com.ar 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 google.com.pe 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 terra.com.br 651586 running animats-5px372h 0 2008-06-16 12:33:24 2008-06-16 12:33:18 livejournal.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 seznam.cz 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 xanga.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 geocities.com 651586 running animats-5px372h 0 2008-06-16 12:33:27 2008-06-16 12:33:18 imagevenue.com 651586 queued \N 0 2008-06-16 12:33:19 2008-06-16 12:33:19 adultfriendfinder.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 dailymotion.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 uwants.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 rakuten.co.jp 651586 queued \N 0 2008-06-16 12:33:25 2008-06-16 12:33:25 googlesyndication.com 651586 queued \N 0 2008-06-16 12:33:23 2008-06-16 12:33:23 wp.pl 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 discuss.com.hk 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 adobe.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 bebo.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 livedoor.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 google.co.ve 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 digg.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 www.safenames.net 651586 hold \N 0 2008-06-16 12:33:22 2008-06-16 12:33:22 apple.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 soso.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 vnet.cn 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 tagged.com 651586 queued \N 0 2008-06-16 12:33:25 2008-06-16 12:33:25 wordpress.com 651586 queued \N 0 2008-06-16 12:33:24 2008-06-16 12:33:24 one.lt 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 badongo.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 deviantart.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 sendspace.com 651586 queued \N 0 2008-06-16 12:33:24 2008-06-16 12:33:24 google.co.il 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 walla.co.il 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 alibaba.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 google.com.au 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 vnexpress.net 651586 queued \N 0 2008-06-16 12:33:22 2008-06-16 12:33:22 about.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 pchome.com.tw 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 ig.com.br 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 comcast.net 651586 queued \N 0 2008-06-16 12:33:25 2008-06-16 12:33:25 sourceforge.net 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 rapidshare.de 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 kooora.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 google.nl 651586 queued \N 0 2008-06-16 12:33:24 2008-06-16 12:33:24 statcounter.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 goo.ne.jp 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18 -- Data set "testdata100" END