Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

A regression bug?

systemsvlexsystemsvlex EntrantInactive User Role Beginner
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 (http://bugs.mysql.com/bug.php?id=14980) for this, and another numbered 26331 for the same (http://bugs.mysql.com/bug.php?id=26231).

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.

Comments

  • xaprbxaprb Mentor Inactive User Role Beginner
    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.
  • systemsvlexsystemsvlex Entrant Inactive User Role Beginner
    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">xaprb wrote on Thu, 07 October 2010 16:06</td></tr><tr><td class="quote">
    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.
    </td></tr></table>

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

    Thank you!!
  • systemsvlexsystemsvlex Entrant Inactive User Role Beginner
    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.
  • xaprbxaprb Mentor Inactive User Role Beginner
    Looks like a bug still. I'd file this bug with MySQL, or hire someone such as Percona to help you with it.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.