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.

[desrosj]

In 59279:

Build/Test Tools: Add MySQL 8.4 support to the Docker environment.

Because caching_sha2_password is not supported on PHP 7.2 & 7.3, the local Docker environment has used the --default-authentication-plugin system variable to always make use of mysql_native_password despite MySQL 8.0 deprecating this auth plugin.

However in MySQL 8.4, the --default-authentication-plugin option was removed in favor of --authentication-policy, and mysql_native_password is now disabled by default. mysql_native_password has also been removed in MySQL 9.0.

This change adds support to the local Docker environment for MySQL 8.4 by adding some helper functions that determine which authentication plugin should be used based on the configured PHP/MySQL versions and automatically making the necessary configuration adjustments.

Props ayeshrajans, johnbillion, aristath, jorbin.
See #61218.

[desrosj]

In 59280:

Build/Test Tools: Test against MySQL 8.4 in automated testing.

Additionally, MySQL 8.1, 8.2 and 8.3 have been removed. These were “innovation releases” and are no longer supported in favor of 8.4, which has LTS.

Props ayeshrajans, johnbillion, aristath, jorbin.
See #61218.

[desrosj]

@ayeshrajans Could you give the changes in [59279] a bit of testing to see if I'm missing anything?

I'm also marking that commit for backport consideration. Since this is just a tooling change and no code changes are required for 8.4 to be tested against, I'd like to properly support testing against MySQL 8.4 for WP 6.7 as well. @peterwilsoncc do you have any concerns there?

Merged in https://core.trac.wordpress.org/changeset/59279 and https://core.trac.wordpress.org/changeset/59280.

[peterwilsoncc]

@desrosj I'm happy for it to be backported. Are you able to set up a PR against the 6.7 branch so I can test it out prior to commit?

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

[desrosj]

In 59281:

Build/Test Tools: Split up upgrade test matrix.

GitHub Actions caps the number of jobs that can be spawned from a single matrix at 256.

The changes in [59280] pushed the WordPress 6.x job over this limit. This splits that matrix into two following established pattern for older branches in the workflow.

See #61218, #62221.

[peterwilsoncc]

@desrosj Thanks for the PR against the 6.7 branch. I've tested it and there are no ill-effects.

I've updated the milestone and marked this as dev-reviewed for backporting to 6.7.

After the changes in Core-59279, Compose files are manually passed to the Docker commands. If a docker-compose.override.yml file is present, it won't be utilized. This attempts to detect that and include the file in the list that is passed.

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

@xknown env variable for which part? Do you mean for the additional value of volumes in the old-php-mysql-84.override.yml file?

I just did some testing, and it seems that Docker fails when an empty value for a volume is supplied. The new override file is needed _in addition_ to the other two supplied in the default docker-compose.yml file, not instead of. So I'm not sure how to get it working with an environment variable with the limited variable substitution available.

@xknown env variable for which part? Do you mean for the additional value of volumes in the old-php-mysql-84.override.yml file?

I just did some testing, and it seems that Docker fails when an empty value for a volume is supplied. The new override file is needed _in addition_ to the other two supplied in the default docker-compose.yml file, not instead of. So I'm not sure how to get it working with an environment variable with the limited variable substitution available.

No, I was just wondering if it'd make sense to add something like the snippet below to utils.js

if ( typeof process.env.DOCKER_COMPOSE_CONFIG_OVERRIDE !== 'undefined' ) {
  composeFiles = composeFiles + process.env.DOCKER_COMPOSE_CONFIG_OVERRIDE
}

No, I was just wondering if it'd make sense to add something like the snippet below to utils.js

if ( typeof process.env.DOCKER_COMPOSE_CONFIG_OVERRIDE !== 'undefined' ) {
  composeFiles = composeFiles + process.env.DOCKER_COMPOSE_CONFIG_OVERRIDE
}

Ah, I see! I think if we find out that people are supplying custom override files we could consider that. But I don't know that is likely to be the case.

We also ​currently recommend using that file name in the README file for working with MySQL < 8.0 on Apple silicon chips. An env variable would also require an additional step (create the file, update the variable). So I think I prefer trying this approach first.

[desrosj]

In 59283:

Build/Test Tools: Support Docker compose override files.

This updates the logic introduced in [59279] to account for the presence of docker-compose.override.yml files.

Props xknown, davidbaumwald.
See #61218.

Merged in https://core.trac.wordpress.org/changeset/59283.

[desrosj]

In 59309:

Build/Test Tools: Add MySQL 8.4 support to the Docker environment.

Because caching_sha2_password is not supported on PHP 7.2 & 7.3, the local Docker environment has used the --default-authentication-plugin system variable to always make use of mysql_native_password despite MySQL 8.0 deprecating this auth plugin.

However in MySQL 8.4, the --default-authentication-plugin option was removed in favor of --authentication-policy, and mysql_native_password is now disabled by default. mysql_native_password has also been removed in MySQL 9.0.

This change adds support to the local Docker environment for MySQL 8.4 by adding some helper functions that determine which authentication plugin should be used based on the configured PHP/MySQL versions and automatically making the necessary configuration adjustments.

Reviewed by peterwilsoncc.
Merges [59279] to the 6.7 branch.

Props ayeshrajans, johnbillion, aristath, jorbin.
See #61218.

[desrosj]

In 59310:

Build/Test Tools: Test against MySQL 8.4 in automated testing.

Additionally, MySQL 8.1, 8.2 and 8.3 have been removed. These were “innovation releases” and are no longer supported in favor of 8.4, which has LTS.

Reviewed by peterwilsoncc.
Merges [59280] to the 6.7 branch.

Props ayeshrajans, johnbillion, aristath, jorbin.
See #61218.

[desrosj]

In 59311:

Build/Test Tools: Support Docker compose override files.

This updates the logic introduced in [59279] to account for the presence of docker-compose.override.yml files.

Reviewed by peterwilsoncc.
Merges 59283] to the 6.7 branch.

Props xknown, davidbaumwald.
See #61218.

Backported in https://core.trac.wordpress.org/changeset/59309 and https://core.trac.wordpress.org/changeset/59310.