Mysql - Mysql2::Error: Incorrect string value:

Question

So I built a scraper and am pulling in some objects. The issue is some are foreign languages and it is tripping the mysql db up a bit. This is the error I got. Any idea what I can do with this? Thanks!

Mysql2::Error: Incorrect string value: '\xC5\x8Dga, ...' for column 'description' at row 1: INSERT INTO sammiches (country, created_at, description, image, name, updated_at) VALUES ('Japan', '2013-05-03 01:17:06', 'A hot dog bun stuffed with fried noodles, frequently topped with pickles, such as beni shōga, with mayonnaise', '/wiki/File:Yakisoba_sandwich_by_kaex0r.jpg', 'Yakisoba-pan', '2013-05-03

Use UTF-8 in the database?

michaelb958--GoFundMonica
– michaelb958--GoFundMonica

2013-05-03 01:43:32 +00:00
Commented May 3, 2013 at 1:43 — michaelb958--GoFundMonica
– michaelb958--GoFundMonica, Commented May 3, 2013 at 1:43

Mikey · Accepted Answer · 2014-07-22 14:50:25Z

17

This can also be triggered if the string you're trying to insert has invalid UTF-8 byte sequences. For example, in ruby you can remove any invalid characters using

string_with_invalid_sequences.encode('utf-8', 'binary', invalid: :replace, undef: :replace, replace: '')

String#scrub can be used in ruby 2.1 onwards

string_with_invalid_sequences.scrub

edited Jul 22, 2014 at 14:50

answered Nov 5, 2013 at 16:13

Mikey

2,95134 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

across Over a year ago

I don't know why, but you first suggestion helped me and scrub doesn't worked for me.

Mikey Over a year ago

@across - does this work for you: string_with_invalid_sequences.scrub('')?

user1735921 Over a year ago

string_with_invalid_sequences.encode only this works for me..., none of the above solutions worked, scrub didn't work as well.

jonbarlo Over a year ago

@ user1735921 still not working? did you check your ruby version?

user160917 · Accepted Answer · 2015-02-01 19:12:02Z

10

Probably your table is set to a non Utf8 CHARACTER SET. You can change this with this sql:

ALTER TABLE `your_database_name`.`your_table` CONVERT TO CHARACTER SET utf8

edited Feb 1, 2015 at 19:12

user160917

9,3604 gold badges55 silver badges63 bronze badges

answered Aug 28, 2013 at 21:07

Pedro Assumpcao

1091 silver badge3 bronze badges

2 Comments

Michael Yagudaev Over a year ago

Inside of a migration use: ModelName.connection.execute('ALTER TABLE model_names CONVERT TO CHARACTER SET utf8')

Carson Reinke Over a year ago

@TomRossi it could be because it is outside the multilingual plane (dev.mysql.com/doc/refman/5.5/en/…)

Community · Accepted Answer · 2017-05-23 12:18:23Z

5

You can find the answer here. I am not sure how to report duplicate. Mysql2::Error: Incorrect string value Rails 3 UTF8

For your convenience, let me reiterate my answer

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.

edited May 23, 2017 at 12:18

CommunityBot

11 silver badge

answered Jun 5, 2013 at 8:07

Anbin Muniandy

7344 silver badges16 bronze badges

Collectives™ on Stack Overflow

Mysql - Mysql2::Error: Incorrect string value:

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related