alxe1528

新開發(fā)的一個網站，每天早上來開就有訪問數(shù)據(jù)庫的操作就出現(xiàn)異常，數(shù)據(jù)庫為Mysql。也就是說在運行中每個一段長的空閑時間就出現(xiàn)異常，異常為:

** 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的文檔以及在線說明發(fā)現(xiàn)，出現(xiàn)這種異常的原因是：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，問題解決。