Migration of Concrete5 stuff (and Laravel) to newer servers

home | blog | Terrible people and places | Covid-19 links | Teh Internet | guest blog |rants | placeholder | political | projects | Gwen and Liam | Citadel patched | Tools | Scouts

Migrating an older C5 site to Mysql 8.0 and hitting this error?
mysqli error: [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 where gID = '1'' at line 1]
in EXECUTE("select * from Groups where gID = '1'")

Check that you applied the mysql updates as well.
If you don't see the error, check your debugging setting:
UPDATE Config SET cfValue = 1 WHERE cfKey = 'SITE_DEBUG_LEVEL';

If you can't connect to the database and it is MySQL 8.0
The codebase might not be using the default new plugin caching_sha2_password
but rather the mysql_native_password
Check for them with:
SELECT User, Plugin from mysql.user;

- gather up the usernames and passwords the code uses.

This might work:
ALTER USER 'c5dbuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'c5password';
flush privileges;

If that does not work re-create the user:
- First record the users privs to which db's!

DROP USER 'c5dbuser'@'localhost';
CREATE USER 'c5dbuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'c5password';
GRANT ALL PRIVILEGES ON c5database.* TO 'c5dbuser'@'localhost';
flush privileges;

You might want to ensure the plugin is available via the my.cnf with
- if you have this in before the users are created, it should just work...
default_authentication_plugin = mysql_native_password

Hitting mysql errors in your Laravel logs like this:
(after setting : APP_DEBUG=true in your .env):
SQLSTATE[42000]: Syntax error or access violation: 1231 Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER' 

It seems later Mysql 8 removed the option, so remove it from your database.php config file