CODEWITHSUNDEEP
mysqlruby-on-railsactiverecordutf-8percona
Dom Hodgson
Dom Hodgson

Reputation: 508

Mysql2::Error: Incorrect string value Rails 3 UTF8

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.

Upvotes: 2

Views: 3864

Answers (2)

user160917
user160917

Reputation: 9350

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

Upvotes: 8

Anbin Muniandy
Anbin Muniandy

Reputation: 724

I had encountered this issue recently. It's essentially mysql default collation type is not utf8_unicode_ci.

Do the following. Backup your data if you have to. I had to drop the database and recreate it

rake db:drop
rake db:create

Change mysql database collation to utf8_unicode_ci ( phpMyAdmin might come in handy here) Finally, restore your migration. 

rake db:migrate

Enjoy.

Upvotes: 3

Related Questions