java sql SQLException Incorrect string value xF0 x9F x91 xBD xF0 x9F

Question

I have the following string value   walmart obama    I am using MySQL and Java   I am getting the following exception   java sql SQLException  Incorrect string value    xF0 x9F x91 xBD xF0 x9F      Here is the variable I am trying to insert into   var1 varchar 255  CHARACTER SET utf8 COLLATE utf8 general ci NOT NULL    My Java code that is trying to insert  walmart obama   is a preparedStatement  So I am using the setString   method   It looks like the problem is the encoding of the values   How can I fix this  Previously I was using Derby SQL and the values  just ended up being two sqaures  I think this is the representation of the null character   All help is greatly appreciated

User · Answer

Weirdly  I found that REMOVING  amp characterEncoding UTF-8 from the JDBC url did the trick for me with similar issues   Based on my properties   jdbc url jdbc mysql   localhost 3306 dbName useUnicode true   I think this supports what  Esailija has said above  i e  my MySQL  which is indeed 5 5  is figuring out its own favorite flavor of UTF-8 encoding    Note  I m also specifying the InputStream I m reading from as UTF-8 in the java code  which probably doesn t hurt

User · Answer

What you have is EXTRATERRESTRIAL ALIEN  U 1F47D  and BROKEN HEART  U 1F494  which are not in the basic multilingual plane  They cannot be even represented in java as one char     length      4  They are definitely not null characters and one will see squares if you are not using fonts that support them   MySQL s utf8 only supports basic multilingual plane  and you need to use utf8mb4 instead      For a supplementary character  utf8 cannot store the character at all    while utf8mb4 requires four bytes to store it  Since utf8 cannot store   the character at all  you do not have any supplementary characters in   utf8 columns and you need not worry about converting characters or   losing data when upgrading utf8 data from older versions of MySQL    So to support these characters  your MySQL needs to be 5 5  and you need to use utf8mb4 everywhere  Connection encoding needs to be utf8mb4  character set needs to be utf8mb4 and collaction needs to be utf8mb4  For java it s still just  utf-8   but MySQL needs a distinction   I don t know what driver you are using but a driver agnostic way to set connection charset is to send the query   SET NAMES  utf8mb4    Right after making the connection    See also this for Connector J      14 14  How can I use 4-byte UTF8  utf8mb4 with Connector J       To use 4-byte UTF8 with Connector J configure the MySQL server with   character set server utf8mb4  Connector J will then use that setting   as long as characterEncoding has not been set in the connection   string  This is equivalent to autodetection of the character set    Adjust your columns and database as well   var1 varchar 255  CHARACTER SET utf8mb4 COLLATE utf8mb4 general ci NOT NULL   Again  your MySQL version needs to be relatively up-to-date for utf8mb4 support

User · Answer

I faced the same issue and solved it by setting the Collation to utf8 general ci for each column

User · Answer

I had kind of the same problem and after going carefully against all charsets and finding that they were all right  I realized that the bugged property I had in my class was annotated as  Column instead of  JoinColumn  javax presistence  hibernate  and it was breaking everything up

User · Answer

execute  show VARIABLES like   char        find character-set-server if is not utf8mb4   set it in your my cnf  like  vim  etc my cnf   add one line   character set server   utf8mb4   at last restart mysql

User · Answer

I guess MySQL doesn t believe this to be valid UTF8 text  I tried an insert on a test table with the same column definition  mysql client connection was also UTF8  and although it did the insert  the data I retrieved with the MySQL CLI client as well as JDBC didn t retrieve the values correctly  To be sure UTF8 did work correctly  I inserted an      instead of an  o  for obama   johan maiden    mysql -vvv test  lt  insert sql  -------------- insert into utf8 test values  utf8  walmart   bama    --------------  Query OK  1 row affected  1 warning  0 12 sec   johan maiden    file insert sql  insert sql  UTF-8 Unicode text   Small java application to test with   package test sql   import java sql Connection  import java sql DriverManager  import java sql PreparedStatement  import java sql ResultSet   public class Test        public static void main String   args                System out println  test string      walmart   bama             String url    jdbc mysql   hostname test useUnicode true amp characterEncoding UTF-8           try                       Class forName  com mysql jdbc Driver   newInstance                Connection c   DriverManager getConnection url   username    password                PreparedStatement p   c prepareStatement  select   from utf8 test                p execute                ResultSet rs   p getResultSet                while   rs isLast                                  rs next                    String retrieved   rs getString 1                   System out println  retrieved       retrieved                                           catch  Exception e                        e printStackTrace                         Output   johan appel   workspaces java javatest bin  java test sql Test test string walmart   bama  retrieved  walmart   bama     Also  I ve tried the same insert with the JDBC connection and it threw the same exception you are getting  I believe this to be a MySQL bug  Maybe there s a bug report about such a situation already

User · Answer

All in all  to save symbols that require 4 bytes you need to update characher-set and collation for utf8mb4    database table column  alter table  lt some table gt  convert to character set utf8mb4 collate utf8mb4 unicode ci database server connection  see    On my development enviromnt for  2 I prefer to set parameters on command line when starting the server  mysqld --character-set-server utf8mb4 --collation-server utf8mb4 unicode ci    btw  pay attention to Connector J behavior with SET NAMES  utf8mb4       Do not issue the query set names with Connector J  as the driver will not detect that the character set has changed  and will continue to use the character set detected during the initial connection setup    And avoid setting characterEncoding parameter in connection url as it will override configured server encoding       To override the automatically detected encoding on the client side  use the characterEncoding property in the URL used to connect to the server

User · Answer

This setting useOldUTF8Behavior true worked fine for me  It gave no incorrect string errors but it converted special characters like    into multiple characters and saved in the database   To avoid such situations  I removed this property from the JDBC parameter and instead converted the datatype of my column to BLOB  This worked perfect

User · Answer

How I solved my problem   I had   useUnicode true amp amp characterEncoding UTF-8   In my hibernate jdbc connection url and I changed the string datatype to longtext in database  which was varchar before

User · Answer

Append the line useUnicode true amp amp characterEncoding UTF-8 to your jdbc url   In your case the data is not being send using UTF-8 encoding

User · Answer

Besides data type can use blob install of varchar or text

[java] java.sql.SQLException: Incorrect string value: '\xF0\x9F\x91\xBD\xF0\x9F...'

Examples related to java

Examples related to mysql

Examples related to encoding

Examples related to character-encoding

Examples related to sqlexception