MYSQL连接8小时未动出现连接错误解决办法

ibatis数据库连接报错No operations allowed after connection closed.Connection was implicitly closed

数据库连接出错信息有下面两条：

com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed due to underlying exception/error:

com.mysql.jdbc.CommunicationsException: Communications link failure

 ================分割线================

一直想不出什么原因导致，网上查资料也不顺利，后来找到了hibernate连接数据库，产生同样的错误时的处理方法，知道了问题出在哪里：

com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:
查看了Mysql的文档，以及Connector/J的文档以及在线说明发现，出现这种异常的原因是：Mysql服务器默认的“wait_timeout”是8小时，也就是说一个connection空闲超过8个小时，Mysql将自动断开该 connection。这就是问题的所在，在C3P0 pools中的connections如果空闲超过8小时，Mysql将其断开，而C3P0并不知道该connection已经失效，如果这时有 Client请求connection，C3P0将该失效的Connection提供给Client，将会造成上面的异常。

上网搜索，在MySQL的论坛上找到一个办法，就是如果在执行sql语句的时候发生了上述异常，就将sql语句重新执行一次。
试验发现，这个办法对这个使用spring+hibernate的服务无效。
进一步搜索发现，MySQL官方不推荐使用autoReconnect=true，参见http://bugs.mysql.com/bug.php?id=5020
需要另外找别的办法来解决这个问题。

由于问题产生的根本原因在于服务到数据库的连接长时间没活动，既然重新连接的办法无效，就可以尝试另外一种办法，就是反空闲。
自己写一个线程来反空闲的话，比较麻烦。
最后在网上找到一个办法。为hibernate配置连接池，推荐用c3p0，然后配置c3p0的反空闲设置idle_test_period，只要小于MySQL的wait timeout即可。
在hibernate.cfg.xml中增加下面几项：
<!-- configuration pool via c3p0-->
<property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
<property name="c3p0.min_size">5</property>
<property name="c3p0.max_size">30</property>
<property name="c3p0.time_out">1800</property> <!-- seconds --><!-- default: 0 -->
<property name="c3p0.max_statement">50</property> <!-- default: 0 -->
<property name="c3p0.acquire_increment">1</property> <!-- default: 1 -->
<property name="c3p0.idle_test_period">120</property> <!-- seconds --><!-- default: 0 -->
<property name="c3p0.validate">true</property>

修改完后测试，问题解决。

================分割线================

具体修改方法如下：

对于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等于多少才是合适的呢？所以这个解决办法不好。)

 

查ibatis文档后修改如下，红字部分是修改的重点：
<transactionManager type="JDBC">
 <dataSource type="SIMPLE">
  <property name="JDBC.Driver" value="com.mysql.jdbc.Driver" />
  <property name="JDBC.ConnectionURL"
   value="jdbc:mysql://192.168.1.4:3306/mail?characterEncoding=utf-8" />
  <property name="JDBC.Username" value="abc" />
  <property name="JDBC.Password" value="abc" />
  <property name="Pool.MaximumActiveConnections" value="10" />
  <property name="Pool.MaximumIdleConnections" value="5" />
  <property name="Pool.MaximumCheckoutTime" value="120000" />
  <property name="Pool.TimeToWait" value="500" />
  <property name="Pool.PingQuery" value="select 1 from redirect_url" />
  <property name="Pool.PingEnabled" value="true" />
  <property name="Pool.PingConnectionsOlderThan" value="0" />
  <property name="Pool.PingConnectionsNotUsedFor" value="3600000" /><!-- 对于空闲的连接一个小时检查一次 -->
 </dataSource>
</transactionManager>

=================================================================================

 

iBatis自己带了一个simple的数据库连接池，基本的功能都有。但是在处理部分数据库（比如mysql）的连接空闲时间太长（mysql是8小时）自动超时的时候，就比不上象c3p0这样的连接池软件了（c3p0能自动处理数据库连接被关闭的情况)。
我目前采用的方法是iBatis本身提供的一种算得上是取巧的办法，基本思想就是每隔一段时间往数据库发一条查询语句，这样使得数据库空闲时间不会太长，而使得其自动关闭。
方法是在SqlMapConfig.xml的dataSource进行如下配置：
<dataSource type="SIMPLE">
    <property name="JDBC.Driver" value="${jdbc.driverClassName}"/>
    <property name="JDBC.ConnectionURL" value="${jdbc.url}"/>
    <property name="JDBC.Username" value="${jdbc.username}"/>
    <property name="JDBC.Password" value="${jdbc.password}"/>
    <property name="Pool.PingEnabled" value="true"/>
    <property name="Pool.PingQuery" value="select 1"/>
    <property name="Pool.PingConnectionsOlderThan" value="0"/>
    <property name="Pool.PingConnectionsNotUsedFor" value="3600000"/>
</dataSource>
开始的３行是关于数据库连接信息的，不需要说明了。
Pool.PingEnabled：是用于设置开启是否允许检测连接状态
Pool.PingQuery：是用于检测连接的查询语名，当然是越简单越好
Pool.PingConnectionOlderThan：对持续连接时间超过设定值（毫秒）的连接进行检测，我将其设置为０（不进行此项检测），否则，iBatis在超过这个时间后，执行每个sql以前检测连接，对于性能可能会有一定的影响。
Pool.PingConnectionsNotUsedFor：对空闲超过设定值（毫秒）的连接进行检测，我设置为1小时（mysql缺省的关闭时间是8小时)

当然，还有一个办法是使用c3p0这样的连接池
但是需要自己写一部分代码，实现以下接口：

public interface DataSourceFactory {

public void initialize(Map map);

public DataSource getDataSource();

}