mysql 数据库连接超时

约定不等于承诺〃 2022-07-14 21:10 297阅读 0赞

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

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

根据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`，如：**

重启服务，问题解决

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