Migrating to MySQL 8.0 for Wordpress – episode 1

on
Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

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:

Error establishing a database connection This either means that the username and password information in your wp-config.php file is incorrect or we can’t contact the database server at localhost:/var/vhosts/database/mysql.sock. This could mean your host’s database server is down.

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.

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

7 thoughts on “Migrating to MySQL 8.0 for WordPress – episode 1

  1. 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? 🙂

    1. 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…

    1. 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)

Leave a Reply

Your email address will not be published. Required fields are marked *

recent

Last Tweets Last Tweets

Locations of visitors to this page
categories