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

MySQL Workbench is a query tool for MySQL, it is not involved when you access a MySQL database from Java, so it is not relevant to your question. — Mark Rotteveel, Apr 23 '18 at 15:07
It seems that it is a known bug https://bugs.mysql.com/bug.php?id=87846 Try upgrading your connector jar mysql-connector-java to 5.1.43 or higher — RubioRic, Apr 23 '18 at 15:09

score 61 · Accepted Answer · answered Apr 23 '18 at 15:10

61

query_cache_size was removed in MySQL 8. Check the docs.

It works with JDBC driver 5.1.44.

answered Apr 23 '18 at 15:10

steven35

3,747
3
34
48

4

I am using mysql-connector-java version 6.0.3 using adding maven dependency in my project. and still I get the same error – Dhaval Bhoot May 15 '18 at 05:08
@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 – Vinay Chittora Aug 10 '18 at 09:43
1

@DhavalBhoot you can use any later SQL connector. I used 8.0.12 and it worked seamlessly. – Sachin Poreyana Oct 12 '18 at 09:14
Latest version as for 2018 October is 8.0.13 – lapkritinis Oct 31 '18 at 12:22
1

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. – Rajesh M Jun 05 '21 at 07:22
This worked in combination with the answer from Enrique Jiménez Flores below – Tum Jun 18 '21 at 10:19

score 34 · Answer 2 · edited Jul 11 '18 at 10:58

34

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!!!!

edited Jul 11 '18 at 10:58

Michał Turczyn

32,028
14
47
69

answered Jul 11 '18 at 10:17

vineeth sharma

341
3
2

1

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. – Leonya Nov 08 '18 at 16:36

score 13 · Answer 3 · answered Nov 12 '18 at 13:52

13

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.

answered Nov 12 '18 at 13:52

Asad Shakeel

1,949
1
23
29

I fix it base on @steven35 and it worked, but anw, thanks for your help!!! – Kiên Trần Trung Nov 13 '18 at 03:01

score 9 · Answer 4 · answered Apr 23 '18 at 15:12

9

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.

answered Apr 23 '18 at 15:12

ISHAN KUMAR GHOSH

226
1
7

Updating the driver version to 5.1.44 fixed the issue for me. Thanks! – Leonya Nov 08 '18 at 16:35

score 9 · Answer 5 · answered Sep 26 '19 at 05:45

9

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

answered Sep 26 '19 at 05:45

Enrique Jiménez Flores

399
3
5

score 6 · Answer 6 · edited Jan 24 '19 at 03:54

6

I stuck on this issue, spend full one day and finally i got solution.

Solution :

1) Use exact mysql-connector-java jar or dependency as your mysql version.
2) if your mysql version is 8 or 6+ try to use this "com.mysql.cj.jdbc.Driver" driver class.

edited Jan 24 '19 at 03:54

Til

5,150
13
26
34

answered Jan 24 '19 at 03:48

Mukesh Kumar yadav

61
1
1

score 5 · Answer 7 · answered Nov 27 '18 at 06:40

5

If you using MySql v8.0 then you should try fix this bug with

mysql-connector-java-5.1.9

answered Nov 27 '18 at 06:40

Đặng Ngọc Phong

51
2
3

score 5 · Answer 8 · edited Sep 12 '20 at 04:51

This exception is related to pom.xml dependency. When I use below dependency in pox.xml:

 <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>6.0.5</version>
 </dependency>

It shows exception:Caused by: java.sql.SQLException: Unknown system variable query_cache_size

But, when I use below dependency:

  <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.13</version>
  </dependency>

It works.You have to use latest dependency

score 2 · Answer 9 · answered Mar 07 '19 at 17:16

2

Ok so what I did as metioned above. I just added the mysql-connector-java-5.1.4 to build path and ran the project on my server and connected fine to mysql

answered Mar 07 '19 at 17:16

Ooguro

121
2
14

score 1 · Answer 10 · answered Aug 19 '19 at 10:55

1

just change the version of mysql use this in pom.xml

<version>8.0.11</version>

answered Aug 19 '19 at 10:55

Shubham

707
9
7

score 0 · Answer 11 · answered Aug 18 '21 at 06:42

0

Use Below mention dependency it will work for you!

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.16</version>
    </dependency>

answered Aug 18 '21 at 06:42

Rushikesh Thakur

1
1

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

11 Answers11

Linked