Using MySQL 8 with Craft CMS 3
Posted: 13th Feb 2020
The latest major version of MySQL has brought a host of performance improvements along a with a few new pieces of additional functionality that make working with complex data easier.
Upgrading from MySQL 5.7 to 8 is also made easier by the inclusion of automated migration tools built into the server, allowing the same data files to be carried over to the new version.
Now that 8 has been generally available for a while it's likely the majority of remaining bugs have been identified, so there should be little reason not to upgrade.
Except for one issue that a lot of developers have been hit with.
What's The Problem?
As well as functional and performance updates, MySQL 8 also included some improvements to security. One such improvement was the use of a new module for hashing user passwords: caching_sha2_password. This new module is incompatible with some of the primary libraries used by PHP during initial database connections.
The problem was identified early and fixes applied to PHP 7.2 and 7.3, but problems persisted due to several bugs in the way that the libraries decided which type of authentication to use.
What Error Is Displayed?
The majority of the time this error will occur when an initial database connection is established and it usually looks like:
The server requested authentication method unknown to the client
This error is letting you know that the database asked for the user to authenticate using the new method and PHP doesn't support it.
In versions of PHP that have added support you might see this error during initial connection:
Access denied for user 'user'@'domain' (using password: YES)
This looks like a normal access denied error, but if you're sure your username and password are set correctly and you're using MySQL 8 it's likely one of the bugs in PHP causing the problem.
Fix #1: Set The Database User's Preferred Authentication Method
Rather than mess with any of MySQL's server settings you can simply run some SQL to set your user's preferred authentication method to the old style.
That can be done using the following:
alter user 'user'@'domain' identified with mysql_native_password by 'password';
This will allow the database user to connect using the old method, however it doesn't always fix the issue on certain versions of PHP. If you're using PHP 7.4.2+ you should be in the clear, but if not you might need to move on to fix #2.
Fix #2: Change MySQL's Default Authentication Method
By telling MySQL to use the old authentication method by default, any new users created will be compatible with all versions of PHP and the initial connection handshake between PHP and the database should always succeed.
You'll need to change one of MySQL's settings to do this which means editing the my.cnf file on your server.
You can find the probable location of your my.cnf by running mysql --help which will output the directories it's going to look in to find it.
Once you've located it, add:
Your file might already have a [mysqld] section, if so just add the second line below that.
If you're running MySQL 8 in docker you can add the following flag to the container's command to achieve the same thing:
Restart MySQL and your changes should take effect, but beware that any users created before this point might still be using the old auth method, so you'll need to apply Fix #1 to those users too.
Fix #3: Upgrade to PHP 7.4.2 Or Above
If you aren't able to alter the configuration for MySQL and you're currently using one of the versions of PHP that has trouble setting up connections with MySQL 8, you might have to update to PHP 7.4.2 to get things working.
This is less than ideal as it could break other things in your code, but it should fix the problem in the majority of cases as PHP 7.4 supports the new authentication method and a number of related bugs have been fixed.
Note: There's still a remaining bug in PHP 7.4.2 when the database user has a password longer than 19 characters. A fix for this should be released in PHP 7.4.3.
Is It Worth It?
We did a few simple tests to find out. The following were executed in fresh docker containers on a system with no other CPU load. For each test Craft v3.4.5 and PHP v7.4.2 was used. The databases were executed with all of their default settings as they would with a fresh installation.
time ./craft install/craft --email [email protected] --username admin --password password --site-name test --site-url "http://localhost" --interactive 0
real 0m2.753s user 0m0.564s sys 0m0.060s
real 0m5.772s user 0m0.626s sys 0m0.031s
Not a great start for MySQL 8. This result was repeatable with a small variance in timing and might be being caused by MySQL 8's changes to how it stores and indexes data internally.
Page Load Speed
The next test was rendering this page from Servd's documentation. The page was refreshed 5 times to allow the database to cache some initial data, these results were then disregarded. The page was then refreshed 10 times with all application caching disabled. The average TTFB was recorded as the overall result.
MySQL 5.7.26: 101ms
MySQL 8.0.19: 130ms
Another consistent fail for MySQL 8.
Perhaps at larger request volumes or for a different data set MySQL 8 will demonstrate an improvement over 5.7.26, but with a relatively simple Craft site, it looks as though the additional complications with internal data representation and establishing connections has had a negative effect on overall page load times.
We'll be doing some more stringent testing over the next few months, but there's nothing making us rush towards adopting MySQL 8 as our default yet...
If configuring MySQL's many settings is something you'd rather not be handling yourself Servd can take care of it for you, along with all the other infrastructure and DevOps tasks you wish you weren't doing.