CODEWITHSUNDEEP
mysqlutf8mb4connector-j
cadams500
cadams500

Reputation: 73

MySQL 5.5 Connector/J CharacterSet Encoding (utf8mb4) Issue

I am trying to properly store utf8mb4 strings into MySQL 5.5.30. We are using ConnectorJ 5.1.18.

According to the documentation ConnectorJ should autodetect the character encoding based upon the character_set_server variable...

However, as best as I can tell it is always defaulting to

SET NAMES latin1 instead of SET NAMES utf8mb4

Log output from the connector-j profiler:

Sat Jul 06 15:45:20 CDT 2013 INFO: Profiler Event: [QUERY]  at java.sql.DriverManager.getConnection(DriverManager.java:579) duration: 1 ms, connection-id: 57, statement-id: 3, resultset-id: 4, message: /* mysql-connector-java-5.1.18 ( Revision: [email protected] ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'
Sat Jul 06 15:45:20 CDT 2013 INFO: Profiler Event: [FETCH]  at java.sql.DriverManager.getConnection(DriverManager.java:579) duration: 0 ms, connection-id: 57, statement-id: 3, resultset-id: 4
Sat Jul 06 15:45:20 CDT 2013 INFO: Profiler Event: [QUERY]  at java.sql.DriverManager.getConnection(DriverManager.java:579) duration: 0 ms, connection-id: 57, statement-id: 3, resultset-id: 5, message: /* mysql-connector-java-5.1.18 ( Revision: [email protected] ) */SELECT @@session.auto_increment_increment
Sat Jul 06 15:45:20 CDT 2013 INFO: Profiler Event: [FETCH]  at java.sql.DriverManager.getConnection(DriverManager.java:579) duration: 0 ms, connection-id: 57, statement-id: 3, resultset-id: 5
Sat Jul 06 15:45:20 CDT 2013 INFO: Profiler Event: [QUERY]  at java.sql.DriverManager.getConnection(DriverManager.java:579) duration: 0 ms, connection-id: 57, statement-id: 4, resultset-id: 6, message: SHOW COLLATION
Sat Jul 06 15:45:20 CDT 2013 INFO: Profiler Event: [FETCH]  at java.sql.DriverManager.getConnection(DriverManager.java:579) duration: 3 ms, connection-id: 57, statement-id: 4, resultset-id: 6
Sat Jul 06 15:45:20 CDT 2013 INFO: Profiler Event: [QUERY]  at java.sql.DriverManager.getConnection(DriverManager.java:579) duration: 1 ms, connection-id: 57, statement-id: 999, resultset-id: 0, message: SET NAMES latin1

The output of the SHOW VARIABLES call is as follows:

character_set_client        utf8
character_set_connection    utf8
character_set_results       utf8
character_set_server        utf8mb4

The server is currently running and so to update the character_set_server value I simply ran 

SET GLOBAL
and 
SET
statements.

UPDATE: When I change the character_set_server value in the my.cnf and restart my server ConnectorJ detects the utf8mb4 exactly as it's supposed to.

When I manually set the value using SET GLOBAL ConnectorJ continues to use Latin1.

Does anybody know why this would be so? Is there any way to update the character set in a way that ConnectorJ will detect without having to take mysql offline?

Upvotes: 3

Views: 3171

Answers (1)

Kafkaesque
Kafkaesque

Reputation: 1283

I have a similar problem. It used to be that the client character-set handshake from ConnectorJ used to use the regular/incomplete utf8 character set instead of utf8mb4

https://dev.mysql.com/doc/relnotes/connector-j/en/news-5-1-13.html

Fixes bugs found since release 5.1.12. Connector/J now auto-detects servers configured with character_set_server=utf8mb4 or treats the Java encoding utf-8 passed using characterEncoding=... as utf8mb4 in the SET NAMES= calls it makes when establishing the connection. (Bug #54175)

However, upgrading ConnectorJ didn't solve anything. Btw thanks for figuring out that changing the config file fixed the problem while changing the value at runtime does not. That saved me a lot of time.

For anyone with the same problem that cannot restart their server for whatever reason. I suggest (contrary to the MySQL documentation that says to omit characterEncoding from the connection string when you want to use utf8mb4) adding the characterEncoding=UTF-8 to the connection string AND doing a manual SET NAMES utf8mb4 on the connection you're having trouble with.

Upvotes: 1

Related Questions