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，如

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>