Ash Searle’s Blog

If you find yourself in a position where your mysqldump backup/restore process isn’t working, it’s worth checking for character-encoding issues – and the best way to do this is often to look at the SQL in your backup file.

To tell vim you prefer working in unicode, you may have added some settings to your .vimrc:

set encoding=utf-8 fenc=utf-8

That doesn’t get vim to treat existing files as UTF-8 though! Vim tries to figure-out the encoding itself, and may well get it wrong.

Looking at the mysqldump file, you could see garbage like:

One naïve approach

Something’s horribly wrong there. It looks like an encoding issue, and a quick :set fenc shows you whether vim opened the file in latin1 or utf-8.

You can force vim to re-open the file in utf-8 using:

:e ++enc=utf8 %

Hopefully, you now see:

One naïve approach

Now you know you’ve got unicode in your mysqldump, you can fix the restore process by a bit of search-and-replace on connection settings and table-creation statements. i.e you’re looking for lines like:

/*!40101 SET NAMES latin1 */;

…and…

ENGINE=MyISAM DEFAULT CHARSET=latin1;

Switch those from latin1 to utf8 and, fingers-crossed, you should be able to restore your db backup, and upgrade all your tables to utf8 in the process.

Spurred on by mass hacking, I’ve updated my old (version 2.0.3) WordPress install to something a little newer.

I decided to err on the side of caution and upgrade a copy of the live DB first – and I’m glad I did. I saw lots of problems with accents and symbols getting munged in the upgrade – well, that’s what I thought.

In reality, the symbols were being screwed in the backup-and-restore process. Words such as “naïve”, £-signs and various typographic quotes were obviously getting messed up through character-encoding issues.

After a bit of sleuthing, I found the output from mysqldump wasn’t valid. The problem was caused by a combination of default connection settings in a .my.cnf, and the fact that the old WordPress install was storing utf-8 characters inside a latin1 database.

My MySQL default settings are:


[client]
default-character-set=utf8


Because we’re pulling latin1 data over a utf-8 connection, MySQL starts doing character-set conversions, and screws up a bunch of text that it thinks is latin1, but in reality is actually already in utf-8.

The fix for the backup process was to override my default settings with latin1:


$ mysqldump --default-character-set=latin1 --opt -h db.example.com -u user -ppassword schema > db-backup-latin1-20080707.sql


That worked fine, and the next step was reloading it into the DB as utf-8. This required a little bit of string replacement using a command-line utility bundled with MySQL. If you’re going to do this yourself, watch what you type: I somehow typed in “lastin1″ halfway through and lost an hour or so trying to figure out what went wrong. Anyhow, here’s the command-line:


$ replace "CHARSET=latin1" "CHARSET=utf8" "SET NAMES latin1" "SET NAMES utf8" < db-backup-latin1-20080707.sql > db-backup-utf8-20080707.sql


You should now be able to blat / restore / overwrite your DB and ensure all tables are in the appropriate character set, ready for a smooth wordpress upgrade.


$ mysql --opt -h db.example.com -u user -ppassword schema < db-backup-utf8-20080707.sql