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:Microsoft Windows (Windows 7)
Assigned to: Marko Mäkelä CPU Architecture:Any
Tags: 1030, 38

[3 Jan 2013 7:23] Yuri Koba
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` ;
[3 Jan 2013 7:40] Shane Bester
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..
[3 Jan 2013 8:32] Shane Bester
still repeatable with 5.6.10 too.  so we should wait for 5.6.11...
[18 Feb 2013 18:50] John Russell
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.