MyBatis连接MySQL报异常：No operations allowed after connection closed.Connection was implicitly closed...

最新推荐文章于 2023-11-05 15:48:56 发布

weixin_33814685

最新推荐文章于 2023-11-05 15:48:56 发布

阅读量286

点赞数

文章标签： java python 数据库

原文链接：https://my.oschina.net/anxiaole/blog/840526

版权

2019独角兽企业重金招聘Python工程师标准>>>

异常信息


org.hibernate.exception.JDBCConnectionException: could not execute query

at 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.CommunicationsException

MESSAGE: Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

java.net.SocketException

MESSAGE: Broken pipe

STACKTRACE:

java.net.SocketException: Broken pipe

at 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种：

增加 MySQL的全局wait_timeout 的时间。
减少 Connection pools 中 connection 的 lifetime。
测试 Connection pools 中 connection 的有效性。

当然最好的办法是同时综合使用上述3种方法，下面就 DBCP、C3P0 和 simple jdbc dataSource 分别做一说明，假设 wait_timeout 为默认的8小时

增加 MySQL的全局wait_timeout 的时间

修改 /etc/mysql/my.cnf文件，在 [mysqld] 节中设置：

# Set a connection to wait 8hours in idle status. 
wait_timeout =86400

或者直接使用sql语句修改：

mysql> show variables like '%timeout%'; 
+--------------------------+-------+ 
| Variable_name | Value | 
+--------------------------+-------+ 
| connect_timeout | 5 | 
| delayed_insert_timeout | 300 | 
| innodb_lock_wait_timeout | 50 | 
| interactive_timeout | 28800 | 
| net_read_timeout | 30 | 
| net_write_timeout | 60 | 
| slave_net_timeout | 3600 | 
| wait_timeout | 28800 | 
+--------------------------+-------+

同一时间，这两个参数只有一个起作用。到底是哪个参数起作用，和用户连接时指定的连接参数相关，缺省情况下是使用wait_timeout。我建议是将这两个参数都修改，以免引起不必要的麻烦。

这两个参数的默认值是8小时(60*60*8=28800)。我测试过将这两个参数改为0，结果出人意料，系统自动将这个值设置为。换句话说，不能将该值设置为永久。
将这2个参数设置为24小时(60*60*24=604800)即可。

set interactive_timeout=604800; 
set wait_timeout=604800;

减少连接池内连接的生存周期

减少连接池内连接的生存周期，使之小于上一项中所设置的 wait_timeout 的值。

修改 c3p0 的配置文件，设置：

# How long to keep unused connections around(in seconds) 
# Note: MySQL times out idle connections after 8hours(28,800seconds) 
# so ensure this value is below MySQL idle timeout 
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> 
    <property name="maxIdleTime" value="25200"/> 
    <!--other properties --> 
</bean>

定期使用连接池内的连接，使得它们不会因为闲置超时而被 MySQL 断开

修改 c3p0 的配置文件：

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> 
	<property name="preferredTestQuery" value="SELECT 1"/> 
	<property name="idleConnectionTestPeriod" value="18000"/> 
	<property name="testConnectionOnCheckout" value="true"/> 
	<!--other properties -->
</bean>

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 = n
interactive-timeout = n

其中 n 为服务器关闭交互式连接前等待活动的秒数。可是就部署而言每次修改 my.ini 比较麻烦，而且 n 等于多少才是合适的值呢？所以并不推荐这个解决办法。)

项目中配置

下面为我正在使用的dataSource配置

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="validationQuery" value="select 1" />
    <property name="testWhileIdle" value="true" />
    <!-- some positive integer -->
    <property name="timeBetweenEvictionRunsMillis" value="3600000" />
    <!-- set to something smaller than 'wait_timeout' -->
    <property name="minEvictableIdleTimeMillis" value="18000000" />
    <!-- if you don't mind a hit for every getConnection(), set to "true" -->
    <property name="testOnBorrow" value="true" />
    <property name="url" value="{datasource.url}?serverTimezone=UTC&amp;useUnicode=true&amp;characterEncoding=utf8&amp;rewriteBatchedStatements=true"></property>
    <property name="username" value="{datasource.username}" />
    <property name="password" value="{datasource.password}" />
