java.sql.SQLException: Unknown system variable 'query_cache_size'

Question

I have a app running with JDBC and get data from MySQL, but I can't build it because of this error :

java.sql.SQLException: Unknown system variable 'query_cache_size'
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964) ~[mysql-connector-java-5.1.41.jar:5.1.41]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973) ~[mysql-connector-java-5.1.41.jar:5.1.41]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909) ~[mysql-connector-java-5.1.41.jar:5.1.41]
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527) ~[mysql-connector-java-5.1.41.jar:5.1.41]
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680) ~[mysql-connector-java-5.1.41.jar:5.1.41]
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2497) ~[mysql-connector-java-5.1.41.jar:5.1.41]
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2455) ~[mysql-connector-java-5.1.41.jar:5.1.41]
    at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1369) ~[mysql-connector-java-5.1.41.jar:5.1.41]
    at com.mysql.jdbc.ConnectionImpl.loadServerVariables(ConnectionImpl.java:3777) ~[mysql-connector-java-5.1.41.jar:5.1.41]
    at com.mysql.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:3240) ~[mysql-connector-java-5.1.41.jar:5.1.41]
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2249) ~[mysql-connector-java-5.1.41.jar:5.1.41]
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2035) ~[mysql-connector-java-5.1.41.jar:5.1.41]
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:790) ~[mysql-connector-java-5.1.41.jar:5.1.41]
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47) ~[mysql-connector-java-5.1.41.jar:5.1.41]
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_131]

I have file application.properties here

#specs.dir=/specs/
#
#################### Spring Boot Data Source Configuration ############
#spring.datasource.driver-class-name=com.mysql.jdbc.Driver
#spring.datasource.url=jdbc:mysql://localhost:3306/savingbooking?useSSL=false
#spring.datasource.username=root
#spring.datasource.password=ZAQ!2wsx
#spring.datasource.initialize=true
#spring.jpa.hibernate.ddl-auto=update
#spring.jpa.properties.hibernate.format_sql=true
#spring.jpa.show-sql=true
#spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQLDialect

Mysql workbench is 8.0 version

Solution 1

query_cache_size was removed in MySQL 8. Check the docs.

It works with JDBC driver 5.1.44.

Solution 2

java.sql.SQLException: Unknown system variable 'query_cache_size'

In pom file of your project, just update the version of the dependency.

For example it's 6.0.2 of mysql java connector just update it to latest one like 8.0.11

It works!!!!

Solution 3

I had a similar problem. I resolved it by specifying the version in mysql dependency

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.45</version>
    <scope>runtime</scope>
</dependency>

Worked like a charm for me.

Solution 4

Try using MySQL 8.0.3 As can be seen on the docs, the query_cache_size was removed on MySQL8. JetBrains guys have suffered the same issue, is seems to be that it is fixed by updating the driver to the version MySQL JDBC driver v 5.1.44.

Solution 5

For mysql 8:

mysql-connector-java-8.0.17.jar (or above) + db.driver=com.mysql.cj.jdbc.Driver instead of com.mysql.jdbc.Driver

View more solutions

Comments

[Dhaval Bhoot]

I am using mysql-connector-java version 6.0.3 using adding maven dependency in my project. and still I get the same error

[Vinay Chittora]

@steven35 I am getting $ sudo mysql_upgrade -u root Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Error: Failed while fetching Server version! Could be due to unauthorized access. FATAL ERROR: Upgrade failed Please help

[Sachin Poreyana]

@DhavalBhoot you can use any later SQL connector. I used <version>8.0.12</version> and it worked seamlessly.

[lapkritinis]

Latest version as for 2018 October is 8.0.13

[Leonya]

Updating the driver version to 5.1.44 fixed the issue for me. Thanks!

[Leonya]

Updating driver version to 5.1.44 fixed the issue for me. I am keeping driver version 5 because I still need to be able to connect to mySQL v5 on some servers, but now can connect to both.

[Kiên Trần Trung]

I fix it base on @steven35 and it worked, but anw, thanks for your help!!!

[Rajesh M]

Still getting same error "Unknown system variable 'query_cache_size'" with latest version mysql:mysql-connector-java:8.0.25 in java spring boot application.

[Tum]

This worked in combination with the answer from Enrique Jiménez Flores below