My connection pool size is low and I am not working under high load. I see the following exception:
org.hibernate.exception.GenericJDBCException: Cannot open connection
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:426)
at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:144)
at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:57)
at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1326)
at com.adventnet.management.transaction.TransactionAPI.beginHbnTxn(TransactionAPI.java:923)
at com.adventnet.management.transaction.TransactionAPI.beginTxn(TransactionAPI.java:856)
at com.adventnet.management.transaction.TransactionAPI.begin(TransactionAPI.java:314)
at com.adventnet.nms.store.relational.RelationalObject.beginTxn(RelationalObject.java:2298)
at com.adventnet.nms.store.relational.RelationalObject.init(RelationalObject.java:208)
at com.adventnet.nms.store.relational.RelationalPolicyObject.init(RelationalPolicyObject.java:39)
at com.adventnet.nms.store.relational.CommonPolicyToRelational.initForClass(CommonPolicyToRelational.java:145)
at com.adventnet.nms.store.relational.CommonPolicyToRelational.<init>(CommonPolicyToRelational.java:76)
at com.adventnet.management.policydb.CommonPolicyAPI.setPersistenceAPI(CommonPolicyAPI.java:63)
at com.adventnet.management.policydb.CommonPolicyAPI.<init>(CommonPolicyAPI.java:43)
at com.adventnet.management.policydb.NmsPolicyMgr.initialize(NmsPolicyMgr.java:115)
at com.adventnet.management.policydb.NmsPolicyMgr.callMain(NmsPolicyMgr.java:99)
at com.adventnet.nms.util.RunProcessSmall.runCommand(RunProcessSmall.java:97)
at com.adventnet.nms.util.RunCmd.run(RunCmd.java:100)
Caused by: java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106)
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:65)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:527)
at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128)
at org.hibernate.connection.C3P0ConnectionProvider.getConnection(C3P0ConnectionProvider.java:56)
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:423)
... 18 more
Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@869113 -- timeout at awaitAvailable()
at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1317)
at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:557)
at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:477)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:525)
When I did some debugging from my side, I figured out that the number of non-transaction connections taken from the connection pool are 10 and non transaction connections are 3. At this point this issue occurs and also if I increase the connection pool size to 45, this issue no longer occurs.
What could be the reason? Is there a way to enable some fine level of debugging in c3p0 ( using log4j).
According to my opinion, there should be something wrong in the Connection Information you have provided in the Hibernate configuration file for database URL, user name and password, etc. Please check the configuration for c3p0 once again, if you find something wrong. Please check here for Hibernate Specific Notes