Shardingsphere: Unknown system variable 'query_cache_size'

[tanhaican]

Bug Report

• There are 4 similar issues, but none of them are exactly like me
• I've tried after the document, but still can not solve the problem

Which version of ShardingSphere did you use?

First, I tried apache-shardingsphere-incubating-4.0.0-RC2-sharding-proxy-bin, Then, I build myself from dev branch (2019-09-24)

Which project did you use? Sharding-JDBC or Sharding-Proxy?

Sharding-Proxy

Expected behavior

Connected successfully by SpringBoot Application with Druid Or HikariCP Datasource

Actual behavior

Connected successfully by Navicat (sometimes throws NullPointerException: null, but still work)
Both of Druid or HikariCP in my SpringBoot Application start faild by throwing "Failed to initialize pool: Unknown system variable 'query_cache_size' "

Reason analyze (If you can)

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

1. Sharding-Proxy (4.0.0-RC2) (config-sharding.yaml)
2. SprintBoot with Druid or HikariCP
3. MySQL 8.0.12
4. Tried mysql-connector-java-5.1.44、mysql-connector-java-8.0.17.jar in SpringBoot application and sharding-proxy/lib，none of them works

Example codes for reproduce this issue (such as a github link).

config-sharding.yaml.txt

server.yaml.txt

[tanhaican]

@terrymanu @tuohai666 Hey guys, What do I found to be the cause of this problem is, the version of datasoure from Sharding-Proxy is "5.6.0-Sharding-Proxy 4.0.0-RC2", however, in the ConnectionImpl class of mysql-connector-java(5.1.44), it will automatically add @@query cache size if the version is lower than 8.0.3.

It is suggested that the version of Sharding-Proxy can be configured by users or modified in other ways

[tuohai666]

1. If we want to change the version of mysql-connector-java from 5.x to 8.x, we should also update the HikariCP. Because HikariCP-java7 at 2.4.11 in Sharding-Proxy now does not compatible with mysql-connector-java 8.x.

2. If we want to upgrade HikariCP, we should also update the Java version from 1.7 to 1.8.

3. Connector/J 8.x also requires Java 1.8
   

So, to resolve this problem we should firstly answer the question that when we upgrade the Java 1.7 to 1.8?

[terrymanu]

We will upgrade java version to 8 after 4.0.0 stable version release

[lirengui]

1. If we want to change the version of mysql-connector-java from 5.x to 8.x, we should also update the  HikariCP. Because HikariCP-**java7** at 2.4.11 in Sharding-Proxy now does not compatible with mysql-connector-java 8.x.

2. If we want to upgrade HikariCP, we should also update the Java version from 1.7 to 1.8.

3. Connector/J 8.x also requires Java 1.8
   ![image](https://user-images.githubusercontent.com/24643893/66699724-b8b0b880-ed1b-11e9-8263-d6aaa83acc3b.png)

So, to resolve this problem we should firstly answer the question that when we upgrade the Java 1.7 to 1.8?

Hello, I'm using RC3 version. According to you, I updated hikaricp, compiled with jdk8, and can't connect with MySQL connector Java 8. X. where can I change it

[kimmking]

proxy:
mysql> set global query_cache_size=1048576*2;
Query OK, 0 rows affected (0.01 sec)

[terrymanu]

Fixed by #4583

[libinglong]

I have compiled the latest matser branch (commit id 932358b4f445a6a0e102def50db45d23e87bcc3a),but the "query_cache_size" problem still occurs.
Do I miss something? @terrymanu

[chrissata]

Verified on proxy 4.1.0 release version and using mysql-connector-java 8.0.20 (both java test code and sharding proxy), still get Unknown system variable 'query_cache_size' error.

Any updates? @terrymanu

[cherrylzhao]

I have found the root reason, in MySQLServerInfo.SERVER_VERSION the version is fixed at 5.6.4, so jdbc-driver will send the sql contains query_cache_size, we should dynamically judge it based on the physical database version.

[cherrylzhao]

this is my enhance step

• In MySQLAuthenticationEngine#handshake, get the first physical datasource to create a connection.
  
  
  
  • getDatabaseProductVersion from connection.getMetaData
  
  
  • then create MySQLHandshakePacket in dynamic mysql version number
  
  

[cherrylzhao]

the simplest way is just replace 5.6.4-xxx => 8.0.20-xxx, since 8.0.20 will be downward compatible with 5.x.

[cherrylzhao]

@chrissata can you contribute a PR for it?

[yangliping86]

@terrymanu ，
I upgraded 4.1.0 ,the mysql8.0.16,and connected to sharding proxy reported an error Unknown system variable 'query_cache_size'

[cherrylzhao]

fix at #5720