How to stop c3p0 connection pool from hiding the cause of connection exceptions?

Question

I have an application that uses c3p0 for connection pooling. When there is any kind of problem connecting to the database, I get an exception like this:

java.sql.SQLException: An SQLException was provoked by the following failure: 
  com.mchange.v2.resourcepool.ResourcePoolException: A ResourcePool cannot acquire 
  a new resource -- the factory or source appears to be down.
    at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106)
    at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:65)
    at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:62)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:531)
    at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128)
    at com.pontiflex.monitor.dao.ConnectionManager.getConfigReadOnlyDbConnection(Unknown Source)
    at com.pontiflex.monitor.dao.MonitorServiceDAO.getLiveIOCountForOrganization(Unknown Source)        at com.pontiflex.monitor.BillingMonitor.getComparisonValueForActivation(Unknown Source)
    at com.pontiflex.monitor.AbstractMonitor.isMonitoringActive(Unknown Source)
    at com.pontiflex.monitor.AbstractMonitor.generateAlerts(Unknown Source)
    at com.pontiflex.monitor.AbstractMonitor.doMonitoring(Unknown Source)
    at com.pontiflex.monitor.worker.MonitorWorker.run(Unknown Source)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:439)
    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
    at java.util.concurrent.FutureTask.run(FutureTask.java:138)        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918)
    at java.lang.Thread.run(Thread.java:662)
Caused by: com.mchange.v2.resourcepool.ResourcePoolException: A ResourcePool cannot 
 acquire a new resource -- the factory or source appears to be down.
    at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1279)
    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)
    ... 14 more

This same exception will occur regardless of what the problem is whether it be network connectivity issues, server issues (too many connections), or configuration problems. If I turn on debug logging for the mysql jdbc driver and c3p0, I get more information:

WARN  com.mchange.v2.resourcepool.BasicResourcePool  
 com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@4e074784 -- 
 Acquisition Attempt Failed!!! Clearing pending acquires. While trying 
 to acquire a needed new resource, we failed to succeed more than the 
 maximum number of allowed acquisition attempts (30). Last acquisition 
 attempt exception: 
 com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: Data 
 source rejected establishment of connection,  message from server: 
 "Too many connections"
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:921)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1070)
    at com.mysql.jdbc.Connection.createNewIO(Connection.java:2775)
    at com.mysql.jdbc.Connection.<init>(Connection.java:1555)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:135)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
    at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
    at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)

Is there anything I can do to get c3p0 to give me the underlying cause when it throws the ResourcePoolException without adjusting the logger configuration?

Answer 1

SQLException is the (checked) Exception that JDBC apis are permitted to throw, so c3p0 must "wrap" underlying Exceptions that ate not SQLExceptions in the usual way. Calling getCause() on these Exceptions will deliver the original Throwable if you want to check for something specific.

In the particular example you cite, something different is going on. The issue is that the Thread which is logging the Exception is not your client Thread. c3p0-internal Threads acquire Connections from the database. Your client Threads acquire Connections from the pool. The two activities are as uncoupled as c3p0 can manage -- that is the whole point of having a Connection pool. First, internal Threads perform a "round" of acquisition attempts (by default 30 attempts with a 1 sec delay between, but you can reconfigure if you want).

If ALL of the attempts to acquire a Connection fail, the c3p0 internal threads...

1) Log the failure, and the last Exception seen while trying. This is the second Exception you quote. Note that this is logged at WARNING, not a DEBUG level. If you have c3p0 logging at INFO -- which you should -- you'll see these messages. [If you want to see each individual Exception, all 30 of the Exceptions that must occur (under defailts) before declaring a failure, you need to turn logging to DEBUG (or FINE or ALL).]

2) After logging that an acquisition series has failed, c3p0 will interrupt wait()ing clients and signal a ResourcePoolException, which becomes gets in an SQLException. This is the first Exception you quote. A ResourcePoolException is signalled.

3) if breakAfterAcquireFailure is set to false (the default), the pool will recover from an acquisition failure. It will try again to acquire new Connections as new clients come in.. If breakAfterAcquireFailure is set to true, however, the pool will always respond to new client requests with an Exception.

Anyway, I hope this isn't entirely unhelpful. c3p0 could define a custom Exception type that would become the cause of the wrapped Exception delivered to clients, so clients could test for an acquisition failure in code, but for now at least it does not. (Sorry!)