How to fix “Incorrect string value” errors?

Question

After noticing an application tended to discard random emails due to incorrect string value errors, I went though and switched many text columns to use the utf8 column charset and the default column collate (utf8_general_ci) so that it would accept them. This fixed most of the errors, and made the application stop getting sql errors when it hit non-latin emails, too.

Despite this, some of the emails are still causing the program to hit incorrect string value errrors: (Incorrect string value: '\xE4\xC5\xCC\xC9\xD3\xD8...' for column 'contents' at row 1)

The contents column is a MEDIUMTEXT datatybe which uses the utf8 column charset and the utf8_general_ci column collate. There are no flags that I can toggle in this column.

Keeping in mind that I don't want to touch or even look at the application source code unless absolutely necessary:

• What is causing that error? (yes, I know the emails are full of random garbage, but I thought utf8 would be pretty permissive)
• How can I fix it?
• What are the likely effects of such a fix?

One thing I considered was switching to a utf8 varchar([some large number]) with the binary flag turned on, but I'm rather unfamiliar with MySQL, and have no idea if such a fix makes sense.

Answer 1

The solution for me when running into this Incorrect string value: '\xF8' for column error using scriptcase was to be sure that my database is set up for utf8 general ci and so are my field collations. Then when I do my data import of a csv file I load the csv into UE Studio then save it formatted as utf8 and Voila! It works like a charm, 29000 records in there no errors. Previously I was trying to import an excel created csv.

Answer 2

In general, this happens when you insert strings to columns with incompatible encoding/collation.

I got this error when I had TRIGGERs, which inherit server's collation for some reason. And mysql's default is (at least on Ubuntu) latin-1 with swedish collation. Even though I had database and all tables set to UTF-8, I had yet to set my.cnf:

/etc/mysql/my.cnf :

[mysqld]
character-set-server=utf8
default-character-set=utf8

And this must list all triggers with utf8-*:

select TRIGGER_SCHEMA, TRIGGER_NAME, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, DATABASE_COLLATION from information_schema.TRIGGERS

And some of variables listed by this should also have utf-8-* (no latin-1 or other encoding):

show variables like 'char%';

Answer 3

I added binary before the column name and solve the charset error.

insert into tableA values(binary stringcolname1);

Answer 4

The table and fields have the wrong encoding; however, you can convert them to UTF-8.

ALTER TABLE logtest CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER TABLE logtest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;

Answer 5

In my case ,first i've meet a '???' in my website, then i check Mysql's character set which is latin now ,so i change it into utf-8,then i restart my project ,then i got the same error with you , then i found that i forget to change the database's charset and change into utf-8, boom,it worked.

Answer 6

First check if your default_character_set_name is utf8.

SELECT default_character_set_name FROM information_schema.SCHEMATA S WHERE schema_name = "DBNAME";

If the result is not utf8 you must convert your database. At first you must save a dump.

To change the character set encoding to UTF-8 for all of the tables in the specified database, type the following command at the command line. Replace DBNAME with the database name:

mysql --database=DBNAME -B -N -e "SHOW TABLES" | awk '{print "SET foreign_key_checks = 0; ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; SET foreign_key_checks = 1; "}' | mysql --database=DBNAME

To change the character set encoding to UTF-8 for the database itself, type the following command at the mysql> prompt. Replace DBNAME with the database name:

ALTER DATABASE DBNAME CHARACTER SET utf8 COLLATE utf8_general_ci;

You can now retry to to write utf8 character into your database. This solution help me when i try to upload 200000 row of csv file into my database.