This has me baffled, here's hoping someone can help.
Query:
insert into `shelter_pages` (`userid`,`relid`)
select :userid, `id` from `shelter` where `stage`='egg' order by rand() limit 30
Simple, right? Take 30 random rows meeting a condition, and save them in the "pages" table along with the user id.
The error:
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`db_name`.`shelter_pages`, CONSTRAINT `shelter_pages_ibfk_2` FOREIGN KEY (`relid`) REFERENCES `shelter` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
Well how can that be? It's literally taking these `shelter`.`id` values in the SELECT query and INSERTing them, how can the foreign key check possibly fail?
If it's of any significance, the table in question is fairly "busy" - it is part of a gameplay element where any player can "adopt" from the shelter, thus deleting the row. Is this as simple as a race condition in what I thought would be an atomic operation?
This is likely happening because you have your transaction isolation level set to "dirty read" (i.e. READ UNCOMMITTED). This means that the SELECT could be reading uncommitted data that would fail the foreign key constraints during the INSERT.