Unknown system variable 'query_cache_size'

tanhaican commented 5 years ago

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.

Sharding-Proxy (4.0.0-RC2) (config-sharding.yaml)
SprintBoot with Druid or HikariCP
MySQL 8.0.12
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 commented 5 years ago

@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 commented 5 years ago

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.
If we want to upgrade HikariCP, we should also update the Java version from 1.7 to 1.8.
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 commented 5 years ago

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

lirengui commented 4 years ago

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 commented 4 years ago

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

terrymanu commented 4 years ago

Fixed by #4583

libinglong commented 4 years ago

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

chrissata commented 4 years ago

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