Bug #95139 cacheServerConfiguration appears to thwart charset detection
Submitted: 26 Apr 2019 0:10 Modified: 21 May 2019 10:21
Reporter: Cott Lang Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:8.0.15 OS:Any
Assigned to: CPU Architecture:Any

[26 Apr 2019 0:10] Cott Lang
Description:
We have a database in latin1 with a few 8-bit extended ASCII characters.

When we enabled cacheServerConfiguration, we suddenly had "incorrect string" errors inserting 8 bit characters. We suspected the JDBC connection was somehow now using utf8mb4, andfound that disabled cacheServerConfiguration allowed inserts again.

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

Variable_name	Value
character_set_client	utf8mb4
character_set_connection	utf8mb4
character_set_database	latin1
character_set_filesystem	binary
character_set_results	utf8mb4
character_set_server	latin1
character_set_system	utf8
collation_connection	utf8mb4_general_ci
collation_database	latin1_swedish_ci
collation_server	latin1_swedish_ci

Note there was a forum discussion a few months ago:

https://forums.mysql.com/read.php?39,671959,671959#msg-671959

How to repeat:
Insert an extended ASCII character into a varchar in a latin1 table with cacheServerConfiguration=true.
[21 May 2019 10:21] Alexander Soklakov
Hi Cott,

Thanks for this report.

I wasn't able to create a reproducible test case yet, but the bug can be detected with a server log:

1) cacheServerConfiguration=false:

2019-05-21T09:45:25.010403Z	   13 Connect	root@localhost on cjtest_5_1 using SSL/TLS
2019-05-21T09:45:25.022922Z	   13 Query	/* @MYSQL_CJ_FULL_PROD_NAME@ (Revision: @MYSQL_CJ_REVISION@) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
2019-05-21T09:45:25.034797Z	   13 Query	SET NAMES latin1
2019-05-21T09:45:25.039838Z	   13 Query	SET character_set_results = NULL
2019-05-21T09:45:25.044939Z	   13 Query	SET autocommit=1

2) cacheServerConfiguration=true, first attempt:

2019-05-21T09:45:52.549871Z	   14 Connect	root@localhost on cjtest_5_1 using SSL/TLS
2019-05-21T09:45:52.667652Z	   14 Query	/* @MYSQL_CJ_FULL_PROD_NAME@ (Revision: @MYSQL_CJ_REVISION@) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
2019-05-21T09:45:52.751236Z	   14 Query	SET NAMES latin1
2019-05-21T09:45:52.783510Z	   14 Query	SET character_set_results = NULL
2019-05-21T09:45:52.816110Z	   14 Query	SET autocommit=1

3) cacheServerConfiguration=true, second attempt:

2019-05-21T09:46:28.666006Z	   15 Connect	root@localhost on cjtest_5_1 using SSL/TLS
2019-05-21T09:46:28.721254Z	   15 Query	SET character_set_results = NULL
2019-05-21T09:46:28.753687Z	   15 Query	SET autocommit=1

You could see that on second attempt, when the chache is already created, c/J does not request server variables as expected but also does not send "SET NAMES latin1", and that's wrong.

The fast fix could be:

===
diff --git a/src/main/core-impl/java/com/mysql/cj/NativeSession.java b/src/main/core-impl/java/com/mysql/cj/NativeSession.java
index 7bd4143..015a22f 100644
--- a/src/main/core-impl/java/com/mysql/cj/NativeSession.java
+++ b/src/main/core-impl/java/com/mysql/cj/NativeSession.java
@@ -518,7 +518,8 @@
                     }
                 }
 
-                if (dontCheckServerMatch || !this.protocol.getServerSession().characterSetNamesMatches(mysqlCharsetName) || ucs2) {
+                if (dontCheckServerMatch || !this.protocol.getServerSession().characterSetNamesMatches(mysqlCharsetName) || ucs2
+                        || this.cacheServerConfiguration.getValue()) {
                     try {
                         sendCommand(this.commandBuilder.buildComQuery(null, "SET NAMES " + mysqlCharsetName + connectionCollationSuffix), false, 0);
===

You could try it if needed. But the final fix should be more sophisticated to avoid issuing SET NAMES in case client and server charsets already match.