MySQL數據庫空閑連接默認8小時后會自動斷開連接，此時由于業務處理使用C3P0連接池，業務在一個長時間休眠恢復后(其實是一個每天的定時任務)出現mysql 讀寫socket異常。異常棧信息如下：
com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:** BEGIN NESTED EXCEPTION **java.io.EOFExceptionMESSAGE: 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.java:188) at com.temp.datamigration.processor.recordprocessor.datacloud.UserSelfDefineProcessor.initUserSelfDefineSdsMode(UserSelfDefineProcessor.java:106) at com.temp.datamigration.processor.recordprocessor.datacloud.UserSelfDefineProcessor.init(UserSelfDefineProcessor.java:79) at com.temp.datamigration.processor.WearProcessor.initDataCloudProfileProcessors(WearProcessor.java:176) at com.temp.datamigration.processor.WearProcessor.init(WearProcessor.java:156) at com.temp.datamigration.bootstrap.Bootstrap.startMerge(Bootstrap.java:155) at com.temp.datamigration.bootstrap.Bootstrap.access$000(Bootstrap.java:43) at com.temp.datamigration.bootstrap.Bootstrap$1.run(Bootstrap.java:125) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:304) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:178) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:745)或者如下
com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:** BEGIN NESTED EXCEPTION **java.net.SocketExceptionMESSAGE: Broken pipeSTACKTRACE:java.net.SocketException: Broken pipe at java.net.SocketOutputStream.socketWrite0(Native Method) at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:113) at java.net.SocketOutputStream.write(SocketOutputStream.java:159) at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82) at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140) at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2744) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1612) 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.java:188) at com.temp.datamigration.processor.WearProcessor.obtailUserToBeMerged(WearProcessor.java:100) at com.temp.datamigration.processor.WearProcessor.init(WearProcessor.java:131) at com.temp.datamigration.bootstrap.Bootstrap.startMerge(Bootstrap.java:156) at com.temp.datamigration.bootstrap.Bootstrap.access$000(Bootstrap.java:43) at com.temp.datamigration.bootstrap.Bootstrap$1.run(Bootstrap.java:126) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:304) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:178) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:745)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(5);dataSource.setAcquireIncrement(5);dataSource.setMaxPoolSize(maxPoolSize);dataSource.setInitialPoolSize(2);dataSource.setAcquireRetryDelay(400);即未設置連接任何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。。。。和文檔不符。。。
新聞熱點
疑難解答
