Opened 4 years ago
Last modified 10 months ago
#52496 new defect (bug)
Improve MySQL 8.0 support
Reported by: | 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:
- 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.
Change History (28)
#3
@
4 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.
4 years ago
#5
@
4 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
@
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
@
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
@
3 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
@
3 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. Its 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.
This ticket was mentioned in Slack in #core by mike. View the logs.
3 years ago
#11
@
3 years ago
- Milestone changed from 6.0 to 6.1
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
This ticket was mentioned in Slack in #hosting-community by mike. View the logs.
3 years ago
#13
follow-up:
↓ 15
@
2 years 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 1685PHP 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.
#15
in reply to:
↑ 13
@
2 years 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.
22 months ago
#17
@
22 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
@
22 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.
18 months ago
#20
@
18 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
@
18 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
@
16 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
@
16 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
@
15 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.
#25
@
15 months ago
I agree with @oglekler that a patch is still needed for this while PHP < 7.4 is still supported by WP.
#26
@
14 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.
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.