Preparing for MySQL 8.4 compatibility

[ayeshrajans]

MySQL 8.4 was released last month with some potentially breaking changes. It's an LTS release, which means that unlikely MySQL innovation branch, MySQL 8.4 will likely be included in near future distros as well.

Among the ​list of changes in MySQL 8.4 is that it does not enable the mysql_native_password plugin by default. If a WordPress site tries to a MySQL 8.4 server with a MySQL user that uses this plugin, the connection fails unless the user is modified to use a modern plugin, or MySQL is configured to load the plugin.

I wrote about the fixes and approaches ​here as well.

Errors due to this change look like:

PDO:
SQLSTATE[HY000] [1524] Plugin 'mysql_native_password' is not loaded

MySQLi:
mysqli_sql_exception Plugin 'mysql_native_password' is not loaded.

I would like to see if we are willing to:

1. Show information about potentially deprecated authentication plugins in the Site Health page. For example, if the MySQL user is using mysql_native_password or sha256_password (not caching_sha2_password), we can show a warning to the user.

2. Automatically attempt to change the auth plugin: It should be trivial to update the auth plugin during a maintenance task, but this is likely outside of WordPress's responsibility.

MySQL 8.4 was released on April 30, 2024 and is the latest LTS version of MySQL.

Trac ticket: https://core.trac.wordpress.org/ticket/61218

[desrosj]

Thanks @ayeshrajans!

I opened a PR to add 8.4 to the testing matrix and came across this. I've temporarily removed the --default-authentication-plugin=mysql_native_password from docker-compose.yml for the sake of running the full test suite to see what else needs to be addressed. There ​does not appear to be any glaring issues related to MySQL 8.4 based on the successful results.

As for how WordPress should handle this, my current opinion is that switching authentication plugins and updating user passwords accordingly is something that should be handled at the hosting level when the MySQL version is changed for a site. When this is handled correctly, everything should "just work" from a Core perspective. This is demonstrated and confirmed by the PR workflows passing (unless I'm missing a nuance), and likely from real world use given a lack of tickets being opened for related problems here in Trac.

However, the one place we need to decide on how to handle this is within the local development environment. When switching PHP versions, someone could just reset and restart the environment manually ensuring the right plugin is used. But that's easy to forget, and there could be scenarios where someone would want to preserve the state of their database. It would be nice if there were a way to just handle this.

We can't just remove --default-authentication-plugin=mysql_native_password because the environment will be broken on PHP < 7.4. We could leave it and turn the plugin on within the environment, but that runs counter to the recommended best practices.

I haven't tested this yet, but I'm wondering if we could do some form of the following:

• Remove the --default-authentication-plugin=mysql_native_password command from the docker-compose.yml file.
• Add a docker run command when the environment starts up to run the necessary ALTER USER commands based on the configured version of PHP.

I'm going to move this to Future Release, but this can be moved to a numbered milestone whenever an approach is agreed upon.

@aristath I saw that you self assigned to perform a review. I think this is getting really close, so wanted to make sure you had a chance to take a look.