Java.sql.SQLException: Invalid string value: '\ xF0 \ x9F \ x91 \ xBD \ xF0 \ x9F ...'

Question

I have the following string value: "walmart obama 👽💔"

I use MySQL and Java.

I get the following exception: `java.sql.SQLException: Invalid string value: '\ xF0 \ x9F \ x91 \ xBD \ xF0 \ x9F ...'

Here is the variable I'm trying to insert:

var1 varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL` 

My Java code, which is trying to insert "walmart obama 👽💔", is a prepared Creation. Therefore, I use the setString() method.

It seems that the problem is encoding values. How can i fix this? I used to use Derby SQL, and the 👽💔 values ​​just turned out to be two sqaures (I think this is a null character representation)

All help is much appreciated!

Answer 1

What you have are EXTRATERRESTRIAL ALIEN (U+1F47D) and EXTRATERRESTRIAL ALIEN (U+1F47D) BROKEN HEART (U+1F494) which are not in the main multilingual plane. They cannot even be represented in Java as a single character, "👽💔".length() == 4 . These are definitely not null characters, and you will see squares if you are not using fonts that support them.

MySQL utf8 only supports the base multilingual plane, and you need to use utf8mb4 instead :

For an extra character, utf8 cannot store the character at all, while utf8mb4 requires four bytes to store it. Since utf8 cannot store a character at all, you have no extra characters in utf8 columns, and you don’t have to worry about character conversion or data loss when updating utf8 data from older versions of MySQL.

Thus, to support these characters, your MySQL should be 5. 5+ and you need to use utf8mb4 everywhere. The connection encoding must be utf8mb4 , the character set must be utf8mb4 and the collaction must be utf8mb4 . For java, this is still just "utf-8" , but MySQL needs a distinction.

I don’t know which driver you are using, but a driver-independent way to set the connection encoding is to send a request:

 SET NAMES 'utf8mb4' 

Immediately after establishing a 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 parameter character_set_server = utf8mb4. Then Connector / J will use this setting until the character encoding is set in the connection string. This is equivalent to automatically detecting a character set.

Customize your columns and database as well:

 var1 varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL 

Again, your MySQL version should be relatively modern to support utf8mb4.

Answer 2

Oddly enough, I found that REMOVING &characterEncoding=UTF-8 from the JDBC url me with similar problems.

Based on my properties,

 jdbc_url=jdbc:mysql://localhost:3306/dbName?useUnicode=true 

I think this supports what @Esailija said above, i.e. my MySQL, which is actually 5.5, figure out its favorite flavor of UTF-8 encoding.

(Please note, I also indicate the InputStream I read as UTF-8 in Java code, which probably won't hurt) ...

Answer 3

In general, to save characters requiring 4 bytes, you need to update the characher set and collation for utf8mb4 :

• table / database column: alter table <some_table> convert to character set utf8mb4 collate utf8mb4_unicode_ci
• connection to the database server ( see )

In my enviromnt development for # 2, I prefer to set options on the command line when starting the server: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

---

btw, notice the behavior of the / J connector with SET NAMES 'utf8mb4' :

Do not display 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 setup of the connection.

And you should not set the characterEncoding parameter in the connection URL, as it will override the configured server encoding:

To override the automatically detected client-side encoding, use the characterEncoding property in the URL used to connect to the server.

Answer 4

How I solved my problem.

I had

 ?useUnicode=true&characterEncoding=UTF-8 

In my hibernate jdbc url connection and I changed the row data type to longtext in the database, which was previously varchar.

Answer 5

I ran into the same problem and solved it by setting Collation to utf8_general_ci for each column.

Answer 6

Add the line useUnicode=true&characterEncoding=UTF-8 to the jdbc url.

In your case, data is not sent using UTF-8 .

Answer 7

I think MySQL does not consider this a valid UTF8 text. I tried pasting into a test table with the same column definition (the connection to the mysql client was also UTF8), and although it was pasting, the data I received with the MySQL CLI client as well as JDBC did not display the values ​​correctly. To make sure that UTF8 is working correctly, I inserted instead of "o" for obama "ö":

 johan@maiden:~$ mysql -vvv test < 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 for testing:

 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&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 " 

In addition, I tried the same insert with a JDBC connection, and this led to the same exception that you get. I believe this is a MySQL error. Maybe there is already an error message there.

Answer 8

I had the same problem, and after carefully studying all the encodings and finding out that everything was fine with them, I realized that the bugged property that I had in my class was annotated as @Column instead of @JoinColumn (javax .presistence; hibernate), and it all broke.

Answer 9

carry out

 show VARIABLES like "%char%"; 

find server character set if not utf8mb4.

install it in your my.cnf as

 vim /etc/my.cnf 

add one line

 character_set_server = utf8mb4 

finally restart mysql

Answer 10

This useOldUTF8Behavior = true parameter worked fine for me. He did not give the wrong string errors, but he converted the special characters, such as , to several characters and stored in the database.

To avoid such situations, I removed this property from the JDBC parameter and converted the data type of my column to BLOB instead. It worked perfectly.

Answer 11

Alternatively, the data type can use blob install from varchar or text.