2

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.

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin

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?

0

Sounds like you've overcome the obvious ones already.

We've done some limited testing with the latest Craft 2 release on MySQL 8+ and no other red flags have been raised from our experience.

1
  • Thanks for your input Brad, that's good to know! Jan 20 at 11:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.