MySQL Bugs: #68019: Table is lost during 'ALTER TABLE' command

Bug #68019	Table is lost during 'ALTER TABLE' command
Submitted:	3 Jan 2013 7:23	Modified:	18 Feb 2013 18:50
Reporter:	Yuri Koba	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.6.9-rc	OS:	Windows (Windows 7)
Assigned to:	Marko Mäkelä	CPU Architecture:	Any
Tags:	1030, 38

Description:
When deleting a column and a foreign key using 'ALTER TABLE' command the table gets lost and the reporting error is

Error Code: 1030. Got error 38 from storage engine

1) Create two tables and link them with one foreign key.
2) Delete the foreign key and the column
3) Do not delete the index of the foreign key when executing step 2.

At this point the table is lost.
It is important to execute the 'ALTER TABLE' command as shown below.

How to repeat:
Create an empty schema and execute the following commands

CREATE TABLE `t2` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `t1` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `t2ID` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `FK_t1_1` (`t2ID`),
  CONSTRAINT `FK_t1_1` FOREIGN KEY (`t2ID`) REFERENCES `t2` (`ID`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `t1` DROP FOREIGN KEY `FK_t1_1`, DROP COLUMN `t2ID` ;

This is repeatable on 5.6.9, but not repeatable on current sources.  So it's been fixed already, please wait for 5.6.10 to be released..

still repeatable with 5.6.10 too.  so we should wait for 5.6.11...

Added to changelog for 5.6.10:

In online DDL operations, a DROP FOREIGN KEY clause was not allowed
in an ALTER TABLE statement that also performed any of the following:

Adding or dropping a column.
Adding or dropping a primary key index.
Making a column NULL or NOT NULL.
Reordering columns.
Changing the ROW_FORMAT or KEY_BLOCK_SIZE properties.