Bug #48645 | Dropping an unique key and adding one leads to a table corruption | ||
---|---|---|---|
Submitted: | 9 Nov 2009 18:24 | Modified: | 20 Sep 2010 14:07 |
Reporter: | jocelyn fournier (Silver Quality Contributor) | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
Version: | 5.1.37, 5.1.40, 5.1.42-bzr | OS: | Any |
Assigned to: | Ramil Kalimullin | CPU Architecture: | Any |
Tags: | corruption, qc, regression |
[9 Nov 2009 18:24]
jocelyn fournier
[9 Nov 2009 18:37]
Valeriy Kravchuk
Verified just as described with recent 5.1.42 from bzr on Mac OS X. Note that on 5.0.88 it works: 77-52-7-73:5.0 openxs$ bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.0.88-debug Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> DROP TABLE IF EXISTS t1; Query OK, 0 rows affected (0.10 sec) mysql> mysql> CREATE TABLE t1 (a int(10) unsigned NOT NULL auto_increment, b int(10) unsigned NOT NULL -> DEFAULT '0', PRIMARY KEY (a), UNIQUE md (b), UNIQUE um (a, b)) ENGINE=MyISAM -> ROW_FORMAT=DYNAMIC DEFAULT CHARSET=UTF8; Query OK, 0 rows affected (0.00 sec) mysql> mysql> ALTER TABLE t1 DROP INDEX md, ADD UNIQUE md (b); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> exit So, this: 77-52-7-73:5.1 openxs$ bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.42-debug Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> DROP TABLE IF EXISTS t1; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE t1 (a int(10) unsigned NOT NULL auto_increment, b int(10) unsigned NOT NULL -> DEFAULT '0', PRIMARY KEY (a), UNIQUE md (b), UNIQUE um (a, b)) ENGINE=MyISAM -> ROW_FORMAT=DYNAMIC DEFAULT CHARSET=UTF8; Query OK, 0 rows affected (0.26 sec) mysql> drop table t1; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE t1 (a int(10) unsigned NOT NULL auto_increment, b int(10) unsigned NOT NULL -> DEFAULT '0', PRIMARY KEY (a), UNIQUE md (b), UNIQUE um (a, b)) ENGINE=MyISAM -> ROW_FORMAT=DYNAMIC DEFAULT CHARSET=UTF8; Query OK, 0 rows affected (0.07 sec) mysql> ALTER TABLE t1 DROP INDEX md, ADD KEY md (b); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 is a regression bug.
[12 Nov 2009 17:49]
Valeriy Kravchuk
5.1.37 is also affected: Password: 77-52-7-73:mysql-5.1.37-osx10.5-x86_64 openxs$ bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.37 MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> DROP TABLE IF EXISTS t1; Query OK, 0 rows affected (0.00 sec) mysql> mysql> CREATE TABLE t1 (a int(10) unsigned NOT NULL auto_increment, b int(10) unsigned NOT NULL -> DEFAULT '0', PRIMARY KEY (a), UNIQUE md (b), UNIQUE um (a, b)) ENGINE=MyISAM -> ROW_FORMAT=DYNAMIC DEFAULT CHARSET=UTF8; Query OK, 0 rows affected (0.05 sec) mysql> mysql> ALTER TABLE t1 DROP INDEX md, ADD UNIQUE md (b); ERROR 1034 (HY000): Incorrect key file for table 't1'; try to repair it mysql> exit So, this is NOT a recent regression in 5.1.x, but regression of 5.1.x comparing to 5.0.x it seems.
[13 Nov 2009 7:43]
Ramil Kalimullin
Simplified test case: CREATE TABLE t1 (a INT, b INT, KEY b(b), KEY ab(a, b)); ALTER TABLE t1 DROP KEY b, ADD KEY b(b); Note: key order is important!
[27 Nov 2009 18:59]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/91983 3226 Ramil Kalimullin 2009-11-27 Fix for bug#48645: Dropping an unique key and adding one leads to a table corruption Problem: dropping a key and adding the same key in a single ALTER TABLE query may lead to table corruption. MySQL stores all table indexes in the following order: - PRIMARY key, - UNIQUE keys where all column are NOT NULL, - UNIQUE keys that don't contain partial segments, - other UNIQUE keys, - normal keys, - fulltext keys. However the sort order within the above key groups is not defined, e.g. we don't sort keys by name etc. So, CREATE TABLE t1 (a INT, b INT, KEY b(b), KEY ab(a, b)); ALTER TABLE t1 DROP KEY b, ADD KEY b(b); may change the t1's key order in the .FRM file. As no actual index change occurs we just rewrite the .FRM file and don't touch index file. For some storage engines (e.g. MyISAM) it's vital that key order in the .FRM file is identical to the key order in the index file. Fix: force indexes rebuild (drop/add) in such cases. @ mysql-test/r/alter_table.result Fix for bug#48645: Dropping an unique key and adding one leads to a table corruption - test result. @ mysql-test/t/alter_table.test Fix for bug#48645: Dropping an unique key and adding one leads to a table corruption - test case. @ sql/sql_table.cc Fix for bug#48645: Dropping an unique key and adding one leads to a table corruption - force index rebuild when only key order is changed by ALTER table.
[5 Apr 2010 16:46]
MySQL Verification Team
what's happening to this bug?
[13 Jul 2010 10:47]
MySQL Verification Team
this looks fixed in 5.1.48. mysql> ALTER TABLE t1 DROP INDEX md, ADD UNIQUE md (b); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select version(); +--------------------------------------+ | version() | +--------------------------------------+ | 5.1.48-enterprise-gpl-advanced-debug | +--------------------------------------+ 1 row in set (0.02 sec) mysql> check table t1; +---------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+-------+----------+----------+ | test.t1 | check | status | OK | +---------+-------+----------+----------+ 1 row in set (0.00 sec)
[20 Sep 2010 14:07]
Ramil Kalimullin
The issue is resolved in the 5.1.
[29 Sep 2010 16:29]
James Ravn
I can still reproduce this in 5.1.51, but using a slightly different execution path. Instead of altering a table, I 'DROP DATABASE IF EXISTS foo', then 'CREATE DATABASE foo', and then recreate the tables with the same primary indexes. This results in the table corruption and is quite easy to reproduce.
[29 Sep 2010 16:46]
Ramil Kalimullin
James, could you please open a new bug for that? Thank you!
[30 Sep 2010 13:52]
James Ravn
I've created http://bugs.mysql.com/bug.php?id=57142.