order by and limit gives wrong result

MySQL ver 5.1.26 with PerconaInnoDB

I’m getting the wrong result with a select that has where, order by and limit clauses.
It’s only a problem when the order by uses the id column.

I saw the MySQL manual for LIMIT Optimization

My guess from reading the manual is that there is some problem with the index on the primary key, id. But I don’t know where I should go from here…

Question: what should I do to best solve the problem?

Works correctly: mysql> SELECT id, created_at FROM billing_invoices WHERE (billing_invoices.account_id = 5) ORDER BY id DESC ; ±-----±--------------------+ | id | created_at | ±-----±--------------------+ | 1336 | 2010-05-14 08:05:25 | | 1334 | 2010-05-06 08:05:25 | | 1331 | 2010-05-05 23:18:11 | ±-----±--------------------+ 3 rows in set (0.00 sec) WRONG result when limit added! Should be the first row, id - 1336 mysql> SELECT id, created_at FROM billing_invoices WHERE (billing_invoices.account_id = 5) ORDER BY id DESC limit 1; ±-----±--------------------+ | id | created_at | ±-----±--------------------+ | 1331 | 2010-05-05 23:18:11 | ±-----±--------------------+ 1 row in set (0.00 sec) Works correctly: mysql> SELECT id, created_at FROM billing_invoices WHERE (billing_invoices.account_id = 5) ORDER BY created_at DESC ; ±-----±--------------------+ | id | created_at | ±-----±--------------------+ | 1336 | 2010-05-14 08:05:25 | | 1334 | 2010-05-06 08:05:25 | | 1331 | 2010-05-05 23:18:11 | ±-----±--------------------+ 3 rows in set (0.01 sec) Works correctly with limit: mysql> SELECT id, created_at FROM billing_invoices WHERE (billing_invoices.account_id = 5) ORDER BY created_at DESC limit 1; ±-----±--------------------+ | id | created_at | ±-----±--------------------+ | 1336 | 2010-05-14 08:05:25 | ±-----±--------------------+ 1 row in set (0.01 sec) Additional info: explain SELECT id, created_at FROM billing_invoices WHERE (billing_invoices.account_id = 5) ORDER BY id DESC limit 1; ±—±------------±-----------------±------±-------------------------------------±-------------------------------------±--------±-----±-----±------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ±—±------------±-----------------±------±-------------------------------------±-------------------------------------±--------±-----±-----±------------+ | 1 | SIMPLE | billing_invoices | range | index_billing_invoices_on_account_id | index_billing_invoices_on_account_id | 4 | NULL | 3 | Using where | ±—±------------±-----------------±------±-------------------------------------±-------------------------------------±--------±-----±-----±------------+

Added SHOW CREATE TABLE billing_invoices result:

Table – billing_invoices Create Table – CREATE TABLE billing_invoices ( id int(11) NOT NULL AUTO_INCREMENT, account_id int(11) NOT NULL, invoice_date date NOT NULL, prior_invoice_id int(11) DEFAULT NULL, closing_balance decimal(8,2) NOT NULL, note varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, monthly_invoice tinyint(1) NOT NULL, created_at datetime DEFAULT NULL, updated_at datetime DEFAULT NULL, PRIMARY KEY (id), KEY index_billing_invoices_on_account_id (account_id) ) ENGINE=InnoDB AUTO_INCREMENT=1337 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I now see that on my development machine, everything is working correctly. That machine has version VERSION() of 5.1.26-rc-log

On my production machine, where the problem is, I see that VERSION() returns 5.1.26-rc-percona-log

So at this point, I’m thinking the problem is with the percona software?

I doubt very much that the problem is with the Percona software. It is almost impossible to imagine that the changes we made to InnoDB and MySQL could cause this. I have a couple of suggestions.

First, the tables are probably in need of repair or upgrade. Did you upgrade MySQL from 5.0 without upgrading the tables? InnoDB does not support REPAIR TABLE, but you can simply use OPTIMIZE or another command that will alter the table. This will rebuild it.

My second suggestion is to consider upgrading from 5.1.26 to the latest 5.1 series. 5.1 had a ton of bug fixes with each new release for a long time after 5.1.26. I would consider 5.1.26 to be very buggy. A lot of those bugs were in the optimizer, and affected things like the problems you are describing.

5.1.30 was actually the GA release:
http://dev.mysql.com/doc/refman/5.1/en/news-5-1-30.html

You are using a release candidate / not the final product.