Description:
Selecting from a table ordered ascending by the second field in a covering index returns correct results but the same query ordered descending returns no results.
How to repeat:
Table in question:
CREATE TABLE `collection_item` (
`id` int(11) NOT NULL auto_increment,
`collection` int(11) default NULL,
`mid` int(11) default NULL,
`position` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `collection_position_mid` (`collection`,`position`,`mid`)
) ENGINE=MyISAM AUTO_INCREMENT=17027 DEFAULT CHARSET=latin1;
Query that succeeds:
SELECT mid FROM collection_item WHERE collection = 851 ORDER BY position ASC;
+---------+
| mid |
+---------+
| 1226250 |
| 1226248 |
| 1226247 |
| 1226245 |
| 1226244 |
| 1226242 |
| 1226241 |
| 1226239 |
| 1226238 |
| 1226237 |
| 1226235 |
| 1226233 |
| 1226231 |
| 1226230 |
| 1226229 |
| 1226228 |
| 1226227 |
| 1226227 |
+---------+
Query that fails:
SELECT mid FROM collection_item WHERE collection = 851 ORDER BY position DESC;
Empty set (0.04 sec)
Previously this table had only the one index on the primary key (id). While only this index was in place no issues were noted. Since this index has been added we've noticed some rows with specific `collection` values fail to return results from the above query while others return correct results.
This only occurs on slaves in a single master, multiple slave environment. Anytime any slave is affected all are affected. We have not been able to reliably reproduce the problem, but triggering events seem of the form:
INSERT INTO collection_item ( collection, position, mid ) VALUES ( y, x, z );
UPDATE collection_item SET position = x WHERE collection = y AND mid = z;
After numerous statements like the above a different group of rows in collection_item may fail to return results when ordered by their position descending. The collection_item rows that fail are entirely unrelated to those that are inserted and updated beforehand.
Later inserts and updates occasionally cause the issue to disappear with no noted relation between the rows that are corrected and those that are inserted/updated.
After one occurence of this I wrote a test application that would attept the query that fails both on the master and one slave and report inconsistencies. After the first run it noted four errors. We then ran "REPAIR TABLE collection_item". At this point the most recently noted failed query would then return correct results, but other queries of the same form against a different collection_id continue to fail. Certain values of collection_id continue to fail and we have not been able to successfully rectify the issue. We have also attempted dropping the index entirely; while only the primary key index was present all queries succeeded, after re-creating the second index the original failed queries failed again.
