You have an error in your SQL syntax

[rodeoclassifieds]

I can place an ad and see it as pending but when I go to approve the ad, nothing shows on the list. When I look at view all orders for the users orders there is nothing on their list, but near the bottom of that page I see the following error:

error: SELECT o.id as order_id, o.status, o.buyer, o.created, o.admin, i.id AS invoice_id, u.id as user_id, u.username, o_r.val_string as gateway_type FROM geodesic_order AS o,geodesic_invoice AS i, geodesic_order_registry as o_r, geodesic_logins as u WHERE i.order = o.id AND o_r.order = o.id AND o_r.index_key = 'payment_type' AND u.id = o.buyer AND o.seller = 0 AND u.username=? GROUP BY o.id DESC ORDER BY order_id DESC LIMIT 0, 20

1

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 'DESC ORDER BY order_id DESC LIMIT 0, 20' at line 9

My host recently migrated me to a new server......suspecting something with that caused this as it was fine before. They tried changing my php version back to 5.4 and I placed a new ad and still had the same issue with the new one. Any advice?

[vicos59]

A few questions:

• Which version of GeoCore are you using?
• Which version of PHP were you using on the old server, and now the new server?
• Are you using MariaDB or mySQL and which version?

[rodeoclassifieds]

Thank you, I heard back from my host. They said : _we use Cloud Linux so the PHP can be changed from 5.6 to 8.2.

The MySQL version is MySQL 8.0_

GeoCore 18.02.0

They didn't specify what they moved me to, but that it is "back to 5.6", but I think in a previous message he said both php and MySQL were moved to 8.

(that was a typo on my part above when I said 5.4).

[vicos59]

We only tested on PHP 7.4 and 8.1. 8.2 was not tested and at least one other user has reported issues, as I would expect.

We did not test with mySQL 8 and since it is a SQL issue you are having, my guess is the problem lies there.

Hosts really need to provide people with notice and options before they change your operating environment.

[vicos59]

Here's the problem. mySQL 8 syntax either changed or is more strict:

https://stackoverflow.com/questions/53846308/group-by-desc-syntax-error-on-mysql-8-0-which-is-fine-on-5-7

change

GROUP BY o.id DESC

to

GROUP BY o.id

and it should work. There are probably more things that will pop up. Best thing would be to get back to a pre-v8 version of mySQL, and PHP 7.4 or 8.1.

[rodeoclassifieds]

Thank you, I appreciate this so very much. I will pass this on to my host.