Bug #62361 Foreign key constraint error while the key exist in the parent table

• Submitted: 6 Sep 2011 10:16
• Modified: 16 Sep 2011 9:06
• Reporter: Cyril SCETBON
• Status: Not a Bug
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.5.15,5.1.58
• OS: Any
• CPU Architecture: Any
• Tags: fk, innodb

[6 Sep 2011 10:16] Cyril SCETBON

Description:
I have a parent table A and a child table B. 
B.ApplicationRef references A.ApplicationRef with CASCADE UPDATE AND DELETE

I have multiple threaded manipulating theses 2 tables and if some threads delete records from the parent table the following sequence fails on the foreign key constraint check :

BEGIN
...
INSERT INTO A(ApplicationRef,...) VALUES('XXX',...); --> Works
INSERT INTO B(...,ApplicationRef,...) VALUES(...,'XXX',...); --> Fails 

The error message is "Cannot add or update a child row: a foreign key constraint fails..."

This job is done by a Java program. When I get the FK error I check in table A if the record with ApplicationRef exists and it's positive !! So why the foreign key constraint fails to find it ?

For your information, other sessions are deleting records from table A but not the record with ApplicationRef='XXX'
If other sessions don't delete records, the FK constraint does not fail anymore

How to repeat:
For now, I can't provide you a test code

[6 Sep 2011 16:07] Cyril SCETBON

Same error with 5.1.58

[7 Sep 2011 16:46] Cyril SCETBON

version added

[12 Sep 2011 8:05] Cyril SCETBON

we still have the issue after having removed the DELETE orders in all sessions. However, we have some UPDATE parent-table SET OLDPK=NEWPK (PK is the foreign key referenced by the child-table)

any one ?

[12 Sep 2011 21:32] Cyril SCETBON

I checked without the last UPDATE and it seems to not be the cause as we still meet the issue.
finallly, the only cause, seems to be the multithreading with foreign keys

[16 Sep 2011 9:04] Cyril SCETBON

investigating the code in depth showed that it was not managing correctly deadlocks situations

[16 Sep 2011 9:06] Cyril SCETBON

I was talking about the java code application of course :)

Sorry for this bug report