Problem with c3p0

[bigor]

Hi, I am using springframework with hibernate and c3p0 connection pool.
I also use spring transaction api.
The probem is that when application is idle for a long time, (few hours) I get exception from transaction manager that says connection is closed.

Here is the exception:



WARNING: Could not obtain connection metadata
java.sql.SQLException: Connections could not be acquired from the underlying database!
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtil s.java:104)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool. checkoutPooledConnection(C3P0PooledConnectionPool. java:264)
at com.mchange.v2.c3p0.PoolBackedDataSource.getConnec tion(PoolBackedDataSource.java:94)
at org.hibernate.connection.C3P0ConnectionProvider.ge tConnection(C3P0ConnectionProvider.java:35)
at org.hibernate.cfg.SettingsFactory.buildSettings(Se ttingsFactory.java:72)
at org.hibernate.cfg.Configuration.buildSettings(Conf iguration.java:1463)
at org.hibernate.cfg.Configuration.buildSessionFactor y(Configuration.java:1004)
at org.springframework.orm.hibernate3.LocalSessionFac toryBean.newSessionFactory(LocalSessionFactoryBean .java:825)
at org.springframework.orm.hibernate3.LocalSessionFac toryBean.afterPropertiesSet(LocalSessionFactoryBea n.java:751)
at org.springframework.beans.factory.support.Abstract AutowireCapableBeanFactory.invokeInitMethods(Abstr actAutowireCapableBeanFactory.java:1091)
at org.springframework.beans.factory.support.Abstract AutowireCapableBeanFactory.createBean(AbstractAuto wireCapableBeanFactory.java:396)
at org.springframework.beans.factory.support.Abstract BeanFactory.getBean(AbstractBeanFactory.java:233)
at org.springframework.beans.factory.support.Abstract BeanFactory.getBean(AbstractBeanFactory.java:145)
at org.springframework.beans.factory.support.BeanDefi nitionValueResolver.resolveReference(BeanDefinitio nValueResolver.java:186)
at org.apache.catalina.core.StandardContext.loadOnSta rtup(StandardContext.java:4013)
at org.apache.catalina.core.StandardContext.start(Sta ndardContext.java:4357)
at org.apache.catalina.core.ContainerBase.start(Conta inerBase.java:1083)
at org.apache.catalina.core.StandardHost.start(Standa rdHost.java:789)
at org.apache.catalina.core.ContainerBase.start(Conta inerBase.java:1083)
<cut>
Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceE xception: A ResourcePool could not acquire a resource from its primary factory or source.
at com.mchange.v2.resourcepool.BasicResourcePool.awai tAcquire(BasicResourcePool.java:972)
at com.mchange.v2.resourcepool.BasicResourcePool.chec koutResource(BasicResourcePool.java:208)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool. checkoutPooledConnection(C3P0PooledConnectionPool. java:260)
... 64 more


Now, I am not sure whrere is the problem, probably somewhere in my configuration, so here it is:

SessionFactory conf:


<bean id="sessionFactoryBean" class="org.springframework.orm.hibernate3.LocalSessionFac toryBean">

<property name="mappingResources">
<list>

<value>hr/klopa/hbm/MyClass.hbm.xml</value>
<value>hr/klopa/hbm/Vote.hbm.xml</value>
<value>hr/klopa/hbm/Video.hbm.xml</value>

</list>
</property>

<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
<prop key="hibernate.c3p0.min_size">5</prop>
<prop key="hibernate.c3p0.max_size">20</prop>
<prop key="hibernate.c3p0.timeout">1800</prop>
<prop key="hibernate.c3p0.max_statements">50</prop>
<prop key="hibernate.connection.driver_class">com.mysql.jdbc.Driver</prop>
<prop key="hibernate.connection.url">jdbc:mysql://127.0.0.1/dbName?useUnicode=true&amp;characterEncoding=UTF8</prop>
<prop key="hibernate.connection.username">root</prop>
<prop key="hibernate.connection.password"></prop>
<prop key="hibernate.query.substitutions">true 1, false 0</prop>
</props>
</property>

</bean>


Transaction config:


<bean id="txManager" class="org.springframework.orm.hibernate3.HibernateTransa ctionManager">
<property name="sessionFactory" ref="sessionFactoryBean"/>
</bean>

<bean class="org.springframework.aop.framework.autoproxy.Defaul tAdvisorAutoProxyCreator" />

<bean class="org.springframework.transaction.interceptor.Transa ctionAttributeSourceAdvisor">
<property name="transactionInterceptor" ref="txInterceptor"/>
</bean>

