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:
None 
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
Description:
Hi,

When executing a specific ALTER TABLE which drop and add a unique key, this leads to a table corruption.

How to repeat:
DROP TABLE IF EXISTS t1;

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;

ALTER TABLE t1 DROP INDEX md, ADD UNIQUE md (b);

=> 

ERROR 1034 (HY000): Incorrect key file for table 't1'; try to repair it

However 

ALTER TABLE t1 DROP INDEX md, ADD KEY md (b);
works correctly

Regards,
  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.