JDBC/MYSQL问题 : Unknown system variable 'query_cache_size'

今天药忘吃喽~ 2024-04-18 15:24 70阅读 0赞

## 背景 ##

今天运行一个开源项目，竟然遇到`Caused by: java.sql.SQLException: Unknown system variable 'query_cache_size'`的问题。真是神奇，`query_cache_size`是什么，如何解决？

`>>>`先看详细报错信息：

#by zhengkai.blog.csdn.net
    2019-09-08 22:20:09.821 ERROR 9400 --- [eate-1098645181] c.a.d.p.DruidDataSource                  : create connection SQLException, url: jdbc:mysql://localhost:3306/mcms?autoReconnect=true&useUnicode=true&characterEncoding=utf8&useSSL=false&allowMultiQueries=true, errorCode 0, state 08001
    
    com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
    	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_222]
    	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[?:1.8.0_222]
    	at sun.reflect.DelegatingConstructorAccessorImpl.__newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.8.0_222]
    	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45009) ~[?:1.8.0_222]
    	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45012) ~[?:1.8.0_222]
    	at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[?:1.8.0_222]
    	at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    	at com.mysql.jdbc.Util.getInstance(Util.java:387) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:917) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    	at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2163) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2088) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:806) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_222]
    	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[?:1.8.0_222]
    	at sun.reflect.DelegatingConstructorAccessorImpl.__newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.8.0_222]
    	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45009) ~[?:1.8.0_222]
    	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45012) ~[?:1.8.0_222]
    	at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[?:1.8.0_222]
    	at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:410) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:328) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    	at com.alibaba.druid.filter.FilterChainImpl.connection_connect(FilterChainImpl.java:149) ~[druid-1.1.9.jar:1.1.9]
    	at com.alibaba.druid.filter.stat.StatFilter.connection_connect(StatFilter.java:218) ~[druid-1.1.9.jar:1.1.9]
    	at com.alibaba.druid.filter.FilterChainImpl.connection_connect(FilterChainImpl.java:143) ~[druid-1.1.9.jar:1.1.9]
    	at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1515) ~[druid-1.1.9.jar:1.1.9]
    	at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1578) ~[druid-1.1.9.jar:1.1.9]
    	at com.alibaba.druid.pool.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:2466) [druid-1.1.9.jar:1.1.9]
    Caused by: java.sql.SQLException: Unknown system variable 'query_cache_size'
    	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:963) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3966) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3902) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2526) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2673) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2503) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    	at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1369) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    	at com.mysql.jdbc.ConnectionImpl.loadServerVariables(ConnectionImpl.java:3833) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    	at com.mysql.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:3283) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    	at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2126) ~[mysql-connector-java-5.1.39.jar:5.1.39]
    	... 18 more

## Reason ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGVuZ2thaS5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70]  
原因是`The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0. Deprecation includes query_cache_size.`

也就是说是`query cache`在MySQL`5.7.20`就已经过时了，而在MySQL8.0之后就已经被真正移除了。所以必须用新版的jdbc-mysql连接库来与新版的mysql8.0匹配

## Solution ##

既然mysql-connecter-java的版本过低，找个8.x的版本就可以了，建议是和数据库版本相对应，例如phpStudy是`8.0.12`，那么jdbc用`8.0.12`即可（更新版本也是ok的）。

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

`>>>`更多版本详情可以在以下地址查看：  
https://mvnrepository.com/artifact/mysql/mysql-connector-java  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGVuZ2thaS5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGVuZ2thaS5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/dc7645539d6942b3b2898ad5f30cd0b6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly96aGVuZ2thaS5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/ed6075dd068d49f4b6416b07de2799b0.png