Error relating this subscription id...

[scardinius]

Hi,

I'd like to resolve my problem with such errors like below and I need some suggestion. Could you help me?

Error relating this subscription id (sub_qwertyxyz) to the one in civicrm_stripe_subscriptions

• I'm using 4.7-dev + CiviCRM 4.7.13
• recurring contributions exist in civicrm, subscription on stripe also exist. I can linked them by email of contact
• civicrm_stripe_subscriptions doesn't contain a record related to the subscription
• I was debuging method doRecurPayment() and I don't know why stripe doesn't insert any subcription. https://github.com/drastik/com.drastikbydesign.stripe/blob/4.7-dev/CRM/Core/Payment/Stripe.php#L821
• stripe recalls the same event to endpoint

Any suggestion? Thank you in advance.

[drastik]

In all of your updates, @h-c-c, did you make use of the civicrm_stripe_subscriptions table? Existing code didn't really need it. It was there because "it could be useful later".

I'm just debating if we need to continue to bother with dead weight.

[laryn]

@scardinius That sounds a lot like what I was seeing after upgrading the extension. If you've upgraded the Stripe extension recently, you may want to look here:

https://github.com/drastik/com.drastikbydesign.stripe/issues/187

[h-c-c]

@scardinius When you look up this subscription in Stripe, does it relate to a non-test recurring contribution that you care about? If you had another civi instance that was used for testing, this will happen and can be safely ignored. If you have such test subscriptions, you can delete them in Stripe to make those errors go away.

civicrm_stripe_subscriptions doesn't contain a record related to the subscription

Does this mean in your civicrm_stripe_subscriptions table you have some NULL values in the subscription_id column? That would be descriptive of what @laryn referenced. If so, I can walk you through how to re-run the update script again to fill in any gaps. Do you have a subscription_id column at all? If not, that would indicate the update script still needs to be run.

Or do you mean that the customer in question has no records in the civicrm_stripe_subsccriptions table? And there no new records are being created in this table? There would other errors if this were happening I think.

Are you using installments?

In all of your updates, @h-c-c, did you make use of the civicrm_stripe_subscriptions table? Existing code didn't really need it. It was there because "it could be useful later". I'm just debating if we need to continue to bother with dead weight.

@drastik Oh yes, we're currently using the civicrm_stripe_subscriptions table. That's how we relate a subscription to a recurring contribution id. This allows us to have multiple subscriptions per customer. I'd say that was good forethought to add it!

[h-c-c]

Hey @scardinius, any update on this?

[scardinius]

Hey @h-c-c, thx for your interest.

This is a case where subscriptions don't exist in civicrm_stripe_subsccriptions at all. civicrm_stripe_subscriptions table has column subscription_id.

I thought that this error is linked to old transaction but this isn't true. Also current transactions have the same problem. And in general we have more customers than subscriptions.

[h-c-c]

Hey @scardinius I can't tell from your post if your subscription_id column has anything in it. Can you tell me if it has any values present? It should have values like sub_qwertysadfgasdf. What is in this column?

You could have run into #192. That would prevent new entries in the civicrm_stripe_subscriptions table. Can you confirm that you have upgraded to the latest 4.7-dev version which addresses this?

[scardinius]

Hey @h-c-c I meant that I have two cases:

1. the record in civicrm_stripe_subsccriptions is complete (including subscription_id column with valid values)
2. the record doesn't exist at all

Yes, I have latest version.

[h-c-c]

2. the record doesn't exist at all

Just for clarity, by this you meant, "no new records are being created at all", right?

Are you saying that the "Error relating this subscription id " errors are limited to only new recurring contributions, or are you saying they happen for all recurring contributions for which you have valid subscription_id values in the civicrm_stripe_subscriptions table?

In other words, does this happen just for new recurring contributions or for all made in the past.

I'm still trying to work out the scope of this issue.

Yes, I have latest version.

And you ran the database upgrade presented to you when visiting https://[yourdomain.com]/civicrm/admin/extensions?reset=1 ?

If you did the db upgrade, I think you may have to turn on more debugging options to see the error. I suspect it is a mysql error.

Turn on all the options here and see if you can learn anything new when you make a new test recurring contribution:

