MySQL Bugs: #38139: ALTER TABLE could cause inconsistent mysqldump results with InnoDB foreign keys

Bug #38139	ALTER TABLE could cause inconsistent mysqldump results with InnoDB foreign keys
Submitted:	15 Jul 2008 14:27	Modified:	15 Oct 2009 7:29
Reporter:	Vasil Dimov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	Vasil Dimov	CPU Architecture:	Any
Tags:	innodb foreign key alter table

Description:
This was found like working on Bug#37538.

ALTER TABLE could leave a table with InnoDB foreign keys in a state that SHOW CREATE TABLE shows incorrectly.

How to repeat:
CREATE TABLE `exam` (
  `id` BIGINT(19) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
)
ENGINE = INNODB
CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE `exam_time` (
  `id` BIGINT(19) NOT NULL AUTO_INCREMENT,
  `exam_id` BIGINT(19) NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT  FOREIGN KEY `FK_exam_time_exam` (`exam_id`)
    REFERENCES `aac_test`.`exam` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION
)
ENGINE = INNODB
CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER TABLE exam_time CHANGE COLUMN exam_id examId BIGINT(19) NULL DEFAULT NULL;

or

ALTER TABLE exam_time CHANGE COLUMN exam_id examId BIGINT(19) NULL DEFAULT NULL, RENAME TO examTime;

Suggested fix:
Block the ALTER TABLE in InnoDB or rename the column in InnoDB but the latter is too risky for 5.1.

Many thanks for reporting a bug.

I don't know what you mean here exactly.

Please can you give an example from show create table where you thing it is a bug.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Susanne,

Given the above CREATE commands and the first ALTER command, execute:

mysql> show create table exam_time;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                      |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| exam_time | CREATE TABLE `exam_time` (
  `id` bigint(19) NOT NULL AUTO_INCREMENT,
  `examId` bigint(19) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_exam_time_exam` (`examId`),
  CONSTRAINT `exam_time_ibfk_1` FOREIGN KEY (`exam_id`) REFERENCES `exam` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

You can see in the above that exam_id is referenced in "FOREIGN KEY (`exam_id`)", now try to create a table with this definition:

mysql> drop table exam_time;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `exam_time` (
    ->   `id` bigint(19) NOT NULL AUTO_INCREMENT,
    ->   `examId` bigint(19) DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `FK_exam_time_exam` (`examId`),
    ->   CONSTRAINT `exam_time_ibfk_1` FOREIGN KEY (`exam_id`) REFERENCES `exam` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1072 (42000): Key column 'exam_id' doesn't exist in table

On the latest MySQL 5.1-r3162 from BZR:

After executing the above 2 CREATE TABLE commands, the first ALTER gives this:

17:08:48 mysql> ALTER TABLE exam_time CHANGE COLUMN exam_id examId BIGINT(19) NULL DEFAULT NULL;
ERROR 1025 (HY000): Error on rename of './aac_test/#sql-111e6_2' to './aac_test/exam_time' (errno: 150)
17:08:58 mysql> 

and InnoDB prints this:

091014 17:08:58  InnoDB: Error: in ALTER TABLE `aac_test`.`exam_time`
InnoDB: has or is referenced in foreign key constraints
InnoDB: which are not compatible with the new table definition.
091014 17:08:58  InnoDB: Error: table `aac_test`.`exam_time` does not exist in the InnoDB internal
InnoDB: data dictionary though MySQL is trying to drop it.
InnoDB: Have you copied the .frm file of the table to the
InnoDB: MySQL database directory from another database?
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html

Which means that this ALTER command is effectively blocked.

If the second ALTER is executed it succeeds:

17:12:35 mysql> ALTER TABLE exam_time CHANGE COLUMN exam_id examId BIGINT(19) NULL DEFAULT NULL, RENAME TO examTime;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

InnoDB prints nothing and the table looks like this after this:

17:13:01 mysql> show create table examTime\G
*************************** 1. row ***************************
       Table: examTime
Create Table: CREATE TABLE `examTime` (
  `id` bigint(19) NOT NULL AUTO_INCREMENT,
  `examId` bigint(19) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_exam_time_exam` (`examId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

The fact that the FK disappears with the second alter:

ALTER TABLE exam_time CHANGE COLUMN exam_id examId BIGINT(19) NULL DEFAULT NULL, RENAME TO examTime;

looks strange. It is because in this alter:

1. a temporary table (1) is created like exam_time
2. exam_time is renamed to a temporary table (2)
3. temporary table (1) is renamed to examTime

but during the creation of the temporary table (1) in step 1. InnoDB cannot pick up the FK definitions from the SQL string because it is "ALTER TABLE exam_time ..." so temporary table (1) is created without the FK.

On an old MySQL checked out with

bzr checkout -r date:2008-07-15 (at startup it says 5.1.26-rc)

The command "ALTER TABLE exam_time CHANGE COLUMN exam_id examId BIGINT(19) NULL DEFAULT NULL, RENAME TO examTime;" results in just one invocation of ha_innobase::rename() exam_time -> examTime without using temporary tables. Thus 
the FK is not lost (but is screwed up by the column rename).

The first ALTER is blocked and the second one does not cause inconsistent output of SHOW CREATE TABLE and/or mysqldump anymore. I have opened "Bug#48070 Foreign keys may disappear after ALTER TABLE" to track the FK disappearance.