MySQL Bugs: #65015: mysql server hangs on update or delete operation

Bug #65015	mysql server hangs on update or delete operation
Submitted:	17 Apr 2012 20:47	Modified:	24 Dec 2012 9:24
Reporter:	smith bob	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.6.5	OS:	MacOS (mysql-5.6.5-osx10.6-x86_64)
Assigned to:		CPU Architecture:	Any
Tags:	circular references, delete, freeze, hang, regression, UPDATE

Description:
Our database schema worked perfectly fine on versions mysql-5.5.15-osx10.6-x86_64 (InnoDB), but now the system hangs on any update or delete on  mysql-5.6.5-osx10.6-x86_64  (InnoDB).

We have a complex schema with various circular dependencies (wich may not all be direct loops, i.e. a -> b -> c ->d-> a   and  e->f->a->e and a->c->e->a  and g->a->g). The result is a graph with many cycles.  

When I updated mysql version to 5.6.5, most things seemed to be working. I was able to recreate the tables from scratch, establish the circular FK constraints with alter table, and I can insert data into the tables. However, when I attempt to update or delete any row in one of the tables (even a simple lookup table), mysql hangs, my cpu usage goes to 100% and the system stays unresponsive forever.  This issue seems to occur only after a certain amount of loops & FK constraints are established in the schema. For example, 2 tables with circular references a -> b -> a does not seem to cause a problem. But a more complex graph does. 

How to repeat:
create a complex data structure, insert into one of the tables and attempt to update an item.

Please, provide a (simple) repeatable test case, if possible.

Please see the attached archive bug-demo.tar.gz. Load the script \. create-tables.sql.

Thank you for the report.

Verified as described. To repeat one need to add "set default_storage_engine='innodb';" to the beginning of create-tables.sql

Is this problem being looked into?

Same problem experienced with m8, on both MacOS (Lion) & Linux (CentOS 6.2) with a different setup, on InnoDB tables.
The bug stops happening when we remove certain foreign keys, even when the hanging query is totally unrelated to the tables involved in the removed relationship.

Do you think problem will be resolved soon? We would love to upgrade to v5.6 but we cannot do so with this issue, and would be surprised how anyone else can do so in this environment.

This is a duplicate of bug#64274, fixed in 5.6.6.