MySQL Bugs: #74862: ALTER TABLE USES STALE DICTIONARY OBJECT DURING FOREIGN KEY LOAD

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:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.7.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Renaming a column in the parent table of a FOREIGN KEY constraint is introducing an inconsistency in the InnoDB data dictionary.

How to repeat:
create table t1(a int not null,b int not null,index idx(a));
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;

ALTER TABLE t1 CHANGE a id INT;

2014-11-14T10:15:19.757236Z 1 [ERROR] InnoDB: dict_load_foreigns() returned 38 for ALTER TABLE t1 CHANGE a id INT
2014-11-14 12:15:19 0x7fffe03e7700  InnoDB: Assertion failure in thread 140736955578112 in file handler0alter.cc line 6585
InnoDB: Failing assertion: 0

The assertion failure is a debug assertion, so a non-debug server does not immediately crash.

(dberr_t)38 == DB_CANNOT_ADD_CONSTRAINT.

Suggested fix:
Ensure that the column is renamed also in SYS_FOREIGN_COLS and dict_foreign_t.

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.

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.

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

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.