Blog Inforeseau.com – GNU Linux, technologie et autres geekeries

Migrating an old WordPress and handling character set, UTF8, latin1, latin1_swedish_ci

Sharing a quick one because it took some research for me to figure this one, not too long, but I don’t have to search again :D

Basically, old WordPress from years ago, were using default character set called « latin1_swedish_ci » as default collation name, latin1 in short.

When you must migrate to a newer system because you were running an old CentOs which is no longer supported, and you start to migrate to a Debian based machine, the new default is UTF8.

So you backup your WordPress, (and ideally you used a dedicated machine for the database, so as you can have more flexibility in your workloads and yet can still backup the full DB server), and you restore it on your new server (a big tar.gz of you site content that you copied with scp by example).

You’ll have to fix all the glitches linked to PHP version change, sometimes the front end won’t work, but the admin page of your WordPress should still load. Web server logs will tell you what is wrong (a theme in my case was using functions that were no longer supported). If you can’t even get to the admin page, wp command line utility should help you update the instance from command line (another topic)

Anyway, while you log in the admin section, you will have to apply updates (as you could not on the old system, stuck with an older PHP version and other libs).

Suddenly you realize that all the accents (yes, French content and more) are totally messed up ! Yes, on the new server, everything is assumed by default to be UTF8 !

So you need to figure out what was the previous encoding of your content in the database.

You have to log on your mysql / mariadb server, as root of mysql and run the following command :

SELECT * FROM information_schema.SCHEMATA;

This will show up all your databases and their actual default character set name and default collation name !

As you can see, a lot of my old sites and databases are in latin1.

Because your WordPress has been updated, the wp-config.php file doesn’t contain all the new options, and it uses the default, including the charset default. In my case UTF8 in the new version, but as you can see, my data is in latin1 encoding.

So the fix was to add in the wp-config.php the DB_CHARSET value, to match with the database :

define ('DB_CHARSET', 'latin1');

Once you did this, all your accents and special chars should display properly !

Memo for myself mainly, but maybe it will help others.