alxe1528

新开发的一个网站，每天早上来开就有访问数据库的操作就出现异常，数据库为Mysql。也就是说在运行中每个一段长的空闲时间就出现异常，异常为:

** BEGIN NESTED EXCEPTION ** 

com.mysql.jdbc.CommunicationsException 
MESSAGE: 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:2590) 
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2523) 
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1517) 
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1626) 
at com.mysql.jdbc.Connection.execSQL(Connection.java:3031) 
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:943) 
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1049) 
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:139) 
at org.hibernate.loader.Loader.getResultSet(Loader.java:1669) 
at org.hibernate.loader.Loader.doQuery(Loader.java:662) 
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224) 
at org.hibernate.loader.Loader.doList(Loader.java:2145) 
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029) 
at org.hibernate.loader.Loader.list(Loader.java:2024) 
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:375) 
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:308) 
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:153) 
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1106) 
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79) 
at org.hibernate.hopeDAO.StudentDBentity.findStudent(StudentDBentity.java:75) 
at com.hope.student.action.StudentAction.execute(StudentAction.java:483) 
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:419) 
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224) 
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1194) 
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432) 
at javax.servlet.http.HttpServlet.service(HttpServlet.java:710) 
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803) 
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290) 
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(Thread.java:595) 


** END NESTED EXCEPTION ** 



Last packet sent to the server was 1 ms ago. 

STACKTRACE: 

com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:

查看了Mysql的文档，以及Connector/J的文档以及在线说明发现，出现这种异常的原因是：Mysql服务器默认的“wait_timeout”是8小时，也就是说一个connection空闲超过8个小时，Mysql将自动断开该 connection。这就是问题的所在，在C3P0 pools中的connections如果空闲超过8小时，Mysql将其断开，而C3P0并不知道该connection已经失效，如果这时有 Client请求connection，C3P0将该失效的Connection提供给Client，将会造成上面的异常。

解决的方法有3种：

1.增加wait_timeout的时间。

2.减少Connection pools中connection的lifetime。

3.测试Connection pools中connection的有效性。

当然最好的办法是同时综合使用上述3种方法，下面就对hibernate使用C3P0分别做一说明，假设wait_timeout为默认的8小时。

 

1.导入c3p0.jar包，hibernate自带的包，也可以在网上下载一个。

2.Hibernate的配置文件，hibernate.cfg.xml中增加：

<property name="c3p0.acquire_increment">1</property>

<property name="c3p0.max_statements">0</property>

<property name="c3p0.maxIdleTime">25000</property>        

<property name="c3p0.testConnectionOnCheckout">true</property>

3.重新启动tomcat，问题解决。