Lesson learned – MySQL client character encoding matters

During some maintenance work on PaperC, namingly fixing a bug in the search system, I stumbled upon a fatal mistake.

I forgot to set the encoding: utf8 flag in the database.yml on the production server.

production:
  ...
  encoding: utf8

This lead to the fact that all tables were created with CHARSET=latin1 due to (mysql)defaults on the production server. But the nature of rails enforces utf8 everywhere, therefore utf8-encoded data was stored inside latin1 tables. Actually it worked for quite a long time, because saving data and displaying it wasn’t buggy at all – the data itself was utf8 encoded. But the other truth is: (thinking) sphinx connects explicitly via utf8 encoding.

The bug was discovered by expecting an important book with umlauts in the search results. Digging deeper, I’ve discovered that all search queries with umlauts where buggy. At this time the database stored about 900 mb of data.

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

As a consequence I’ve written this little capistrano task to create the correct database.yml during deployment:

Snippet from config/deploy.rb:
You could hook this to after setup or somehwere there.

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

What did I do to rescue the data from the table encoding mismatch?

After some research on the web for practical solutions, and some trial and error on the staging database, I’ve choosen this procedere:

Set the website into maintenance mode, stop all background workers
Dump the production database with the following command¹:

$ mysqldump -u root -p --opt --default-character-set=latin1 paperc_production -r paperc_production-latin1.sql

Replace all CHARSET and NAMES instructions to be utf8²:

$ replace "CHARSET=latin1" "CHARSET=utf8" "SET NAMES latin1" "SET NAMES utf8" < paperc_production-latin1.sql > paperc_production-utf8.sql

Try to restore the dump on another database:

$ mysql -u root -p paperc_staging < paperc_production-utf8.sql

Connect to the staging server, do a reindex, check the data, pull some samples to see if everything is alright
Restore the dump on the production database
Do a complete reindex, unset maintenance mode and restart background workers

And, thank science, it worked!

¹ I’ve used the -r option to write the data to the disk, because using the unix output pipe > may cause faulty encoding

² Search and replace using a text editor didn’t went well on a ~ 900mb file.