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

User · Answer

To fix this error I upgraded my MySQL database to utf8mb4 which supports the full Unicode character set by following this detailed tutorial  I suggest going through it carefully  because there are quite a few gotchas  e g  the index keys can become too large due to the new encodings after which you have to modify field types

User · Answer

I tried almost every steps mentioned here  None worked  Downloaded mariadb  It worked  I know this is not a solution yet this might help somebody to identify the problem quickly or give a temporary solution   Server version  10 2 10-MariaDB - MariaDB Server Protocol version  10 Server charset  UTF-8 Unicode  utf8

User · Answer

In my case  Incorrect string value    xCC x88      the problem was that an o-umlaut was in its decomposed state  This question-and-answer helped me understand the difference between o   and     In PHP  the fix for me was to use PHP s Normalizer library  E g   Normalizer  normalize  o     Normalizer  FORM C

User · Answer

I have tried all of the above solutions  which all bring valid points   but nothing was working for me    Until I found that my MySQL table field mappings in C  was using an incorrect type  MySqlDbType Blob   I changed it to MySqlDbType Text and now I can write all the UTF8 symbols I want    p s  My MySQL table field is of the  LongText  type  However  when I autogenerated the field mappings using MyGeneration software  it automatically set the field type as MySqlDbType Blob in C     Interestingly  I have been using the MySqlDbType Blob type with UTF8 characters for many months with no trouble  until one day I tried writing a string with some specific characters in it   Hope this helps someone who is struggling to find a reason for the error

User · Answer

That error means that either you have the string with incorrect encoding  e g  you re trying to enter ISO-8859-1 encoded string into UTF-8 encoded column   or the column does not support the data you re trying to enter  In practice  the latter problem is caused by MySQL UTF-8 implementation that only supports UNICODE characters that need 1-3 bytes when represented in UTF-8  See  quot Incorrect string value quot  when trying to insert UTF-8 into MySQL via JDBC  for details  The trick is to use column type utf8mb4 instead of type utf8 which doesn t actually support all of UTF-8 despite the name  The former type is the correct type to use for all UTF-8 strings

User · Answer

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

User · Answer

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

User · Answer

Hi i also got this error when i use my online databases from godaddy server i think it has the mysql version of 5 1 or more  but when i do from my localhost server  version 5 7  it was fine after that i created the table from local server and copied to the online server using mysql yog i think the problem is with character set  Screenshot Here

User · Answer

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

User · Answer

In my case that problem was solved by changing Mysql column encoding to  binary   data type will be changed automatically to VARBINARY   Probably I will not be able to filter or search with that column  but I m no need for that

User · Answer

I would not suggest Richies answer  because you are screwing up the data inside the database  You would not fix your problem but try to  hide  it and not being able to perform essential database operations with the crapped data   If you encounter this error either the data you are sending is not UTF-8 encoded  or your connection is not UTF-8  First  verify  that the data source  a file       really is UTF-8   Then  check your database connection  you should do this after connecting   SET NAMES  utf8   SET CHARACTER SET utf8    Next  verify that the tables where the data is stored have the utf8 character set   SELECT    tables   TABLE NAME      collations   character set name  FROM    information schema   TABLES  AS  tables      information schema   COLLATION CHARACTER SET APPLICABILITY  AS  collations  WHERE    tables   table schema    DATABASE     AND  collations   collation name     tables   table collation      Last  check your database settings   mysql gt  show variables like   colla    mysql gt  show variables like   charac      If source  transport and destination are UTF-8  your problem is gone

User · Answer

xE4 xC5 xCC xC9 xD3 xD8  isn t valid UTF-8   Tested using Python    gt  gt  gt    xE4 xC5 xCC xC9 xD3 xD8  decode  utf-8       UnicodeDecodeError   utf8  codec can t decode bytes in position 0-2  invalid data   If you re looking for a way to avoid decoding errors within the database  the cp1252 encoding  aka  Windows-1252  aka  Windows Western European   is the most permissive encoding there is - every byte value is a valid code point   Of course it s not going to understand genuine UTF-8 any more  nor any other non-cp1252 encoding  but it sounds like you re not too concerned about that

