---
title: "谨防mysql maxContionTime(连接超时) 8小时工作制"
author: "icejadelove"
date: "2009-10-30T22:35:00Z"
tags: []
slug: "mysql-maxcontiontime-8"
draft: false
lastmod: "2009-10-30T22:35:00Z"
id: 572004
url: "https://jdkdev.iteye.com/blog/572004"
---

1.今天的程序发布到服务器，过了一晚后发现程序不能访问了，好像是连接池问题。错误如下：

\[log4j:] 2009-10-30 17:32:10,353 - com.school.dao.OperaDAO -65984388 \[http-8080-12] ERROR com.school.dao.OperaDAO - find all failed  
org.hibernate.exception.JDBCConnectionException: could not execute query  
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:74)  
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)  
at org.hibernate.loader.Loader.doList(Loader.java:2223)  
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)  
at org.hibernate.loader.Loader.list(Loader.java:2099)  
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)  
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)  
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)  
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)  
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)  
at com.school.dao.OperaDAO.findAll(OperaDAO.java:126)  
at org.apache.jsp.index\_jsp.\_jspService(index\_jsp.java:103)  
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)  
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)  
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)  
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:342)  
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267)  
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)  
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)  
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)  
at org.apache.struts2.dispatcher.FilterDispatcher.doFilter(FilterDispatcher.java:413)  
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)  
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)  
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)  
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)  
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)  
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)  
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)  
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)  
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)  
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)  
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)  
at java.lang.Thread.run(Thread.java:619)  
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: 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: Communications link failure  
Last packet sent to the server was 2 ms ago.  
STACKTRACE:  
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure  
Last packet sent to the server was 2 ms ago.  
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)  
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)  
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)  
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)  
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)  
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)  
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3134)  
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1818)  
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1961)  
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2543)  
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1737)  
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1888)  
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)  
at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)  
at org.hibernate.loader.Loader.doQuery(Loader.java:674)  
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)  
at org.hibernate.loader.Loader.doList(Loader.java:2220)  
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)  
at org.hibernate.loader.Loader.list(Loader.java:2099)  
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)  
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)  
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)  
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)  
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)  
at com.school.dao.OperaDAO.findAll(OperaDAO.java:126)  
at org.apache.jsp.index\_jsp.\_jspService(index\_jsp.java:103)  
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)  
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)  
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)  
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:342)  
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267)  
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)  
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)  
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)  
at org.apache.struts2.dispatcher.FilterDispatcher.doFilter(FilterDispatcher.java:413)  
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)  
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)  
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)  
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)  
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)  
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)  
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)  
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)  
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)  
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)  
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)  
at java.lang.Thread.run(Thread.java:619)  
Caused by: java.net.SocketException: Broken pipe  
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:3119)  
... 40 more  
\** END NESTED EXCEPTION \**  
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)  
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)  
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)  
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)  
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)  
at com.mysql.jdbc.Util.getInstance(Util.java:381)  
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:985)  
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)  
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)  
at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1113)  
at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4038)  
at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4004)  
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:505)  
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:423)  
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139)  
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1547)  
at org.hibernate.loader.Loader.doQuery(Loader.java:673)  
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)  
at org.hibernate.loader.Loader.doList(Loader.java:2220)  
... 30 more

查了一下，原来是，连接池超时，在mysql的配置文件中有此配置wait\_timeout 默认参数是8小时，只要数据库在8小时内有连接数据库，就会自动关闭连接。只要把它改大一点就行。

同时可以配置下hibernate连接池，加入

&lt;property name="hibernate.connection.provider\_class"&gt;&lt;br&gt; org.hibernate.connection.C3P0ConnectionProvider &lt;br&gt;&lt;/property&gt;  
&lt;property name="hibernate.c3p0.max\_size"&gt;20&lt;/property&gt;  
&lt;property name="hibernate.c3p0.min\_size"&gt;5&lt;/property&gt;  
&lt;property name="hibernate.c3p0.timeout"&gt;120&lt;/property&gt;  
&lt;property name="hibernate.c3p0.max\_statements"&gt;100&lt;/property&gt;  
&lt;property name="hibernate.c3p0.idle\_test\_period"&gt;120&lt;/property&gt;  
&lt;property name="hibernate.c3p0.acquire\_increment"&gt;2&lt;/property&gt; 就ok了~