Skip to content

Lesson learned: MySQL client character encoding matters

Recovering a large production database from a MySQL client-encoding mismatch.

Published
Reading time
3 min read

While fixing a PaperC search bug, I discovered a serious configuration mistake: the production database.yml did not set encoding: utf8.

production:
  ...
  encoding: utf8

Because of the production server’s MySQL defaults, every table was created with CHARSET=latin1. Rails nevertheless sent UTF-8 data, so UTF-8 bytes ended up in tables declared as Latin-1. Saving and displaying data appeared to work because the bytes remained intact, but Thinking Sphinx connected explicitly with UTF-8 expectations.

I discovered the mismatch when an important book containing umlauts failed to appear in search results. Further investigation showed that every search involving umlauts was broken. By then, the database contained about 900 MB of data.

Note to myself: Always check your database configuration for the encoding flag, both in development and production.

Generate the correct database configuration during deployment

I wrote a small Capistrano task to generate database.yml with an explicit encoding. The task can run after the initial setup:

namespace :db do
  desc "upload the result of config/database.yml.erb to \#{shared_path}/config/database.yml"
  task :configure, :roles => :db do
    db_configuration = ERB.new File.read("#{local_root}/config/database.yml.erb")
    set :db_host, 'localhost'
    set :db_user, user
    set :db_password, ask("Type the mysql password on #{db_host} for #{db_user}: ")
    put db_configuration.result(binding), "#{shared_path}/config/database.yml"
  end
end

In config/database.yml.erb:

common: &common
  adapter: mysql
  username: <%= db_user %>
  password: <%= db_password %>
  host: <%= db_host %>
  encoding: utf8

development:
  <<: *common
  database: <%= application %>_development

test:
  <<: *common
  database: <%= application %>_test

production:
  <<: *common
  database: <%= application %>_production

staging:
  <<: *common
  database: <%= application %>_staging

How I rescued the data

After researching practical solutions and testing them against the staging database, I used this recovery procedure:

  • Put the website into maintenance mode and stop all background workers.
  • Dump the production database with the following command.1
$ mysqldump -u root -p --opt --default-character-set=latin1 paperc_production -r paperc_production-latin1.sql
  • Replace every CHARSET and NAMES instruction with its UTF-8 equivalent.2
$ replace "CHARSET=latin1" "CHARSET=utf8" "SET NAMES latin1" "SET NAMES utf8" < paperc_production-latin1.sql > paperc_production-utf8.sql
  • Restore the dump into a separate database first.
$ mysql -u root -p paperc_staging < paperc_production-utf8.sql
  • Connect to staging, rebuild the search index, inspect the data, and verify representative samples.
  • Restore the validated dump into the production database.
  • Rebuild the complete search index, leave maintenance mode, and restart the background workers.

The procedure worked.

1 I used the -r option to write directly to disk because a Unix output pipe could alter the encoding.

2 Search and replace in a text editor did not work well on a file of roughly 900 MB.