3

I have java application through which I do different operations on MySQL DB. The probleam is that when inserting utf8 String it is not inserted correctly. The charset of DB is utf8 and I have set collation to utf8_unicode_ci. Server connection collation is also utf8_unicode_ci. Furthermore when I insert data from phpMyAdmin it is inserted correctly, but when I do it from Java application using JOOQ - it is not. Example:

Result<ExecutorsRecord> executorsRecord =
                        context.insertInto(EXECUTORS, EXECUTORS.ID, EXECUTORS.NAME, EXECUTORS.SURNAME, EXECUTORS.REGION, EXECUTORS.PHONE, EXECUTORS.POINTS, EXECUTORS.E_TYPE)
                                .values(id, name, surname, region, phone, 0, type)
                                .returning(EXECUTORS.ID)
                                .fetch();

where name = "Бобр" and surname = "Добр", produces tuple with ???? as a name and ???? as surname. I have checked both strings, they are passed correctly to the method correctly.

Benny
  • 51
  • 4
FirePapaya
  • 509
  • 5
  • 21
  • 1
    Stated here is the server connection collation... have you verified that the encoding is specified correctly on the JDBC connection from Java e.g. `?useUnicode=yes&characterEncoding=UTF-8` (My suspicion is that the JDBC driver/connection is "helping" in a way that is not very helpful.) – spencer7593 May 02 '18 at 06:06
  • @spencer7593 could you please link some documentation? – FirePapaya May 02 '18 at 06:08
  • Which JDBC driver are you using, MySQL Connector/J or the MariaDB Connector/J ? and what version ? https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-charsets.html and https://mariadb.com/kb/en/library/about-mariadb-connector-j/ – spencer7593 May 02 '18 at 06:13
  • Also check the strings that you pass. Sometimes the unicode characters can be lost before the insert statement (for example if you pass the value between strings etc) – Veselin Davidov May 02 '18 at 06:15
  • @spencer7593, thank you. I have added `?characterEncoding=utf8`, now it works. Consider writing an answer, i guess it might be helpful for someone. – FirePapaya May 02 '18 at 06:18
  • My suggestion was just a guess. When we've got things configured correctly, and things aren't working, our goto suspect is the JDBC driver. To get timezone differences between the JVM and the MySQL database sorted out, to prevent the JDBC driver from "helping" by doing an illogical combination of various operations, we had to add two extra obscurely documented settings to the connection string. I suggest you write an answer to the question. I'm sure you are not the only one that has run into this issue. – spencer7593 May 02 '18 at 06:29
  • See "question mark" in https://stackoverflow.com/questions/38363566/trouble-with-utf-8-characters-what-i-see-is-not-what-i-stored – Rick James May 03 '18 at 04:04
  • @RickJames, thanks. Added a link to the answer as further reading. – FirePapaya May 03 '18 at 12:22

1 Answers1

2

As @spencer7593 suggested the problem could be in JDBC connector. So I added into url of connection following: ?characterEncoding=utf8 so that final url was "jdbc:mysql://localhost:3306/mydb?characterEncoding=utf8", where mydb is a name of database. This has sorted out my problem. Also I would like to add the following statement (again by @spencer7593):

When we've got things configured correctly, and things aren't working, our goto suspect is the JDBC driver. To get timezone differences between the JVM and the MySQL database sorted out, to prevent the JDBC driver from "helping" by doing an illogical combination of various operations, we had to add two extra obscurely documented settings to the connection string.

Further reading

FirePapaya
  • 509
  • 5
  • 21