Trouble with c3p0 jdbc pool, BasicResourcePool - A resource could not be refurbished for checkout

jove · August 25, 2024, 9:29pm

(I guess there is no way to disable c3p0 jdbc connection pooling for a metabase db driver)

Hi there, I am developing/updating a custom driver: GitHub - timeplus-io/metabase-proton-driver: Proton database driver for the Metabase business intelligence front-end.

The Release v0.50.4 · timeplus-io/metabase-proton-driver · GitHub works with metabase 0.50.x but doesn't work with our latest database. After I upgraded the database jar file, I got overflow of BasicResourcePool.checkoutResource

	at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:594) ~[metabase.jar:?]
	at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:594) ~[metabase.jar:?]
	at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:594) ~[metabase.jar:?]
	at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:594) ~[metabase.jar:?]

However, if I define a database connection, restart the metabase server, from the server log, I can tell the JDBC connection was setup

2024-08-25 13:52:09,022 [streaming-response-thread-pool-1] DEBUG com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool - Testing PooledConnection [com.mchange.v2.c3p0.impl.NewPooledConnection@3b801def] on CHECKOUT.
2024-08-25 13:52:09,022 [streaming-response-thread-pool-1] DEBUG com.timeplus.proton.client.AbstractClient - Connecting to: ProtonNode(addr=http:localhost/<unresolved>:8123, db=default)@2119068065
2024-08-25 13:52:09,022 [streaming-response-thread-pool-1] DEBUG com.timeplus.proton.client.AbstractClient - Connection established: com.timeplus.proton.client.http.HttpUrlConnectionImpl@7778b6c7
2024-08-25 13:52:09,022 [streaming-response-thread-pool-1] DEBUG com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool - Test of PooledConnection [com.mchange.v2.c3p0.impl.NewPooledConnection@3b801def] on CHECKOUT has FAILED.
java.sql.SQLException: Connection is invalid
	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.testPooledConnection(C3P0PooledConnectionPool.java:573) ~[metabase.jar:?]
	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.finerLoggingTestPooledConnection(C3P0PooledConnectionPool.java:452) [metabase.jar:?]
	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.finerLoggingTestPooledConnection(C3P0PooledConnectionPool.java:444) [metabase.jar:?]
	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.refurbishResourceOnCheckout(C3P0PooledConnectionPool.java:337) [metabase.jar:?]
	at com.mchange.v2.resourcepool.BasicResourcePool.attemptRefurbishResourceOnCheckout(BasicResourcePool.java:1771) [metabase.jar:?]
	at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:558) [metabase.jar:?]
	at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:594) [metabase.jar:?]
	at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:594) [metabase.jar:?]
	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:758) [metabase.jar:?]
	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:685) [metabase.jar:?]

github.com

swaldman/c3p0/blob/5640dcc2e3e216953325c9ed7059626e735e5032/src/java/com/mchange/v2/c3p0/impl/C3P0PooledConnectionPool.java#L758


      
          
               
              private Object checkoutAndMarkConnectionInUse() throws TimeoutException, CannotAcquireResourceException, ResourcePoolException, InterruptedException
              {
                  Object out = null; 
          	boolean success = false;
          	while (! success)
          	    {
          		try
          		    {
          			out = rp.checkoutResource( checkoutTimeout );
          			if (out instanceof AbstractC3P0PooledConnection)
          			    {
          				// cast should succeed, because effectiveStatementCache implies c3p0 pooled Connections
          				AbstractC3P0PooledConnection acpc = (AbstractC3P0PooledConnection) out;
          				Connection physicalConnection = acpc.getPhysicalConnection();
          				success = tryMarkPhysicalConnectionInUse(physicalConnection);
          			    }
          			else
          			    success = true; //we don't pool statements from non-c3p0 PooledConnections
          		    }

                case ConnectionTester.CONNECTION_IS_INVALID:
                    Exception throwMe;
                    if (rootCause == null)
                        throwMe = new SQLException("Connection is invalid");
                    else
                        throwMe = SqlUtils.toSQLException("Connection is invalid", rootCause);
                    throw throwMe;

I cannot figure out how c3p0 validate the JDBC connection. I guess it uses com.mchange.v2.c3p0.impl.DefaultConnectionTester preferredTestQuery is null.

Can I set testConnectionOnCheckout to false or disable db pooling, if I want a working solution first?