MyBatis连接MySQL报异常:No operations allowed after connection closed.Connection was implicitly closed
MyBatis连接MySQL报异常:No operations allowed after connection closed.Connection was implicitly closed
安小乐 发表于10个月前
MyBatis连接MySQL报异常:No operations allowed after connection closed.Connection was implicitly closed
  • 发表于 10个月前
  • 阅读 118
  • 收藏 0
  • 点赞 0
  • 评论 0

腾讯云 技术升级10大核心产品年终让利>>>   

摘要: 在阿里云服务器上部署项目后,第二天一大早,发现项目/user/login接口返回的状态码为500.查看了异常信息,并查到了解决方案。

异常信息


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种:

  1. 增加 MySQL的全局wait_timeout 的时间。
  2. 减少 Connection pools 中 connection 的 lifetime。
  3. 测试 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

标签: MySQL MyBatis
  • 点赞
  • 收藏
  • 分享
粉丝 9
博文 85
码字总数 45626