Page MenuHomePhabricator

Re-run omnirecipient repair to catch the few missed ones
Open, NormalPublic1 Story Points

Description

Per https://phabricator.wikimedia.org/T200240#4946379 I want to track re-running the omnirecipient repair job until the remaining rows are removed

For now I propose to run the job again with a 5 hour gap & then do some checks - & run again as appropriate

In preparation I reset the drupal param

drush vset civicrm_repair_omnirecipient_last_processed_timestamp "2017-02-16 00:00:00"

I have put up a gerrit for changing to 300 mins BUT it is failing on the time change part & to fix that I need to double check the time it should be changed to by retrieving a relevant record from spop & doing a quick check (although if it has already aged out of spop I will just adjust the test to match & it won't matter that much as there is no risk of us doing a re-retrieve & getting new dupes - I suspect that might be the case)

Event Timeline

Eileenmcnaughton triaged this task as Normal priority.
Eileenmcnaughton updated the task description. (Show Details)
Eileenmcnaughton updated the task description. (Show Details)

Change 489969 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Adjust different on omnirecipient repair - then I can kick it off & catch a few more

https://gerrit.wikimedia.org/r/489969

Eileenmcnaughton updated the task description. (Show Details)

Change 489969 merged by jenkins-bot:
[wikimedia/fundraising/crm@master] Adjust different on omnirecipient repair - then I can kick it off & catch a few more

https://gerrit.wikimedia.org/r/489969

Change 492941 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Repair omnirecipient with 14 hour gap

https://gerrit.wikimedia.org/r/492941

Change 492941 merged by jenkins-bot:
[wikimedia/fundraising/crm@master] Repair omnirecipient with 14 hour gap

https://gerrit.wikimedia.org/r/492941

Change 493134 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@deployment] (cherry-picked from master) Repair omnirecipient with 14 hour gap

https://gerrit.wikimedia.org/r/493134

Change 493134 abandoned by Eileen:
(cherry-picked from master) Repair omnirecipient with 14 hour gap

https://gerrit.wikimedia.org/r/493134

Eileenmcnaughton closed this task as Resolved.
Eileenmcnaughton reopened this task as Open.Mar 4 2019, 10:47 PM

Dang re-opening this - I realise I filtered on 'Sent' - perhaps for priority reasons but

SELECT      mp.contact_identifier, mp.recipient_action_datetime, mp.event_type,mp.contact_id,      mp2.contact_identifier as ci2, mp2.recipient_action_datetime as date_time2, mp2.event_type as event_type2,         TIMESTAMPDIFF(MINUTE,mp.recipient_action_datetime , mp2.recipient_action_datetime) as time_diff                  FROM civicrm_mailing_provider_data mp               INNER JOIN civicrm_mailing_provider_data mp2           ON mp.contact_identifier = mp2.contact_identifier              AND mp.mailing_identifier = mp2.mailing_identifier             AND mp.event_type = mp2.event_type  AND mp.recipient_action_datetime < mp2.recipient_action_datetime                     WHERE mp2.`contact_identifier` IS NOT NULL             AND TIMESTAMPDIFF(MINUTE,mp.recipient_action_datetime , mp2.recipient_action_datetime) IN (60,120,180,240,300,360,420,480,540,600,660,720,780,840,900,960,1020,1080,1140,1200,1260,1320,1380,1440)             AND mp.event_type = mp2.event_type           AND mp.recipient_action_datetime < '2017-04-18'       LIMIT 200;

OK so here is current for that search (it takes a long time to run - longer with out some filtering like

AND mp.recipient_action_datetime < '2017-04-18'
SELECT    count(*),        TIMESTAMPDIFF(MINUTE,mp.recipient_action_datetime , mp2.recipient_action_datetime) as time_diff                  FROM civicrm_mailing_provider_data mp               INNER JOIN civicrm_mailing_provider_data mp2           ON mp.contact_identifier = mp2.contact_identifier              AND mp.mailing_identifier = mp2.mailing_identifier             AND mp.event_type = mp2.event_type  AND mp.recipient_action_datetime < mp2.recipient_action_datetime                     WHERE mp2.`contact_identifier` IS NOT NULL             AND TIMESTAMPDIFF(MINUTE,mp.recipient_action_datetime , mp2.recipient_action_datetime) IN (840)     AND mp.recipient_action_datetime < '2017-04-18'         AND mp.event_type = mp2.event_type ;
count(*)time_diff
25794840
Eileenmcnaughton added a comment.EditedMar 11 2019, 8:09 PM

This is the generic query with variables to make it easy to re-use & edit

SET @fromDate = '2010-01-01';
SET @toDate= '2017-04-18';


SELECT    count(*),        TIMESTAMPDIFF(MINUTE,mp.recipient_action_datetime , mp2.recipient_action_datetime) as time_diff                 
 FROM civicrm_mailing_provider_data mp             
 INNER JOIN civicrm_mailing_provider_data mp2          
   ON mp.contact_identifier = mp2.contact_identifier    
   AND mp.mailing_identifier = mp2.mailing_identifier             
   AND mp.event_type = mp2.event_type  
   AND mp.recipient_action_datetime < mp2.recipient_action_datetime        
WHERE mp2.`contact_identifier` IS NOT NULL   
   AND TIMESTAMPDIFF(MINUTE,mp.recipient_action_datetime , mp2.recipient_action_datetime) IN (60,120,180,240,300,360,420,480,540,600,660,720,780,840,900,960,1020,1080,1140,1200,1260,1320,1380,1440)  
   AND mp.recipient_action_datetime < @toDate
   AND mp.recipient_action_datetime > @fromDate
   AND mp.event_type = mp2.event_type ;

Just checked status on the 420 run - it's up to

civicrm_repair_omnirecipient_last_processed_timestamp: "2017-07-16 00:00:00"

Eileenmcnaughton added a comment.EditedTue, Mar 26, 12:36 AM

civicrm_repair_omnirecipient_last_processed_timestamp: "2017-10-09 00:00:00"

(I think this run finishes early Nov when daylight savings kicks in so probably a few more days & then tweak the number)

Looks like it's crawling at the moment "2017-10-14 00:00:00'

 drush vget | grep omni
civicrm_repair_omnirecipient_last_processed_timestamp: "2017-10-29 00:00:00"