devbox
我家自动化
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

ConnectionIsClosedException: No operations allowed after connection closed_java.sql.sqlnontransientconnectionexception: no op

作者：我家自动化 | 2024-05-19 03:05:43

赞

踩

java.sql.sqlnontransientconnectionexception: no operations allowed after con

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

  1. ### 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.
  2. ### The error may exist in class path resource [mapper/UserMapper.xml]
  3. ### The error may involve com.demo.dao.mapper.UserDao.selectByExample
  4. ### The error occurred while executing a query
  5. ### 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.
  6. 	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
  7. 	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:149)
  8. 	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
  9. 	at sun.reflect.GeneratedMethodAccessor132.invoke(Unknown Source)
  10. 	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  11. 	at java.lang.reflect.Method.invoke(Method.java:483)
  12. 	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
  13. 	... 96 common frames omitted
  14. 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.
  15. 	at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:81)
  16. 	at org.mybatis.spring.transaction.SpringManagedTransaction.openConnection(SpringManagedTransaction.java:82)
  17. 	at org.mybatis.spring.transaction.SpringManagedTransaction.getConnection(SpringManagedTransaction.java:68)
  18. 	at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:336)
  19. 	at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:85)
  20. 	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62)
  21. 	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
  22. 	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
  23. 	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
  24. 	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)
  25. 	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
  26. 	... 101 common frames omitted
  27. Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30033ms.
  28. 	at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:676)
  29. 	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:190)
  30. 	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:155)
  31. 	at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128)
  32. 	at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:157)
  33. 	at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:115)
  34. 	at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:78)
  35. 	... 111 common frames omitted
  36. Caused by: java.sql.SQLNonTransientConnectionException: No operations allowed after connection closed.
  37. 	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:526)
  38. 	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513)
  39. 	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:505)
  40. 	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:479)
  41. 	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:489)
  42. 	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:66)
  43. 	at com.mysql.cj.jdbc.ConnectionImpl.setNetworkTimeout(ConnectionImpl.java:4395)
  44. 	at com.zaxxer.hikari.pool.PoolBase.setNetworkTimeout(PoolBase.java:550)
  45. 	at com.zaxxer.hikari.pool.PoolBase.isConnectionAlive(PoolBase.java:165)
  46. 	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:179)
  47. 	... 116 common frames omitted
  48. Caused by: com.mysql.cj.core.exceptions.ConnectionIsClosedException: No operations allowed after connection closed.
  49. 	at sun.reflect.GeneratedConstructorAccessor363.newInstance(Unknown Source)
  50. 	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
  51. 	at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
  52. 	at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:54)
  53. 	at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:93)
  54. 	at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:133)
  55. 	at com.mysql.cj.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:931)
  56. 	at com.mysql.cj.jdbc.ConnectionImpl.setNetworkTimeout(ConnectionImpl.java:4392)
  57. 	... 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.

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版本问题，老版本会存在这样的问题，需要升级到最新版本。

 我的数据源配置如下：

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

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

版本我已经升级到8.0.21了

  1.         <dependency>
  2.             <groupId>mysql</groupId>
  3.             <artifactId>mysql-connector-java</artifactId>
  4.             <version>8.0.21</version>
  5.         </dependency>

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

HikariCP配置说明：

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

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

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

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/我家自动化/article/detail/591128
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号