Announcement

Announcement Module
Collapse
No announcement yet.

Bug in fast index creation ?

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

  • Bug in fast index creation ?

    my.cnf:

    [client]#password = your_passwordport = 3306socket = /tmp/mysql.sock[mysqld]port = 3306socket = /tmp/mysql.sockuser = mysqldatadir = /var/db/mysqlbasedir = /usr/localtmpdir = /var/db/tmpmax_connect_errors = 10innodb_file_format=Barracudainnodb_file_per_tabl e


    mysql> show create table Files \G

    *************************** 1. row *************************** Table: FilesCreate Table: CREATE TABLE `Files` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `MD5Hash` char(32) NOT NULL DEFAULT '', `SHA256Hash` char(64) NOT NULL DEFAULT '', `FileSize` int(11) NOT NULL DEFAULT '0', `Ext` char(4) NOT NULL DEFAULT '', PRIMARY KEY (`ID`), KEY `File_k_2` (`MD5Hash`(4),`SHA256Hash`(4),`FileSize`)) ENGINE=InnoDB AUTO_INCREMENT=22728476 DEFAULT CHARSET=utf81 row in set (0.00 sec)



    mysql> create table Files2 like Files;

    Query OK, 0 rows affected (0.02 sec)


    mysql> insert into Files2 select * from Files limit 375000;

    Query OK, 375000 rows affected (15.75 sec)Records: 375000 Duplicates: 0 Warnings: 0


    mysql> check table Files2;

    +-------------+-------+----------+----------+| Table | Op | Msg_type | Msg_text |+-------------+-------+----------+----------+| test.Files2 | check | status | OK |+-------------+-------+----------+----------+1 row in set (1.23 sec)


    recreating index:

    mysql> alter table Files2 drop KEY `File_k_2`;

    Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0


    mysql> alter table Files2 add KEY `File_k_2` (`MD5Hash`(4),`SHA256Hash`(4),`FileSize`);

    Query OK, 0 rows affected (1.23 sec)Records: 0 Duplicates: 0 Warnings: 0


    mysql> check table Files2;

    +-------------+-------+----------+----------+| Table | Op | Msg_type | Msg_text |+-------------+-------+----------+----------+| test.Files2 | check | error | Corrupt |+-------------+-------+----------+----------+1 row in set (0.66 sec)


    message in .err:


    Error: index `File_k_2` of table `test`.`Files2` contains 20164 entries, should be 375000


    Is it bug or I missed something ?
    I tried Percona-Server 5.1.45-rel10.2 and MySQL 5.1.47 with built-in InnoDB plugin.

  • #2
    That certainly looks like a bug. What if you don't use the Barracuda format? Did you search bugs.mysql.com for a related bug?

    Comment


    • #3
      xaprb wrote on Mon, 07 June 2010 07:30

      That certainly looks like a bug. What if you don't use the Barracuda format?


      Without Barracuda all works without errors, but very long.
      With smaller dataset (200k rows) Barracuda also works fine.

      xaprb wrote on Mon, 07 June 2010 07:30

      Did you search bugs.mysql.com for a related bug?


      Yes, but I found only closed bugs about data corruption.

      Comment


      • #4
        I think you can probably report it as a new bug, then. Do you have a reproducible test case?

        Comment


        • #5
          xaprb wrote on Mon, 07 June 2010 11:19

          I think you can probably report it as a new bug, then. Do you have a reproducible test case?


          It looks like specific to my dataset. I can't reproduce it on random data.

          Can you try this dump http://213.174.150.7/Files2.sql.bz2 and reproduce bug ?

          Comment


          • #6
            Unfortunately I doubt I will have time to try to reproduce it.

            Comment


            • #7
              I'm able to reproduce it on standard 5.1.47, Innodb plugin 1.0.8. It's definitely a bug. Pservit, can you file the bug report please?

              Output:


              mysql> \s--------------mysql Ver 14.14 Distrib 5.1.47, for redhat-linux-gnu (x86_64) using readline 5.1Connection id: 7126Current database: testCurrent user: root@localhostSSL: Not in useCurrent pager: stdoutUsing outfile: ''Using delimiter: ;Server version: 5.1.47-log MySQL Community Server (GPL) by RemiProtocol version: 10Connection: Localhost via UNIX socketServer characterset: utf8Db characterset: utf8Client characterset: latin1Conn. characterset: latin1UNIX socket: /var/lib/mysql/mysql.sockmysql> show create table Files2 \G*************************** 1. row *************************** Table: Files2Create Table: CREATE TABLE `Files2` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `MD5Hash` char(32) NOT NULL DEFAULT '', `SHA256Hash` char(64) NOT NULL DEFAULT '', `FileSize` int(11) NOT NULL DEFAULT '0', `Ext` char(4) NOT NULL DEFAULT '', PRIMARY KEY (`ID`), KEY `File_k_2` (`MD5Hash`(4),`SHA256Hash`(4),`FileSize`)) ENGINE=InnoDB AUTO_INCREMENT=426137 DEFAULT CHARSET=utf81 row in set (0.00 sec)mysql> create table Files like Files2;Query OK, 0 rows affected (0.02 sec)mysql> insert into Files select * from Files2 limit 375000;Query OK, 375000 rows affected (8.00 sec)Records: 375000 Duplicates: 0 Warnings: 0mysql> check table Files;+------------+-------+----------+----------+| Table | Op | Msg_type | Msg_text |+------------+-------+----------+----------+| test.Files | check | status | OK |+------------+-------+----------+----------+1 row in set (19.24 sec)mysql> alter table Files drop KEY `File_k_2`;Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> alter table Files add KEY `File_k_2` (`MD5Hash`(4),`SHA256Hash`(4),`FileSize`);Query OK, 0 rows affected (0.93 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> check table Files;+------------+-------+----------+--------------------------------------------------------------------+| Table | Op | Msg_type | Msg_text |+------------+-------+----------+--------------------------------------------------------------------+| test.Files | check | Warning | InnoDB: Index 'File_k_2' contains 20164 entries, should be 375000. || test.Files | check | error | Corrupt |+------------+-------+----------+--------------------------------------------------------------------+2 rows in set (18.54 sec)

              Uptime: 1 day 2 hours 23 min 54 sec

              Comment


              • #8
                krteq wrote on Tue, 08 June 2010 04:51

                I'm able to reproduce it on standard 5.1.47, Innodb plugin 1.0.8. It's definitely a bug. Pservit, can you file the bug report please?



                Thank you.

                http://bugs.mysql.com/bug.php?id=54330

                Comment


                • #9
                  Quote:

                  Fix checked in mysql-5.1-innodb:
                  3512 Jimmy Yang 2010-06-16
                  Fix Bug #54330 Broken fast index creation. Add additional array to
                  account for each merge run's start offset, so correct offsets are
                  paired up for multiple merge runs.

                  Comment


                  • #10
                    Good job, hope that Percona will release an update soon.

                    Comment

                    Working...
                    X