No operations allowed after connection closed.Connection was implicitly closed
异常信息
org.hibernate.exception.JDBCConnectionException: could not execute queryat org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:74)at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43).......Caused by: com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed due to underlying exception/error:** BEGIN NESTED EXCEPTION **com.mysql.jdbc.CommunicationsExceptionMESSAGE: Communications link failure due to underlying exception:** BEGIN NESTED EXCEPTION **java.net.SocketExceptionMESSAGE: Broken pipeSTACKTRACE:java.net.SocketException: Broken pipeat java.net.SocketOutputStream.socketWrite0(Native Method)......** END NESTED EXCEPTION ** |
原因分析
查看了Mysql的文档，以及Connector/J的文档以及在线说明发现，出现这种异常的原因是： Mysql服务器默认的“wait_timeout”是8小时，也就是说一个connection空闲超过8个小时，Mysql将自动断开该connection。这就是问题的所在，在C3P0 pools中的connections如果空闲超过8小时，Mysql将其断开，而C3P0并不知道该connection已经失效，如果这时有Client请求connection，C3P0将该失效的Connection提供给Client，将会造成上面的异常。解决方案
解决的方法有3种：- 增加 wait_timeout 的时间。
- 减少 Connection pools 中 connection 的 lifetime。
- 测试 Connection pools 中 connection 的有效性。
DBCP 增加以下配置信息:
validationQuery = "select 1" testWhileIdle = "true" //some positive integer timeBetweenEvictionRunsMillis = 3600000 //set to something smaller than 'wait_timeout' minEvictableIdleTimeMillis = 18000000 //if you don't mind a hit for every getConnection(), set to "true" testOnBorrow = "true"
C3P0 增加以下配置信息:
//获取connnection时测试是否有效 testConnectionOnCheckin = true //自动测试的table名称 automaticTestTable=C3P0TestTable //set to something much less than wait_timeout, prevents connections from going stale idleConnectionTestPeriod = 18000 //set to something slightly less than wait_timeout, preventing 'stale' connections from being handed out maxIdleTime = 25000 //if you can take the performance 'hit', set to "true" testConnectionOnCheckout = true
simple jdbc dataSource 增加以下配置信息:
Pool.PingQuery = select 1 Pool.PingEnabled = true Pool.PingConnectionsOlderThan = 0 //对于空闲的连接一个小时检查一次 Pool.PingConnectionsNotUsedFor = 3600000
其他方案（不推荐）
对于 MySQL5 之前的版本，如 Mysql4.x，只需要修改连接池配置中的 URL，添加一个参数：autoReconnect=true(如jdbc:mysql://hostaddress:3306/schemaname?autoReconnect=true)，如果是 MySQL5 及以后的版本，则需要修改 my.cnf(或者my.ini) 文件，在 [mysqld] 后面添加上：
wait_timeout = ninteractive-timeout = n |
正文到此结束
- 本文标签： autoReconnect=true MySQL5 dataSource C3P0 DBCP wait_timeout
- 版权声明： 本文为互联网转载文章，出处已在文章中说明(部分除外)。如果侵权，请联系本站长删除，谢谢。
- 本文海报： 生成海报一 生成海报二
热门推荐
相关文章
Loading...
![[HBLOG]公众号](http://www.liuhaihua.cn/img/qrcode_gzh.jpg)
