Moodle database read error post migration

[dgsmith]

I have migrated our moodle installation from server 2008 r2 to server 2016, following the instructions to export and reimport database (as utf8), copy moodledata, the Moodle install folder and adjust the config.php accordingly. The mysql username and db name remain the same.

 

Upon trying to login post migration, I got an "Error reading from database" error. Adjusting the config file to show debug errors gives the following:

 

Debug info: 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, groups_members gm

WHERE gm.groupid=g.id AND gm.userid='21' at line 2

SELECT g.id, g.courseid

FROM groups g, groups_members gm

WHERE gm.groupid=g.id AND gm.userid=?

[array (

0 => '21',

)]

Error code: dmlreadexception

Stack trace:

 

line 486 of \lib\dml\moodle_database.php: dml_read_exception thrown

line 1245 of \lib\dml\mysqli_native_moodle_database.php: call to moodle_database->query_end()

line 4815 of \lib\moodlelib.php: call to mysqli_native_moodle_database->get_records_sql()

line 4312 of \lib\moodlelib.php: call to get_complete_user_data()

line 143 of \login\index.php: call to authenticate_user_login()

 

The old server ran php 7 and mysql 5 whereas the new one is on php 7.2 and mysql 7, although I upgraded to moode 3.6 prior to migration. A simple php script successfully queries the moodle database with the credentials. I don't understand what this message suggests or what could be changed/checked as an appropriate diagnostic process.

 

Anyone seen it before or shed some light on where to start?

Edited February 19, 2019 by dgsmith

[dgsmith]

Noone seen this or could advise where I may look to try and resolve?

[Achandler]

I'm no expert with MySQL etc and we use Linux boxes normally.

 

However, I suspect that maybe Moodle is using a deprecated function (so it existed within MySQL 5 but not 7), so when it attempts to run the command against the MySQL an error is thrown out. There is probably nothing wrong with your actual database.

 

Do you use any extra plugins?

[Marci]

Are you certain about that MySQL version? MySQL 7 is specifically used for MySQL Cluster. General Release MySQL Community Server leaps from 5.x directly to 8.x.

 

Under MySQL 8.0.2, GROUPS is now a reserved word so can’t be used in queries without either being wrapped in backticks first, or alternatively referenced using table.column_name notation, therefore, Moodle 3.6 doesn’t yet support latest MySQL 8. This would explain the issue you’re experiencing fully, and trigger the exact error you’re seeing (hence check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups g,... which implies the issue is with the use of the word groups in the query)

 

Solution: Downgrade MySQL to the latest minor version of 5.x (5.7.25)

Edited February 20, 2019 by Marci

[dgsmith]

Yes sorry typo, it is mysql 8 (I still had the php 7 upgrade in my head). This would make sense I guess, though I didn't consider that Moodle would still not have some support the mysql 8 branch. Fortunately the server is a newly commissioned one so a reinstallation wouldn't be too bad. I guess I will have to go back to mysql 5!