Last night's End of Day processing for AMO Collection stats (2012-10-21) failed with the following info: 2012/10/21 20:17:40 - Output to collection_counts.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Error inserting row into table [stats_collections_counts] with values: [2012-10-21], [36ec7258-2cae-4fe1-87eb-a5dfbbfbb3f0], ,  2012/10/21 20:17:40 - Output to collection_counts.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Cannot add or update a child row: a foreign key constraint fails (`addons_mozilla_org`.`stats_collections_counts`, CONSTRAINT `stats_collections_counts_ibfk_1` FOREIGN KEY (`collection_id`) REFERENCES `collections` (`id`)) I re-ran the job today at 10:06:43 PDT and it completed successfully. Please check if there is a problem in the database that would prevent this from working at the scheduled time.
Interestingly, the stats_collections_counts table in addons_mozilla_org has 4 columns defined: `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `collection_id` int(10) unsigned NOT NULL DEFAULT '0', `count` int(10) unsigned NOT NULL DEFAULT '0', `date` date NOT NULL DEFAULT '0000-00-00', And yet, the values trying to be inserted are: [2012-10-21] [36ec7258-2cae-4fe1-87eb-a5dfbbfbb3f0]   I'm pretty sure the first field is "date", and I'm guessing the last field is collection_id: mysql> select * from collections where id=2240920; Empty set (0.00 sec) And that's the problem you ran into. But the bigger problem is this value: 36ec7258-2cae-4fe1-87eb-a5dfbbfbb3f0 Where exactly is that supposed to go? It's possible the parent collection was not created because of a similar data type mismatch.....
The "36ec7258-2cae-4fe1-87eb-a5dfbbfbb3f0" appears to be a red herring - it's the collection's UUID, looked up in a previous step of the job. The actual fields used are `collection_id`, `count`, and `date`. I was about to poke in the a_addon_collection_request_totals in the metrics mysql db, but I can't connect to mpt at the moment.
OK, well the problem then is that the collection_id, 2240920, is not in the collections table. Can you debug that insert to see what may have failed from that?
That collection seems to be gone altogether now... mysql> select uuid, id, name from collections where uuid = '36ec7258-2cae-4fe1-87eb-a5dfbbfbb3f0'; Empty set (0.03 sec) Do collections get deleted? If so, maybe that's what happened here?
the collection was gone a long time ago, at least by 2012/10/21 20:17:40. I will look in the binary logs to see if I can see when it was deleted.
I couldn't find where in the binary logs the collection was deleted :(
I'm going to close this out for now - I haven't seen any more errors like this, and re-running worked without incident. If this kind of thing crops up again I'll reopen.
Status: NEW → RESOLVED
Last Resolved: 6 years ago
Resolution: --- → WORKSFORME
Product: mozilla.org → Data & BI Services Team
You need to log in before you can comment on or make changes to this bug.