PayPal IPN random fails

Hi Kyle,
Thanks for bearing with me. 
It doesn't look like the IPNs are stepping on each other. I'm looking at the log times and the PayPal IPN times, and the apache logs show that it's not the case. Also, remember that I've had this setup for several years (over 6 years now) and never had an issue before. 

I'm pouring through the tables and logs to try to find a thread or a common line. Still searching, though I have found some crumbs in the history table. 

It could be some issue with the legacy API. What I can say for certain that this happened with my upgrade. Unfortunately the IPNs was not something that I could have done much about, or even properly test. The upgrade included a full revamp:

• Joomla 3.10 to 4.3 and minor CB/CBSubs upgrade (as I mentioned eariler)
• Also moved to a new server, with Ubuntu 22.04 and it's default LAMP versions (PHP 8.1 and MySQL  

A year or so ago, I had tested upgrading MySQL to v8, but it didn't work with my setup (PHP 7.2 at the time) and tested upgrading to PHP 8 while still on Joomla 3, but there were some extensions that did not work on PHP 8. It seemed best to upgrade to J4 and PHP8 at the same time. However, another big aspect of this seems to be the MySQL version, I went from 5.7 to 8.0.

For this new server I'm working on, I built it on Ubuntu 22.04 and all the default versions provided for Apache, PHP & MySQL. It could be that some kind of data is not playing nice with the API and MySQL 8. 

What I have found is that:

• About 15% of the PayPal IPNs are failing with an error 500.
• Clients who's IPN fail, continue to fail after that, even if they start a new subscription in a different plan or gateway. 
• There have been no Stripe failures.
• Almost all the failures are from non-English speaking countries.
• There's only 1 English (US) failure and that person has an ' (%92) in her PayPal name. 

While this doesn't seem relevant it's the only patterns I see. 

Thank you,
Tomás 

I wonder if the IPNs are failing at a server level. As in they never reach Joomla. Maybe some sort of server security is blocking them or a Joomla security extension if you have one installed? You should ideally whitelist PayPal's IPN IP Addresses to avoid stuff like that if you have such security configured.

It's hard to say what's going wrong as you've quite literally changed the entire environment around your site. The PayPal gateway in CBSubs hasn't changed, but the environment has. So that suggests it's something environment related, but I don't know what unfortunately.

Do all the failed IPNs have names with special characters in them? Wonder if it is an encoding problem somewhere. There has to be a common element here since all IPNs from specific users appear to fail.

Hi Kyle,
Thanks for the patience. 

After much digging and poking around, it's definitively a character-set issue.

I went through the database looking for all the fields that PayPal reports through its IPN with a query (regexp '[^ -~]') and ended up with pretty much the same list of users that are failing, some have not paid yet, others used Stripe, but the list matches up. 

I checked PayPal IPNs and somewhere it said that it was using UTF-8 and I looked at a couple of the IPN message of the failing ones. There was a ...&charset=windows-1252... within the "IPN Message" and then in the same message there's &last_name=H%E9rault  (for Hérault).

These few IPN messages fail with an error 500 and there's nothing written to the database. If one of these users started a new payment, then the basket is created with a subscription, but without an IPN confirmation it deletes basket and subscription a few hours later. The IPN is trying though, but gets nothing but 500. 

The PayPal IPs are coming through OK, as those other IPNs coming through use the same IP.

I found that in /components/com_comprofiler/plugin/user/plug_cbpaidsubscriptions/processors/paypal/cbpaidsubscriptions.paypal.php, line 871 considers the windows character set, but it is commented out.

Of course, I have no idea how to solve the issue, but I'm happy to have found the origin of the problem. It's clear that my upgrade to PHP 8 and MySQL 8 has something to do with it, as another server I have which is still on PHP 7.4 and MySQL 5.7 is doing fine. However, that server has reached EOL and I have to move, which I'm not looking forward to. 

If I were to guess, and it would be nothing more than a semi-educated guess, the way MySQL 8 (utf8mb4) handles the discrepancies in charset is different than MySQL 5.7 (latin1) and hence the hard error 500. 

I can send you the full IPN Message content of a few of the failing IPNs via private message if you want. 

Thank you,
Tomás 

Awesome that you found the root cause of the issue!

I have had seen such a charset issue many many years ago, and if I recall correctly (dusty), there was a setting at Paypal.com where there was a choice for the default charset, but their UI changed completely since them, . I had suggested at that time to Paypal to send IPNs in the same charset of the original payment by default.

I searched it up for you in their new UI, and funnily it's still in the old UI style.

Go to:
- Account Settings (in your name icon at top right)
- Products and Services -> "Website Payments" (left)
- "PayPal button language encoding" (click "Update")
- Now you are in the older style UI of Paypal
- "Your website language": I have "Western European languages (including English)"
- BUT: There, there is a small button: "More Options" below, click it!
- You will see there:

More Encoding Options

Use the following drop-down menu to select the encoding used on your website.

->> choose UTF-8

Encoding:
Do you want to use the same encoding for data sent from PayPal to you (e.g., IPN, downloadable logs, emails)?
Yes
No, use:

-> in the "No", choose UTF-8
-> but choose "Yes" and save.


What were the settings in your case for the Paypal account(s) which failed on non-US-ascii characters ?

SUCCESS!! 
Thank you so much. I never even knew it was there, and would have never found it.

All the PayPal IPNs went through perfectly, including one that was on my watch list. 
I had "windows-1252" on the PayPal IPN settings, and so did the other PayPal accounts. 
Interestingly enough, the other PayPal account for the server I haven't moved yet, had the settings blank, nothing selected. I set it to UTF-8. I set that up in 2009, maybe updated it once since, but I assume those options came in after I set it up initially. 

Thank you! 
Tomás