Announcement Announcement Module
Collapse
No announcement yet.
Problem with c3p0 Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Problem with c3p0

    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:

    Code:
    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(SqlUtils.java:104)
            at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:264)
            at com.mchange.v2.c3p0.PoolBackedDataSource.getConnection(PoolBackedDataSource.java:94)
            at org.hibernate.connection.C3P0ConnectionProvider.getConnection(C3P0ConnectionProvider.java:35)
            at org.hibernate.cfg.SettingsFactory.buildSettings(SettingsFactory.java:72)
            at org.hibernate.cfg.Configuration.buildSettings(Configuration.java:1463)
            at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1004)
            at org.springframework.orm.hibernate3.LocalSessionFactoryBean.newSessionFactory(LocalSessionFactoryBean.java:825)
            at org.springframework.orm.hibernate3.LocalSessionFactoryBean.afterPropertiesSet(LocalSessionFactoryBean.java:751)
            at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1091)
            at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:396)
            at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:233)
            at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:145)
            at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:186)
            at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:4013)
            at org.apache.catalina.core.StandardContext.start(StandardContext.java:4357)
            at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1083)
            at org.apache.catalina.core.StandardHost.start(StandardHost.java:789)
            at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1083)
            <cut>
    Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source.
            at com.mchange.v2.resourcepool.BasicResourcePool.awaitAcquire(BasicResourcePool.java:972)
            at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(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:

    Code:
    <bean id="sessionFactoryBean" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    	
    		<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:
    Code:
    <bean id="txManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
      		<property name="sessionFactory" ref="sessionFactoryBean"/>
    	</bean>
    	
    	<bean class="org.springframework.aop.framework.autoproxy.DefaultAdvisorAutoProxyCreator" />
    
    	<bean class="org.springframework.transaction.interceptor.TransactionAttributeSourceAdvisor">
      		<property name="transactionInterceptor" ref="txInterceptor"/>
    	</bean>
    
    	<bean id="txInterceptor" class="org.springframework.transaction.interceptor.TransactionInterceptor">
      		<property name="transactionManager" ref="txManager"/>
      		<property name="transactionAttributeSource">
        			<bean class="org.springframework.transaction.annotation.AnnotationTransactionAttributeSource"/>
      		</property>
    	</bean>
    DAO bean config:

    Code:
    <bean id="myBean" class="MyDAOlmpl">
    		<property name="sessionFactory">
    			<ref bean="sessionFactoryBean" />
    		</property>	
    </bean>
    and this is DAO method implementation:

    Code:
    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

  • #2
    Any help?
    http://opensource.atlassian.com/proj...rowse/HHH-2032

    Comment


    • #3
      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.

      Comment


      • #4
        Originally posted by karldmoore View Post
        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.


        Originally posted by Costin Leau View Post
        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.

        Comment


        • #5
          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

          Comment


          • #6
            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

            Comment


            • #7
              re problem with c3p0

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

              Code:
              <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

              Comment


              • #8
                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
                Last edited by motevalian; May 20th, 2009, 04:38 AM.

                Comment


                • #9
                  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

                  Comment


                  • #10
                    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

                    Comment

                    Working...
                    X