DB Error: Constraint Violation

[onefiftyfour]

This happens only for some of my recurring payments . Here's some partial log snippets:

PEAR_Error->PEAR_Error("DB Error: constraint violation", -3, 16, (Array:2 ), "INSERT INTO civicrm_membership_payment (membership_id , contribution_id ) VAL...") ... Jan 18 20:56:24 [info] Contribution record updated successfully Jan 18 20:56:24 [info] Receipt sent Jan 18 20:56:24 [info] Success: Database updated

But, when I look at the contact record, there is no new contribution.

I have some contacts that were merged at some point and I had to manually fix the database tables so that the correct contribution_recur_id matches the correct membership_id, invoice_id, etc. I've been able to fix a number of these contacts so that future contributions are successfully updated in civi. But I don't know what is happening with this constraint violation. For these contacts all of the relevant tables look good.

I'm on wordpress, civicrm 4.6.23, com.drastikbydesign.stripe 4.7-dev (downloaded from here in Dec 2016)

Let me know if you want more of the log file.

[h-c-c]

I think more of the log is needed. I'd say update, but the Upgrader.php script needs some love....which I'm in the process of giving it.

[h-c-c]

Shoot, I realized that the useful information would be in a previous "fatal error" in your log because it shows what the parameter values are. At least that's how it would appear in Drupal....not sure about Wordpress. However, you may not want to post this publicly anyway. This is probably better suited to chat...feel free to ping me (peter) on Mattermost.

What the version you're running does when it receives an invoice.payment.succeeded event from Stripe, is try to run api.contribution.repeattransaction on the original contribution. You can piece together what happened yourself. If you aren't positive already, look for a paid invoice event in Stripe...which also rules out the possibility that the payment failed.

Customer id in hand, look at your civicrm_stripe_subscriptions table. Get the original invoice id corresponding to your customer. Search for the contribution that has this invoice id. Look at the contact id for this record.....does it still exist or does the contact record have a "1" in the is_deleted column?

If a missing or "is_deleted" contact was the issue, the new version would probably not help. Instead of using the original contribution with api.contribution.repeattransaction, it passes the recurring contribution id. The api in turn uses this to find the previous contribution in the recurring set. A missing contact or "is_deleted" would fail the same way I think.

[h-c-c]

Ok, I tested the is_delete =1, theory myself... that wouldn't cause the issue you're having. Do you by chance have multiple line items associated with this contribution? In practice, I believe this means a recurring membership and some other contribution on the same contribution page. That might cause an issue....according to @eileen, repeattransaction gets "grumpy" if there are multiple line items.

[onefiftyfour]

In the logs, each instance of this problem only differs with this line:

21 /srv/www/.../public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/Payment/BaseIPN.php(708): CRM_Core_Payment_BaseIPN->addRecurLineItems("111", Object(CRM_Contribute_BAO_Contribution))

That "111" is the contribution_recur_id.

I should have responded to your prior comment about deleted contacts. I know that these issues are with contacts not deleted.

There are not multiple line items with the contribution, just the membership dues. The contribution page is setup for the user to choose the default $5 or they can enter "other amount".

There's no pattern with the issue and if the contact used the other amount option or not. I've seen the issue with both cases. And I've seen no issue occuring with both cases.

If I choose to update to the latest 4.7-dev, Can I manually verify what should be happening with upgrader.php ? Is it just changes to db tables?

By the way, I'm using two stripe accounts. I have that setup correctly with the ppid's in the webhook and it is working.

[onefiftyfour]

Here's a full log instance with private info removed.

constraintviolation.txt

[onefiftyfour]

I just discovered an interesting pattern. In the civicrm_membership table, is_override = NULL for all of my problem memberships while is_override = 0 for my working memberships.

I've checked - if you take a recurring membership that has is_override = 0, then from within civicrm , edit the membership and check status overide, then save the edit. ... Then edit it again, uncheck status override. in the db is_override will be NULL.

Could this be the problem?

[h-c-c]

The issues currently with the upgrade script have to do mainly with db update 4601 not populating the processor_id fields. Maybe this is common knowledge and everyone has manually fixed their databases?

I just made the commit. If you want to test, checkout 4.7-dev_fix-db4601 in my fork.

There are not multiple line items with the contribution, just the membership dues.

Yeah, but the membership dues are the contribution with multiple line items, right?

Is there anything you're redacting from the ends of lines 4-6 in the log? It would be helpful to know what the constraint violation is. I think possibly, it's trying to pass a NULL in contribution id, or a contribution id that doesn't exist.

I'd like to wrap my head around how you're using this contribution page. So people can decide on their own recurring amount for membership dues? Or is the other amount a one-time deal in addition to the dues? Are pricesets being used?

It would be cool to understand what's actually happening, so you can prevent it. But I believe the new code could help you get out of the current situation of membership dues not being recorded. The reason is that the new code repeats the previous contribution associated with the recurring id not the original contribution. IF you retrofitted your plan descriptions with the membertype_N- as described in my blog, you could do this :

