[solved] Error in SQL syntax (SQL version 8)

Since upgrading to the latest version of Mysql (version I am getting this error when visiting the "charges" front end page:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a.id LIMIT 20' at line 5

In addition the API seems to no longer record the correct status in the database (confirmed, failed, pending ect.)

Any ideas please?

Many thanks in advance.

Hell Matt,
Thank you for reporting this.
I'll double check the mysql8 issue. I have no particular clue for now.

For the database status issue, this is weird as I just check in my live #__coinbasecommerce_charges table.
All entries have theyr type as: charge:failed or charge confirmed.
Even latest orders from yesterday.
Are you referring to that type column too?

Thank you for the response.

I think there maybe a syntax error in the query that updates the database in regards to the status. Everything is set correctly and definitely worked before.

However I will double check everything my side.

An update for you here:

After repairing and optimizing all the tables the syntax error went away so it looks like all is working OK with version mysql version 8!

In regard to the API issue (charge created, failed confirmed ect) not updating the database I found out that Cloudflare "bot fight mode" is currently blocking all IP's from coinbase commerce. You have to whitelist their IP range (54.175.255.0/24) in the cloudflare firewall rules or the web hook fails.

Hope this may help others or your customers.

Thanks for clarifying !