Bug #46766 ALTER TABLE corrupts table on combined drop and add index
Submitted: 17 Aug 2009 19:26 Modified: 7 Feb 2010 11:37
Reporter: Holger Kunst Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: DDL Severity:S2 (Serious)
Version:5.1.37-community OS:Windows (XP Pro - SP3)
Assigned to: Assigned Account CPU Architecture:Any
Tags: ALTER TABLE, corruption

[17 Aug 2009 19:26] Holger Kunst
Description:
Dropping and recreating an index in the same ALTER TABLE statement fails with a corrupted table. It was not possible to repair the table with "MySQL Administrator".

The failure only happens if there is another index present and the index that contains the renamed field is defined first in the create table statement. 

Problem is limited to MyISAM - works fine with InnoDB.

Tested on Win XP SP3 and Freebsd 7.2 - same error.

How to repeat:
Run script 1 and you get the error, script 2 runs fine. Note that the only difference between the scripts is the order of the keys in the CREATE TABLE. Splitting the ALTER TABLE in two different statements also works fine (see script 3)

Script 1: The following results in a corrupted test table and the ALTER TABLE reports Error 1034: Incorrect key file for table 'test'; try to repair it

DROP TABLE IF EXISTS `test`;

CREATE TABLE `test` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `idx_b` (`b`) USING BTREE,
  KEY `idx_a` (`a`) USING BTREE
  ) ENGINE=MyISAM;

ALTER TABLE `test`
  DROP INDEX `idx_b`,
  ADD INDEX `idx_b` USING BTREE(`b`);

--------------------------------------------------

Script 2: The following does work as expected

DROP TABLE IF EXISTS `test`;

CREATE TABLE `test` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `idx_a` (`a`) USING BTREE,
  KEY `idx_b` (`b`) USING BTREE
  ) ENGINE=MyISAM;

ALTER TABLE `test`
  DROP INDEX `idx_b`,
  ADD INDEX `idx_b` USING BTREE(`b`);

--------------------------------------------------

Script 3: Also works fine

DROP TABLE IF EXISTS `test`;

CREATE TABLE `test` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `idx_b` (`b`) USING BTREE,
  KEY `idx_a` (`a`) USING BTREE
  ) ENGINE=MyISAM;

ALTER TABLE `test` DROP INDEX `idx_b`;

ALTER TABLE `test` ADD INDEX `idx_b` USING BTREE(`b`);
[17 Aug 2009 19:29] Holger Kunst
Erroneously mentioned "renamed" field, because I discovered the bug while renaming indexed fields, but narrowed it down to the add/drop index issue.
[17 Aug 2009 21:03] MySQL Verification Team
Thank you for the bug report. Verified as described. 5.0/5.4 aren't affected by this bug.

c:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3306 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.38-Win X64 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.1 >create database que;
Query OK, 1 row affected (0.05 sec)

mysql 5.1 >use que
Database changed
mysql 5.1 >DROP TABLE IF EXISTS `test`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.1 >
mysql 5.1 >CREATE TABLE `test` (
    ->   `a` int(11) DEFAULT NULL,
    ->   `b` int(11) DEFAULT NULL,
    ->   KEY `idx_b` (`b`) USING BTREE,
    ->   KEY `idx_a` (`a`) USING BTREE
    ->   ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.14 sec)

mysql 5.1 >
mysql 5.1 >ALTER TABLE `test`
    ->   DROP INDEX `idx_b`,
    ->   ADD INDEX `idx_b` USING BTREE(`b`);
ERROR 1034 (HY000): Incorrect key file for table 'test'; try to repair it
mysql 5.1 >check table test;
+----------+-------+----------+-------------------------------------------------------+
| Table    | Op    | Msg_type | Msg_text                                              |
+----------+-------+----------+-------------------------------------------------------+
| que.test | check | Error    | Incorrect key file for table 'test'; try to repair it |
| que.test | check | error    | Corrupt                                               |
+----------+-------+----------+-------------------------------------------------------+
2 rows in set (0.00 sec)

mysql 5.1 >

c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.85-Win X64 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.0 > create database cual;
Query OK, 1 row affected (0.01 sec)

mysql 5.0 > use cual

c:\dbs>c:\dbs\5.4\bin\mysql -uroot --port=3540 --prompt="mysql 5.4 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.4.4-alpha-Win X64 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement

mysql 5.4 >create database ese;
Query OK, 1 row affected (0.01 sec)

mysql 5.4 >use ese
Database changed
mysql 5.4 >DROP TABLE IF EXISTS `test`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.4 >
mysql 5.4 >CREATE TABLE `test` (
    ->   `a` int(11) DEFAULT NULL,
    ->   `b` int(11) DEFAULT NULL,
    ->   KEY `idx_b` (`b`) USING BTREE,
    ->   KEY `idx_a` (`a`) USING BTREE
    ->   ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.14 sec)

mysql 5.4 >
mysql 5.4 >ALTER TABLE `test`
    ->   DROP INDEX `idx_b`,
    ->   ADD INDEX `idx_b` USING BTREE(`b`);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.4 >
Database changed
mysql 5.0 > DROP TABLE IF EXISTS `test`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.0 >
mysql 5.0 > CREATE TABLE `test` (
    ->   `a` int(11) DEFAULT NULL,
    ->   `b` int(11) DEFAULT NULL,
    ->   KEY `idx_b` (`b`) USING BTREE,
    ->   KEY `idx_a` (`a`) USING BTREE
    ->   ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.19 sec)

mysql 5.0 >
mysql 5.0 > ALTER TABLE `test`
    ->   DROP INDEX `idx_b`,
    ->   ADD INDEX `idx_b` USING BTREE(`b`);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.0 >
[15 Sep 2009 9:55] Satya B
This is not myisam bug. the ALTER TABLE  decides it needs to do a frm change only for the myisam storage engine. The server doesn't communicate the reorder of columns to myisam and hence it fails with myisam.

The reason it works with Innodb is ALTER TABLE uses temporary table.

Server should either communicate with myisam about the column reorder Or pass the columns to the myisam engine in the correct order.
[7 Feb 2010 11:37] Ramil Kalimullin
Thank you for the bug report!

There is patch pending bug #48645: "Dropping an unique key and adding one leads to a table corruption" which is a duplicate. As the patch is already submitted, setting to "Duplicate" this one.