Connection exception with hibernate, jpa and c3p0

Question

I have a problem with c3p0-0.9.5-pre10, hibernate-release-4.2.16.Final and JPA 2.0. When mysql is inactive for 8 hours or more, I get this error and my app doesn't work anymore. Why doesn't C3P0 work?

Dec 11, 2014 7:45:15 AM com.sun.faces.context.AjaxExceptionHandlerImpl handlePartialResponseError
SEVERE: javax.faces.el.EvaluationException: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not prepare statement
    at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:101)
    at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:102)
    at javax.faces.component.UICommand.broadcast(UICommand.java:315)
    at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:790)
    at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1282)
    at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:81)
    at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
    at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:198)
    at javax.faces.webapp.FacesServlet.service(FacesServlet.java:646)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.primefaces.webapp.filter.FileUploadFilter.doFilter(FileUploadFilter.java:72)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:936)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
    at org.apache.coyote.ajp.AjpProcessor.process(AjpProcessor.java:200)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:744)
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not prepare statement
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1387)
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1310)
    at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:277)
    at br.com.clikyapp1.modelo.UsuarioDAO.login(UsuarioDAO.java:27)
    at br.com.clikyapp1.controle.ControleLogin.efetuarLogin(ControleLogin.java:29)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.apache.el.parser.AstValue.invoke(AstValue.java:278)
    at org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:274)
    at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:105)
    at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:87)
    ... 27 more
Caused by: org.hibernate.exception.GenericJDBCException: could not prepare statement
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:124)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:193)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:157)
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1881)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1858)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1838)
    at org.hibernate.loader.Loader.doQuery(Loader.java:906)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:348)
    at org.hibernate.loader.Loader.doList(Loader.java:2550)
    at org.hibernate.loader.Loader.doList(Loader.java:2536)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2366)
    at org.hibernate.loader.Loader.list(Loader.java:2361)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:495)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:357)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:198)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1194)
    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
    at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:268)
    ... 37 more
Caused by: java.sql.SQLException: A problem occurred while trying to acquire a cached PreparedStatement in a background thread.
    at com.mchange.v2.c3p0.stmt.GooGooStatementCache.acquireStatement(GooGooStatementCache.java:571)
    at com.mchange.v2.c3p0.stmt.GooGooStatementCache.checkoutStatement(GooGooStatementCache.java:204)
    at com.mchange.v2.c3p0.impl.NewPooledConnection.checkoutStatement(NewPooledConnection.java:321)
    at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:1086)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:159)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:183)
    ... 53 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)
    at com.mysql.jdbc.Util.getInstance(Util.java:383)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1023)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:997)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:983)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:928)
    at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1323)
    at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1315)
    at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4533)
    at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4498)
    at sun.reflect.GeneratedMethodAccessor199.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StmtAcquireTask.run(GooGooStatementCache.java:546)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)

Here is my persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0"
    xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
    <persistence-unit name="clikyapp1" transaction-type="RESOURCE_LOCAL">

        <!-- <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider> -->
         <provider>org.hibernate.ejb.HibernatePersistence</provider>   

        <class>br.com.clikyapp1.bean.Cultura</class>
        <class>br.com.clikyapp1.bean.Cidade</class>
        <class>br.com.clikyapp1.bean.Estado</class>
        <class>br.com.clikyapp1.bean.TipoDespesa</class>
        <class>br.com.clikyapp1.bean.Safra</class>
        <class>br.com.clikyapp1.bean.TipoContrato</class>
        <class>br.com.clikyapp1.bean.Area</class>
        <class>br.com.clikyapp1.bean.Armazem</class>
        <class>br.com.clikyapp1.bean.Conta</class>
        <class>br.com.clikyapp1.bean.Parceiro</class>
        <class>br.com.clikyapp1.bean.Usuario</class>
        <class>br.com.clikyapp1.bean.Fixacao</class>
        <class>br.com.clikyapp1.bean.Venda</class>
        <class>br.com.clikyapp1.bean.Modalidade</class>
        <class>br.com.clikyapp1.bean.LocalEntrega</class>
        <class>br.com.clikyapp1.bean.VinculoContrato</class>
        <class>br.com.clikyapp1.bean.OcorrenciaContrato</class>
        <class>br.com.clikyapp1.bean.LocalEntregaContrato</class>
        <class>br.com.clikyapp1.bean.Contrato</class>
        <class>br.com.clikyapp1.bean.Indexador</class>
        <class>br.com.clikyapp1.bean.CessaoCredito</class>
        <class>br.com.clikyapp1.bean.Penhor</class>
        <properties>
            <property name="javax.persistence.jdbc.user" value="root" />
            <property name="javax.persistence.jdbc.password" value="********" />
            <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/clikyapp1?autoReconnect=true" />
            <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
            <property name="hibernate.cache.provider_class" value="org.hibernate.cache.NoCacheProvider" />
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5Dialect" />
            <property name="hibernate.connection.autocommit" value="false" />
            <property name="hibernate.hbm2ddl.auto" value="update" />

            <property name="hibernate.temp.use_jdbc_metadata_defaults"
                value="false" />

            <property name="hibernate.connection.provider_class"
                value="org.hibernate.connection.C3P0ConnectionProvider" />
            <property name="hibernate.c3p0.min_size" value="7"/>
            <property name="hibernate.c3p0.max_size" value="53"/>
            <property name="hibernate.c3p0.timeout" value="100"/>
            <property name="hibernate.c3p0.max_statements" value="50"/>
            <property name="hibernate.c3p0.idle_test_period" value="1000"/>
            <property name="hibernate.c3p0.validate" value="true"/>
            <property name="hibernate.connection.provider_class" value="org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider"/>

        </properties>
    </persistence-unit>
</persistence>

Any idea??? Thanks...

Answer 1

It is likely that your application is holding open Connections rather than acquiring them as necessary and close()ing them (checking them back into the pool) promptly. To verify this, try setting the following two parameters:

1. unreturnedConnectionTimeout
2. debugUnreturnedConnectionStackTraces

For more explanation, see also here.

Set unreturnedConnectionTimeout to a value large enough that ordinary db operations should have an easy time completing. (Unless you are working with large datasets or complex queries, 60 seconds is probably more than enough, but it does depend on your application.) Set debugUnreturnedConnectionStackTraces to true. Then look in your logs to see whether unreturned Connections are expired and stack traces logged after the timeout you have set elapses. Examine the stack trace: it will point you to the code that checked out the Connection that was not promptly returned.

If your application checks out Connections and holds them open, c3p0 cannot test them, expire them, or guarantee their "freshness". Applications should take care to check out Connections on a "just-in-time" basis, and return them promptly.

Also: your maxStatements setting is not likely to be appropriate for a pool that can expand to 53. I'd set maxStatements to zero (no Statement caching) while you work out the kinks of your application. Then, if you want to try to enhance performance with Statement caching, you'll need to use a larger value for maxStatements, or else consider using the easier to understand maxStatementsPerConnection. More discussion here.

Finally: To set these c3p0 parameters via hibernate, you can use properties like hibernate.c3p0. unreturnedConnectionTimeout. See here.