@Cstone could this have triggered anything donor-facing via PayPal?

We have now had 4 donors reach out to us and we don't generally hear from super old donors whose donations are suspended.

#1512121 / CID 49554136 - Last billed September 19th, 2021
#1512052 / CID 46068848 - Last billed February 10th, 2021
#1512776 / CID 47286605 - Last billed December 8th, 2021
and another one I don't have to hand.

I guess they are receiving the cancelled by merchant notification via PayPal or something? Thanks for confirming! I'm not clear what the first paypal cancel date bug was or what we ran to fix it. Apologies if you have already explained this, Sandra is out this week and just in case it snowballs... Thanks!

@krobinson hmm I think this is a different issue, I'll make a separate phab. Both of these issues have to do with long inactive paypal subscriptions on their end.

The one mentioned above was us getting IPNs that would update the cancel date of an already long cancelled donation (so for one cancelled say in 2022 it would update to today) We fixed that issue and the data cleanup was for that.

This though seems to be something where the donors are being notified as you saw. I'll link the new phab here once I make it.

New phab filed to cancel failed PP recurring donations: https://phabricator.wikimedia.org/T373947

Change #1081414 had a related patch set uploaded (by Ejegg; author: Ejegg):

[wikimedia/fundraising/crm@master] Drop subscr_failed messages for cancelled recurrings

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

I think this SQL should update those cancel dates to the last good one from the logs:

update civicrm_contribution_recur r set cancel_date = (
  select max(l.cancel_date) from log_civicrm_contribution_recur l
  where l.id = r.id
)
where r.cancel_reason='Automatically cancelled for inactivity'
and r.cancel_date='1969-12-31 16:00:00';

Oops, that doesn't work because the log table is updated by a trigger on the main table. It'll need a temp table to store those values in:

create temporary table recur_temp as (
  select r.id, max(l.cancel_date) as cancel_date
  from civicrm_contribution_recur r
  inner join log_civicrm_contribution_recur l on r.id = l.id
  where r.cancel_reason='Automatically cancelled for inactivity'
  and r.cancel_date='1969-12-31 16:00:00' group by r.id
);

update civicrm_contribution_recur r
inner join recur_temp t on r.id = t.id
set r.cancel_date = t.cancel_date;

This runs in under 8 seconds on staging.

OK, this is all set.

Thanks @Ejegg does this mean this issue is fixed? @Cstone I believe the data is correct in Superset right? I recall that work was done too. I mean here: https://analytics.frdev.wikimedia.org/superset/dashboard/175/

The dates are fixed, but I haven't cancelled them on paypal's side yet. I'm going to set up a new recurring for myself and cancel it via the script to see if I get an email.

Change #1081414 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Drop subscr_failed messages for cancelled recurrings

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