mysql 数据库连接超时

声明：本博文用于学习总结及工作心得

进行数据库插入操作时，发现与该表关联的另一张表，出现数据库连接超时

根据Tomcat日志返回的信息，大致描述为:

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

......

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


The last packet successfully received from the server was 115,122 milliseconds ago.  The last packet sent successfully to the server was 0 milliseconds ago.

......

Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
        at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3119)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3573)
        ... 96 more
org.hibernate.exception.GenericJDBCException: Cannot release connection

......

Caused by: java.sql.SQLException: Already closed.
        at org.apache.commons.dbcp.PoolableConnection.close(PoolableConnection.java:84)
        at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.close(PoolingDataSource.java:181)
        at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.closeConnection(LocalDataSourceConnectionProvider.java:95)
        at org.hibernate.jdbc.ConnectionManager.closeConnection(ConnectionManager.java:451)
        ... 79 more

常见方法为:

修改MySQL配置文件 添加

wait_timeout=31536000
interactive_timeout=31536000

实际修改以后测试，发现并没有解决问题，通过数据库连接池解决上述问题

解决方法为:

1) JDBC连接池 ，在定义datasource增加属性validationQuery和testOnBorrow，如

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
   <property name="driverClassName" value="${jdbc.driver}" />
   <property name="url" value="${jdbc.url}" />
   <property name="username" value="${jdbc.username}" />
   <property name="password" value="${jdbc.password}" />
   <property name="validationQuery" value="SELECT 1" />
   <property name="testOnBorrow" value="true"/>
</bean>

2) 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>

重启服务，问题解决

需要注意的是，有时候多个表关联的时候，可能当前表并不会有什么错误，此时应该关注一下关联的表是否出现连接超时的问题