使用JDBC+Hikari连接MySQL时遇query_cache_size未知变量错误求助

问题现象 #

应用启动时Hikari连接池无法创建连接，抛出如下错误：

{"message":"HikariPool-1 - Starting..."}
{"timestamp":"16 Jun 2023 13:42:04,078","thread":"Timer-0","level":"DEBUG","logger":"com.zaxxer.hikari.pool.PoolBase","message":"HikariPool-1 - Failed to create/setup connection: Unknown system variable 'query_cache_size'"}
{"timestamp":"16 Jun 2023 13:42:04,078","thread":"Timer-0","level":"DEBUG","logger":"com.zaxxer.hikari.pool.HikariPool","message":"HikariPool-1 - Cannot acquire connection from data source","error.type":"java.sql.SQLException","error.message":"Unknown system variable 'query_cache_size'","stack_trace":"java.sql.SQLException: Unknown system variable 'query_cache_size'\n\tat com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:130)\n\tat com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)\n\tat com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:825)\n\tat com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:446)\n\tat com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:239)\n\tat com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:188)\n\tat com.mysql.cj.jdbc.MysqlDataSource.getConnection(MysqlDataSource.java:417)\n\tat com.mysql.cj.jdbc.MysqlDataSource.getConnection(MysqlDataSource.java:129)\n\tat com.mysql.cj.jdbc.MysqlDataSource.getConnection(MysqlDataSource.java:114)\n\tat com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:367)\n\tat com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:196)\n\tat com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:467)\n\tat com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:541)\n\tat com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115)\n\tat com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:81)\n\tat java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)\n\tat java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77)\n\tat java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)\n\tat java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499)\n\tat java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:480)\n\tat org.springframework.beans.BeanUtils.instantiateClass(BeanUtils.java:211)\n\tat org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:117)\n\tat org.springframework.beans.factory.support.ConstructorResolver.instantiate(ConstructorResolver.java:311)\n\tat org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:296)\n\tat org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireConstructor(AbstractAutowireCapableBeanFactory.java:1372)\n\tat org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1222)\n\tat org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:582)\n\tat org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:542)\n\tat org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335)\n\tat org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)\n\tat org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333)\n\tat org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208)\n\tat org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:330)\n\tat org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:113)\n\tat org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1707)\n\tat org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1452)\n\tat org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:619)\n\tat org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:542)\n\tat org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335)\n\tat org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)\n\tat org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333)\n\tat org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208)\n\tat com.paytm.wallet.dao.manager.BeanManager.<clinit>(BeanManager.java:47)\n\tat com.paytm.wallet.dao.domain.PlatformManager.getAllPlatforms(PlatformManager.java:123)\n\tat com.paytm.wallet.dao.cache.PlatformCache.loadPlatformHashmap(PlatformCache.java:64)\n\tat com.paytm.wallet.dao.cache.PlatformCache$LoadPlatform.run(PlatformCache.java:46)\n\tat java.base/java.util.TimerThread.mainLoop(Timer.java:566)\n\tat java.base/java.util.TimerThread.run(Timer.java:516)\n"}

已执行操作 #

• 更新MySQL驱动至8.0.33，依赖配置如下：

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

• 使用的MySQL版本为8.0.31（社区版GPL）
• 当前JDBC属性配置：

jdbc.master.driver=com.mysql.cj.jdbc.Driver
#jdbc.master.driver=com.mysql.jdbc.Driver

jdbc.master.url=jdbc:mysql://localhost:6033/test

jdbc.master.maximumPoolSize=30

jdbc.master.dataSource.cachePrepStmts= true

jdbc.master.dataSource.prepStmtCacheSize= 250

jdbc.master.dataSource.prepStmtCacheSqlLimit= 2048

jdbc.master.connectionTimeout=250

jdbc.master.dataSource.useLocalSessionState=true

jdbc.master.dataSource.readOnlyPropagatesToServer=true

jdbc.master.dataSource.cacheServerConfiguration=true

jdbc.master.dataSource.characterEncoding=UTF-8

错误原因及解决方案 #

原因分析 #

MySQL 8.0已完全移除了query_cache_size等查询缓存相关的系统变量，而当前配置中的cacheServerConfiguration=true会让驱动缓存服务器的系统变量信息，其中包含已被移除的查询缓存变量，导致连接创建时触发错误。

解决步骤 #

1. 调整缓存配置：将jdbc.master.dataSource.cacheServerConfiguration=true改为false，或直接移除该配置项，避免驱动缓存无效的系统变量。
2. 更新JDBC URL：在URL中添加useQueryCache=false参数，明确告知驱动禁用查询缓存：

   jdbc.master.url=jdbc:mysql://localhost:6033/test?useQueryCache=false
   

3. 清理依赖残留：执行mvn dependency:tree命令检查项目依赖树，确保没有旧版本的mysql-connector-java存在。
4. 移除无效配置：检查代码或其他配置文件，确保没有手动设置query_cache相关的参数。

内容的提问来源于stack exchange，提问作者Tushar Srivastava