ConnectionIsClosedException: No operations allowed after connection closed

最新推荐文章于 2024-04-27 22:36:27 发布

编译人生

最新推荐文章于 2024-04-27 22:36:27 发布

阅读量3.6k

点赞数

分类专栏： mysql 文章标签： mybatis spring java

本文链接：https://blog.csdn.net/aayygg1234/article/details/117919724

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

最近生产上spring boot项目使用Hikari 数据源在一个用户信息查询操作数据库时总是报如题的错误。网上查看很多资料一直没有解决，但目前也没有什么好的办法，哪位大神给指导下。报错信息如下：

### Error querying database.  Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30033ms.
### The error may exist in class path resource [mapper/UserMapper.xml]
### The error may involve com.demo.dao.mapper.UserDao.selectByExample
### The error occurred while executing a query
### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30033ms.
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:149)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
	at sun.reflect.GeneratedMethodAccessor132.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:483)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
	... 96 common frames omitted
Caused by: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30033ms.
	at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:81)
	at org.mybatis.spring.transaction.SpringManagedTransaction.openConnection(SpringManagedTransaction.java:82)
	at org.mybatis.spring.transaction.SpringManagedTransaction.getConnection(SpringManagedTransaction.java:68)
	at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:336)
	at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:85)
	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
	... 101 common frames omitted
Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30033ms.
	at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:676)
	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:190)
	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:155)
	at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128)
	at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:157)
	at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:115)
	at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:78)
	... 111 common frames omitted
Caused by: java.sql.SQLNonTransientConnectionException: No operations allowed after connection closed.
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:526)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:505)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:479)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:489)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:66)
	at com.mysql.cj.jdbc.ConnectionImpl.setNetworkTimeout(ConnectionImpl.java:4395)
	at com.zaxxer.hikari.pool.PoolBase.setNetworkTimeout(PoolBase.java:550)
	at com.zaxxer.hikari.pool.PoolBase.isConnectionAlive(PoolBase.java:165)
	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:179)
	... 116 common frames omitted
Caused by: com.mysql.cj.core.exceptions.ConnectionIsClosedException: No operations allowed after connection closed.
	at sun.reflect.GeneratedConstructorAccessor363.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
	at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:54)
	at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:93)
	at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:133)
	at com.mysql.cj.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:931)
	at com.mysql.cj.jdbc.ConnectionImpl.setNetworkTimeout(ConnectionImpl.java:4392)
	... 119 common frames omitted

看到如上报错信息，重要几条：

Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30033ms.

Caused by: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30033ms.

Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30033ms.

Caused by: java.sql.SQLNonTransientConnectionException: No operations allowed after connection closed.

Caused by: com.mysql.cj.core.exceptions.ConnectionIsClosedException: No operations allowed after connection closed.

分析原因是不允许去获取了一个已经关闭的数据库连，导致超时引起的。

The error may involve com.demo.dao.mapper.UserDao.selectByExample

从如上报错信息可以很明显看出我是使用mybatis 自动生成的查询语句进行查询的，网上看到有三种说法：

1、配置的问题，需要修改配置

2、需要手动检查连接，如果连接已经关闭，要重新获取连接（硬编码方式）

3、mysql版本问题，老版本会存在这样的问题，需要升级到最新版本。

我的数据源配置如下：

spring.datasource.hikari.minimum-idle=10
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.pool-name=MyHikariCP
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.max-lifetime=600000
spring.datasource.hikari.validation-timeout=10000
spring.datasource.hikari.auto-commit=true
spring.datasource.hikari.read-only=false
spring.datasource.hikari.connection-test-query=SELECT 1
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.tomcat.max-wait=10000
spring.datasource.tomcat.time-between-eviction-runs-millis=18800
spring.datasource.tomcat.min-evictable-idle-time-millis=300000

配置我尝试改来该去也没好，关键测试环境没有问题，无法验证。

版本我已经升级到8.0.21了

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

手动检测连接，编码这个可能需要我不使用mybatis自动生成的这套东西才能测试吧，不符合实际场景。

HikariCP配置说明：

目前还没有解决，解决后更新方法。

今天看到了一片文章可能有帮助大家：

【追光者系列】HikariCP 诡异问题拨开迷雾推理破案实录（上） | 芋道源码 —— 纯源码解析博客

编译人生

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
4
评论
ConnectionIsClosedException: No operations allowed after connection closed

### Error querying database. Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out a.
复制链接

扫一扫