1. Edit your customer's subscription to a new membertype. (assuming you have multiple types!)

2. Once you verify the members level has changed in civi and see a new pending contribution, change back to the original membertype.

This will leave you with a single new pending charge that will be completed on the users next billing date. When the next cycle happens this completed one gets repeated....etc. All is groovy. If you see any invoice items in Stripe for the customer that don't cancel each other out exactly, you can delete them. This might happen if you took some time between editing back to the original subscription.

[h-c-c]

I've checked - if you take a recurring membership that has is_override = 0, then from within civicrm , edit the membership and check status overide, the save the edit. ... Then edit it again, uncheck status override. in the db is_override will be NULL.

This shouldn't matter.

Something worth looking for is this: are there multiple line items associated with a contribution that the webhook is trying to repeat? Here's how specifically to do this:

1. Isolate a problematic subscription and copy the civicrm invoice number from your subscriptions table.
2. Search the contributions table by this invoice number.
3. take the contribution id and search the civicrm_line_items table.

Do you get more that one result? If so, you've got multiple line items! And we know that api.contribution.repeattransaction does not like this! If this is the case we should consult @eileenmcnaughton for help.

[onefiftyfour]

Just one line item per contribution. I've checked two of the problem subscriptions.

My contribution page other amount is for membership only. You either choose the default $5 per month or you can pay whatever you want per month (minimum $5).

[h-c-c]

Ok, that's good there aren't multiple line items. I'd say you need more information from your logs to zero in on exactly what's happening with that constraint violation. It would be good to know what values are being passed.....I just don't see it in the backtrace. Was there more information at the ends of lines 3-6 where the ellipses are? I think the Drupal watchdog would report more info in this case...sorry, I know that's not helpful.

You could approach it from the other direction also. You could take a member who's contribution hasn't been recorded because of this issue and try repeating the contribution using the api explorer. It may give you a useful error message. If it works, then the contribution is recorded. Although that would be disconcerting. :P

[onefiftyfour]

I've found the problem using the API explorer and the mysql general log turned on briefly. First of all I noticed that the INSERT with the constraint violation was trying to insert the original contribution_id into the membership_id column.
It was getting that value from the civicrm_line_item table. (even with one entry per contribution id) In all of my problem cases, entity_id = contribution_id in that table. Once I changed entity_id to the actual membership_id, I got the repeattransaction to work without an error.

So what do you think caused the entity_id to equal contribution_id in the civicrm_line_item table? Possibly merging duplicate contacts.

[eileenmcnaughton]

"So what do you think caused the entity_id to equal contribution_id in the civicrm_line_item table? Possibly merging duplicate contacts."

There was an issue fixed in 4.7.14 or 15 related to that. You are probably on an earlier 4.7 version?

[onefiftyfour]

I'm actually on 4.6.23.

[eileenmcnaughton]

hmm I don't know if 4.6.23 is affected by that or not.

[onefiftyfour]

One reason for the entity_id = contribution_id could be this situation: We had paypal recurring contributions setup, then we switched to stripe (setting up a new payment processor). If a customer had a paypal recurring contribution setup, then canceled the paypal setup, then renewed with the new stripe PP. I've noticed that for paypal - the line items are normally entity_id = contribution_id

[eileenmcnaughton]

Hmm - interesting. Are you able to replicate it? If so it would be worth seeing if it is also replicable on 4.7

[onefiftyfour]

I haven't tried to replicate and I don't think I'll bother. We won't be having this problem now that I know how to fix it. We will eventually have everyone migrated to stripe. But, this scenario might be something for the developers to think about.

[eileenmcnaughton]

yeah - good to know google knows about it.....

[onefiftyfour]

I'm seeing entity_id = contribution_id when a contact with an annual membership, renews with a monthly recurring membership.

[eileenmcnaughton]

On civicrm 4.7.17? (well 4.7.16 until Wed, but I'd confirm on 4.7.17 once out)

[onefiftyfour]

I'm using 4.6.26.

[eileenmcnaughton]

ah OK, I don't really touch 4.6 at all anymore. I did think that might be fixed in latest 4.7 but you would need to test

[onefiftyfour]

So, I'm seeing this happen regularly now. Is it considered a bug in civicrm 4.6 and should I report it to the civicrm people or is there a way to address it here?

To be clear, what I see happening is when an existing nonrecurring annual membership converts to recurring monthly, the entity_id = contribution_id in the civicrm_line_item table. Subsequently, at each recurring transaction, a Constraint Violation occurs because, I suppose the repeat transaction mistakenly is using the contribution_id for the membership_id.

[eileenmcnaughton]

There was definitely a bug like that which was fixed in 4.7. It did take a lot of time to fix and the fix won't backport easily.

I suspect it would be hard to get someone to fix it in 4.6 because of the time involved and because the option of upgrading to 4.7 already exists