MySQL Forums
Forum List  »  InnoDB

Very odd issue with foreign key constraint failure
Posted by: Vesselin Kenashkov
Date: October 03, 2013 09:36AM

Hi,

We have a case where a REPLACE operation odd times throws
"Cannot add or update a child row: a foreign key constraint fails" & SQL State: 23000.
The exact message is:
"(`dbname`.`table1`, CONSTRAINT `table1_fk1_constraint` FOREIGN KEY (`class_id`) REFERENCES `table2` (`class_id`) ON DELETE CASCADE ON UPDATE CASCADE)"

The exact query is:
REPLACE
INTO table1
(
class_id, object_id, object_version, role_id, action_id
)
VALUES
(
37, 125128, 0, 11026, 1
)

The obvious reason would be that there is some inconsistency with the data in `table2`.class_id (as in 37 is not be there) but this is not the case. This table is read only - there are no write operations whatsoever there (none!) and the value we are trying to insert (37) does exist there (the app will fail immediately if it is not there). More over - there are thousands of queries like this one per day and they dont fail. Even this one executed again doesnt fail... The server is under load and I would think this is some sort of storage problem.

Does anyone know of similar problem? I think to just rerun the query when we get state 2300. Is there anything else we can do?

Thank you!
Vesko

Options: ReplyQuote


Subject
Views
Written By
Posted
Very odd issue with foreign key constraint failure
1929
October 03, 2013 09:36AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.