(Hibernate2) Genericjdbcexception: Could not execute query - Closed session error while browsing the platform

Question

(Hibernate2) Genericjdbcexception: Could not execute query - Closed session error while browsing the platform

Asked 9 years, 7 months ago

Viewed 947 times

2

Good afternoon!

I have a web application and decided to migrate it to Cloud Google. When I start my Cat, the application runs smoothly, but from a certain time, starts to appear these errors.

09:24:35,719 WARN  [JDBCExceptionReporter] SQL Error: 0, SQLState: 08003
09:24:35,719 ERROR [JDBCExceptionReporter] No operations allowed after connection closed.
09:24:35,719 WARN  [JDBCExceptionReporter] SQL Error: 0, SQLState: 08003
09:24:35,719 ERROR [JDBCExceptionReporter] No operations allowed after connection closed.
09:24:35,719 ERROR [BaseDAO] Error executing query ( from net.alforria.b2c.modelo.Secao as s where s.idLoja = ? and s.id = ?).
net.sf.hibernate.exception.GenericJDBCException: Could not execute query
        at net.sf.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:90)
        at net.sf.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:79)
        at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
        at net.sf.hibernate.impl.SessionImpl.convert(SessionImpl.java:4131)
        at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1557)
        at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:49)
        at net.alforria.b2c.dao.BaseDAO.findByQuery(BaseDAO.java:121)
        at net.alforria.b2c.dao.ProdutoDAO.loadSecaoByLoja(ProdutoDAO.java:115)
        at net.alforria.b2c.loja.produto.ProdutoAction.listSecao(ProdutoAction.java:161)
        at sun.reflect.GeneratedMethodAccessor179.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:276)
        at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:196)
        at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:421)
        at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:226)
        at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1164)
        at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:397)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:624)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:748)
        at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:486)
        at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:411)
        at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:338)
        at net.alforria.b2c.web.UrlRewriteFilter.comitaPaginaUrlAmigavel(UrlRewriteFilter.java:121)
        at net.alforria.b2c.web.UrlRewriteFilter.doFilter(UrlRewriteFilter.java:91)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
        at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:423)
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1079)
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:620)
        at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918)
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
        at java.lang.Thread.run(Thread.java:662)
Caused by: com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: No operations allowed after connection closed.
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:888)
        at com.mysql.jdbc.Connection.checkClosed(Connection.java:1932)
        at com.mysql.jdbc.Connection.prepareStatement(Connection.java:4768)
        at com.mysql.jdbc.Connection.prepareStatement(Connection.java:4734)
        at org.apache.tomcat.dbcp.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:281)
        at org.apache.tomcat.dbcp.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:313)
        at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:263)
        at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:236)
        at net.sf.hibernate.impl.BatcherImpl.prepareQueryStatement(BatcherImpl.java:67)
        at net.sf.hibernate.loader.Loader.prepareQueryStatement(Loader.java:784)
        at net.sf.hibernate.loader.Loader.doQuery(Loader.java:269)
        at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:138)
        at net.sf.hibernate.loader.Loader.doList(Loader.java:1063)
        at net.sf.hibernate.loader.Loader.list(Loader.java:1054)
        at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
        at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1554)
        ... 40 more

This is my Hibernate 2 configuration:

            <!-- properties -->
            <property name="connection.datasource">java:/comp/env/jdbc/B2C</property>
            <property name="dialect">net.sf.hibernate.dialect.MySQLDialect</property>
            <property name="show_sql">false</property>
            <!-- connection pool configurations -->
            <property name="hibernate.connection.pool_size">q</property>
            <property name="hibernate.c3p0.min_size">5</property>
            <property name="hibernate.c3p0.max_size">50</property>
            <property name="hibernate.c3p0.timeout">14400</property>
            <property name="hibernate.c3p0.max_statements">10</property>
            <property name="hibernate.c3p0.idle_test_period">100</property>
            <property name="hibernate.c3p0.validate">true</property>
            <property name="hibernate.c3p0.preferredTestQuery">SELECT 1</property>
            <property name="hibernate.c3p0.testConnectionOnCheckout">true</property>
            <property name="use_outer_join">true</property>]

findByQuery method of Basedao:

