servd

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:

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:

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:

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.

Installation Speed

time ./craft install/craft --email test@servd.host --username admin --password password --site-name test --site-url "http://localhost" --interactive 0

MySQL 5.7.26

MySQL 8.0.19

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...