Bug #87846 connector/j 5.1.42 cannot connect to MySQL 8.0.3
Submitted: 22 Sep 2017 19:19 Modified: 21 May 22:38
Reporter: Mark Callaghan Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / J Documentation Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any

[22 Sep 2017 19:19] Mark Callaghan
Description:
My client uses Connector/J 5.1.42 and cannot connect to 8.0.3
This has been fixed in either 5.1.43 or 5.1.44 but I didn't see a bug for it.

How to repeat:

Run linkbench and check out the error message:
ERROR 2017-09-22 11:47:23,687 [Thread-1]: error connecting to database:
java.sql.SQLException: Unknown system variable 'query_cache_size'
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2444)
        at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1381)
        at com.mysql.jdbc.ConnectionImpl.loadServerVariables(ConnectionImpl.java:3766)
        at com.mysql.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:3229)
        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2238)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2024)
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:779)
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:389)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
        at java.sql.DriverManager.getConnection(DriverManager.java:579)
        at java.sql.DriverManager.getConnection(DriverManager.java:221)
        at com.facebook.LinkBench.LinkStoreMysql.openConnection(LinkStoreMysql.java:148)
        at com.facebook.LinkBench.LinkStoreMysql.initialize(LinkStoreMysql.java:120)
        at com.facebook.LinkBench.NodeLoader.run(NodeLoader.java:120)
        at com.facebook.LinkBench.LinkBenchDriver$1.run(LinkBenchDriver.java:429)
        at java.lang.Thread.run(Thread.java:722)

Suggested fix:
Why is it so hard to make Connector/J forward compatible?
See https://bugs.mysql.com/bug.php?id=86221

Looks like 5.1.43 or 5.1.44 was updated to be clever about checking for query cache my.cnf vars...

2017-09-22T19:11:04.379020Z       477 Query     /* mysql-connector-java-5.1.44 ( Revision: b3cda4f864902ffdde495b9df93937c3e20009be ) */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, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeou
t, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_
timeout AS net_write_timeout, @@have_query_cache AS have_query_cache, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_i
solation AS transaction_isolation, @@wait_timeout AS wait_timeout
[23 Sep 2017 5:28] Umesh Shastry
Hello Mark,

Thank you for the report and feedback.
I checked reported issue at my end and observed that issue occurred when C/J was run against MySQL 8.0.3.  I couldn't find any community reported bug either but looks like this was fixed by alexander soklakov's internally reported BUG 26399958 - UNABLE TO CONNECT TO MYSQL 8.0.3 
Which was pushed to c/J 5.1.43, commit 1d14b699eff3e6112aaedb1cbe5a151ab81f98f1

I'm unable to locate this info in the change log of 5.1.43 - https://dev.mysql.com/doc/relnotes/connector-j/5.1/en/news-5-1-43.html

I'll process this issue as request to include this missing details from change log.

##
## 5.1.42

[umshastr@hod03]~/bugs: javac -cp '.:mysql-connector-java-5.1.42/mysql-connector-java-5.1.42-bin.jar' TestMysqlJdbc.java
[umshastr@hod03]~/bugs: java -cp '.:mysql-connector-java-5.1.42/mysql-connector-java-5.1.42-bin.jar' TestMysqlJdbc
java.sql.SQLException: Unknown system variable 'query_cache_size'
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2444)
        at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1381)
        at com.mysql.jdbc.ConnectionImpl.loadServerVariables(ConnectionImpl.java:3766)
        at com.mysql.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:3229)
        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2238)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2024)
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:779)
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:389)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
        at java.sql.DriverManager.getConnection(DriverManager.java:571)
        at java.sql.DriverManager.getConnection(DriverManager.java:215)
        at TestMysqlJdbc.main(TestMysqlJdbc.java:16)
[umshastr@hod03]~/bugs:

## 5.1.43

[umshastr@hod03]~/bugs: javac -cp '.:mysql-connector-java-5.1.43/mysql-connector-java-5.1.43-bin.jar' TestMysqlJdbc.java
[umshastr@hod03]~/bugs: java -cp '.:mysql-connector-java-5.1.43/mysql-connector-java-5.1.43-bin.jar' TestMysqlJdbc
java.sql.SQLException: Unknown system variable 'tx_isolation'
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2490)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2448)
        at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1381)
        at com.mysql.jdbc.ConnectionImpl.loadServerVariables(ConnectionImpl.java:3787)
        at com.mysql.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:3233)
        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2242)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2024)
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:779)
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:389)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
        at java.sql.DriverManager.getConnection(DriverManager.java:571)
        at java.sql.DriverManager.getConnection(DriverManager.java:215)
        at TestMysqlJdbc.main(TestMysqlJdbc.java:16)
[umshastr@hod03]~/bugs:

## 5.1.44

[umshastr@hod03]~/bugs: javac -cp '.:mysql-connector-java-5.1.44/mysql-connector-java-5.1.44-bin.jar' TestMysqlJdbc.java
[umshastr@hod03]~/bugs: java -cp '.:mysql-connector-java-5.1.44/mysql-connector-java-5.1.44-bin.jar' TestMysqlJdbc
MySQL

Regards,
Umesh
[25 Sep 2017 7:22] Chiranjeevi Battula
http://bugs.mysql.com/bug.php?id=87848 marked as duplicate of this one.
[2 Oct 2017 7:36] Alexander Soklakov
Hi Mark,

You need to use c/J 5.1.44 to connect to MySQL 8.0.3. There were two incompatible changes in this server version:

1. Query cache removal. The fix was done in c/J 5.1.43 and updated later in c/J 5.1.44 (Bug#26399958, UNABLE TO CONNECT TO MYSQL 8.0.3). There is no changelog entry for this change.

2. Renaming of tx_* variables to transaction_* ones. Fix was done in c/J 5.1.44 (Bug#26440544, CONNECTOR/J SHOULD NOT USE TX_{READ_ONLY,ISOLATION} WHICH IS PLANNED FOR REMOVAL). Changelog for c/J 5.1.44 contains the "Important Change" note about that.

I've changed the category of this bug report to "Connector/J Documentation", we should update the c/J 5.1.43 changelog.

Thanks.
[2 Oct 2017 16:28] Daniel So
Posted by developer:
 
An entry has now been added for Bug#26399958 in the Connector/J 5.1.43 changelog.
[21 May 22:38] Filipe Silva
I believe this is no longer an issue. In any case, Connector/J 8.0 should be used instead.