MyBatis异常:No operations allowed after connection closed
来源:互联网 发布:二战日本装备知乎 编辑:程序博客网 时间:2024/05/17 23:31
MyBatis连接MySQL报异常:No operations allowed after connection closed.Connection was i
连接池报错
myBatis连接MySQL报异常: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)......
原因分析
查看了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 的有效性。
当然最好的办法是同时综合使用上述3种方法,下面就 DBCP、C3P0 和 simple jdbc dataSource 分别做一说明,假设 wait_timeout 为默认的8小时
DBCP 增加以下配置信息:
validationQuery = "select 1"testWhileIdle = "true"//some positive integertimeBetweenEvictionRunsMillis = 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 staleidleConnectionTestPeriod = 18000//set to something slightly less than wait_timeout, preventing 'stale' connections from being handed outmaxIdleTime = 25000//if you can take the performance 'hit', set to "true"testConnectionOnCheckout = true
simple jdbc dataSource 增加以下配置信息:
Pool.PingQuery = select 1Pool.PingEnabled = truePool.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 = n
interactive-timeout = n
其中 n 为服务器关闭交互式连接前等待活动的秒数。可是就部署而言每次修改 my.ini 比较麻烦,而且 n 等于多少才是合适的值呢? 所以并不推荐这个解决办法。)
- MyBatis异常:No operations allowed after connection closed
- myBatis连接MySQL报异常:No operations allowed after connection closed.Connection was implicitly closed
- myBatis连接MySQL报异常:No operations allowed after connection closed.Connection was i
- MyBatis连接MySQL出错:No operations allowed after connection closed
- No operations allowed after connection closed异常的解决
- 异常问题:No operations allowed after connection closed.
- No operations allowed after connection closed 错误
- solr no operations allowed after connection closed
- No operations allowed after connection closed
- Struts.......No operations allowed after connection closed.
- MySQLNonTransientConnectionException: No operations allowed after connection closed.
- No operations allowed after connection closed. 解决办法
- 问题:No operations allowed after connection closed.
- No operations allowed after connection closed
- No operations allowed after connection closed
- nutz连接mysql异常处理:MySQLNonTransientConnectionException: No operations allowed after connection closed
- 连接mysql异常处理:MySQLNonTransientConnectionException: No operations allowed after connection closed
- JDBC中No operations allowed after connection closed
- 模板方法模式
- Android之多线程解析(一)之Thread、Runnable
- 简单工厂设计模式与工厂设计模式
- ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)(YES也一样的)
- C语言之递归函数
- MyBatis异常:No operations allowed after connection closed
- 二分法求最大距离的最小值
- C++ 引用计数写时拷贝
- ROM 之 DDS的调频
- [BZOJ3572][HNOI2014]世界树-虚树+树形DP
- 23种设计模式之——工厂方法模式
- 洛谷P1200 [USACO1.1]你的飞碟在这儿Your Ride Is He…
- MySQL的Query Cache原理分析
- 人人都看得懂的正则表达式