Improve MySQL 8.0 support

[jrf]

While setting up the GH Action for the WP Importer plugin which runs the plugin integration tests (using the WP Core test framework) against various PHP/MySQL/WP combinations, I noticed a number of PHP/MySQL 8.0 combinations which error out during the running of the WP Core test bootstrap with errors which seem to indicate underlying incompatibilities with MySQL 8.0.

Relevant files in the Importer plugin setting up the test environment:

• ​https://github.com/WordPress/wordpress-importer/blob/master/phpunit/install.sh
• ​https://github.com/WordPress/wordpress-importer/blob/master/phpunit/bootstrap.php

Test run showing the errors: ​https://github.com/WordPress/wordpress-importer/actions/runs/556887133

Errors reported:

PHP 5.6 / MySQL 8.0 / WP 5.5 and latest (5.6)
(Error line numbers are based on latest)

PHP Warning:  mysqli_real_connect(): Server sent charset (255) unknown to the client. Please, report to the developers in /tmp/wordpress/wp-includes/wp-db.php on line 1653
PHP Warning:  mysqli_real_connect(): (HY000/2054): Server sent charset unknown to the client. Please, report to the developers in /tmp/wordpress/wp-includes/wp-db.php on line 1653
PHP Warning:  mysql_connect(): Server sent charset (255) unknown to the client. Please, report to the developers in /tmp/wordpress/wp-includes/wp-db.php on line 1685
PHP Warning:  mysql_connect(): Server sent charset unknown to the client. Please, report to the developers in /tmp/wordpress/wp-includes/wp-db.php on line 1685

PHP 7.0 - 7.3 / MySQL 8.0 / WP latest (5.6)

PHP Warning:  mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password] in /tmp/wordpress/wp-includes/wp-db.php on line 1653
PHP Warning:  mysqli_real_connect(): (HY000/2054): The server requested authentication method unknown to the client in /tmp/wordpress/wp-includes/wp-db.php on line 1653

Note: with the exact same setup, the PHP 7.4/8.0 - MySQL 8.0 - WP 5.6 test runs will pass.

Investigating needed

I haven't dug into the code. Reporting it here for further investigation.

I imagine that either the PHP/MySQL version check could be adjusted to indicate that MySQL 8.0 is only supported when running on PHP 7.4 or higher.

Alternatively, the underlying issues will need to be solved.

I also wonder if the WP Core tests in CI are being run against enough PHP/MySQL combinations, as I would imagine that these issues could have been discovered earlier if the above problem combinations would haved been used in WP Core CI.

Related: #49344 #51740

[jrf]

Oops.. as it's very late in the 5.7 cycle, this should, of course, be addressed in the 5.8 development cycle, though any patches _may_ need to be backported to WP 5.4-5.7 patch releases.

[SergeyBiryukov]

Related: #49364, #51740.

[ayeshrajans]

Re: PHP 7.0 - 7.3 / MySQL 8.0 / WP latest (5.6)

PHP <= 7.3 does not support caching_sha2_password, so it required --default-authentication-plugin=mysql_native_password at mysql process init, or changing the user authentication plugin to mysql_native_password for MySQL 8 to work. This should not a problem in PHP 7.4 with caching_sha2_password built-in. Related: ​https://php.watch/articles/PHP-7.4-MySQL-8-server-gone-away-fix.

In a CI setup using MySQL 8 + PHP 7.0..7.3, perhaps we can use an init option --default-authentication-plugin=mysql_native_password?

I'm not sure about the charset issue. My wild guess is that it's related to utf8mb4_0900_* class of collations only available in MySQL 8 and is the default now.

[JeffPaul]

Given that 5.8 Beta 1 is a week away and no patch/PR exists on this ticket, I'm going to punt to Future Release. Once there's a patch/PR ready for this it can get added back to a numbered milestone.

[desrosj]

I'd like to solve the MySQL 8 test failures for 5.9 to that we can look at expanding our testing combinations.

[hellofromTonya]

As it's 4 days to 5.9 Beta 1 and no activity on this ticket, moving it to 6.0.

[pyrobd]

I wanted to reproduce and test the issue, not sure what GH Action means using WP Importer plugin, can someone point me in the right direction?

[jrf]

@pyrobd GH Action = GitHub Actions = a way to run automated tests/manage continuous integration via a service GitHub offers (previously Travis was often used for this).

​WP Importer is an official WordPress plugin.

