0

I am having trouble inserting utf8 string into a mysql database using ‘mysql connector/j ver 8.0.29’

I am working on a java springboot application.

This problem does not happen in ‘mysql connector/j ver 8.0.27’

Anybody encounter this problem?

—- Mysql server is also 8.0.29 but there is no problem since I can successfully execute ‘INSERT’ sql command using UTF-8 on the server itself

It is only when sending ‘INSERT’ sql command via client pc using springboot that this problem happen

Server pc OS is windows10, client pc is Windows11

This is my table.

enter image description here

This is the error.

Caused by: java.sql.SQLException: Incorrect string value: '\x95|\x82\xA2\x98b' for column 'path' at row 1
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)

this is the connection string:

spring.datasource.url=jdbc:mysql://localhost/${xpac.sql-database-name}?serverTimezone=Asia/Tokyo

this is the error:

Caused by: java.sql.SQLException: Incorrect string value: '\x95|\x82\xA2\x98b' for column 'path' at row 1 at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)

怖い話 ---> '\x95|\x82\xA2\x98b'

jetpack
  • 169
  • 1
  • 9
  • Show us the connection parameters. Verify that the encoding in the client is UTF-8. – Rick James Jun 23 '22 at 16:15
  • I did not set character encoding in application.properties, it uses default, which is UTF-8 – jetpack Aug 16 '22 at 16:51
  • mysql connector/j 8.0.29 has this change in its release https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-29.html#mysqld-8-0-29-charset – jetpack Aug 16 '22 at 16:54
  • anybody encounter this problem? – jetpack Aug 16 '22 at 16:57
  • "UTF-8" is not a MySQL Character Set. Please provide the exact text of the connection. To check the client encoding, can you display the HEX of what is about to be inserted. – Rick James Aug 16 '22 at 17:05
  • this is the connection string: spring.datasource.url=jdbc:mysql://localhost/${xpac.sql-database-name}?serverTimezone=Asia/Tokyo – jetpack Aug 17 '22 at 00:51
  • I edit my post and included information regarding connection string at the bottom – jetpack Aug 17 '22 at 00:52
  • This problem happens when I upgraded mysql connection/j version from 8.0.27 to 8.0.29 – jetpack Aug 17 '22 at 00:53
  • In mysql connection/j version from 8.0.27, this problem does not happen – jetpack Aug 17 '22 at 00:54
  • `dirpath` is in the error message; `path` is in the image. – Rick James Aug 17 '22 at 04:47
  • its `path`....not `dirpath` – jetpack Aug 17 '22 at 05:33
  • I tried to use the connection string `&useUnicode=yes&characterEncoding=UTF-8`...but the error still happens – jetpack Aug 17 '22 at 05:33
  • I also tried to change server settings, `my.ini` ...default-character-set = utf8mb4, character-set-server = utf8mb4....but still the error happens – jetpack Aug 17 '22 at 05:35
  • The server itself when I tried to insert string in the table, there is no problem...seems its the mysql connector/j for ver 8.0.29 is causing the problem.... – jetpack Aug 17 '22 at 05:36
  • If possible, dump a string from Java in hex. (I agree that the minor upgrade should not have led to the error; I am trying to figure out what is wrong now, then work backward.) – Rick James Aug 17 '22 at 16:44

2 Answers2

0

Somewhere in the processing, either cp932 or sjis was used.

'Proof' -- Using either CHARACTER SET cp932 or sjis in

HEX(CONVERT('怖い話' USING ...)) => '957C82A29862'

Based on your comments it seems to be caused by a change in Connector/J

(No, I don't know how to fix it; but this might help in chasing it down.)

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • thanks for this, server has `character_set_client`, `character_set_connection`, `character_set_results` as `cp932 ` which is the default..........when I changed it to `utf8mb4`, error still happens, may be I miss something here – jetpack Aug 18 '22 at 00:47
  • Presumably, the bad setting was in effect when the data was inserted in the table. Do the SELECT suggested in https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored to see what is in the table. I suspect you will get 957C... – Rick James Aug 18 '22 at 02:13
  • this is what happened, when I save the value just in my pc(local), the value is `??????`....but when I saved it on server (remote PC),, SQLException `Incorrect String...` happens and data is not saved in the remote database – jetpack Aug 18 '22 at 12:20
  • i have found the culprit, `path` is defined as LONGTEXT in database, in the java code it was annotated as `@Lob`, this is causing the problem....without the @Lob annotation, everything is working fine.....this only happens in mysql connector/j ver 8.0.29 – jetpack Aug 18 '22 at 16:08
  • @jetpack - `TEXT` has a "character set"; violation of that can lead to '???; or other garbage. `BLOB` blindly takes the bytes. – Rick James Aug 18 '22 at 16:12
0

Found the cause.

The java application itself when run as service uses the OS default character encoding. Which is obviously not UTF-8. This causes unexpected errors when the application is interacting with a database.

When running the application, it is advisable to explicitly specify that it should use UTF-8. And this is done by specifying the java option Dfile.encoding=utf-8

Perhaps in mysql ver 8.0.27, it forces data to be saved to use UTF-8. Then when it was updated to mysql ver 8.0.29 this was removed and you have to specify explicitly that you will use UTF-8 encoding.

jetpack
  • 169
  • 1
  • 9