C3p0 getConnection () throws an exception: ResourcePool could not get the resource from its main factory or source

Question

we have the configuration [Hibernate + c3p0 + MySQL] for our project. We encounter the following exception:

com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source. at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1319) ~[c3p0-0.9.1.2.jar:0.9.1.2] at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:557) ~[c3p0-0.9.1.2.jar:0.9.1.2] at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:477) ~[c3p0-0.9.1.2.jar:0.9.1.2] at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:525) ~[c3p0-0.9.1.2.jar:0.9.1.2] Wrapped by: java.sql.SQLException: Connections could not be acquired from the underlying database! at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106) ~[c3p0-0.9.1.2.jar:0.9.1.2] at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:529) ~[c3p0-0.9.1.2.jar:0.9.1.2] at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128) ~[c3p0-0.9.1.2.jar:0.9.1.2] at org.hibernate.service.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:141) ~[hibernate-core-4.0.1.Final.jar:4.0.1.Final] at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:276) ~[hibernate-core-4.0.1.Final.jar:4.0.1.Final] at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:297) ~[hibernate-core-4.0.1.Final.jar:4.0.1.Final] Wrapped by: org.hibernate.exception.GenericJDBCException: Could not open connection at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:52) ~[hibernate-core-4.0.1.Final.jar:4.0.1.Final] at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125) ~[hibernate-core-4.0.1.Final.jar:4.0.1.Final] at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110) ~[hibernate-core-4.0.1.Final.jar:4.0.1.Final] at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:304) ~[hibernate-core-4.0.1.Final.jar:4.0.1.Final] at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.getConnection(LogicalConnectionImpl.java:169) ~[hibernate-core-4.0.1.Final.jar:4.0.1.Final] at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:67) ~[hibernate-core-4.0.1.Final.jar:4.0.1.Final] at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.begin(AbstractTransactionImpl.java:160) ~[hibernate-core-4.0.1.Final.jar:4.0.1.Final] at org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1263) ~[hibernate-core-4.0.1.Final.jar:4.0.1.Final] at org.springframework.orm.hibernate4.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:399) ~[spring-orm-3.1.2.RELEASE.jar:3.1.2.RELEASE] Wrapped by: org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.GenericJDBCException: Could not open connection at org.springframework.orm.hibernate4.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:440) ~[spring-orm-3.1.2.RELEASE.jar:3.1.2.RELEASE] at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:371) ~[spring-tx-3.1.2.RELEASE.jar:3.1.2.RELEASE] at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:336) ~[spring-tx-3.1.2.RELEASE.jar:3.1.2.RELEASE] at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:105) ~[spring-tx-3.1.2.RELEASE.jar:3.1.2.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.1.2.RELEASE.jar:3.1.2.RELEASE] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202) ~[spring-aop-3.1.2.RELEASE.jar:3.1.2.RELEASE] at $Proxy28.messageExists(Unknown Source) ~[na:na] 

Our c3p0 configuration:

 <bean id="provDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close"> … <property name="acquireIncrement" value="1" /> <property name="idleConnectionTestPeriod" value="100"/> <property name="minPoolSize" value="5" /> <property name="maxPoolSize" value="50" /> <property name="maxIdleTime" value="1800" /> </bean> 

Note that we also have another similar c3p0 data source configured on top of this MySQL instance.

Can someone give me some suggestions on how to debug this?

Answer 1

Although I did not answer your question, I did not want to add code in the comments, since the code in the comments is unreadable.

Here is an example MySQL configuration with C3P0 from my project. pay attention to all additional parameters to make sure that the connections do not die. (In the end, I went with Postgres because it didn’t kill my connections and was more stable at all, and also repeat checks using select 1; not very efficient)

 <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close"> <property name="driverClass" value="${jdbc.driverClassName}"/> <property name="jdbcUrl" value="${jdbc.url}"/> <property name="properties"> <props> <prop key="c3p0.minPoolSize">0</prop> <prop key="hc3p0.maxPoolSize">1000</prop> <prop key="hc3p0.timeout">3000</prop> <prop key="c3p0.acquire_increment">10</prop> <prop key="c3p0.max_statement">50</prop> <prop key="hibernate.c3p0.idle_test_period">60</prop> <prop key="c3p0.preferredTestQuery">SELECT 1;</prop> <prop key="hibernate.c3p0.testConnectionOnCheckout">false</prop> <prop key="user">${jdbc.username}</prop> <prop key="password">${jdbc.password}</prop> </props> </property> </bean>