Proxysql: JDBC about "Unknown system variable 'query_cache_size'"

[IThinkIGottaGo]

When I try use JDBC to connect ProxySQL at my playground environment, the error of title just showed.
Details:
ProxySQL version: 1.4.7

JDBC driver version:
I test 5.1.35 (it throw Unknown system variable 'tx_isolation', but it can test connect success, but can not list any schema or table)
both with 5.1.40/44/45 、 8.0.8-dmr and 8.0.9-rc, all throw Unknown system variable 'query_cache_size'

MySQL: two ,master and slaver, both 8.0.5 version

Connect Pool: HikariCP 2.7.8

more details:
em, then i use

CREATE USER 'sbuser'@'%' IDENTIFIED BY '5439';
GRANT ALL ON * . * TO 'sbuser'@'%';
FLUSH PRIVILEGES;

create a user, and i also config "Read-Write Separate". When I use navicat log in ProxySQL client (just like sbuser 6033 sbuser 5439), it looks work very well. I can insert SQL ,or drop database. And result both sync to my 'master' and 'slaver' database, show table records also ok.

But then I try to use my jdbc connect it , it throw title problem, and can not connect success.
Then changes some JDBC driver. Both in vain.

Here is my JDBC URL sample : jdbc:mysql://localhost:6033/jtdb?useSSL=false&useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true
username:sbuser
password:5439

by the way, I install ProxySQL in the docker . And I expose 6032 and 6033 port.

At the end of issue i will put /var/lib/proxysql/proxysql.log , the end of file both these errors.

Emmmm, Maybe I missed some properties or made some mistake? I don't know, hope these information could be a helper.

Hope my poor English won't make issue too hard to understand. :)

proxysql.log

[Tusamarco]

Hi @FreeStyleNight I had already explain this in my blog http://www.tusacentral.net/joomla/index.php/mysql-blogs/198-proxysql-server-version-impersonation.html

In short u need to upgrade the jdbc to 5.5 or 8 when using against 8,, or you will have an issue.

[Tusamarco]

@FreeStyleNight and change mysql-server_version proxysql variable

[IThinkIGottaGo]

@Tusamarco thank you. But I have try 8.0.8-dmr (add it to my maven project) and use jdbcdriver:com.jdbc.cj.Driver. It throw same.

Ok, I will check my mymysql-server_version soon.

[Tusamarco]

Check the blog I explain why, but using JDBC 8 and mymysql-server_version will fix it.
If not let me know I have the environment to perform more in depth debug of the JDBC itself, I can also patch it for u if you really need it.
@FreeStyleNight

[IThinkIGottaGo]

@Tusamarco Wow, like a miracle. It works fine now and list record fluently after I followed your ProxySQL sql. And even I shutdown the master database, the ProxySQL could send query to slaver too!

I have already add this article to my browser favorites. Thank you very mush. 👍

[egbiner]

thanks