</bean>

相关知识

1. 即使在创建Mysql时url中加入了autoReconnect=true参数，一但这个连接两次访问数据库的时间超出了服务器端wait_timeout的时间限制,还是会

CommunicationsException: The last packet successfully received from the server was xxx milliseconds ago.

2. 服务器端的参数可以用以下sql语句来进行设置，但是wait_timeout值不应该设的太高.

  show global variables like 'wait_timeout'; 
  set global wait_timeout = 10;

wait_timeout 单位为秒(s)

有关wait_timeout的知识请看：https://my.oschina.net/anxiaole/blog/840555

3. 较好的策略是对处于idle状态的connection定时发送一个sql，来刷新服务器上的时间戳。这可以使用c3p0r的连接池。http://bzhang.iteye.com/blog/321832
4. 对于tomcat的server.xml中使用的连接池:http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.html
使用DBCP的连接池可以采用:http://commons.apache.org/dbcp/configuration.html

<Resource name="jdbc/test" 
	auth="Container" 
	type="javax.sql.DataSource" 
	driverClassName="com.mysql.jdbc.Driver" 
	url="jdbc:mysql://localhost:3306/test?characterEncoding=utf-8" 
	username="root" password="test" 
	maxActive="500" 
	maxIdle="10" 
	maxWait="-1" 
	timeBetweenEvictionRunsMillis="10000" 
	minEvictableIdleTimeMillis="10000" 
/>

  4.1 设置validationQuery，这样每次borrow(默认为开启)时会通过这个sql校验连接的有效性，但是增加了时间。
  4.2 设置timeBetweenEvictionRunsMillis="10000" minEvictableIdleTimeMillis="10000" 依赖evictor thread线程来把超时的连接关闭。
  4.3 设置testWhileIdle="true" timeBetweenEvictionRunsMillis="10000" validationQuery="select 1" 使得定时去用query检测处于idle状态的连接，也就刷新了服务器端的时间。

5.每次提交的最大packet大小
show global variables like 'max_allowed_packet';
set global max_allowed_packet=1024*1024;

6.SQLyog 中连接参数的设置
  6.1 在SQLyog中的设置 set autocommit=0，这样当前连接的自动提交为false，可以控制事务了。
  6.2 begin; 事务开始
  6.3 select * from test where 1=1 and id =1 for update;这样就把选到的记录行锁上了，再开一个SQLyog，也执行以上相同的操作，就会一直wait在那里。
  6.4 commit; 提交
  6.5 rollback; 回滚
  6.6 set autocommit=0;后应该加上
      set transaction isolation level read committed;
      这样其它客户端就能看到commit的数据，
  疑问:
      如果不设置set transaction isolation level read committed;如果两个客户端都select 相同的数据，一个客户端修改然后提交，另一个客户端不提交当前事务的前提下，去执行select ，取不到另一客户端提交的数据，不知道SQLyog默认的事务级别是什么样的。

7.SQLyog中查看mysql的状态，show global variables like '%lock%'; 是个好方法。对于事务锁(例如for update)报Lock wait timeout exceeded ，只能通过修改my.ini文件innodb_lock_wait_timeout = 100;才能生效。

8.linux下修改用户密码 mysqladmin -u root password "new_pass"

参考

http://fengbin2005.iteye.com/blog/1906488

http://www.jb51.net/article/32284.htm

转载于:https://my.oschina.net/anxiaole/blog/840526

weixin_33814685

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MyBatis连接MySQL报异常：No operations allowed after connection closed.Connection was implicitly closed...

2019独角兽企业重金招聘Python工程师标准>>> ...
复制链接

扫一扫