User · Answer

I added binary before the column name and solve the charset error   insert into tableA values binary stringcolname1

User · Answer

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

User · Answer

There s good answers in here  I m just adding mine since I ran into the same error but it turned out to be a completely different problem   Maybe on the surface the same  but a different root cause    For me the error happened for the following field    Column nullable   false  columnDefinition    VARCHAR 255    private URI consulUri    This ends up being stored in the database as a binary serialization of the URI class  This didn t raise any flags with unit testing  using H2  or CI integration testing  using MariaDB4j   it blew up in our production-like setup   Though  once the problem was understood  it was easy enough to see the wrong value in the MariaDB4j instance  it just didn t blow up the test   The solution was to build a custom type mapper   package redacted   import javax persistence AttributeConverter  import java net URI  import java net URISyntaxException   import static java lang String format   public class UriConverter implements AttributeConverter lt URI  String gt         Override     public String convertToDatabaseColumn URI attribute            return attribute toString                Override     public URI convertToEntityAttribute String field            try               return new URI field                     catch  URISyntaxException e                throw new RuntimeException format  could not convert database field to URI   s   field                        Used as follows    Column nullable   false  columnDefinition    VARCHAR 255     Convert converter   UriConverter class  private URI consulUri    As far as Hibernate is involved  it seems it has a bunch of provided type mappers  including for java net URL  but not for java net URI  which is what we needed here

User · Answer

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

User · Answer

1 - You have to declare in your connection the propertie of enconding UTF8  http   php net manual en mysqli set-charset php   2 - If you are using mysql commando line to execute a script  you have to use the flag  like  Cmd  C  wamp64 bin mysql mysql5 7 14 bin mysql exe  -h localhost -u root  -P 3306   --default-character-set utf8 omega empresa parametros 336  lt  C  wamp64 www PontoEletronico PE10002Corporacao BancoDeDadosModelo omega empresa parametros sql

User · Answer

I got a similar error  Incorrect string value    xD0 xBE xDO xB2       for  content  at row 1   I have tried to change character set of column to utf8mb4 and after that the error has changed to  Data too long for column  content  at row 1   It turned out that mysql shows me wrong error  I turned back character set of column to utf8 and changed type of the column to MEDIUMTEXT  After that the error disappeared  I hope it helps someone  By the way MariaDB in same case  I have tested the same INSERT there  just cut a text without error

User · Answer

Although your collation is set to utf8 general ci  I suspect that the character encoding of the database  table or even column may be different    ALTER TABLE tabale name MODIFY COLUMN column name VARCHAR 255    CHARACTER SET utf8 COLLATE utf8 general ci NOT NULL

User · Answer

I solved this problem today by altering the column to  LONGBLOB  type which stores raw bytes instead of UTF-8 characters   The only disadvantage of doing this is that you have to take care of the encoding yourself  If one client of your application uses UTF-8 encoding and another uses CP1252  you may have your emails sent with incorrect characters  To avoid this  always use the same encoding  e g  UTF-8  across all your applications   Refer to this page http   dev mysql com doc refman 5 0 en blob html for more details of the differences between TEXT LONGTEXT and BLOB LONGBLOB  There are also many other arguments on the web discussing these two

User · Answer

MySQL   s utf-8 types are not actually proper utf-8     it only uses up to three bytes per character and supports only the Basic Multilingual Plane  i e  no Emoji  no astral plane  etc     If you need to store values from higher Unicode planes  you need the utf8mb4 encodings

User · Answer

If you happen to process the value with some string function before saving  make sure the function can properly handle multibyte characters  String functions that cannot do that and are  say  attempting to truncate might split one of the single multibyte characters in the middle  and that can cause such string error situations   In PHP for instance  you would need to switch from substr to mb substr

[mysql] How to fix "Incorrect string value" errors?

Examples related to mysql