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.
[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.
Comment