Bug #74862 | ALTER TABLE USES STALE DICTIONARY OBJECT DURING FOREIGN KEY LOAD | ||
---|---|---|---|
Submitted: | 14 Nov 2014 10:18 | Modified: | 24 Nov 2014 19:20 |
Reporter: | Marko Mäkelä | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S1 (Critical) |
Version: | 5.7.6 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[14 Nov 2014 10:18]
Marko Mäkelä
[14 Nov 2014 10:21]
Marko Mäkelä
Posted by developer: This was originally posted by Ramesh Sivaraman to MySQLbug 74363 (Bug#19830715). It is not at all related to that bug. I wonder if this bug is a recent regression in 5.7, or if it is present in 5.6 already.
[14 Nov 2014 11:14]
Marko Mäkelä
Posted by developer: According to Annamalai Gurusami, the CREATE TABLE statement fails in MySQL 5.6: mysqltest: At line 3: query 'CREATE TABLE t2(a INT KEY,b INT,INDEX ind (b),FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB' failed: 1215: Cannot add foreign key constraint Because t2.b is declared as INT and t1.a is declared as INT NOT NULL, this is a type mismatch. The semantics would be somewhat unclear when some row in t1 gets deleted or updated such that t1.a=NULL. For some reason, this type mismatch is not detected by CREATE TABLE in MySQL 5.7.6. But, ALTER TABLE is detecting the mismatch when loading the foreign key definitions.
[24 Nov 2014 18:56]
Daniel Price
Posted by developer: commit 9562619396a4be75a31427aadbbddcc28b7af273 Author: Annamalai Gurusami <annamalai.gurusami@oracle.com> Date: Fri Nov 21 10:12:34 2014 +0530 Bug #20031243 Problem: In an alter table command, when we modify the name of a column that is part a foreign key constraint, then reloading of the foreign key constraint faile This problem happened because the foreign object with old column names are there in the dictionary cache. Solution: To avoid problem, when a column name is changed, then evict the correspondin foreign key object from the dictionary cache. It will be loaded subsequentl with foreign keys are loaded. rb#7369 in review
[24 Nov 2014 19:20]
Daniel Price
Posted by developer: Fixed as of the upcoming 5.6.23, 5.7.6 release, and here's the changelog entry: An "ALTER TABLE" operation that changed the name of a foreign key column resulted in a failure when reloading the foreign key constraint. The previous column name remained in the data dictionary cache instead of being evicted.