<bean id="txInterceptor" class="org.springframework.transaction.interceptor.Transa ctionInterceptor">
<property name="transactionManager" ref="txManager"/>
<property name="transactionAttributeSource">
<bean class="org.springframework.transaction.annotation.Annotat ionTransactionAttributeSource"/>
</property>
</bean>

DAO bean config:


<bean id="myBean" class="MyDAOlmpl">
<property name="sessionFactory">
<ref bean="sessionFactoryBean" />
</property>
</bean>

and this is DAO method implementation:


public MyPojo getMyPojo(long id)
{
Session session = sessionFactory.getCurrentSession();
MyPojo pojo = (MyPojo) session.createQuery("from MyPojo as o where o.id="+id).uniqueResult();
return pojo;
}

in another app with same configuration it doesn't happen (everythin works OK all the time), and only difference is that this another app doesn't use sessionFactory.getCurrentSession() for obtaining Session, but SessionFactoryUtils.getSession(sessionFactory,true );
and SessionFactoryUtils.releaseSession(session,session Factory); at the end of method.

So, anybody has idea what is the cause of this probelm

[karldmoore]

Any help?
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2032

[Costin Leau]

I think your problem here is not c3p0 but rather mysql. I was bitten by this problem some time ago also. By default, mysql closes connections if they've been idle for a certain time (8h if I recall correctly). When you start using the connections again, the connection pool has 'dead' connections and that's why the exception that you have.
You can configure c3p0 to execute some statements on the idle connections and to retry to recreate them - this way preventing any 'dead' connections - however this can be potentially expensive since the work will be done even if your application hasn't been idle.
The second alternative is to configure mysql to not close the idle connections anymore.
I think there is even a third approach where you could configure c3p0 to eliminate connections from the pool using a timeout similar to the one set for mysql. This way, c3p0 will eliminate the connection before they are killed by the db.

[bigor]

Any help?
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2032

No, this didn't help. I replaced old version jar 0.9.0.4 with 0.9.1 and the problem remains. However, thank you for information about connection leak.



I think your problem here is not c3p0 but rather mysql. I was bitten by this problem some time ago also. By default, mysql closes connections if they've been idle for a certain time (8h if I recall correctly). When you start using the connections again, the connection pool has 'dead' connections and that's why the exception that you have.
You can configure c3p0 to execute some statements on the idle connections and to retry to recreate them - this way preventing any 'dead' connections - however this can be potentially expensive since the work will be done even if your application hasn't been idle.


I will try that, thanx.

[bigor]

neither of theese sugestions worked :-(

I asume that problem is that transaction doesn't release session automatically at the end of transaction, but I don't know why

[karldmoore]

Hmmm, I don't seem to be able to view the c3p0 docs at the minute (the site seems to keep going down). There are a bunch of settings regarding when to check timed out connections, they might be worth looking at. The other issue here would be that there is a connection leak somewhere in your application.
http://www.hibernate.org/214.html

[winterspring]

Just ran into a similar issue and adding the following properties inside my datasource solved the issue.


<bean id="myDataSource"class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="idleConnectionTestPeriod" value="10800"/>
<property name="maxIdleTime" value="21600"/>
</bean>


Test period needs to be less than wait_timeout period in db (in my case mysql is set to 8 hours).

Hope this helps someone :)

[motevalian]

Hi all,

Just to share my experience. I used Hibernate and MySql in one of my projects, ofcourse with C3P0 as connection pool. Whenever I tried to run my project I got exception like above. After having some trials I found that the problem was due to my C3P0 configs. Actually at setup time of MySql, I let max no. of connections as its default: 15.
In addition, I set below property in hibernate config:
<property name="c3p0.max_size">15</property>.

Having both of above configs the same value, I got the exception. So, my first solution was to decrease max_size from 15 to some value less (10 for example) and fortunately it solved my problem.

Regards,
Alireza

[Sounie]

Hi Alireza,

Did you determine why you needed to reduce your maximum pool size?

Was something else using connections on the same database?

I sleep better when I know why settings need to be changed :)

--
Stephen

[motevalian]

Dear Stephen,

I didn't hav much time to survey the reason completely. But, actually my guest was that, I had a DB utility (Query Browser) open, in addition to my Web Server. So the total no. of open connections must have exceeded the max. of MySql.
I would be happy to be informed whether someone has an exact explanation about the reason.
Actually, the major problem is that, at the beginning, hibernate should not captured connections up to max. defined for it. But my program threw exception whenever I tried to create sessionFactory for the first time.

Regards,
Alireza