Mysql空闲连接超时自动断开问题记录

最新推荐文章于 2023-10-10 19:36:59 发布

saroll57

最新推荐文章于 2023-10-10 19:36:59 发布

阅读量4.5k

点赞数

分类专栏： Mysql java-web

本文链接：https://blog.csdn.net/saroll57/article/details/59499092

版权

当Mysql数据库的空闲连接超过8小时，会自动断开，导致使用C3P0连接池的业务在长时间休眠后出现异常。异常信息包括' mysql server gone away '。解决方案包括调整mysql的wait_timeout或配置C3P0进行连接测试。在C3P0中，未设置Connection Testing，而minPoolSize设为5，可能导致空闲连接被断开。验证方案是将wait_timeout设为30s，并频繁执行任务。另外，Tomcat dbcp的testOnBorrow默认为true，与文档不符，可能避免了类似问题。

摘要由CSDN通过智能技术生成

Mysql数据库空闲连接默认8小时后会自动断开连接，此时由于业务处理使用C3P0连接池，业务在一个长时间休眠恢复后(其实是一个每天的定时任务)出现mysql 读写socket异常。异常栈信息如下：

com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

java.io.EOFException
MESSAGE: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.

STACKTRACE:

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:1997)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2411)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2916)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3283)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1332)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1467)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:1418)
    at com.temp.datamigration.tool.DatasourceUtil.executMysqlQuery(DatasourceUtil