A regression bug?

MySQL & MariaDB Other MySQL® Questions
1

Hi.

One of our mysql servers:
Server version: 5.1.50-rel11.4-log (Percona Server (GPL), 11.4 , Revision 111)
64 bits
OS: GNU/Linux Ubuntu 10.04 Lucid

All tables in our databases are InnoDB.

If we execute this query:

select count() from ordenes_compra where b_updated=‘S’ and estado=‘O’;
±---------+
| count() |
±---------+
| 0 |
±---------+

mmm… it isn’t correct!! We’ll try another time:

mysql> select count(id_orden) from ordenes_compra where b_updated=‘S’ and estado=‘O’;
±----------------+
| count(id_orden) |
±----------------+
| 1682 |
±----------------+

Here it is the EXPLAINs for the querys:

mysql> explain select count(id_orden) from ordenes_compra where b_updated=‘S’ and estado=‘O’;
±—±------------±---------------±-----±--------------- -------------±---------------±--------±------±-----±— ---------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±---------------±-----±--------------- -------------±---------------±--------±------±-----±— ---------+
| 1 | SIMPLE | ordenes_compra | ref | sys_c0010069,orden_modified | orden_modified | 6 | const | 4928 | Using where |
±—±------------±---------------±-----±--------------- -------------±---------------±--------±------±-----±— ---------+

mysql> explain select count(*) from ordenes_compra where b_updated=‘S’ and estado=‘O’;
±—±------------±---------------±------------±-------- --------------------±----------------------------±-------- ±-----±-----±-------------------------------------------- ---------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±---------------±------------±-------- --------------------±----------------------------±-------- ±-----±-----±-------------------------------------------- ---------------------------+
| 1 | SIMPLE | ordenes_compra | index_merge | sys_c0010069,orden_modified | orden_modified,sys_c0010069 | 6,12 | NULL | 2404 | Using intersect(orden_modified,sys_c0010069); Using where; Using index |
±—±------------±---------------±------------±-------- --------------------±----------------------------±-------- ±-----±-----±-------------------------------------------- ---------------------------+

But if we apply the IGNORE INDEX clause the results are fine:

mysql> select count() from ordenes_compra IGNORE INDEX(sys_c0010069) where b_updated=‘S’ and estado=‘O’;
±---------+
| count() |
±---------+
| 1667 |
±---------+

There is a bug 14980 in mysql database ([URL]MySQL Bugs: #14980: COUNT(*) incorrect on MyISAM table with certain INDEX) for this, and another numbered 26331 for the same ([URL]MySQL Bugs: #26231: select count(*) on myisam table returns wrong value when index is used).

We are very surprised because these are bugs from three or four years ago.

Please, could you help us with this problem?

Thank you very much.

2

The bugs you linked to are for MyISAM. The bug that is probably biting you is something in the fast index creation in InnoDB. There are open bugs on this functionality. I suggest that you use OPTIMIZE TABLE, if possible, to rebuild the entire table and see if that fixes the problem.

3
[B]xaprb wrote on Thu, 07 October 2010 16:06[/B]
The bugs you linked to are for MyISAM. The bug that is probably biting you is something in the fast index creation in InnoDB. There are open bugs on this functionality. I suggest that you use OPTIMIZE TABLE, if possible, to rebuild the entire table and see if that fixes the problem.

Ok, I will try to OPTIMIZE this table. I try to maintain you informated about this.

Thank you!!

4

Ok, here it is the results:

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select count() from ordenes_compra where b_updated=‘S’ and estado=‘O’;
±---------+
| count() |
±---------+
| 0 |
±---------+
1 row in set (0.93 sec)

mysql> optimize table ordenes_compra;
±---------------------------±---------±---------±------- -----------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
±---------------------------±---------±---------±------- -----------------------------------------------------------+
| accounts_db.ordenes_compra | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| accounts_db.ordenes_compra | optimize | status | OK |
±---------------------------±---------±---------±------- -----------------------------------------------------------+
2 rows in set (10.07 sec)

mysql> select count() from ordenes_compra where b_updated=‘S’ and estado=‘O’;
±---------+
| count() |
±---------+
| 0 |
±---------+
1 row in set (0.05 sec)

I think this doesn’t work as we expected…

Regards.

5

Looks like a bug still. I’d file this bug with MySQL, or hire someone such as Percona to help you with it.