best solution for Mysql error in rails 4 app 'Incorrect string value'?

Question

I have a rails application, in which I am using ‘delayed_job_active_record’ gem for running background jobs. While using the method ‘.delay’ with an object, I am getting the following mysql error: **

‘Incorrect string value: '\xE2\x9C\x93"\x0A ...' for column 'handler' at row 1

** I already searched for the above error and found that its because of the difference in encoding in mysql and rails. The solution suggested by many programmers is to alter the encoding in mysql database to utf8. But I also read that MySQL’s utf8 charset only partially implements proper UTF-8 encoding. It can only store UTF-8-encoded symbols that consist of one to three bytes; encoded symbols that take up four bytes aren’t supported. Which might cause trouble in some other cases. Also, when I tried to insert the value directly in mysql, it worked like a charm. Suggesting that the issue might lie elsewhere. So, can anyone please suggest the right method to rectify this problem?

Answer 1

Today, I found a fixed a very similar bug.

You say:

when I tried to insert the value directly in mysql, it worked like a charm

... it's not clear whether you're inserting the value into the model, or into the DelayedJob#handler column?

In my case, the problem was, certain columns in my (old, legacy) database had DEFAULT CHARSET=latin1 ... so, I needed to manually convert them to UTF8.

Specifically, the model that .delay was being called upon was UTF8, but the delayed_jobs table was latin1. So it was only when the app serialized the UTF8 model and attempted to insert it into the latin1 handler column of the delayed_jobs table, that the exception was raised. It's a little tricky.

Here is the core of the migration I wrote to convert the rando-latin1 tables to utf8:

%w( table1
table2
table3 ).each do |latin1_table_with_char_columns|
  execute("ALTER TABLE #{latin1_table_with_char_columns} CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;")
end

Here is a good, related StackOverflow post which is more generally about converting db columns to UTF8: How to convert an entire MySQL database characterset and collation to UTF-8?

Best of luck!