Mysql空閑連接配接逾時自動斷開問題記錄

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  bytes, read  bytes before connection was unexpectedly lost.

STACKTRACE:

java.io.EOFException: Can not read response from server. Expected to read  bytes, read  bytes before connection was unexpectedly lost.
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:)
    at com.temp.datamigration.tool.DatasourceUtil.executMysqlQuery(DatasourceUtil.java:)
    at com.temp.datamigration.processor.recordprocessor.datacloud.UserSelfDefineProcessor.initUserSelfDefineSdsMode(UserSelfDefineProcessor.java:)
    at com.temp.datamigration.processor.recordprocessor.datacloud.UserSelfDefineProcessor.init(UserSelfDefineProcessor.java:)
    at com.temp.datamigration.processor.WearProcessor.initDataCloudProfileProcessors(WearProcessor.java:)
    at com.temp.datamigration.processor.WearProcessor.init(WearProcessor.java:)
    at com.temp.datamigration.bootstrap.Bootstrap.startMerge(Bootstrap.java:)
    at com.temp.datamigration.bootstrap.Bootstrap.access$000(Bootstrap.java:)
    at com.temp.datamigration.bootstrap.Bootstrap$1.run(Bootstrap.java:)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:)
    at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:)
    at java.lang.Thread.run(Thread.java:)
           

或者如下

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

** BEGIN NESTED EXCEPTION **

java.net.SocketException
MESSAGE: Broken pipe

STACKTRACE:

java.net.SocketException: Broken pipe
        at java.net.SocketOutputStream.socketWrite0(Native Method)
        at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:)
        at java.net.SocketOutputStream.write(SocketOutputStream.java:)
        at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:)
        at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:)
        at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:)
        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:)
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:)
        at com.temp.datamigration.tool.DatasourceUtil.executMysqlQuery(DatasourceUtil.java:)
        at com.temp.datamigration.processor.WearProcessor.obtailUserToBeMerged(WearProcessor.java:)
        at com.temp.datamigration.processor.WearProcessor.init(WearProcessor.java:)
        at com.temp.datamigration.bootstrap.Bootstrap.startMerge(Bootstrap.java:)
        at com.temp.datamigration.bootstrap.Bootstrap.access$000(Bootstrap.java:)
        at com.temp.datamigration.bootstrap.Bootstrap$1.run(Bootstrap.java:)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:)
        at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:)
        at java.lang.Thread.run(Thread.java:)
           

mysql server gone away

mysql wait_timeout變量說明

C3P0連接配接可用測試配置說明

下面是我們代碼中初始化C3P0 DataSource的代碼

ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setDriverClass("com.mysql.jdbc.Driver");
dataSource.setJdbcUrl(mysqlConfig.getJdbcUrl());
dataSource.setUser(mysqlConfig.getUsername());
dataSource.setPassword(mysqlConfig.getPasswd());
dataSource.setMinPoolSize();
dataSource.setAcquireIncrement();
dataSource.setMaxPoolSize(maxPoolSize);
dataSource.setInitialPoolSize();
dataSource.setAcquireRetryDelay();
           

即未設定連接配接任何Connection Testing 配置，都使用預設值

preferredTestQuery 預設值null，沒有校驗連接配接可用性的測試語句

testConnectionOnCheckin 預設值false，建立連接配接時不會校驗連接配接可用性

testConnectionOnCheckout 預設值false，從連接配接池borrow時不會校驗連接配接可用性

idleConnectionTestPeriod 預設值為0，即不進行空閑測試逐出處理。

而連接配接池的minPoolSize卻被設定為5，連接配接池預設最小有5個空閑連接配接。尼瑪，這5個連接配接在長時間(8小時)後被mysql server斷連了。

修改方案1：修改mysql wait_timeout值超過24小時。但不能從根本上解決問題，而且一般現網的mysql不能随意修改，該方案不可行。

修改方案2：配置C3P0的Connection Testing ，由連接配接池自行校驗連接配接可用性。

dataSource.setPreferredTestQuery("select 1");
dataSource.setTestConnectionOnCheckout(true);
dataSource.setTestConnectionOnCheckin(true);
           

測試驗證：

1)set global wait_timeout=30;—-将mysql的wait_timeout全局變量設定為30s。

2)将業務(定時讀取Mysql的任務)定時周期挑戰為1分鐘。

即可複現和驗證該問題。

而現網的Tomcat業務伺服器未出現過類似問題，而我們的Tomcat業務服務使用Tomcat dbcp資料庫連接配接池中間件，并設定了validationQuery為”select 1”，但未配置其他Connection Testing。

檢視DBCP配置參數說明

testOnBorrow、testOnConnect、testOnReturn、testWhileIdle的預設值也都是False，按道理也應該會存在連接配接斷連的問題的啊。

檢視源碼發現 org.apache.tomcat.dbcp.dbcp.BasicDataSource.java

testOnBorrow預設值是true。。。。和文檔不符。。。