MySQL character encoding trouble pre Rails 1.2

Back in the days when Rails didn’t care about the encoding of the database connection you sometimes ended up with UTF-8 encoded strings from the browser travelling over a ISO-8859-1 encoded connection with MySQL to a UTF-8 database. This isn’t a big problem in itself as long and you don’t slice strings on the database level. When you decide to migration your application to a newer version of Rails you might run into trouble.

You know you have this problem when the characters from the first column of the following table look like the characters in the second column.

Fortunately it’s pretty easy to fix, you just need to convince mysqldump that it’s operating on a Latin-1 database and load the dump back into the database. Make sure you have a backup before you try this though!

$ mysqldump --skip-set-charset --default-character-set=latin1 databasename > fixed.sql
$ mysql databasename < fixed.sql

Now make sure that your Rails app talks UTF-8 to the database by setting the encoding in database.yml:

production:
  adapter: mysql
  database: databasename
  encoding: utf8