MySQL Bugs: #48070: Foreign keys may disappear after ALTER TABLE

Bug #48070	Foreign keys may disappear after ALTER TABLE
Submitted:	15 Oct 2009 7:23	Modified:	10 Apr 2019 22:16
Reporter:	Vasil Dimov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	innodb foreign key alter table

Description:
[I found this while working on Bug#38139 ALTER TABLE could cause inconsistent mysqldump results with InnoDB foreign keys]

Foreign keys disappear from the table definition after ALTER TABLE if MySQL decides to recreate the table during the ALTER command. This is because InnoDB picks up the FK definitions from the SQL command, but during the creation of the temporary table (that is later going to become the real table) the SQL command is "ALTER TABLE ..." and obviously does not contain any FK definitions. Thus the new table is created without foreign keys.

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;

show create table exam_time;

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

show create table exam_time;

notice the FK is gone from exam_time after the ALTER command.

Suggested fix:
The FK definitions should be picked up from the create_info struct passed to ::create() not from the SQL command by parsing it.

OR which is more like a hack:

When calling ::create() MySQL should set thd_query(thd) to the original CREATE TABLE command so InnoDB can pick the FK definitions from it.

This bug could be related to
Bug#35521 Foreign keys: ALTER is destructive
a third solution is for MySQL to explicitly create the FKs on the temporary table after it creates it. If this is how Bug#35521 was fixed, then this fix should be ported to MySQL 5.1.

Verified just as described:

...
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,
  `exam_id` bigint(19) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_exam_time_exam` (`exam_id`),
  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)

mysql> ALTER TABLE exam_time CHANGE COLUMN exam_id examId BIGINT(19) NULL DEFAUL
T NULL, RENAME
    -> TO
    -> examTime;
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table exam_time\G
ERROR 1146 (42S02): Table 'test.exam_time' doesn't exist
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)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.1.39-community |
+------------------+
1 row in set (0.02 sec)

int
ha_innobase::create(
/*================*/
                                        /* out: error number */
        const char*     name,           /* in: table name */
        TABLE*          form,           /* in: information on table
                                        columns and indexes */
        HA_CREATE_INFO* create_info)    /* in: more information of the
                                        created table, contains also the
                                        create statement string */

There is no FK definitions in either of the structs TABLE or HA_CREATE_INFO, InnoDB is helpless here - it can only look at thd_query(thd) which is "ALTER ..." and does not contain FK defs.

Works for me on Ubuntu 9.04 with MySQL 5.1.31-1ubuntu2. After creating exam, creating exax_time, and running the alter table statement, my examTime table looks fine:

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`),
  CONSTRAINT `examtime_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)

This server uses a couple of non-standard settings:
* lower_case_table_names=1
* innodb_file_per_table

Hope this helps, and let me know if you'd like more information about this setup.

I can repro the bug on two other database servers:
* Ubuntu 8.04, MySQL version 5.0.51a-3ubuntu5.4-log (standard Ubuntu package installed via "apt")
* Ubuntu 8.04, MySQL version 5.1.39 (binary downloaded from MySQL and installed by hand in /usr/local/mysql-5.1.39-linux-i686-glibc23)

Adam,

Are you saying that you cannot reproduce the bug on "MySQL 5.1.31-1ubuntu2", but you can reproduce it on "MySQL version 5.1.39", all other settings being the same? The OS version shouldn't matter.

Thanks!

> Are you saying that you cannot reproduce the bug on "MySQL
> 5.1.31-1ubuntu2", but you can reproduce it on "MySQL
> version 5.1.39", all other settings being the same?

Correct, well, the settings should be mostly the same. I can dump the configuration for these two databases and reply back with the diff. Any help what all should be "diffed" would be appreciated.

> The OS version shouldn't matter.

Agreed, it shouldn't. I suppose there may be something different with a shared library or disk configuration or something, though.

By the way, since fixing (or at least learning more about) this issue is valuable to me, too, so if you think it would be expedient to have a call or text chat, let me know via email and I'll share my contact information. My gmail username is "haircut".

Also, not sure if this helps, but we (the Grameen Foundation) are technical contacts on the support contract used by our customer Grameen Koota, and we have permission to use their support contract for issues that don't require consultative support. We may also be able to use consultative support hours, but we should ask Grameen Koota first.

SHOW VARIABLES output from 5.1.39 server where bug does repro

Attachment: box_with_repro.txt (text/plain), 36.84 KiB.

SHOW VARIABLES output from 5.1.31-1ubuntu2 server where bug does NOT repro

Attachment: box_without_repro.txt (text/plain), 18.84 KiB.

Setting innodb_flush_log_at_trx_commit to 1 on the 5.1.31-1ubuntu2 server had no effect (ie: did not cause the bug to repro).

Posted by developer:
 
Hello!

The problem is still repeatable in MySQL 5.7.27-git using the
following simplified test case:

CREATE TABLE parent (pk INT PRIMARY KEY);
INSERT INTO parent VALUES (1);
CREATE TABLE child (fk INT, b INT, FOREIGN KEY (fk) REFERENCES parent (pk));
INSERT INTO child VALUES (1, 1);
SHOW CREATE TABLE child;
# Table	Create Table
# child	CREATE TABLE `child` (
#	  `fk` int(11) DEFAULT NULL,
#	  `b` int(11) DEFAULT NULL,
#	  KEY `fk` (`fk`),
#	  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `parent` (`pk`)
#	) ENGINE=InnoDB DEFAULT CHARSET=latin1
ALTER TABLE child MODIFY COLUMN b BIGINT, RENAME TO child_renamed;
# Foreign key is missing from the table definition now!
SHOW CREATE TABLE child_renamed;
#Table	Create Table
# child_renamed   CREATE TABLE `child_renamed` (
#			`fk` int(11) DEFAULT NULL,
#			`b` bigint(20) DEFAULT NULL,
#			 KEY `fk` (`fk`)
#		  ) ENGINE=InnoDB DEFAULT CHARSET=latin1

# And removal of parent row succeeds!
DELETE FROM parent WHERE pk = 1;

In fact, the problem is caused by any ALTER TABLE which is executed using COPY
algorithm and which also has RENAME TABLE clause. Such ALTER doesn't update 
meta information for existing foreign keys correctly.

Quoting more detailed analysis from bug #18713399 "FK CHILD TABLE CANNOT BE
CREATED: PROBLEMS AFTER TABLE RENAME":

===
Current flow in mysql_alter_table is:
1) create a table with a name starting with #sql
2) copy all records from t1 with ha_innobase::write_row()
3) rename t1 to a name starting with #sql2
4) rename #sql to t2
5) remove #sql2

The issue comes from the step 4), all original FK constraints of t1 don't
exist in #sql, so this rename would make t2 lose all old FK constraints of
t1.
===

Moreover not only old FK constraints are not associated with new table name,
but actually they remain associated with old table name in the InnoDB's
internal data dictionary causing problems if someone tries to create
table with FKs with the old table name. This is what bug #18713399 is about.
In fact if the add two more steps to the end of the above test case we will
get simplified test case for bug #18713399:

DROP TABLE child_renamed;

# The below CREATE TABLE fails unexpectedly.
CREATE TABLE child (fk INT, b INT, FOREIGN KEY (fk) REFERENCES parent (pk));
# ERROR 23000: Can't write; duplicate key in table 'child'

I am updating title of this bug to emphasize that it is repeatable only if
RENAME TABLE clause is present in ALTER TABLE.

Posted by developer:
 
Bug #18713399 "FK CHILD TABLE CANNOT BE CREATED: PROBLEMS
AFTER TABLE RENAME" was marked as a duplicate of this bug.

Posted by developer:
 
Bug #13970648 /	64922 "FOREIGN KEY ERROR ON CREATE TABLE AFTER ALTER
TABLE AND DROP TABLE STATEM" was marked as duplicate of this bug.

Posted by developer:
 
Bug #16768216 "FOREIGN KEY CONSTRAINTS ARE LOST IN ALTER TABLE" was
marked as duplicate of this bug.

Posted by developer:
 
Bug #25467454 / #84709 "RENAMING A TABLE WITH ALTER TABLE ... COPY
REMOVES FOREIGN KEYS" was marked as duplicate of this bug.

Posted by developer:
 
Bug #27867581 "DROP RENAME FK NOT UPDATING INNODB_SYS_FOREIGN" has
been marked as duplicate of this bug.

Posted by developer:
 
Bug #26884010 / 87893 "FK ENTRY CANNOT BE DROPPED AFTER AN
ALTER COPY OPERATION" has been marked as a duplicate of this
bug.

Posted by developer:
 
Hello!

As it was mentioned above the problem occurs during last phase of copying
ALTER TABLE on which we rename internal table with name #sql... representing
new table version to new_table_name. The issue was that old foreign keys were
not added to this #sql... table, nor they were associated with new_table_name
(like it happens for ALTER TABLE without RENAME clause) and thus not picked
up at the end of operation.

The good news are that this issue has been solved by moving information
about foreing keys to the New Data Dictionary and by fix for bug @25915132
"INPLACE ALTER TABLE WITH FOREIGN KEYS CAUSES TABLE DEFINITION MISMATCH".
The latter has changed ALTER TABLE implementation in such a way that old
foreign keys are now associated with new table version under #sql... name
as well.

So I am closing this bug as fixed in 8.0.5.

Moving it to Documenting state to let Documentation Team decide if they
want to extend release notes with information about this bug.

Posted by developer:
 
Bug#25915132 was actually fixed in 8.0.11, so marking the present bug as fixed in that version as well.

An in-place ALTER TABLE operation on a table with foreign keys
resulted in a table definition mismatch. The new table definition
passed to storage engine methods during the ALTER TABLE execution
contained invalid foreign key names.

Posted by developer:
 
Correction: Fixed in 8.0.5.

ALTER TABLE ... RENAME operations could lose foreign keys defined on
the table if executed using the COPY algorithm.

Posted by developer:
 
Correction: There is no version 8.0.5. It was released as 8.0.11.

Posted by developer:
 
Bug #25038564 "RENAME TABLE::ALGO COPY::FAILED TO LOAD TABLE WHICH
HAS A FOREIGN KEY CONSTRAINT" has been marked as duplicate of this one.

Bug#64922 Foreign Key Error on CREATE TABLE after ALTER TABLE and DROP TABLE statements.

was marked as a duplicate.