MySQL连接池“Communications link failure”的问题

错误描述

使用MySQL，业务程序需要每天运行一次，然后将结果保存到MySQL。发现每次第一次运行没有问题，到了第二次运行的时候就会报错：

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 86,399,655 milliseconds ago.  The last packet sent successfully to the server was 54 milliseconds ago.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1121)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3673)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3562)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4113)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2812)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2761)
    at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1612)
    at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
    at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
    at com.fb.DB.DBProcess.get(DBProcess.java:30)
    at com.fb.task.PersonalInfoTask$1.run(PersonalInfoTask.java:35)
    at java.util.TimerThread.mainLoop(Timer.java:555)
    at java.util.TimerThread.run(Timer.java:505)
Caused by: java.net.SocketException: Software caused connection abort: recv failed
    at java.net.SocketInputStream.socketRead0(Native Method)
    at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
    at java.net.SocketInputStream.read(SocketInputStream.java:170)
    at java.net.SocketInputStream.read(SocketInputStream.java:141)
    at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:114)
    at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:161)
    at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:189)
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3116)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3573)
    ... 13 more

错误产生原因

Mysql服务器默认的“wait_timeout”是8小时（28800秒），如果一个connection的空闲时间超过8小时，Mysql将自动断开该connection，而连接池由于默认不对连接有效性进行检查，会认为该连接仍是有效的。当应用申请该连接时，就会导致上面的错误。

解决办法

• 增加有效性检测
  DHCP提供testOnBorrow,testOnReturn, testWhileIdle进行校验，默认都是关闭的，可以选择打开。
  使用DataSource的set函数可以进行设置。
  对应的JAVA代码为：

        ds.setTestOnBorrow(true);
        ds.setTestOnReturn(true);
        ds.setTestWhileIdle(true);
        ds.setValidationQuery("select count(*) from DUAL");

连接池校验机制有2种：
1. 同步验证方式：
每次获取连接或关闭连接时，执行一个预定义的验证语句（sql语句），来验证连接池中的连接是否有效，如果验证失败，彻底关闭此连接。这种方式会导致每次执行数据库操作时都有额外的开销，对性能影响较大。
2. 心跳验证方式：
每隔特定的时间进行一次验证，执行一个预定义的验证语句（sql语句），来验证连接池中的连接是否有效，如果验证失败，彻底关闭此连接。
可以根据具体情况，适当的调节验证间隔时间。这种方式以牺牲较小的性能开销为代价，来保持系统的稳定性。

一般来说不推荐使用同步验证方式，如果应用与数据库之间环境不稳定，推荐使用心跳验证方式；如果环境稳定，而且对数据库I/O性能要求较高时，可以不进行验证。

• 如果使用的是JDBC，在JDBC URL上添加?autoReconnect=true，如：

jdbc:mysql://10.10.10.10:3306/dbname?autoReconnect=true

• 如果是在Spring中使用DBCP连接池，在定义datasource增加属性validationQuery和testOnBorrow，如：

<bean id="vrsRankDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="${jdbc.driverClassName}" />
    <property name="url" value="${countNew.jdbc.url}" />
    <property name="username" value="${countNew.jdbc.user}" />
    <property name="password" value="${countNew.jdbc.pwd}" />
    <property name="validationQuery" value="SELECT 1" />
    <property name="testOnBorrow" value="true"/>
</bean>

• 如果是在Spring中使用c3p0连接池，则在定义datasource的时候，添加属性testConnectionOnCheckin和testConnectionOnCheckout，如：

<bean name="cacheCloudDB" class="com.mchange.v2.c3p0.ComboPooledDataSource">
    <property name="driverClass" value="${jdbc.driver}"/>
    <property name="jdbcUrl" value="${cache.url}"/>
    <property name="user" value="${cache.user}"/>
    <property name="password" value="${cache.password}"/>
    <property name="initialPoolSize" value="10"/>
    <property name="maxPoolSize" value="${cache.maxPoolSize}"/>
    <property name="testConnectionOnCheckin" value="false"/>
    <property name="testConnectionOnCheckout" value="true"/>
    <property name="preferredTestQuery" value="SELECT 1"/>
</bean>

参考
http://nkcoder.github.io/blog/20140712/mysql-reconnect-packet-lost/