Mysql数据库查看show processlist的连接丢失问题（高分，不够再开帖补）

lsy110 2009-05-27 01:27:03

一个java项目，出现如下问题：
Eclipse平台，apache + mysql的架构，连接池是c3p0的。

1，当应用服务器和数据库在同一个服务器上（或者同局域网内），连接都正常，不会出现连接失败。
2，当应用服务器和数据库分属不同的网段（通过VPN），当应用服务器到mysql空闲了900秒，即没有任何人操作的时候，再次登录应用服务器（请求Mysql验证登录用户），就一定会出现http:500的错误页面，而此时：

a),c3p0显示的连接池的连接应该是有效的，否则会自动获取新连接
b),到mysql进行监控，发现mysql用show processlist，显示的thread每到900秒就自动消失了。
（应该就是这个问题，导致了连接失败，mysql设定的timeout时间18000秒，不存在超时问题）
c),而mysql中local的用户却即使空闲了几千秒都不会掉。
d),用户必须重新连接主页，重新登录既OK了。即，空闲一段时间后，第一次登陆一定失败（500错误），第二次登陆就OK了。
（在mysql端，看到的thread马上存在了，而且time也初始化了。）

java扑捉的实时错误如下：
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Last packet sent to the server was 0 ms ago.
at sun.reflect.GeneratedConstructorAccessor77.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: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 com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
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 org.springframework.orm.hibernate3.HibernateTemplate$29.doInHibernate(HibernateTemplate.java:856)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:373)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:847)
at hlss.dao.UserDaoImpl.isInUser(UserDaoImpl.java:80)
at hlss.loginScreen.service.UserServiceImpl.login(UserServiceImpl.java:35)
at hlss.loginScreen.Login.button1_action(Login.java:459)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.el.parser.AstValue.invoke(AstValue.java:131)
at org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:276)
at org.apache.jasper.el.JspMethodExpression.invoke(JspMethodExpression.java:68)
at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:77)
at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:91)
at com.sun.rave.web.ui.appbase.faces.ActionListenerImpl.processAction(ActionListenerImpl.java:91)
at javax.faces.component.UICommand.broadcast(UICommand.java:383)
at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:447)
at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:752)
at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:97)
at com.sun.faces.lifecycle.LifecycleImpl.phase(LifecycleImpl.java:251)
at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:117)
at com.sun.faces.extensions.avatar.lifecycle.PartialTraversalLifecycle.execute(PartialTraversalLifecycle.java:94)
at javax.faces.webapp.FacesServlet.service(FacesServlet.java:244)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.sun.webui.jsf.util.UploadFilter.doFilter(UploadFilter.java:267)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at hlss.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:92)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at hlss.filters.IsLoginFilter.doFilter(IsLoginFilter.java:146)
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:230)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:261)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:581)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Unknown Source)
Caused by: java.net.SocketException: Connection reset by peer: socket write error
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(Unknown Source)
at java.net.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:3119)
... 63 more
2009-05-26 09:14:06,578 org.hibernate.util.JDBCExceptionReporter WARN---> SQL Error: 0, SQLState: 08S01
2009-05-26 09:14:06,578 org.hibernate.util.JDBCExceptionReporter ERROR---> Communications link failure

Last packet sent to the server was 0 ms ago.
2009-05-26 09:14:06,578 org.hibernate.jdbc.JDBCContext DEBUG---> after autocommit
2009-05-26 09:14:06,578 org.hibernate.jdbc.ConnectionManager DEBUG---> aggressively releasing JDBC connection
2009-05-26 09:14:06,578 org.hibernate.impl.SessionImpl DEBUG---> after transaction completion
2009-05-26 09:14:06,578 org.hibernate.impl.SessionImpl DEBUG---> closing session

================================================================

所有网上提到的尝试：
什么jdbc连接参数添加autoreconnect啦，
什么设置mysql数据库的timeout时间啦，
什么设置c3p0的testconnectiononcheckin，
还有空闲最大时间，连接数等等，
都设了。还是每到900秒就死了

那么各位大侠，有如下问题：
1，mysql端的connection thread是在什么情况下可能被干掉？（除了timeout）
2，导致这个跨网段的连接问题，会有哪些情况？可有解决方案？

分数不够可以再加，万分紧急，非常感谢！！！

...全文