PayPal IPN random fails

krileon wrote:

CBSubs not logging anything is concerning. That usually means CBSubs wasn't loaded yet since our logging hooks directly into PHP error handler. It's likely there's something else on the site possibly causing your issue. You mentioned both sites. Is this happening on both sites or only 1 of them?


 

It's happening on both sites

There's also a potential concern in your access log. Notice the one that succeeded has &cbpgacctno=2 and the one that failed has &cbpgacctno=1. cbpgacctno is the gateway id. So the one that succeeded has a gateway id of 2 and the one that failed has a gateway id of 1. Please check your payment gateways within CBSubs > Gateways and review your settings for the gateway with an id of 1. Seams like you have 2 PayPal gateways?

I have 6 PayPal gateways, that's what I was referring to with the different gateway owners and secondary gateways. 
Both sites have close to identical setups. 

There's one main plan (now that you have implemented the payment options, thank you) and several "sub" plans. 

• Users have to register and get access to the site via the main plan, which has 2 gateways: my PayPal and my Stripe. Both have Owner=0
• Once they are members, users can take classes from a handful of teachers on the site. 
• These teachers have their own PayPal gateway (Owner 1 through 5) which are linked to their individual PayPal accounts and I don't see or control.
• Right now I have only 3 active teachers and all 3 are having issues, including me with Owner=0. 

I've had this setup for a few years and has been working fine until the last upgrade from J3, PHP 7.4 and CBSubs 4.6.1+build.2023.05.17.16.17.22.cdc1f46c2 to Joomla 4, PHP 8.1 and CBSubs 4.6.2+build.2023.08.28.17.18.16.905ab5925 

Here's an example of a &cbpgacctno=1 that passed OK 


And here's an example of a &cbpgacctno=2 that failed with error 500.


This is why I can't make heads or tails of it. Some go and others don't. So only for some, the error is stopping the whole process early on. I agree that there's some serious concern. 

One possibility is that the error is happening when it's first trying to write to the database, failing and returning error 500. Remember that when I tried to enter the failing urls I always had the error "1062 Duplicate entry '12' for key 'jos_cbsubs_notifications.PRIMARY'" This is a wild guess, but it's all I can think of. 

On a side note: 


Here are the latest fails:


Thank you for your help on this.
Tomás

You may also want to consider getting rid of the multiple PayPal setups. I recommend using Stripe and handling payouts through Stripe. Effectively you would pay them instead of trying to link multiple PayPal accounts to a single system. You could of course do the same with PayPal as well, but Stripe lets you automate this using Connect. PayPal might have an automated way as well, but I've no idea what it is.

One possibility is that the error is happening when it's first trying to write to the database, failing and returning error 500. Remember that when I tried to enter the failing urls I always had the error "1062 Duplicate entry '12' for key 'jos_cbsubs_notifications.PRIMARY'" This is a wild guess, but it's all I can think of.

You can try purging notifications by emptying the _cbsubs_notifications table and see if that stops that from happening. If you need those notifications just make a copy of the table before doing this (e.g. _cbsubs_notifications_backup). It's possible something broke the auto incrementing of the table.

krileon wrote:

You may also want to consider getting rid of the multiple PayPal setups. I recommend using Stripe and handling payouts through Stripe. Effectively you would pay them instead of trying to link multiple PayPal accounts to a single system. You could of course do the same with PayPal as well, but Stripe lets you automate this using Connect. PayPal might have an automated way as well, but I've no idea what it is.

That would be a very difficult task. There's hundreds of recurring subscriptions that would have to be cancelled and moved to Stripe. 

I could dump the table, but I'm not sure that would help. I have noticed that while the table #_cbsubs_payments_baskets is skipping some numbers in the auto increment ID, however #_cbsubs_notifications is not. 

I've also noticed in looking at the tables that there seems to be a mixture of collations some are utf8mb3_general_ci while others are uft8mb4_unicode_ci 
Could this be interfering in writing to the tables?  CBSubs tables seem to be all utf8mb3_general_ci while comprofiler tables are a mix. 

I've noticed that the notifications having issues are from Germany, France and Brazil. I don't see any failures from the US or UK. 

If the collations should be updated to utf8mb4, what would be the best way to do that? 

Thank you,
Tomás

PS I've attached phpMyAdmin screenshots

Suggest emptying the _cbsubs_notifications table and correcting all collations to uft8mb4_unicode_ci. See if that helps your issue. As for updating the collations you should be able to use phpmyadmin to do that as there's a feature under the Operations table to change collation for entire tables including their columns.

Hi Kyle,
Thanks for your suggestion. Unfortunately, I'm still getting failed IPNs.
Here's what I did:

1. Backed up the table as you said.
2. deleted all records of the remaining table (without drooping the entire table)
3. Converted all tables and records to uft8mb4_unicode_ci with the operations option in phpMyAdmin.

Had 2 issues:

• errors with default values on dates being 000-00-00. Solved it by removing "strict mode" in MySQL
• Provinces in Aïn Defla - Algeria was throwing a duplicate error, which I solved by just adding a dot to the end of the Arabic characters. Did not see a duplicate to it, however, but it solved the issue. 

Starting with a clean table for Saturday I had:

• 17 Successful Payments: Gateway1: 11 - Gw2: 6 - Gw4: 0  (21 Entries in the notifications table)
• 3 Failed IPNs:  Gateway1: 2 -  Gw2: 0 - Gw4: 1

Since I had the failures, backed up and dropped the notifications table, then I installed a clean version of Joolma 4 and latest CB/CBSubs on a clean site, backed up the newly created notifications table from that new site and restored it the working database. Hoping that is if there was something messed up with the table structure, this would clear it out.  Unfortunately, it didn't. 

Starting with a fully clean table for Sunday I had:

• 5 Successful Payments: all Gateway 1 (16 entries in the Notifications table)
• 1 Failed IPNs from Gateway 4

One pattern that I'm seeing is that US and UK customers don't have problems, while Brazil, and Europe have issues (except UK). That's why I thought the encoding might help. Gateway 4 is a Brazilian PayPal account. It still has plenty successful IPNs, but has a higher failure rate. 

So what now? Considering that the ones that fail continue to fail, I'd like to track those notification's process.  Could you tell me which tables are written to and in what order when a notification comes in from PayPal? It seems to me, that given the error 500 and no logs, the problem should be early on, likely when writing to a table. 
If you have any other ideas, I'm all ears. 
Thank you,
Tomás 

I believe the table write order is as follows for a payment.

_cbsubs_notifications
_cbsubs_payments
_cbsubs_payment_baskets
_cbsubs_subscription

Throughout each of those the following will be written to.

_cbsubs_history

It's just incredibly strange it's happening so randomly. We've never had a test case of multiple PayPal accounts coming into a single CBSubs install as it was never designed for mutli-owner usage. What might be happening is 2 IPNs being processed at the exact same time because of this. Normally with a single PayPal account they all are queued and sent through properly timed. Looking at your timings above though that's not always the case so I'm not sure if that's what is doing it.

It might be time for us to completely rewrite the PayPal gateway to latest APIs. We're seeing strange reports as of late and wonder if PayPal is silently breaking their legacy API. Just doesn't make sense for something stable for years to just suddenly mess up.