MySQL Bugs: #46766: ALTER TABLE corrupts table on combined drop and add index

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

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`);

Erroneously mentioned "renamed" field, because I discovered the bug while renaming indexed fields, but narrowed it down to the add/drop index issue.

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 >

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.

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.