CODEWITHSUNDEEP
mysql
DynastySS
DynastySS

Reputation: 395

Mysql - Mysql2::Error: Incorrect string value:

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

Upvotes: 9

Views: 10584

Answers (3)

Pedro Assumpcao
Pedro Assumpcao

Reputation: 109

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

Upvotes: 10

Mikey
Mikey

Reputation: 2951

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

Upvotes: 17

Anbin Muniandy
Anbin Muniandy

Reputation: 724

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.

Upvotes: 5

Related Questions