7

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?

Unix One
  • 1,151
  • 7
  • 14
Niet the Dark Absol
  • 320,036
  • 81
  • 464
  • 592
  • Does your `shelter` table have data in that matches your `where` conditions? – TZHX Mar 21 '16 at 07:12
  • 3
    Share tables structure. – itzmukeshy7 Mar 21 '16 at 07:12
  • @TZHX 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. – Niet the Dark Absol Mar 21 '16 at 07:14
  • 3
    It's worth noting that the error is extremely intermittent, practically one in a million. – Niet the Dark Absol Mar 21 '16 at 07:19
  • it is "select :userid, `id` from..." or "select userid, `id` from..." – Zafar Malik Mar 21 '16 at 07:23
  • @ZafarMalik That is a parameter, the user ID is passed in from the outside to be saved with each row. – Niet the Dark Absol Mar 21 '16 at 07:35
  • I would check triggers on table `shelter` and `shelter_pages`.. – Yuri Mar 21 '16 at 07:35
  • 1
    @Yuri There are no triggers. – Niet the Dark Absol Mar 21 '16 at 07:35
  • Is shelter.id nullable? If yes, are there any records there with null values? – Shadow Mar 21 '16 at 07:41
  • @RyanVincent There is a transaction wrapping the whole AJAX request that this is included in. – Niet the Dark Absol Mar 21 '16 at 08:09
  • @Shadow No, `shelter.id` is not nullable. – Niet the Dark Absol Mar 21 '16 at 08:10
  • 3
    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 :) – Allan S. Hansen Mar 21 '16 at 08:13
  • @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? – Niet the Dark Absol Mar 21 '16 at 08:15
  • 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 :) – Allan S. Hansen Mar 21 '16 at 08:17
  • @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? – Niet the Dark Absol Mar 21 '16 at 08:49
  • 1
    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. – Allan S. Hansen Mar 21 '16 at 09:17
  • this [SO question](http://stackoverflow.com/a/21489486/985375) could give some insight – lp1051 Apr 01 '16 at 21:38
  • 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` –  May 31 '16 at 18:28
  • 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. – Unix One Jul 03 '16 at 04:28
  • @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! – Niet the Dark Absol Jul 03 '16 at 09:00
  • 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. – Unix One Jul 03 '16 at 14:30

1 Answers1

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.

Unix One
  • 1,151
  • 7
  • 14