protected final List findByQuery(String hql, Object[] paramValues, Type[] paramTypes) {
    List ret;
    Session s = HibernateUtil.getSession();

    try {
        Query query = s.createQuery(hql);

        if(paramValues != null) {
            for (int i = 0; i < paramValues.length; i++) {
                Object value = paramValues[i];
                Type type = paramTypes[i];
                query.setParameter(i, value, type);
            }
        }

        ret = query.list();
    }
    catch(HibernateException e) {
        log.error("Error executing query (" + hql + ").", e);
        throw new InfraException(e);
    }

    return ret;
}

loadSecaobyProduct Store method:

public Secao loadSecaoByLoja(long idLoja, long id) {
    List list = findByQuery(
        " from " + Secao.class.getName() + " as s" +
        " where s.idLoja = ? and s.id = ?",
        new Object[] {idLoja, id},
        new Type[] {Hibernate.LONG, Hibernate.LONG}
    );

    return (Secao)getFirst(list);
}

Post what is relevant from BaseDAO and ProdutoDAO and configuration of DataSource.

– Anthony Accioly

2015/05/25 at 19:36
So... the Product here is irrelevant, because this error does not occur only in product queries... The main problem here is the connection closing itself... It is possible that there is some error with the c3p0 configs?

– Vitor Carvalho

2015/05/25 at 19:44
Unfortunately I can not say, only with the information you posted, if the problem is the connection pool, internal database settings / Tomcat or simply the way Session / EntityManager are being injected / used. Playing your problem on Google you will find all these variations (and some others) to MySQLNonTransientConnectionException. How have you included settings like testConnectionOnCheckout=true and idle_test_period=100 i would test 1) If your settings are really getting 2) If the problem isn’t elsewhere.

– Anthony Accioly

2015/05/25 at 22:28
Write a mvce is an excellent way to isolate the problem. This will not only help us diagnose the problem, it may be that you find the solution while trying to isolate it.

– Anthony Accioly

2015/05/25 at 22:32
I think you need to figure out how long your bank is closing down idle connections and then set up c3p0 according to the information that makes sense in your scenario. Do not forget the properties: <Property name="Hibernate.c3p0.timeout">300</Property> <Property name="Hibernate.c3p0.idle_test_period">30</Property> All of which are in seconds.

– wryel

2015/05/25 at 23:21
I appreciate the help guys, we tried all this and nothing solved yet I will put the relevant information here of the classes in the post.

– Vitor Carvalho

2015/05/26 at 16:39
Um... Pick up a session directly from HibernateUtil in a web application usually give problem. The default for scenarios without CDI / Spring / EJB, etc is to create a HibernateListener (optionally you can also play the SessionFactory as an attribute in scope of application). All application points of use must open their own Session from the SessionFactory (and close the session after use). See if this resolves.

– Anthony Accioly

2015/05/26 at 23:03
Additionally, set up the connection directly in Hibernate as suggested above. With your current settings you are using the c3p0 on top of a tomcat datasource... Besides the pooling work being duplicated, the c3p0 will get connections Stale if you do not set parameters as maxWait correctly in the Tomcat pool.

– Anthony Accioly

2015/05/26 at 23:19
Once again I appreciate the help and the information! I think we can solve here, soon edit the post with the solution.

– Vitor Carvalho

2015/05/29 at 10:45

Show 4 more comments

1 answer

Browser other questions tagged java mysql hibernate java-ee cloud

You are not signed in. Login or sign up in order to post.

by Michel Simões • **1,236** points · Answer 1 · 2015-05-25T19:25:53+00:00

1

When setting the database path just create an autoReconnect... By default the server disables the connection after certain idle time! In my case it’s like this:

jdbc:mysql://caminho:3306/banco?autoReconnect=true

So, Michel, it was worth a lot, but this configuration is already like this, look -> url="jdbc:mysql://path? autoReconnect=true&useUnicode=true&characterEncoding=latin1"

– Vitor Carvalho

2015/05/25 at 19:28
In my case, solved well... If you remove the other parameters, he can not interpret?

– Michel Simões

2015/05/25 at 19:34
So, this url is already configured this way in our other server... And there works perfectly! The error started happening only on the servers we put when migrating to Google Cloud!

– Vitor Carvalho

2015/05/25 at 19:49