Bug #59937 select query with order by clause repeats data and repair table says status ok
Submitted: 4 Feb 2011 9:20 Modified: 4 Feb 2011 13:22
Reporter: Vito Impagliazzo Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.1.41 OS:Linux (ubuntu maverick)
Assigned to: CPU Architecture:Any
Tags: select sort repair

[4 Feb 2011 9:20] Vito Impagliazzo 
Description:
When selecting from a table defined as CREATE TABLE `EUR_USD` (
  `quoteId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `time` datetime DEFAULT NULL,
  `bid` double DEFAULT NULL,
  `ask` double DEFAULT NULL,
  `sourceId` int(10) unsigned NOT NULL DEFAULT '0',
  `importId` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`quoteId`),
  KEY `time` (`time`)
) ENGINE=MyISAM AUTO_INCREMENT=92019380 DEFAULT CHARSET=latin1

we get

mysql> select * from EUR_USD where time > "2006-12-29 12:54:20" order by time limit 100;
+----------+---------------------+---------+---------+----------+-----------+
| quoteId | time | bid | ask | sourceId | importId |
+----------+---------------------+---------+---------+----------+-----------+
| 71073676 | 2006-12-29 12:54:22 | 1.31805 | 1.3182 | 1 | 910181153 |
| 71073677 | 2006-12-29 12:54:22 | 1.318 | 1.31815 | 1 | 910181153 |
| 71073674 | 2006-12-29 12:54:23 | 1.31802 | 1.31817 | 1 | 910181153 |
| 71073675 | 2006-12-29 12:54:23 | 1.31803 | 1.31818 | 1 | 910181153 |
| 71073673 | 2006-12-29 12:54:24 | 1.31797 | 1.31812 | 1 | 910181153 |
| 71073672 | 2006-12-29 12:54:27 | 1.31795 | 1.3181 | 1 | 910181153 |
| 71073670 | 2006-12-29 12:54:28 | 1.31795 | 1.3181 | 1 | 910181153 |
| 71073671 | 2006-12-29 12:54:28 | 1.318 | 1.31815 | 1 | 910181153 |
| 71073669 | 2006-12-29 12:54:29 | 1.31797 | 1.31812 | 1 | 910181153 |
| 71073667 | 2006-12-29 12:54:31 | 1.31801 | 1.31816 | 1 | 910181153 |
| 71073668 | 2006-12-29 12:54:31 | 1.318 | 1.31815 | 1 | 910181153 |
| 71073664 | 2006-12-29 12:54:32 | 1.31797 | 1.31812 | 1 | 910181153 |
| 71073665 | 2006-12-29 12:54:32 | 1.318 | 1.31815 | 1 | 910181153 |
| 71073666 | 2006-12-29 12:54:32 | 1.31804 | 1.31819 | 1 | 910181153 |
| 71073661 | 2006-12-29 12:54:35 | 1.31797 | 1.31812 | 1 | 910181153 |
| 71073662 | 2006-12-29 12:54:35 | 1.31801 | 1.31816 | 1 | 910181153 |
| 71073676 | 2006-12-29 12:54:22 | 1.31805 | 1.3182 | 1 | 910181153 |
| 71073677 | 2006-12-29 12:54:22 | 1.318 | 1.31815 | 1 | 910181153 |
| 71073674 | 2006-12-29 12:54:23 | 1.31802 | 1.31817 | 1 | 910181153 |
| 71073675 | 2006-12-29 12:54:23 | 1.31803 | 1.31818 | 1 | 910181153 |
| 71073673 | 2006-12-29 12:54:24 | 1.31797 | 1.31812 | 1 | 910181153 |
| 71073672 | 2006-12-29 12:54:27 | 1.31795 | 1.3181 | 1 | 910181153 |
...
...

as you see data starts to repeat from beginning at row 18. we have tried:

mysqlcheck --auto-repair -uroot tickdb2
...
tickdb2.EUR_USD OK
...

mysql> CHECK TABLE EUR_USD EXTENDED;
+-----------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------+-------+----------+----------+
| tickdb2.EUR_USD | check | status | OK |
+-----------------+-------+----------+----------+
1 row in set (14 min 3.33 sec)

Just no idea how to fix that. 

How to repeat:
Table is very big...
[4 Feb 2011 13:03] Valeriy Kravchuk 
Please, check if the same problem happens with a newer version of MySQL server, 5.1.54.
[4 Feb 2011 13:22] Vito Impagliazzo 
Unfortunately our developer has dropped and recreated the table. So far no more testing is available :( I will reopen the bug if problem happens again. Sorry about that.