MYSQL连接一段时间不操作后出现异常的解决方案

浏览数：23 / 时间：2015年06月12日

最近做的网站使用的是MYSQL数据库发现果超过8小时应用程序不去访问数据库，数据库就断掉连接。这时再次访问就会抛出异常，如下所示：

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Last packet sent to the server was 0 ms ago.
  at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
  at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
  at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
  at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
  at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
  at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
  at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2120)
  at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:723)
  at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
  at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
  at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
  at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
  at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
  at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
  at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:302)
  at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:282)
  at java.sql.DriverManager.getConnection(DriverManager.java:579)
  at java.sql.DriverManager.getConnection(DriverManager.java:243)
  at mysql.DBUtil.ConnectAccess(DBUtil.java:29)
  at mysql.DBUtil.getConn(DBUtil.java:15)
  at obj.IndexTurnPic.getAll(IndexTurnPic.java:49)
  at action.GetTurnPic.doAction(GetTurnPic.java:17)
  at maincontrol.MainControlServlet.service(MainControlServlet.java:34)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
  at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
  at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
  at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
  at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
  at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
  at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
  at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
  at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
  at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
  at org.apache.coyote.ajp.AjpProcessor.process(AjpProcessor.java:200)
  at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
  at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
  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:722)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

查了一下发现应用程序和mysql数据库建立连接，如果超过8小时应用程序不去访问数据库，数据库就断掉连接。这时再次访问就会抛出异常。

关于mysql自动断开的问题研究结果如下，在mysql中有相关参数设定，当数据库连接空闲一定时间后，服务器就会断开等待超时的连接：

1、相关参数，红色部分

mysql> show variables like ‘%timeout%‘;

+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| connect_timeout | 5 |

| delayed_insert_timeout | 300 |

| innodb_lock_wait_timeout | 50 |

| interactive_timeout | 28800 |

| net_read_timeout | 30 |