Mysql2::Error: Incorrect string value Rails 3 UTF8

Question

I've got an app which deals with a lot of data in other languages (webpage titles and meta descriptions). I recently switched from MySQL to Percona and have found all the errors which MySQL seemed to have silently errored on.

The only one I haven't managed to fix is

ActiveRecord::StatementInvalid: Mysql2::Error: Incorrect string value: 

I've looked at the current questions and answers but they all assume a known encoding to convert to UTF8, I don't know the encoding.

What I'd like to do is get rails to convert it to UTF8 (I've tried force_encoding but that didn't work) or I'd like to check if it's UTF8 and if not get rid of it.

Answer 1

I ran into this problem when trying to insert unicode emoji into a mysql database... You might think you are safe with utf8, you're not. Emoji uses 4 bytes, and utf8 only uses 3 bytes. The solution is to use utf8mb4 encoding and collation.

You can do this by first updateing your database.yml too look like

development:
  adapter: mysql2
  database: my_database
  username: a_user
  password: the_password
  encoding: utf8mb4
  collation: utf8mb4_unicode_ci

If you have an existing database, you need to run this sql:

ALTER TABLE `[table]` 
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
MODIFY [column] VARCHAR(250)
    CHARACTER SET utf8mb4 COLLATE utf8mb4_bin

If your haven't released to production yet, you can just do

bundle exec rake db:drop db:create db:migrate

Shamelessly taken from Jason Seifer