Announcement

Announcement Module
Collapse
No announcement yet.

A regression bug?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • A regression bug?

    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.

  • #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.

    Comment


    • #3
      xaprb wrote on Thu, 07 October 2010 16:06

      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!!

      Comment


      • #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.

        Comment


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

          Comment


          • #6
            xaprb wrote on Thu, 07 October 2010 17:42

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


            Ok Schwartz, thank you for your great help

            Regards.

            Comment

            Working...
            X