MySQL Bugs: #75977: More FK list / RB-tree size mismatches even in versions => 5.5.40

Bug #75977	More FK list / RB-tree size mismatches even in versions => 5.5.40
Submitted:	20 Feb 2015 11:02	Modified:	22 Aug 2015 11:44
Reporter:	Hartmut Holzgraefe	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	MySQL 5.5.40	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	assertion failure, foreign keys, innodb

Description:
Even with bug #73650 (private) fixed there seem to be cases 
where the list of referenced keys and the corresponding 
red-black trees (introduced in 5.5.39) are getting out of
sync and so are triggering the 

  UT_LIST_GET_LEN(table->referenced_list) == rbt_size(table->referenced_rbt)

assertion in dict_foreign_find() in innodb/dict/dict0dict.c

Bug #73761 was closed as duplicate of #73650 (but not marked 
private) but it's not clear there whether the different 
incidents reported on it are indeed fixed in 5.5.40

Debian bug https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=768489
shows the same assertion failure, but there the version is clearly
MySQL 5.5.40 which *should* have this fixed.

And I've also seen one case where the same assertion was triggered
in MariaDB 5.5.41 ... unfortunately there are not much details beyond
this that I can share. Backtrace was the same as with the other cases
and the statement that lead to it was of the form

  ALTER TABLE `db1`.`t1` CHANGE COLUMN `some_id` `some_id` INT NOT NULL;

Unfortunately the table this happened on had since been dropped and
the some_id column didn't exist yet in the most recent schema version
that could be recovered.

Nonetheless we have at least two cases (Debian bug report and mine)
where the 

  UT_LIST_GET_LEN(table->referenced_list) == rbt_size(table->referenced_rbt)

assertion was hit in versions based on 5.5.40 and beyond that have the
fix for bug #73650 applied. So there still seem to be other unhandled
edge cases where the referenced_list and referenced_rbt structures get
out of sync ...

How to repeat:
Unfortunately I don't have anything better than

  Do some ALTER operation on a table with foreign
  key constraints ...

yet ...

Suggested fix:
Identify and fix all cases where the "referenced" list and RB-tree can get out of sync ...

Workaround for now: roll back to version 5.5.38

need to check if fix for http://bugs.mysql.com/bug.php?id=75684 helps...

indeed, even current 5.5 has more problems with FK, but I didn't get this assert.
Anyway, we should keep an eye on:
Bug 20762798 - FK DDL: CRASH IN DICT_FOREIGN_REMOVE_FROM_CACHE

Hartmut, please check with 5.5.45.  There is this bug fix noted that could explain this:

InnoDB: An assertion was raised when InnoDB attempted to dereference a NULL foreign key object. (Bug #20762798)