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

addis_a · January 5, 2015, 11:50am

Hi guys,

I have a rails application, in which I am using
‘delayed_job_active_record
https://github.com/collectiveidea/delayed_job’ 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 https://mathiasbynens.be/notes/mysql-utf8mb4 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?

Any help would be greatly appreciated.

Thanks,

Kriti

Kriti_A · January 5, 2015, 5:56pm

On Mon, Jan 5, 2015 at 2:44 AM, Kriti A. [email protected]
wrote:

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.

So, what encodings/collations are you using? And what version of
MySQL? What versions of Ruby and Rails?

–
Hassan S. ------------------------ [email protected]

twitter: @hassan

Kriti_A · January 5, 2015, 7:01pm

Following are the encodings/collations and versions that I am using:
Rails : utf8, 4.0
Mysql: latin_general_ci, 14.14 Distrib 5.5.40

Kriti_A · January 6, 2015, 6:37am

Thanks for the insight, but I think the issue is at the rails level.
Since
when I tried inserting the same values in the mysql db directly, it
worked
without any errors. It might be because the character that I am trying
to
insert is not UTF8. But rails is enforcing that encoding. What would be
the
encoding change required to be done at rails level?

Also, I have a system where I would need to scale things later. So, I
would
have to change the encoding of a particular table every time I have to
create a new instance. Is there any way to deal with that?

Kriti_A · January 6, 2015, 7:28am

On Mon, Jan 5, 2015 at 9:36 PM, Kriti A. [email protected]
wrote:

Thanks for the insight, but I think the issue is at the rails level.

Have you confirmed that all the character set/collation settings I
mentioned are consistent? Including the encoding specified in
config/database.yml?

That’s basic configuration. If you don’t fix it, well, good luck.

Also, I have a system where I would need to scale things later. So, I would
have to change the encoding of a particular table every time I have to
create a new instance. Is there any way to deal with that?

Sorry, I have no idea what the above paragraph means. Can you
clarify “create a new instance”?

–
Hassan S. ------------------------ [email protected]

twitter: @hassan

Kriti_A · January 5, 2015, 7:14pm

On Mon, Jan 5, 2015 at 10:00 AM, Kriti A. [email protected]
wrote:

Following are the encodings/collations and versions that I am using:
Rails : utf8, 4.0
Mysql: latin_general_ci, 14.14 Distrib 5.5.40

Well, it should be pretty obvious you have a mismatch there, which is
a problem. But there’s not just one encoding-related variable. Example:

You want, at the least, all of those consistent. If you need the 4-byte
character capability, then set them appropriately.

Note: you’ll need to manually alter (or drop and recreate) existing
databases/tables afterwards.

HTH,

Hassan S. ------------------------ [email protected]

twitter: @hassan