Hibernate version:3.0

Code between sessionFactory.openSession() and session.close(): session-per-request pattern as described in CaveatEmptor

Name and version of the database you are using:MySQL version 4.1.12

Hi, I am having a problem with a web application that uses Hibernate and c3p0. I am using the session-per-request pattern as described in CaveatEmptor.

It works fine most of the time but from time to time throw a "You can't operate on a closed connection!!!". And without any intervention, the next database query would work fine as if nothing wrong had happened before.

I've been using DBCP pool previously without such problem. But I'd rather prefer c3p0 no to depend on tomcat.

I am getting the following error:

Hibernate: select this_.employee_id_employee as employee1_0_, this_.permission_id_permission as permission2_0_, this_.name as name0_ from role this_ where this_.employee_id_employee=? and this_.permission_id_permission=?
39628 [http-8080-Processor24] WARN util.JDBCExceptionReporter - SQL Error: 0, SQLState: null
39628 [http-8080-Processor24] ERROR util.JDBCExceptionReporter - You can't operate on a closed connection!!!
39641 [http-8080-Processor24] ERROR web.Dispatcher -
org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:92)
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:80)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1518)
at org.hibernate.loader.Loader.list(Loader.java:1498)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:111)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1253)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:299)
at twm.db.access.Role.isPermitted(Role.java:32)
at twm.web.UserBean.isPermitted(UserBean.java:83)
at twm.web.Dispatcher.processRequest(Dispatcher.java:103)
at twm.web.Dispatcher.doGet(Dispatcher.java:59)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
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 twm.util.HibernateFilter.doFilter(HibernateFilter.java:44)
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.valves.AccessLogValve.invoke(AccessLogValve.java:526)
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:856)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
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(Thread.java:595)
Caused by: java.sql.SQLException: You can't operate on a closed connection!!!
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:68)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.close(NewProxyPreparedStatement.java:1682)
at org.hibernate.jdbc.AbstractBatcher.closePreparedStatement(AbstractBatcher.java:393)
at org.hibernate.jdbc.AbstractBatcher.closeStatement(AbstractBatcher.java:195)
at org.hibernate.jdbc.AbstractBatcher.closeQueryStatement(AbstractBatcher.java:212)
at org.hibernate.jdbc.AbstractBatcher.closeQueryStatement(AbstractBatcher.java:141)
at org.hibernate.loader.Loader.doQuery(Loader.java:408)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:206)
at org.hibernate.loader.Loader.doList(Loader.java:1515)
... 28 more
Caused by: java.lang.NullPointerException
at com.mysql.jdbc.Statement.realClose(Statement.java:1601)
at com.mysql.jdbc.PreparedStatement.realClose(PreparedStatement.java:2082)
at com.mysql.jdbc.ServerPreparedStatement.realClose(ServerPreparedStatement.java:1115)
at com.mysql.jdbc.ServerPreparedStatement.close(ServerPreparedStatement.java:712)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.close(NewProxyPreparedStatement.java:1674)
... 35 more
39789 [http-8080-Processor24] INFO web.Dispatcher - GET /administrate********
/*
* work again without any intervention
*/
112680 [http-8080-Processor25] INFO web.Dispatcher - GET /administrate********
112681 [http-8080-Processor25] INFO util.HibernateUtil - Get a new Hibernate Session 23104867 for this thread
Hibernate: select this_.employee_id_employee as employee1_0_, this_.permission_id_permission as permission2_0_, this_.name as name0_ from role this_ where this_.employee_id_employee=? and this_.permission_id_permission=?
Hibernate: select this_.id_company as id1_0_, this_.name as name0_0_, this_.adress as adress0_0_, this_.zipcode as zipcode0_0_, this_.city as city0_0_, this_.county as county0_0_, this_.country as country0_0_, this_.description as descript8_0_0_, this_.activity as activity0_0_ from company this_ order by this_.name asc
113036 [http-8080-Processor25] INFO web.Dispatcher - GET /administrate********
113036 [http-8080-Processor25] INFO util.HibernateUtil - Closing Hibernate Session 23104867 of this thread.


Here are my c3p0 configuration parameters in hibernate configuration (no c3p0.properties)

<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="connection.url">jdbc:mysql://servername:3306/dbname?autoReconnect=true</property>
<property name="c3p0.acquire_increment">3</property>
<property name="c3p0.idle_test_period">1800</property> (set to 100 reduce the frequency of the problem)
<property name="c3p0.min_size">5</property>
<property name="c3p0.max_size">20</property>
<property name="c3p0.timeout">100</property>
<property name="c3p0.max_statements">50</property>

Full c3p0 parameters at initialisation

Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@1b117fc [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@f02243 [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, idleConnectionTestPeriod -> 1800, initialPoolSize -> 5, maxIdleTime -> 100, maxPoolSize -> 20, maxStatements -> 50, maxStatementsPerConnection -> 0, minPoolSize -> 5, nestedDataSource -> com.mchange.v2.c3p0.DriverManagerDataSource@82daec [ description -> null, driverClass -> null, factoryClassLocation -> null, jdbcUrl -> jdbc:mysql://servername:3306/dbname?autoReconnect=true, properties -> {user=******, password=******} ] , preferredTestQuery -> null, propertyCycle -> 300, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, usesTraditionalReflectiveProxies -> false ] , factoryClassLocation -> null, numHelperThreads -> 3, poolOwnerIdentityToken -> 1b117fc ]

I wonder if it's a c3p0 configuration problem or something else.

Any help is welcome
Thanks
Matthieu