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