sviluppiamo comunicazione con weLaika Advertising

A wp-cli command that transform to UTF8 WordPress DB tables

Tag: wp-cli, wp, cli, wordpress, datadase, db, utf8, utf8mb4, mysql
Alessandro Fazzi -

It’s not the best thing I had wished myself, to write an article - and a little piece of software! - focused on managing a problem brought to you by advers hosting companies.

I really like some of MediaTemple’s services, do not misunderstand me; anyway this is what I have on a grid service:

1
2
3
4
5
6
7
mysql> SHOW VARIABLES LIKE 'version';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| version       | 5.1.63-rel13.4 |
+---------------+----------------+
1 row in set (0.00 sec)

If you know what I mean: 5.1.63 was released on 7 May 2012.

So what’s up with WordPress?

WordPress upgraded its default tables encoding to UTF8mb4 since version 4.2. UTF8mb4 in MySQL is almost the support for real UTF8 encoding, since MySQL’s UTF8 had some lacks on this front. Do you know when MySQL introduced the support to UTF8-mb4? On 24 March 2010 with version 5.5.3.

And yes, I’m still here to fight against 5.1.63 for some of my staging servers.

When does this problem happens?

WordPress is smart enough to setup UTF8 tabels when it founds no support for UTF8mb4. The problem is that in my development environment I have a MySQL version which supports UTF8mb4, so when I bootstrap a new project the database will have UTF8mb4 tables.

Now, when I’ll wordmove push -d - I’m sure you know what Wordmove is - to my staging server I’ll get the error about unsupported encoding. Here is the problem.

How we rapidly solve the problem

A colleague of mine, wrote a little unpublished WordPress plugin, for internal purpose only. The plugin worked well, but was hooked to the after_db_upgrade action, so I found myself to firing it up manually inside a repl or inside theme’s function.

Then I thought how much we love and use wp-cli and thus decided to write a little custom command. So we can rapidly solve the situation by

1
wp package install welaika/wp-cli-db2utf8

then, inside WordPress’ root path

1
wp db2utf8

I advice you to use at your own risk and only on new installations with fresh database: altering tables this way could lead to unwanted data loss if you have already filled the DB with contents.

Source code? Questions? Bugs?

Head to the GitHub repo and open an issue if you need.

A special thanks goes to @danielbachhuber for his kind help and enthusiasm spent maintaining the wp-cli project.

welaika