Yesterday I decided to upgrade the database server of lefred.be running the latest WordPress to MySQL 8.0.
The previous version was 5.7.19.
My dedicated server is still running on CentOS 6.x, and I had just to enable the MySQL Community 8.0 yum repository and upgrade the rpms:
# yum update mysql-community-server --enablerepo=mysql80-community
After the upgrade, I had to modify my.cnf to remove the lines related to the non regretted Query Cache:
#query_cache_type=0 #query_cache_size=0
After this, I could start mysqld and run mysql_upgrade.
I tried to connect with the command line client and it worked perfectly.
Then, I wanted to remove the maintenance mode on lefred.be but WordPress returned me an error that it couldn’t connect to the database:
I verified the mysql error log, nothing wrong (bad credentials are logged), and no error in the apache’s log.
I decided to run WordPress in debug mode :
in wp-config.php set
define('WP_DEBUG', false)
Then I could see this error message that sounded familiar:
Warning: mysqli_real_connect(): Server sent charset (255) unknown to the client. Please, report to the developers in /var/…/www/wp-includes/wp-db.php on line 1548
I modified again my.cnf to add the following line under [mysqld]:
collation-server = utf8mb4_general_ci
After a restart of mysqld, everything went back normal !
The MySQL connector used on my system for PHP is php55w-mysqlnd-5.5.38-1.w6.x86_64:
Name : php55w-mysqlnd
Arch : x86_64
Version : 5.5.38
Release : 1.w6
Size : 956 k
Repo : installed
From repo : webtatic
Summary : A module for PHP applications that use MySQL databases
URL : http://www.php.net/
License : PHP
Description : The php-mysqlnd package contains a dynamic shared object that will add
: MySQL database support to PHP. MySQL is an object-relational database
: management system. PHP is an HTML-embeddable scripting language. If
: you need MySQL support for PHP applications, you will need to install
: this package and the php package.
:
: This package use the MySQL Native Driver
The next post will be related to the workload analysis using performance_schema & sys and see if something can be improved.
Good to see you leading the way and eating your own dog food. You did not mention which version of MySQL 8.0 you were using whether it was the 8.0.3 released publicly or an internal build. (Both are fine) but it would be good to clarify, as even if 8.0.3 is a release candidate there’s still time for some things to change.
Now to fix planet.mysql.com bugs.mysql.com and any other sites hosted by a 5.7 MySQL server? 🙂
Hi Simon,
Thank you for your comment.
The MySQL version used is the one publicly released in the yum repository: 8.0.3 RC1
About other sites, I don’t have any control on them but I’m pretty sure somebody will migrate them whenever it will be the “right” time 😀
Now about changing the default collation, I guess this will be a pain to suffer for a while… you know there are still people using old password format 😉 So I do expect having this collation defined in my.cnf for many projects for a long time…
I’ve also tried running my blog on MySQL 8.0 (since early DMRs). Just a couple of notes:
– The “Server sent charset ..” error is fixed by upgrading to a newer client library (PHP bug: https://bugs.php.net/bug.php?id=74461). I recommend using the new collations 🙂
– You have a typo: s/[mysqd]/[mysqld]/
Thank you for the info Morgo.
My problem and maybe the problem of many people is that they are using the most recent release of an old PHP 😉
A better approach would have been to upgrade PHP first (the new charset is supported since 7.1.5)
+1
[…] that MySQL 8.0.3 RC1 is installed and that we saw how to verify the workload, it’s time to see if we can optimize some of the […]
[…] that MySQL 8.0.3 RC1 is installed and that we saw how to verify the workload, it’s time to see if we can optimize some of the […]
`define(‘WP_DEBUG’, false)` should be `define(‘WP_DEBUG’, true)` above, but still, thanks for the tip, it helped me address my own problem.
Thanks for the article. I’ve ran into the same issue with connection to database, but your solution did not work. Instead the solution was to start MySQL with another default password plugin, enabling legacy password encryption option. I’ve written on this issue here: https://www.akamenskiy.com/fix-wordpress-database-connection-mysql-8/
Thank you sir for sharing such a good blog with us
Wow, I did fixed issue as per given your instruction. Thank you so much.