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

你的名字 2021-04-12 15:16 688阅读 0赞

## 异常信息 ##

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"/> 
         
    </bean>

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

修改 c3p0 的配置文件：

### 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配置

## 相关知识 ##

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)

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

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_tomcat.apache.org_tomcat-6.0-doc_jndi-datasource-examples-howto.html]: http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.html
[http_commons.apache.org_dbcp_configuration.html]: http://commons.apache.org/dbcp/configuration.html