https://[yourdomain.com]/civicrm/admin/setting/debug?reset=1

If you don't see any errors when making a test recurring contribution, then turn on mysql logging in civicrm. Prepare for bleeding eyeballs....this will produce a lot of output.

For this you'd add define('CIVICRM_DEBUG_LOG_QUERY', 'backtrace'); to your civicrm.settings.php file.

[h-c-c]

Hey @scardinius is this still an issue? If so, can you get back to me with answers to the questions in the previous post? Thanks

[scardinius]

Hi @h-c-c

I found out that this error occurs after event customer.subscription.deleted. Stripe can cancel subscriptions because of several reasons.

After such event such errors occur in civilog for about 20 times.

it seems like unnecessary calls after deletion...

[h-c-c]

Hey @scardinius , can you elaborate on the issue? I don't understand what happened.

Stripe can cancel subscriptions because of several reasons.

What are these these several reasons?

Which API version are you using? From my experience, if a customers subscription payments fail for long enough I will receive a customer.subscription.updated event, but not deleted event. I've never encountered a subscription being deleted, unless I explicitly do it in the Stripe UI.

If your API version (listed in your Stripe UI) works as you say, then this is an important issue that we need to make users aware of.

[rthouvenin]

Hi, reviving this issue with more info.

From what I can see, the error (not finding a subscription id in the table) occurs for 2 reasons:

• Subscription manually created in Stripe dashboard. Of course CiviCRM does not know about it.
• Subscription cancelled automatically by Stripe after many failed payments. What I've seen is that the webhook notification of the last failed invoice can be done just after the notification of the canceled subscription. So what happens is:
  • Stripe calls the webhook about canceled subscription
  • The extension cancels the associated recurring donation, and deletes the subscription from civicrm_stripe_subscription
  • Stripe calls the webhook about failed payment
  • The extension cannot find the subscription (as it deleted the previous second) and throws the fatal error.

The solutions I can see are:

• Ignore missing subscriptions when the event is invoice.payment_failed. CiviCRM DB will miss one failed contribution, but I guess this could be acceptable. This is the cheapest solution.
• Do some guesswork: when the subscription cannot be found but there is a Stripe recurring contribution that was canceled, assume this is the one. If several, take the latest or maybe give up. Doesn't sound like a lot of work, but guesswork can be risky if we miss use cases where the guess is wrong.
• Do not deleted subscriptions from the table, do a soft delete instead. This way, the extension can still process events after the subscription is canceled. Requires schema update (extra column in subscription table)

What do you think?

[rthouvenin]

In fact, I can already see how guesswork is wrong: if a subscription is canceled and a new one created manually (happens commonly when staff updates the donation amount on donor's request), then assuming the missing subscription is the canceled recurring contribution is wrong. This leaves us only the other two solutions, or one that I didn't think of.

[h-c-c]

@rthouvenin Oooh, I see. An invoice can persist after a subscription is canceled, and could even be paid if it is re-opened after being dormant.

Soft delete is the way to go! We can add an is_deleted column to civicrm_stripe_subscriptions. I'll work up a patch.

Thanks!

if a subscription is canceled and a new one created manually (happens commonly when staff updates the donation amount on donor's request),

We don't actually support this yet. Do you see this as something that can be done within Civi? I don't see any way of creating a backend recurring contribution?

I think this wouldn't be too hard if it were initiated in Stripe and handled by the webhook.

[rthouvenin]

Thanks for working on a patch! Let me know if I can be of any help. For sure I will help test it.

I think you're right that creating the contribution in Civi from the webhook if it does not exist shouldn't be too hard, easier that offering a form to edit a Stripe subscription from Civi. In any case I'd say this is a much less urgent use case for us.

[h-c-c]

I think you're right that creating the contribution in Civi from the webhook if it does not exist shouldn't be too hard, easier that offering a form to edit a Stripe subscription from Civi. In any case I'd say this is a much less urgent use case for us.

And this isn't too far off from my subscription editing feature, for which I have a patch ready, btw. :-P

[rthouvenin]

@h-c-c Any estimate of when you could release a patch? Not to put any pressure, just want to give myself an idea of for how long I should ignore these errors. Thanks