Yes, that's not a typo. This is for a Craft 2 site (long story). Because MySQL 5.6 is EOL in a few weeks, I am validating how feasible it can be to run under MySQL 8.0, there's not really any option to go 5.7, as our upstream provider provisions MySQL 8 by default now.
I'm asking mainly for anyone else who may have looked at this in addition to validating my own testing and what I have found. I provisioned a NGINX, PHP 7.3 FPM, MySQL 8.0 environment and did initially find several problems with MySQL 8 and Craft 2 that mean you couldn't get past the "cannot connect to DB error" even though the DB credentials were fine. Confirming that it wasn't me being dumb with wrong .env credentials I downgraded my DB container back to MySQL 5.7 and everything immediately worked, so it confirmed that MySQL 8 was the issue.
I eventually wrote a simple PDO MySQL test outside of Craft and found some errors being thrown which we not visible when going to /admin/install (I may have got more clues looking at the logs, but sometimes, you need to sanity check yourself!). Which thankfully did then explain why things were failing and there were several issues in the end.
Default authentication plugin changes
I found Craft 2 couldn't even connect with PDO because of the default authentication plugin change for new MySQL 8 servers. Because this environment was provisioned with docker the default setting was caching_sha2_password. Older databases probably won't hit this.
This required the following change on the DB user however:
ALTER USER 'db_user' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;
- db_user - MySQL database user
- password - MySQL database user password
SQL mode
SQL mode requires the following modification, because it references a removed option in MySQL 8 which will break the DB connection as well. From the original topic on Craft 2 on MySQL 5.7.5+: Getting this SQL Error: GROUP BY incompatible with sql_mode=only_full_group_by
SQL mode needs to be set to:
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
NO_AUTO_CREATE_USER needs to be removed from initSQLs or any .cnf configuration for MySQL 8.0+.
After that, this appears to allow Craft 2 to connect properly.
General issues/problems?
So once I'd figured out why Craft 2 was having a hard time connecting to the DB backend, I pulled a DB export from our currently live environment using MySQL 5.6 and imported it into MySQL, aside from a minor issue with row sizes on the craft_content table (not MySQL 8 specific another issue) the DB seems to be OK and running under MySQL 8 fine. I haven't extensively tested all functionality yet, but for the most part it does seem to be running OK, no server errors when performing some save actions and such.
Are there any major considerations or issues specifically for Craft 2 and MySQL 8 that should be monitored?
