Using this query, I was able to pull out contacts that tried to cancel but failed:

select a.created_date as "Activity Creation Date", a.source_record_id as "Contribution Recur ID",ac.contact_id as "Contact ID", cr.contribution_status_id as "Recur Status" from civicrm_activity as a join civicrm_contribution_recur as cr on a.source_record_id = cr.id join civicrm_activity_contact ac on a.id = ac.activity_id where a.activity_type_id=53 and a.subject LIKE "Donor cancelled recurring through the Donor Portal%" and cr.contribution_status_id != 3  order by a.created_date desc limit 100;

Here's the result:

Where Status ID: 5: In Progress and 4: Failed

The activity creation date can also be thought of as the cancelled date.

Still looking to why these transactions failed to cancel, though one of these transactions is mine which I cancelled and restored. Please confirm if I should go ahead and manually cancel these recurrings @krobinson

Yes, we need to cancel these so please go ahead and cancel. We will also need to refund any that have processed since the cancel was actioned. Is there a way to automate that @Damilare ? cc. @MBeat33

I'm also a little confused by the last two, as they seem to be from before the portal went live to donors. Are all of these failures due to the donor portal?

I can refund any donations from that list that processed after the cancel date

I think we may need to do outreach to the donors too @MBeat33, otherwise they may follow up questioning the refund.

@Damilare we noticed CID 39206274 isn't on the list you shared. Is it possible there are any others? As two donors reaching out indicates more than a dozen cases (we used to estimate one donor would reach out for every 50-100 live cases). Thanks for confirming!

Hi @krobinson 39206274 had already been cancelled at the time of running the query as such it didn't fulfil the set requirement to be added to the list. I'd check again to see if I missed anything.

Please ignore the last 2 on the list as they are from Elliott's and I testing at the initial phase of deploying the Donor Portal.

Change #1310605 had a related patch set uploaded (by Damilare Adedoyin; author: Damilare Adedoyin):

[wikimedia/fundraising/crm@master] Add logging and ensure the contribution recur update returns a result

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

The donor followups are all set. Three had no donations after the donor attempted the cancellation.

Change #1310605 abandoned by Damilare Adedoyin:

[wikimedia/fundraising/crm@master] Add logging and ensure the contribution recur update returns a result

Reason:

Abandoning in favour of the upstream fix

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

I've identified the source of this issue. The recurring contribution isn't being updated properly. The API call to update the contribution recur succeeds, but then the next call to queue the email fails due to a DB deadlock on the queue table (presumably the consumer is reading and locks the table). Once that happens, core code in the DB package rollsback the transaction, wiping out the contribution recur update. It retries, but it only retries the last statement that failed (the insert into the queue table) and not the whole transaction, which results in the loss of the statements that came before (the contribution recur update here). It should actually retry the whole transaction that it rolled back, not just the last statement.

This is some fundamental code in Civi (actually upstream in PEAR DB which is very old), so it might take a little time to fix as it requires careful thought and review. That said, we know what the problems is here, we know it is rare and we can re-run the query that Dami posted above to handle any with this problem in the meantime. It started due to code changes on our end not too long before we see the problem start happening in the table above.

To find the log entries, in /srv/org.wikimedia.civicrm/private/log, you can do:

grep -rP '(?<!\()RECURID(?!\))' . | grep "CONTACTID"

Thanks for the extra info @Lars

Quick follow up - how often should we rerun the query? Should it be a weekly thing and will there be a way of knowing if any have since processed (to do any subsequent refunds)?

thanks!

@krobinson Yes, once per week sounds like plenty. Will flag if any process.

Upstream patch

Thanks, @Lars for the update. I'll send a followup note to the donor.

Cancelled a few more, none had been charged since cancellation.

Change #1321676 had a related patch set uploaded (by Lars SG; author: Lars SG):

[wikimedia/fundraising/crm@master] Prevent retry of single statement when within db transaction

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

Change #1321676 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Prevent retry of single statement when within db transaction

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

@MBeat33 The fix has been deployed for this. There are no recurrings with this issue currently, but I will keep an eye on it for the next couple weeks before closing as these are fairly rare anyways.