1261031 - ADI counts vastly over-counted

Status: RESOLVED FIXED

(Socorro :: General, task)

Description

[Peter Bengtsson [:peterbe]]

This morning (March 31st, crontabber job running *for* March 30th), the fetch-adi-from-hive job failed to complete because the database password for the secondary destination was incorrect. I corrected that this morning. 

But the job had failed 11 times since I caught this. Each time, it would successfully run the Hive query, then insert into raw_adi and raw_adi_logs on prod but when it came to writing into stage's DB it failed and the whole app thus failed. For each re-try of the whole crontabber app it would accumulate more and more counts on the prod DB.

Comment 1

[Peter Bengtsson [:peterbe]]

To undo this, I wipe away the numbers first: 

breakpad=> begin;
BEGIN
breakpad=> delete from raw_adi_logs where report_date >= '2016-03-30'; delete from raw_adi where date >= '2016-03-30'; delete from product_adu where adu_date >= '2016-03-30';
DELETE 2702260
DELETE 2695070
DELETE 149
breakpad=> commit;
COMMIT

Comment 2

[Peter Bengtsson [:peterbe]]

Next, I wind back the clock on the crontabber state:

breakpad=> begin;
BEGIN
breakpad=> update crontabber set last_success = last_success - interval '1 day', next_run = now() where app_name = 'fetch-adi-from-hive';
UPDATE 1
breakpad=> commit;
COMMIT

Comment 3

[Peter Bengtsson [:peterbe]]

Now raw_adi and raw_adi_logs look much more normal:

breakpad=> select sum(count) from raw_adi_logs where report_date >= '2016-03-30';
    sum
-----------
 150251605
(1 row)

breakpad=> select sum(count) from raw_adi_logs where report_date >= '2016-03-29' and report_date < '2016-03-30';
    sum
-----------
 150999725
(1 row)

Comment 4

[Peter Bengtsson [:peterbe]]

And...

breakpad=> select sum(adi_count) from raw_adi where date >= '2016-03-30';
    sum
-----------
 150014271
(1 row)

breakpad=> select sum(adi_count) from raw_adi where date >= '2016-03-29' and date < '2016-03-30';
    sum
-----------
 150760431
(1 row)

Comment 5

[Peter Bengtsson [:peterbe]]

Next, we need to rewind so the adu-matview (which populates product_adu from the raw_adi table):

breakpad=> begin;
BEGIN
breakpad=> update crontabber set last_success = last_success - interval '1 day', next_run=now() where app_name='adu-matview';
UPDATE 1
breakpad=> commit;
COMMIT

Comment 6

[Peter Bengtsson [:peterbe]]

And build-adu-matview:

breakpad=> begin;
BEGIN
breakpad=> update crontabber set last_success = last_success - interval '1 day', next_run=now() where app_name='build-adu-matview';
UPDATE 1
breakpad=> commit;
COMMIT

Comment 7

[Peter Bengtsson [:peterbe]]

Now the product_adu numbers look much better:


breakpad=> select sum(adu_count) from product_adu where adu_date >= '2016-03-30' and adu_date < '2016-03-31';
    sum
-----------
 121287666
(1 row)
breakpad=> select sum(adu_count) from product_adu where adu_date >= '2016-03-29' and adu_date < '2016-03-30';
    sum
-----------
 122065764
(1 row)

Comment 8

[Peter Bengtsson [:peterbe]]

Going to run full backfill for march 30th.

Comment 9

[Peter Bengtsson [:peterbe]]

Backfill complete for Mar 30