alxe1528

新開發(fā)的一個(gè)網(wǎng)站，每天早上來(lái)開就有訪問數(shù)據(jù)庫(kù)的操作就出現(xiàn)異常，數(shù)據(jù)庫(kù)為Mysql。也就是說在運(yùn)行中每個(gè)一段長(zhǎng)的空閑時(shí)間就出現(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服務(wù)器默認(rèn)的“wait_timeout”是8小時(shí)，也就是說一個(gè)connection空閑超過8個(gè)小時(shí)，Mysql將自動(dòng)斷開該 connection。這就是問題的所在，在C3P0 pools中的connections如果空閑超過8小時(shí)，Mysql將其斷開，而C3P0并不知道該connection已經(jīng)失效，如果這時(shí)有 Client請(qǐng)求connection，C3P0將該失效的Connection提供給Client，將會(huì)造成上面的異常。

解決的方法有3種：

1.增加wait_timeout的時(shí)間。

2.減少Connection pools中connection的lifetime。

3.測(cè)試Connection pools中connection的有效性。

當(dāng)然最好的辦法是同時(shí)綜合使用上述3種方法，下面就對(duì)hibernate使用C3P0分別做一說明，假設(shè)wait_timeout為默認(rèn)的8小時(shí)。

 

1.導(dǎo)入c3p0.jar包，hibernate自帶的包，也可以在網(wǎng)上下載一個(gè)。

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.重新啟動(dòng)tomcat，問題解決。