804217 - Intermittent failure exporting nightly AMO Collection stats

Status: RESOLVED WORKSFORME

(Data & BI Services Team :: DB: MySQL, task)

Description

[Mark Reid [:mreid]]

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.

Comment 1

[Sheeri Cabral [:sheeri]]

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.....

Comment 2

[Mark Reid [:mreid]]

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.

Comment 3

[Sheeri Cabral [:sheeri]]

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?

Comment 4

[Mark Reid [:mreid]]

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?

Comment 5

[Sheeri Cabral [:sheeri]]

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.

Comment 6

[Sheeri Cabral [:sheeri]]

I couldn't find where in the binary logs the collection was deleted :(

Comment 7

[Mark Reid [:mreid]]

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.