mysql断开连接默认8小时sql语句_MYSQL 8小时断开链接问题和解决办法

MYSQL 8小时 断开链接问题

解决办法有两个：第一是设置autoReconnect属性设置为true；第二是设置DBCP 时将testquery等几个属性一并设置。

问题的原因是，MySQL的参数interactive_timeout，也就是交互超时时间默认为8小时。也就是如果一个链接在8小时后，还没有和服务器交互，这个连接就会被MySQL服务器断开。因为MySQL能够承受的并发连接有限，所以这个参数有助于清除那些长时间不活动的链接。

错误如下：

ERROR JDBC begin failed com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: ** BEGIN NESTED EXCEPTION ** java.net.SocketException MESSAGE: Software caused connection abort: socket write error STACKTRACE: java.net.SocketException: Software caused connection abort: socket write error at java.net.SocketOutputStream.socketWrite0(Native Method) at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92) at java.net.SocketOutputStream.write(SocketOutputStream.java:136) at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:65) at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:123) 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:3277) at com.mysql.jdbc.Connection.setAutoCommit(Connection.java:5442) at

针对连接池的配置来说：

DBCP配置信息:

//set to 'SELECT 1'

validationQuery = "SELECT 1"

//set to 'true'

testWhileIdle = "true"

//some positive integer

timeBetweenEvictionRunsMillis = 3600000

//set to something smaller than 'wait_timeout'

minEvictableIdleTimeMillis = 18000000

//if you don't mind a hit for every getConnection(), set to "true"

testOnBorrow = "true"

C3P0配置信息:

在C3P0 pools中的connections如果空闲超过8小时，Mysql将其断开，而C3P0并不知道该connection已经失效，如果这时有Client请求connection，C3P0将该失效的Connection提供给Client，将会造成上面的异常。

mysql配置中my.cnf 的wait_timeout值一定要大于等于连接池种的idle_timeout 值。否则mysql会在wait_timeout的时间后关闭连接，然而连接池还认为该连接可用，这样就会产生SocketException。

解决的方法有3种：

增加wait_timeout的时间。

减少Connection pools中connection的lifetime。

测试Connection pools中connection的有效性。

//获取connnection时测试是否有效   testConnectionOnCheckin = true   //自动测试的table名称   automaticTestTable=C3P0TestTable   //set to something much less than wait_timeout, prevents connections from going stale   idleConnectionTestPeriod = 18000   //set to something slightly less than wait_timeout, preventing 'stale' connections from being handed out   maxIdleTime = 25000   //if you can take the performance 'hit', set to "true"   testConnectionOnCheckout = true