MySQL Bugs: #58215: Renaming a table and dropping a column causes foreign key constraint drop

Bug #58215	Renaming a table and dropping a column causes foreign key constraint drop
Submitted:	15 Nov 2010 23:01	Modified:	18 Oct 2012 18:37
Reporter:	Radu Chiriac	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.0.56/5.5	OS:	Any
Assigned to:	Jimmy Yang	CPU Architecture:	Any

Description:
Dropping a column and renaming the table as part of the same ALTER TABLE statement causes MySQL to drop a foreign key constraint. The dropped constraint is no longer shown by "show create table" statement, nor is present in the information_schema's table_constraints table.

The disappeared constraint's name is impossible to use again.

How to repeat:
create table parent (
  id mediumint unsigned not null,
  primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table child77 (            
  parent_id mediumint unsigned not null,
  location_id mediumint unsigned not null,
  CONSTRAINT child_fk77 FOREIGN KEY (parent_id)
    REFERENCES parent (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- at this point "show create table" is showing the constraint properly, so does a select from table_constraints:
-- mysql> select * from table_constraints where constraint_name like '%child_fk77%'\G

-- CONSTRAINT_CATALOG: NULL
-- CONSTRAINT_SCHEMA: m2db
--   CONSTRAINT_NAME: child_fk77
--      TABLE_SCHEMA: m2db
--        TABLE_NAME: new_child77
--   CONSTRAINT_TYPE: FOREIGN KEY

alter table child77
drop column location_id,
rename to new_child77;

-- at this point the FK constraint no longer exists, select from table_constraints returns no rows and "show create table" displays (note the missing FK constraint):

--       Table: new_child77
-- Create Table: CREATE TABLE `new_child77` (
--   `parent_id` mediumint(8) unsigned NOT NULL,
--   KEY `child_fk77` (`parent_id`)
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- try dropping the table and re-creating the original table:

drop table new_child77;

create table child77 (            
  parent_id mediumint unsigned not null,
  location_id mediumint unsigned not null,
  CONSTRAINT child_fk77 FOREIGN KEY (parent_id)
    REFERENCES parent (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--------------

ERROR 1005 (HY000): Can't create table './m2db/child77.frm' (errno: 121)

show innodb status displayed:

LATEST FOREIGN KEY ERROR
------------------------
101116  0:40:27 Error in foreign key constraint creation for table `m2db/child77`.
A foreign key constraint of name `m2db/child_fk77`
already exists. (Note that internally InnoDB adds 'databasename/'
in front of the user-defined constraint name).
Note that InnoDB's FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.

A workaround: use two alter table statements.

Thank you for the bug report.

C:\DBS>c:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.7-rc-Win X64-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 >use test
Database changed
mysql 5.5 >create table parent (
    ->   id mediumint unsigned not null,
    ->   primary key (id)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.30 sec)

mysql 5.5 >
mysql 5.5 >create table child77 (
    ->   parent_id mediumint unsigned not null,
    ->   location_id mediumint unsigned not null,
    ->   CONSTRAINT child_fk77 FOREIGN KEY (parent_id)
    ->     REFERENCES parent (id) ON DELETE CASCADE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.50 sec)

mysql 5.5 >alter table child77
    -> drop column location_id,
    -> rename to new_child77;
Query OK, 0 rows affected (0.70 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.5 >
mysql 5.5 >drop table new_child77;
Query OK, 0 rows affected (0.14 sec)

mysql 5.5 >
mysql 5.5 >create table child77 (
    ->   parent_id mediumint unsigned not null,
    ->   location_id mediumint unsigned not null,
    ->   CONSTRAINT child_fk77 FOREIGN KEY (parent_id)
    ->     REFERENCES parent (id) ON DELETE CASCADE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1005 (HY000): Can't create table 'test.child77' (errno: 121)
mysql 5.5 >

See: http://bugs.mysql.com/bug.php?id=13741.

The problem here is that we will rename the table name in foreign key constraint if there is a direct alter table rename:

 alter table child77 rename to new_child77;

row_rename_table_for_mysql()
{
   3929         if (err != DB_SUCCESS) {
   3930 
   3931                 goto end;
   3932         } else if (!new_is_tmp) {  <=== If the table is not temp table
   3933                 /* Rename all constraints. */
   3934 

If there are other operations in the alter (such as alter drop), the row_rename_table_for_mysql is called twice, first from old_table_name to a temp table name, and then from temp_table_name to new_table_name. Thus in either case, the constraint will not be renamed.

Breakpoint 4, row_rename_table_for_mysql (old_name=0x90c7860 "test/child77", 
    new_name=0x90cf948 "test/#sql2-6c6d-1", trx=0x90c8f20, commit=1)

Breakpoint 5, ha_innobase::rename_table (this=0x90c50e8, 
    from=0xb2b2f23a "./test/#sql-6c6d_1", to=0xb2b2f039 "./test/new_child77")

We will need a fix to remember the old table name in this two row_rename_table_for_mysql case, so that the constraint can be renamed properly.

We would actually need more information from MySQL to get this alter table rename with alter table drop to work properly with FK:

The "alter table child77 drop column location_id, rename to new_child77" takes following steps:

1) old table rename to temp table A 
(ha_innobase::rename_table(from=0xb2b2f23a "./test/child77", to=0xb2b2f039 "./test/#sql2-450-1")
 
2) temp table B created for the alter drop column operation

3) temp table B is renamed to new table
(ha_innobase::rename_table(from=0xb2b2f23a "./test/#sql-450-1", to=0xb2b2f039 "./test/new_child77")

This is the same step with "alter table drop column", except in the last step instead of alter temp table B back to old table name, it substitute it with the new table name.

From InnoDB standpoint, it does not know table name has changed. So it is not able to handle the FK appropriately. It would be good that MySQL pass in some additional information on the alter table type (drop, rename etc.), and provide the old table name if we alter rename to a new table through  ha_innobase::rename_table() interfaces.

Fixed as part of online DDL enhancements in 5.6.6.