To be honest, neither is actually relevant to reproduce the issues. It's the combination of PHP < 7.4 with MySQL 8.0 which is the relevant bit and with that combination, you should be able to reproduce the issues with WP itself.

[kirasong]

This ticket was discussed in ​a 6.0 bug scrub.

Since it does not have a patch and seems unlikely to land before RC on May 3, I'm going to move this out of the 6.0 milestone.

It looks like some folks are already aware, but because I've seen some related issues chatted about in the #hosting-community channel, I'm going to bring up the ticket there as well.

Edit: Clarify why

[SergeyBiryukov]

Thanks for the ticket!

PHP 5.6 / MySQL 8.0 / WP 5.5 and latest (5.6)

PHP Warning:  mysqli_real_connect(): Server sent charset (255) unknown to the client. Please, report to the developers in /tmp/wordpress/wp-includes/wp-db.php on line 1653
PHP Warning:  mysqli_real_connect(): (HY000/2054): Server sent charset unknown to the client. Please, report to the developers in /tmp/wordpress/wp-includes/wp-db.php on line 1653
PHP Warning:  mysql_connect(): Server sent charset (255) unknown to the client. Please, report to the developers in /tmp/wordpress/wp-includes/wp-db.php on line 1685
PHP Warning:  mysql_connect(): Server sent charset unknown to the client. Please, report to the developers in /tmp/wordpress/wp-includes/wp-db.php on line 1685

PHP 7.0 - 7.3 / MySQL 8.0 / WP latest (5.6)

PHP Warning:  mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password] in /tmp/wordpress/wp-includes/wp-db.php on line 1653
PHP Warning:  mysqli_real_connect(): (HY000/2054): The server requested authentication method unknown to the client in /tmp/wordpress/wp-includes/wp-db.php on line 1653

I could not reproduce the charset issues yet, but I was able to reproduce the issue with caching_sha2_password and confirm the observations from comment:3:

In a CI setup using MySQL 8 + PHP 7.0..7.3, perhaps we can use an init option --default-authentication-plugin=mysql_native_password?

This resolves the second issue in my testing.

[jrf]

Related: #30462

[desrosj]

Unfortunately this has not received any attention during the 6.1 cycle. I'm going to punt to 6.2, but if progress is made in time, it can be moved back.

[costdev]

This ticket was discussed during the recent bug scrub. This ticket doesn't appear to have had any attention during the 6.2 cycle so far.

Is this one likely to land in 6.2, or should it be moved to Future Release until it's ready to be milestoned in a release cycle?

[jrf]

"Future Release" is like the garbage bin for tickets which will never get patched, so I'd rather move this to 6.3 to ensure it keeps getting some focus as I do believe this should be patched sooner rather than later while PHP < 7.4 is still supported.

[chaion07]

Thanks @jrf for reporting this. We discussed this ticket during a recent bug-scrub session and decided to keep it on track for a few more weeks so that this can get some attention from volunteers. Cheers!

Props to @costdev for the discussion.

[oglekler]

Due to lack of patch, I am moving this ticket to the 6.4 milestone. Sorry, but it requires a work from someone which is not happened until now.

Possible, we can add a keyword to MySQL 8 and collect tickets for it to address in a batch.

[elrae]

Now that WordPress is officially recommending >= PHP 7.4 ​https://wordpress.org/about/requirements/ do we still need to pursue work on this ticket? If someone is running PHP <= 7.3 it's unlikely they're going to be upgrading from MySQL 5 to 8 without also updating their PHP given how old the software is.

[ayeshrajans]

@elrae I think you are right, that we probably won't need to bother working around MySQL 8 compatibility. I'm seeing a significant MySQL 8 usage in WordPress stats page. I even had a test site running on MySQL innovation 8.1 that I didn't notice any problems. One of 8.1's major changes are with the default auth plugin which PHP >= 7.4 supports anyway.

[oglekler]

We still supporting PHP 7.0+:
​https://make.wordpress.org/core/handbook/references/php-compatibility-and-wordpress-versions/
And due to this I assume we still need a patch for this issue.

Another solution can be to write explicitly in the requirements that only PHP 7.4 and higher can be used with MySQL 8.0.

[jrf]

I agree with @oglekler that a patch is still needed for this while PHP < 7.4 is still supported by WP.

[oglekler]

I don't see how it will be possible to make a patch, test and commit it in a week, so I am moving this ticket to the next milestone.

[audrasjb]

As per today's bug scrub, we decided to move this ticket to Future Release to give it more time to get a patch.