Bug #51431 | Wrong sort order after import of dump file | ||
---|---|---|---|
Submitted: | 23 Feb 2010 17:49 | Modified: | 14 Oct 2010 14:12 |
Reporter: | Johannes Hammoud | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.1.44 | OS: | Any |
Assigned to: | Sergei Glukhov | CPU Architecture: | Any |
Tags: | DESC, dump, import, order, regression, sort, wrong |
[23 Feb 2010 17:49]
Johannes Hammoud
[23 Feb 2010 17:50]
Johannes Hammoud
Dump with single table to reproduce the bug
Attachment: TEST.dump (application/octet-stream, text), 2.87 KiB.
[23 Feb 2010 18:55]
MySQL Verification Team
Thank you for the bug report. C:\DBS>c:\dbs\5.1\bin\mysql -uroot --port=3306 --prompt="mysql 5.1 >" Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.1.45-Win X64-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.1 >use testdb_local Database changed mysql 5.1 >select * from TEST -> where F_ID = 1 and T_STATUS = true -> order by T_ID desc -> limit 5; +------+------+----------+----------+ | T_ID | F_ID | T_NUMBER | T_STATUS | +------+------+----------+----------+ | 1 | 1 | 991 | 1 | | 2 | 1 | 992 | 1 | | 3 | 1 | 993 | 1 | | 4 | 1 | 994 | 1 | | 5 | 1 | 995 | 1 | +------+------+----------+----------+ 5 rows in set (0.00 sec) mysql 5.1 >exit Bye C:\DBS>50 C:\DBS>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > " Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.91-Win X64-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.0 > create database TestDB_local; Query OK, 1 row affected (0.00 sec) mysql 5.0 > use testdb_local Database changed mysql 5.0 > source c:/bugs/test.dump Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) <cut> Query OK, 0 rows affected (0.00 sec) mysql 5.0 > select * from TEST -> where F_ID = 1 and T_STATUS = true -> order by T_ID desc -> limit 5; +------+------+----------+----------+ | T_ID | F_ID | T_NUMBER | T_STATUS | +------+------+----------+----------+ | 27 | 1 | 9922 | 1 | | 26 | 1 | 9921 | 1 | | 25 | 1 | 9920 | 1 | | 24 | 1 | 9919 | 1 | | 23 | 1 | 9918 | 1 | +------+------+----------+----------+ 5 rows in set (0.01 sec) mysql 5.0 > C:\bugs\mysql-5.1.23a-maria-alpha-winx64>bin\mysql -uroot --port=3321 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.23a-maria-alpha-community-maria MySQL Community Server [Maria] (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create database TestDB_local; Query OK, 1 row affected (0.00 sec) mysql> use testdb_local Database changed mysql> source c:/bugs/test.dump Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> select * from TEST -> where F_ID = 1 and T_STATUS = true -> order by T_ID desc -> limit 5; +------+------+----------+----------+ | T_ID | F_ID | T_NUMBER | T_STATUS | +------+------+----------+----------+ | 27 | 1 | 9922 | 1 | | 26 | 1 | 9921 | 1 | | 25 | 1 | 9920 | 1 | | 24 | 1 | 9919 | 1 | | 23 | 1 | 9918 | 1 | +------+------+----------+----------+ 5 rows in set (0.00 sec) mysql>
[4 May 2010 7:03]
Pete Yandell
I ran the above test on 5.1.38 (on OS X 10.6), and saw the same problem.
[5 May 2010 4:41]
Pete Yandell
We're seeing this issue intermittently when running the tests that back the envato.com marketplace sites (about 40 millions hits per month.) The table being affected is: CREATE TABLE `lines` ( `id` int(11) NOT NULL AUTO_INCREMENT, `txn_id` varchar(255) DEFAULT NULL, `account` varchar(255) DEFAULT NULL, `scope` varchar(255) DEFAULT NULL, `code` varchar(255) DEFAULT NULL, `amount` int(11) DEFAULT NULL, `balance` int(11) DEFAULT NULL, `partner_id` int(11) DEFAULT NULL, `partner_account` varchar(255) DEFAULT NULL, `partner_scope` varchar(255) DEFAULT NULL, `meta` varchar(255) DEFAULT NULL, `detail_id` int(11) DEFAULT NULL, `detail_type` varchar(255) DEFAULT NULL, `created_at` datetime DEFAULT NULL, `updated_at` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `lines_account_idx` (`account`), KEY `lines_scope_account_idx` (`scope`,`account`), KEY `lines_account_code_created_at_idx` (`account`,`code`,`created_at`), KEY `lines_account_id_idx` (`account`,`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 The following query is the one that's giving us problems: SELECT * FROM `lines` WHERE (account = 'earnings' AND scope = '602') ORDER BY id DESC; Whether the query sorts correctly depends on which index the query optimiser decides to use. After a query returning incorrectly sorted results, an EXPLAIN on the query gives: +----+-------------+-------+-------+--------------------------------------------------------------------------------------------------+-------------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+--------------------------------------------------------------------------------------------------+-------------------+---------+------+------+-------------+ | 1 | SIMPLE | lines | range | lines_account_code_created_at_idx,lines_account_id_idx,lines_account_idx,lines_scope_account_idx | lines_account_idx | 768 | NULL | 1 | Using where | +----+-------------+-------+-------+--------------------------------------------------------------------------------------------------+-------------------+---------+------+------+-------------+ If I then do an ANALYZE TABLE `lines`, the query will start returning correctly sorted results, and EXPLAIN now gives: EXPLAIN SELECT * FROM `lines` WHERE (account = 'earnings' AND scope = '602') ORDER BY id DESC; +----+-------------+-------+------+--------------------------------------------------------------------------------------------------+-----------------------------------+---------+-------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+--------------------------------------------------------------------------------------------------+-----------------------------------+---------+-------+------+-----------------------------+ | 1 | SIMPLE | lines | ref | lines_account_code_created_at_idx,lines_account_id_idx,lines_account_idx,lines_scope_account_idx | lines_account_code_created_at_idx | 768 | const | 1 | Using where; Using filesort | +----+-------------+-------+------+--------------------------------------------------------------------------------------------------+-----------------------------------+---------+-------+------+-----------------------------+ Unfortunately I haven't got a data set that will reproducibly get the table into a state where the query is failing.
[7 Jun 2010 9:48]
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/110323 3405 Sergey Glukhov 2010-06-07 Bug#51431 Wrong sort order after import of dump file The problem is that QUICK_SELECT_DESC behaviour depends on used_key_parts value which can be bigger than selected best_key_parts value if an engine supports clustered key. But used_key_parts is overwritten with best_key_parts value that prevents from correct selection of index access method. The fix is to preserve used_key_parts value for further use in QUICK_SELECT_DESC. @ mysql-test/r/innodb_mysql.result test case @ mysql-test/t/innodb_mysql.test test case @ sql/sql_select.cc preserve used_key_parts value for further use in QUICK_SELECT_DESC.
[10 Jun 2010 8: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/110691 3422 Sergey Glukhov 2010-06-10 Bug#51431 Wrong sort order after import of dump file The problem is that QUICK_SELECT_DESC behaviour depends on used_key_parts value which can be bigger than selected best_key_parts value if an engine supports clustered key. But used_key_parts is overwritten with best_key_parts value that prevents from correct selection of index access method. The fix is to preserve used_key_parts value for further use in QUICK_SELECT_DESC. @ mysql-test/r/innodb_mysql.result test case @ mysql-test/t/innodb_mysql.test test case @ sql/sql_select.cc preserve used_key_parts value for further use in QUICK_SELECT_DESC.
[30 Jun 2010 6:19]
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/112515 3453 Sergey Glukhov 2010-06-30 Bug#51431 Wrong sort order after import of dump file The problem is that QUICK_SELECT_DESC behaviour depends on used_key_parts value which can be bigger than selected best_key_parts value if an engine supports clustered key. But used_key_parts is overwritten with best_key_parts value that prevents from correct selection of index access method. The fix is to preserve used_key_parts value for further use in QUICK_SELECT_DESC. @ mysql-test/r/innodb_mysql.result test case @ mysql-test/t/innodb_mysql.test test case @ sql/sql_select.cc preserve used_key_parts value for further use in QUICK_SELECT_DESC
[30 Jun 2010 13:03]
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/112578 3454 Sergey Glukhov 2010-06-30 Bug#51431 Wrong sort order after import of dump file The problem is that QUICK_SELECT_DESC behaviour depends on used_key_parts value which can be bigger than selected best_key_parts value if an engine supports clustered key. But used_key_parts is overwritten with best_key_parts value that prevents from correct selection of index access method. The fix is to preserve used_key_parts value for further use in QUICK_SELECT_DESC. @ mysql-test/r/innodb_mysql.result test case @ mysql-test/t/innodb_mysql.test test case @ sql/sql_select.cc preserve used_key_parts value for further use in QUICK_SELECT_DESC
[19 Jul 2010 14:35]
Bugs System
Pushed into 5.1.49 (revid:build@mysql.com-20100719143034-omcma40sblwmay3x) (version source revid:sergey.glukhov@sun.com-20100630130625-9p7mu3m3x8a04idz) (merge vers: 5.1.48) (pib:16)
[20 Jul 2010 2:42]
Paul DuBois
Noted in 5.1.49 changelog. Some ORDER BY ... DESC queries sorted incorrectly. Setting report to Need Merge pending further pushes.
[23 Jul 2010 12:26]
Bugs System
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100723121820-jryu2fuw3pc53q9w) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)
[23 Jul 2010 12:33]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)
[31 Jul 2010 16:45]
Paul DuBois
Noted in 5.5.6 changelog.
[14 Oct 2010 8:33]
Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:21)
[14 Oct 2010 8:49]
Bugs System
Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:21)
[14 Oct 2010 9:03]
Bugs System
Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:21)
[14 Oct 2010 14:12]
Jon Stephens
Already documented in the 5.1.49 changelog; no additional changelog entries required. Set back to Closed state.