Bug in fast index creation ?

MySQL & MariaDB Other MySQL® Questions
1

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_table

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?

3
[B]xaprb wrote on Mon, 07 June 2010 07:30[/B]
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.

[B]xaprb wrote on Mon, 07 June 2010 07:30[/B]
Did you search bugs.mysql.com for a related bug?

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

4

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

5
[B]krteq wrote on Tue, 08 June 2010 04:51[/B]
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.

[URL=“http://MySQL Bugs: #54330: Broken fast index creation”]MySQL Bugs: #54330: Broken fast index creation

6

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