Intermittent failure exporting nightly AMO Collection stats

RESOLVED WORKSFORME

Status

Data & BI Services Team
DB: MySQL
RESOLVED WORKSFORME
6 years ago
4 years ago

People

(Reporter: mreid, Unassigned)

Tracking

Details

(Reporter)

Description

6 years ago
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], [1], [2240920]
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]
[1]
[2240920]

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.....
(Reporter)

Comment 2

6 years ago
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?
(Reporter)

Comment 4

6 years ago
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 :(
(Reporter)

Comment 7

6 years ago
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.