Make WordPress Core

Opened 3 years ago

Last modified 4 months ago

#52496 new defect (bug)

Improve MySQL 8.0 support

Reported by: jrf's profile jrf Owned by:
Milestone: Future Release Priority: normal
Severity: normal Version: 5.4
Component: Database Keywords: needs-patch
Focuses: Cc:

Description

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:

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

Change History (28)

#1 @jrf
3 years ago

  • Milestone changed from 5.7 to 5.8

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.

#2 @SergeyBiryukov
3 years ago

Related: #49364, #51740.

Last edited 3 years ago by SergeyBiryukov (previous) (diff)

#3 @ayeshrajans
3 years ago

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.

This ticket was mentioned in Slack in #core by chaion07. View the logs.


3 years ago

#5 @JeffPaul
3 years ago

  • Keywords needs-patch added
  • Milestone changed from 5.8 to Future Release

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.

#6 @desrosj
3 years ago

  • Milestone changed from Future Release to 5.9

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

#7 @hellofromTonya
3 years ago

  • Milestone changed from 5.9 to 6.0

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

#8 @pyrobd
2 years ago

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?

#9 @jrf
2 years ago

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

Last edited 2 years ago by jrf (previous) (diff)

This ticket was mentioned in Slack in #core by mike. View the logs.


2 years ago

#11 @kirasong
2 years ago

  • Milestone changed from 6.0 to 6.1

This ticket was discussed in a 6.0 bug scrub.

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

Version 0, edited 2 years ago by kirasong (next)

This ticket was mentioned in Slack in #hosting-community by mike. View the logs.


2 years ago

#13 follow-up: @SergeyBiryukov
22 months ago

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.

#14 @jrf
21 months ago

Related: #30462

Last edited 21 months ago by SergeyBiryukov (previous) (diff)

#15 in reply to: ↑ 13 @desrosj
20 months ago

  • Milestone changed from 6.1 to 6.2

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.

This ticket was mentioned in Slack in #core by costdev. View the logs.


16 months ago

#17 @costdev
16 months ago

  • Keywords 2nd-opinion added

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?

#18 @jrf
16 months ago

  • Keywords 2nd-opinion removed
  • Milestone changed from 6.2 to 6.3

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

This ticket was mentioned in Slack in #core by chaion07. View the logs.


11 months ago

#20 @chaion07
11 months ago

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.

#21 @oglekler
11 months ago

  • Milestone changed from 6.3 to 6.4

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.

#22 @elrae
9 months ago

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.

#23 @ayeshrajans
9 months ago

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

#24 @oglekler
8 months ago

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.

Last edited 8 months ago by oglekler (previous) (diff)

#25 @jrf
8 months ago

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

#26 @oglekler
8 months ago

  • Milestone changed from 6.4 to 6.5

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.

This ticket was mentioned in Slack in #core by audrasjb. View the logs.


4 months ago

#28 @audrasjb
4 months ago

  • Milestone changed from 6.5 to Future Release

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

Note: See TracTickets for help on using tickets.