Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.0

Mapping documents:hibernate.cfg.xml



Hi,
Comunication error between web application and DBMS (MySql) with hibernate 3.0.x.
This is the problem: after 8 hours that the web application is in standby
(in this time not query and db comunication is instaured),
the connection is stoped from DBMS( wait_timeout parameter... )and the web application catch the following exception:

org.hibernate.exception.JDBCConnectionException: could not execute query using iterate:com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:

Full stack trace of any exception that occurs:
** 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(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:2739)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2650)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1581)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3004)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1128)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1222)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:139)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1669)
at org.hibernate.loader.hql.QueryLoader.iterate(QueryLoader.java:397)
at org.hibernate.hql.ast.QueryTranslatorImpl.iterate(QueryTranslatorImpl.java:318)
at org.hibernate.engine.query.HQLQueryPlan.performIterate(HQLQueryPlan.java:177)
at org.hibernate.impl.SessionImpl.iterate(SessionImpl.java:1156)
at org.hibernate.impl.QueryImpl.iterate(QueryImpl.java:46)
at syspri.framework.servlet.login.Access.doPost(Access.java:58)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at syspri.framework.common.FilterSession.doFilter(FilterSession.java:30)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Unknown Source)


** END NESTED EXCEPTION **



Last packet sent to the server was 0 ms ago.
org.hibernate.exception.JDBCConnectionException: could not execute query using iterate
at syspri.framework.servlet.login.Access.doPost(Access.java:65)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at syspri.framework.common.FilterSession.doFilter(FilterSession.java:30)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Unknown Source)
Caused by: com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:



I have tested my application with another connection pool framework C3Po that it had to resolve my problem.
My c3po configuration is:
<property name="c3p0.minPoolSize">5</property>
<property name="c3p0.maxPoolSize">20</property>
<property name="c3p0.timeout">1800</property>
<property name="c3p0.max_statement">50</property>
<property name="c3p0.preferredTestQuery">SELECT 1</property>
<property name="c3p0.idleConnectionTestPeriod">30</property>
<property name="c3p0.maxIdleTime">50</property>
<property name="c3p0.testConnectionOnCheckin">true</property>
<property name="c3p0.testConnectionOnCheckout">false</property>

with this configuration the problem is the same.
I think that i haven't not configured correctly my connection pool.

Do you have any idea???
Thank you

Hi. Many of the c3p0 parameters you set cannot currently be set in your hibernate config file. They must be set in a c3p0.properties file.

Please see c3p0's docs, the section on hibernate integration (online at http://www.mchange.com/projects/c3p0/index.html#appendix_d) for more information.

In any case, for just about any c3p0-related problem, it's a good idea to check out the config dump c3p0 spews (at INFO level) on initialization of the pool, and ensure that all configuration properties you think are setting are actually taking effect.

Setting (properly) c3p0.testConnectionOnCheckout=true and c3p0.preferredTestQuery=SELECT 1 is a safe and decent approach under MySql. Your approach, using c3p0.testConnectionOnCheckin / c3p0.idleConnectionTestPeriod is also reasonable, and avoids the client performance hit of a synchronous test. When you set up c3p0 properly, your problems may well disappear. If you try the latest c3p0 (0.9.1-pre7), the new parameter c3p0.maxConnectionAge, which limits the absolute age of Connections in the pool, is a conceptually straightforward way to avoid timeouts. (For older versions, use c3p0.maxIdleTime, which hibernate overrides with hibernate.c3p0.timeout. Under hibernate, only hibernate.c3p0.timeout has any effect.)

good luck!
Steve

(I've been corresponding with one of from hibernate's developers about allowing all c3p0 properties to be set from within hibernate's config. Many users are confused by the some-c3p0-properties-in-hibernate-others-in-c3p0-properties thing. We'll see how things evolve...)

I've cofigurated my application with your suggestions and it work fine.
Thanks for your help.

Bye