关于mysql的wait_timeout连接超时问题报错解决方案

关于 MySQL的 wait_timeout连接超时问题报错解决方案 Mysql服务器默认的“wait_timeout”是 8小时【也就是默认的值默认是 28800 秒】，也就是说一个 connection空闲超过 8个小时，Mysql 将自动断开该 connection，通俗的讲就是一个连接在 8小时内没有活动，就会自动断开该连 接。 wait timeout的值可以设定，但最多只能是 2147483，不能再大了。也就是约 24.85天 所以即使你 MySQL通过 my.ini 在 # The TCP/IP Port the MySQL Server will listen on port=3306下面添加 # this is myown dinifition for mysql connection timeout wait_timeout=31536000 interactive_timeout=31536000 无论超过最大限度多大的数值，只能被 MySQL解析为 2147483，2147483 天后你 的程序该出什么错还是什么错，避免不了的 在说这个错误之前先说明我的项目是通过 Hibernate来进行数据库操作的 关于 MySQL连接超时问题，估计很多人都遇到过：大致情形都是这样，开发测 试时程序都是正常的，一到第二天就出先莫名错误，比如在我的项目中就是定 时任务执行，每天凌晨一点执行一次，也就是 24小时每隔 24小时执行，远远 超出了 8小时 如果你刚好在数据库超时的第一时间内看到日志记录的话那么，第一次超时发 生的错误就是这样的： ERROR org.hibernate.util.JDBCExceptionReporter - Communications link failure Last packet sent to the server was 0 ms ago. 如果不是第一次超时后执行，以后每次报错就变成嵌套的错误了，就是下面这 样： ERROR org.hibernate.util.JDBCExceptionReporter - No operations allowed after connection closed.Connection was implicitly closed due to underlying exception/error: * BEGIN NESTED EXCEPTION * com.mysql.jdbc.exceptions.jdbc4.CommunicationsException MESSAGE: The last packet successfully received from the server was86395 milliseconds ago.The last packet sent successfully to the server was 86395 milliseconds ago, which is longer than the server configured value of wait_timeout. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property autoReconnect=true to avoid this problem. STACKTRACE: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was86395 milliseconds ago.The last packet sent successfully to the server was 86395 milliseconds ago, which is longer than the server configured value of wait_timeout. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property autoReconnect=true to avoid this problem. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) at java.lang.reflect.Constructor.newInstance(Unknown Source) at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1 074) at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3270) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1932) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2554) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.ja va:1761) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java: 1912) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java: 208) at org.hibernate.loader.Loader.getResultSet(Loader.java:1812) at org.hibernate.loader.Loader.doQuery(Loader.java:697) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Lo ader.java:259) at org.hibernate.loader.Loader.doList(Loader.java:2232) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129) at org.hibernate.loader.Loader.list(Loader.java:2124) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401) at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.ja va:363) at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:1 96) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1149) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102) at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.j ava:835) at .util.db.TargetRecordDaoImpl.findbyIdAndDate(TargetRecordDaoImp l.java:23) at .util.parser.ExcelOperate.readExcel(ExcelOperate.java:324) at .util.parser.ExcelParser.parser(ExcelParser.java:41) at .util.timer.CRMExcelParserTarger.execute(CRMExcelParserTarger.j ava:76) at org.quartz.core.JobRunShell.run(JobRunShell.java:199) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.j ava:549) Caused by: .SocketException: Software caused connection abort: socket write error at .SocketOutputStream.socketWrite0(Native Method) at .SocketOutputStream.socketWrite(Unknown Source) at .SocketOutputStream.write(Unknown Source) at java.io.BufferedOutputStream.flushBuffer(Unknown Source) at java.io.BufferedOutputStream.flush(Unknown Source) at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3251) . 24 more * END NESTED EXCEPTION * 具体解释是这样的：Mysql 服务器默认的“wait_timeout”是 8小时【也就是 默认的值默认是 28800秒】，也就是说一个 connection空闲超过 8个小时， Mysql将自动断开该 connection，通俗的讲就是一个连接在 8小时内没有活动， 就会自动断开该连接。 wait timeout的值可以设定，但最多只能是 2147483，不能再大了。也就是约 24.85天 所以即使你 MySQL通过 my.ini 在 # The TCP/IP Port the MySQL Server will listen on port=3306下面添加 # this is myown dinifition for mysql connection timeout wait_timeout=31536000 interactive_timeout=31536000 无论超过最大限度多大的数值，只能被 MySQL解析为 2147483，2147483 天后你 的程序该出什么错还是什么错，避免不了的 后来发现 Hibernate的内置连接池性能是非常的差，还不如直接用第三方的 c3p0,改用 C3P0连接池，这 个连接池会自动 处理数据库连接被关闭的情况。要使用 C3P0很简单，先从 Hibernate里把 c3p0-0.9.1.jar复 制到项目的 lib目