Impossible MySQL insert ... select foreign key constraint error‽

Question

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?

Sidran

Does your shelter table have data in that matches your where conditions? – Autochthon

Share tables structure. – Berlyn

@Autochthon Yes, thousands of rows in fact, from which the random selection of 30 is pulled. I don't think it matters though, as an empty insert..select just inserts zero rows. – Sidran

It's worth noting that the error is extremely intermittent, practically one in a million. – Sidran

it is "select :userid, id from..." or "select userid, id from..." – Marivaux

@ZafarMalik That is a parameter, the user ID is passed in from the outside to be saved with each row. – Sidran

I would check triggers on table shelter and shelter_pages.. – Professionalize

@Professionalize There are no triggers. – Sidran

Is shelter.id nullable? If yes, are there any records there with null values? – Toland

@RyanVincent There is a transaction wrapping the whole AJAX request that this is included in. – Sidran

@Toland No, shelter.id is not nullable. – Sidran

Add some error handling so when it fails, you'll get the exact data that fails - and then you can use the data to debug. The reason it's sporadic error is because it is data related and you're taking 'random' data, so the error will only happen when you randomly select the data that makes it fail. So it's, debug time :) – Perpendicular

@AllanS.Hansen How would I go about debugging out the "rows that were randomly selected" in this query, since it's a single insert..select query? – Sidran

Change it to select into temporary structure, use temporary structure to insert final then you can dumb temporary structure when it goes bad. Remember, it's for debug purpose, not something that should live for ever in a production environment :) – Perpendicular

@AllanS.Hansen Hmm. Wouldn't that just make the error more likely, as it makes the system more prone to race conditions? The reason I did the select..insert was in the hopes it would be reasonably atomic without locking the entire table. I realise that's idealistic, but I wonder... Would insert ignore..select work here, by having it drop the rows that fail the foreign key? – Sidran

How you debug it is fully up to you - the key point I was trying to make is that it seems like data errors when it's sporadic - so if wanting to solve it, you'll need to figure out which data is the problem. How you identify the data is up to you, but personally I opt for the easy/fastest way when it's not code for production purposes. If it is product purpose, transactional control etc would properly be needed. – Perpendicular

this SO question could give some insight – Mainis

thinking that @AllanS.Hansen might have a very strong point, please do try changing your query a bit and share results if you wish. insert into shelter_pages (userid,relid) select :userid, distinct(id) from shelter where stage='egg' order by rand() limit 30 – Wolfish

One way this could happen is if your transaction isolation level was set to dirty read. If so, the select could be reading uncommitted data that would fail the foreign key constraints during the insert. It's a complete shot in the dark, but maybe worth checking. – Psychogenesis

@UnixOne I think you might have nailed it. That is indeed the isolation level I am using (READ UNCOMMITTED), due to the stricter isolation levels causing huge deadlock problems. I've "fixed" it with INSERT IGNORE but it's definitely something I need to keep in mind. Thank you! – Sidran

OK, phew, sanity returns and there's no bug to blame. I'll write this as an answer so whoever finds this doesn't have to go through all comments. – Psychogenesis

Answer 1

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.

Psychogenesis