MySQL Bugs: #37538: how to orphan a foreign key in InnoDB by using a compound statement

Bug #37538	how to orphan a foreign key in InnoDB by using a compound statement
Submitted:	20 Jun 2008 3:22	Modified:	15 Jul 2008 14:29
Reporter:	Ben Krug	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.0.50, 5.0.60	OS:	Any
Assigned to:	Vasil Dimov	CPU Architecture:	Any
Tags:	foreign key, innodb, workbench

Description:
Reported as a way to orphan a foreign key, such that after dropping the table, it could not be recreated unless ibdata files were deleted and everything was started from scratch.

sql involved was generated by MySQL Workbench.  

A table is created with a foreign key.  If you try to rename the child field, an error is generated

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

returns

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-230_8' to '.\aac_test\ex

However, if you make it a compound statement:

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

returns without error, and renames the table, but orphans the foreign key.

How to repeat:

DROP DATABASE  if exists `aac_test`;

CREATE DATABASE IF NOT EXISTS `aac_test`
  CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `aac_test`;

DROP TABLE IF EXISTS `aac_test`.`exam`;
CREATE TABLE `aac_test`.`exam` (
  `id` BIGINT(19) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
)
ENGINE = INNODB
CHARACTER SET utf8 COLLATE utf8_general_ci;

DROP TABLE IF EXISTS `aac_test`.`exam_time`;
CREATE TABLE `aac_test`.`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;

-- following statement fails:

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

-- following statement returns:

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

-- following statement will show that foreign key is lost:

SHOW CREATE TABLE examTime;

-- now users drops entire database, and tries to recreate it with original
-- table; it cannot be recreated.  Alternatively, simply try to recreate
-- the original exam_time table, or drop examTime and try to recreate exam_time.

DROP DATABASE  if exists `aac_test`;

CREATE DATABASE IF NOT EXISTS `aac_test`
  CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `aac_test`;

DROP TABLE IF EXISTS `aac_test`.`exam`;
CREATE TABLE `aac_test`.`exam` (
  `id` BIGINT(19) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
)
ENGINE = INNODB
CHARACTER SET utf8 COLLATE utf8_general_ci;

-- following statement will not work, as orphaned FK still exists somewhere...

DROP TABLE IF EXISTS `aac_test`.`exam_time`;
CREATE TABLE `aac_test`.`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;

Suggested fix:
compound statement

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

should return an error, just as the first part of it:

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

would.

Verified just as described with 5.0.60.

5.1 is also affected, but in a different way:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.25-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DROP DATABASE  if exists `aac_test`;
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql>
mysql> CREATE DATABASE IF NOT EXISTS `aac_test`
    ->   CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> USE `aac_test`;
Database changed
mysql>
mysql> DROP TABLE IF EXISTS `aac_test`.`exam`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `aac_test`.`exam` (
    ->   `id` BIGINT(19) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> )
    -> ENGINE = INNODB
    -> CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql> DROP TABLE IF EXISTS `aac_test`.`exam_time`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `aac_test`.`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;
Query OK, 0 rows affected (0.08 sec)

mysql> ALTER TABLE `exam_time` CHANGE COLUMN `exam_id` `examId` BIGINT(19) NULL
DEFAULT NULL;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

So, ALTER TABLE works. But look at the result:

mysql> show create table exam_time\G
*************************** 1. row ***************************
       Table: exam_time
Create Table: 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.03 sec)

Column name is FOREIGN KEY definition is NOT changed...

This is related to bug http://bugs.mysql.com/bug.php?id=21704.

Hi!

Dropping a foreign key should succeed in 5.1.xx and 6.0.x with Vasil's patch to: Bug "#18942 DROP DATABASE does not drop an orphan FOREIGN KEY constraint" http://bugs.mysql.com/bug.php?id=18942.

Assigning this bug to Vasil.

Vasil, please check if DROP DATABASE drops the constraint in 5.1.2x.

The fact that RENAME COLUMN causes problems to InnoDB's foreign keys is known from the bug report Ken mentioned. But I do not understand why the behavior in this bug report is as reported. Vasil, please investigate.

Regards,

Heikki

Ben, what version of MySQL did you use to get the "DROP DATABASE does not wipe orphan FKs"? The fix for Bug#18942 has not been committed into 5.0.

I confirm that DROP DATABASE wipes the orphan FKs in 5.1.27-r2662.

Otherwise this seems to be the same as Bug#21704.

Marked as duplicate of http://bugs.mysql.com/bug.php?id=21704

This is not a duplicate of 21704, although it's related to it.  The new aspect here is that this is a new way to rename the affected column without error, which then leads to 21704.  Simply trying to rename the field will return an error.  But an additional change clause to the ALTER TABLE statement allows it to be processed without error, thus leading to 21704.  The bug is that the compound statement is allowed to execute without error.

However, that said, fixing 21704 means that this bug is no longer a bug, as then it will be OK for the field to be renamed.

BTW to answer Vasil, yes, I used 5.0 when I first tested this bug.

Ok, this is not a duplicate. The bug is that

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

does not return an error, just like

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

does. The first statement should also return an error.

Here is some analysis why one of the statements fails and the other one succeeds.

On a fresh database execute these:

CREATE DATABASE `aac_test` CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `aac_test`;

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;

After this:

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

fails because the following code path is executed:

in mysql_alter_table():

rename exam_time -> #sql2-543d-2 (ok)

rename #sql-543d_2 -> exam_time (error)

sql_table.cc:3935
  row_rename_table_for_mysql(#sql-543d_2 -> exam_time)
    dict_load_foreigns(exam_time) (error)
      dict_load_foreign(exam_time_ibfk_1) (error)
        dict_foreign_add_to_cache(exam_time_ibfk_1) (error)
          dict0dict.c:2329
          dict_foreign_find_index(table=exam_time, column=exam_id) (NULL)
            // cannot find a matching index, the index is on column
            // examId, FK on column exam_id

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

succeeds because:

rename exam_time -> #sql2-9baf-2 (ok)

rename #sql-9baf_2 -> examTime (ok)

sql_table.cc:3935
  row_rename_table_for_mysql(#sql-9baf_2 -> examTime)
    row0mysql.c:3880
    dict_load_foreigns(examTime) (success)
      // The system table SYS_FOREIGN does not contan any
      // rows for the table examTime, i.e. no FK associated with
      // the table examTime

So dict_load_foreigns() returns success if there are no FKs associated with the given table, but returns an error if there is a FK but it cannot be loaded.

Vasil,

thank you for an intelligent analysis.

Can you write a function that checks if the RENAME has renamed also a column, and make InnoDB RENAME it in SYS_FOREIGN and associated memory structures like dict_col_t?

Or maybe easier to block the RENAME?

I understand the bug is only in 5.0? We cannot endanger the stability of 5.0 with a complex patch like this.

--Heikki

Looks like any fix for this is too risky for 5.0.

In 5.1 both ALTER statements succeed leaving inconsistent results like:

CREATE TABLE `examTime` (
  `id` bigint(19) NOT NULL AUTO_INCREMENT,
  `examId` bigint(19) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_exam_time_exam` (`examId`),
  CONSTRAINT `examTime_ibfk_1` FOREIGN KEY (`exam_id`) REFERENCES `exam` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

which would cause problems with mysqldump & restore

Setting to "Won't fix" as the fix is too risky for 5.0.

I have opened Bug#38139 to handle this in 5.1.