Login MYSQL Error

Permalink
I transferred a site running on Concrete5 8.5.1 from a Windows 10 environment using PHP 5.6.20 and MYSQL 5.6.29 to Linux Mint 19.1 with PHP 7.3.6 and MYSQL 8.0.16

The site works fine in the Windows 10 environment and in Linux until I try to log into the Linux site when I get:-

An exception occurred while executing 'SELECT g.gName, u.uID FROM Groups g LEFT JOIN UserGroups ug ON ug.gID=g.gID INNER JOIN Users u ON ug.uID=u.uID AND (u.uName=? OR u.uEmail=?)' with params ["admin", "admin"]:

SQLSTATE[42000]: Syntax error or access violation: 1064 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 'Groups g LEFT JOIN UserGroups ug ON ug.gID=g.gID INNER JOIN Users u ON ug.uID=u.' at line 1.

In both:
groups has the standard set of Administrator, Registered User and Guest
usergroups is empty
users just has admin


As an aside, is =? equivalent to IS NOT NULL. I've not seen ? used like this before.

 
JohntheFish replied on at Permalink Reply
JohntheFish
Usual culprit with windows to linux database moves is case sensitivity of table names/columns. However, when that happens the errors usually cascade before you login to the dashboard, so perhaps it is something else.
DL0rnie replied on at Permalink Reply
Thanks, I should have added that after I got this problem with the migrated site on the Linux machine I then did a fresh install (on the Linux machine) - I downloaded and installed concrete 5.8.1, installing it with an empty theme. After installation it comes up with admin logged in as usual. However when I logged out and then tried to log in again I got the same error message.
DL0rnie replied on at Permalink Reply
Part of the problem appears to be that the word groups is reserved in the latest versions of MYSQL and needs to be escaped using `. The problem query worked when I changed it to:

SELECT g.gName, u.uID FROM `Groups` g
LEFT JOIN UserGroups ug ON ug.gID=g.gID
INNER JOIN Users u ON ug.uID=u.uID AND (u.uName IS NOT NULL OR u.uEmail IS NOT NULL)

although, as you can see I still had to use the NOT NULL rather than =? which still gave me problems.
Version 8 System Requirements says it will work with MYSQL 5.1.5 or higher but that does not seem to be true.
Any Comments?
JohntheFish replied on at Permalink Best Answer Reply
JohntheFish
There is a github issue on GROUPS that will be in 8.5.2
https://github.com/concrete5/concrete5/issues/7686...
Unfortunately the issue does not reference a specific pull request for the fix.
DL0rnie replied on at Permalink Reply
Thanks, This worked perfectly, I can now login.
surefyre replied on at Permalink Reply
surefyre
So if you upgrade your DB without knowing this do you have to grep all 'into Groups' and 'from Groups' in C5 source to be able to upgrade the site to a version that quotes the Groups table name?

This actually raises the issue that all core code should enclose table names in `` marks (maybe it does now but the site I'm working on is an older ver of C5).