c3P0 checkConnectionsOnCheckOut - How it works?

[PardalDev]

Dear all,

We are using an implementation of c3p0 pool based db connection, and we noticed that after some long period of time, those connections get broken, because a firewall configuration issue or maybe a DB idle connection parameter. Any way, we are trying to figure how checkConnectionsOnCheckOut actually works.

Our connection pool was defined like this:

    <default-config>
            <!-- Pool connection acquire -->
            <property name="checkoutTimeout">10000</property>
            <property name="idleConnectionTestPeriod">60</property>
            <property name="PreferredTestQuery">SELECT 1 FROM DUAL</property>
            <property name="acquireRetryAttempts">5</property>
            <property name="acquireRetryDelay">60000</property>
            <property name="breakAfterAcquireFailure">false</property>
            <property name="debugUnreturnedConnectionStackTraces">true</property>
            <property name="unreturnedConnectionTimeout">0</property>
            <!-- Pool connection release in seconds -->
            <property name="maxConnectionAge">3600</property>
            <property name="testConnectionOnCheckout">true</property>

            <!-- C3P0 working threads -->
            <property name="numHelperThreads">15</property>
    </default-config>
	
	<named-config name="mts_historic">
            <property name="minPoolSize">50</property>
            <property name="initialPoolSize">50</property>
            <property name="maxPoolSize">50</property>
            <property name="acquireIncrement">50</property>
            <property name="connectionCustomizerClassName">us.inswitch.jextdb.database.MTSConnectionCustomizer</property>
            <property name="jdbcUrl">jdbc:oracle:thin:@(DESCRIPTION_LIST= (DESCRIPTION= (ADDRESS = (PROTOCOL = TCP)(HOST =XXXXXXXX)(PORT =1521)) (CONNECT_DATA= (SERVICE_NAME= XXXXXXX))))</property>
            <property name="driverClass">oracle.jdbc.driver.OracleDriver</property>
            <property name="user">XXXXXX</property>
            <property name="password">UQsdKzKfo9wwy0/HnGR50g==</property>
	</named-config>

So, by using this configuration, we have noticed that the connection testing always succeed, no one of the connections are marked as failed or boken. But, after a long period of time (for example during morning) of inactivity when we try to use a connection, it is being checked by the parameter testConnectionOnCheckout and it fails but the query also fails.

1. The testConnectionOnCheckout is an internal connection testing from DB pool? It and should be transparent for the clients?

2. It triggers a new connection? The desision of a new connection depends on something else?

3. If the c3p0 connection is restablished in an automatic way after the testConnectionOnCheckout failure, the issue that we are currently facing could be related to the aquire increment that we are using? (we are using an static pool max = min = acuireIncrement)

Thanks you in advance.

Kind regards.
Luis

[fschiettecatte]

Luis I notice that ‘PreferredTestQuery’ has an initial capital, should that be ‘preferredTestQuery’? And do you know if 'SELECT 1 FROM DUAL’ is making it to the server ? François

…

On Jul 21, 2020, at 6:01 PM, Pardal Developers ***@***.***> wrote: Dear all, We are using an implementation of c3p0 pool based db connection, and we noticed that after some long period of time, those connections get broken, because a firewall configuration issue or maybe a DB idle connection parameter. Any way, we are trying to figure how checkConnectionsOnCheckOut actually works. Our connection pool was defined like this: <default-config> <!-- Pool connection acquire --> <property name="checkoutTimeout">10000</property> <property name="idleConnectionTestPeriod">60</property> <property name="PreferredTestQuery">SELECT 1 FROM DUAL</property> <property name="acquireRetryAttempts">5</property> <property name="acquireRetryDelay">60000</property> <property name="breakAfterAcquireFailure">false</property> <property name="debugUnreturnedConnectionStackTraces">true</property> <property name="unreturnedConnectionTimeout">0</property> <!-- Pool connection release in seconds --> <property name="maxConnectionAge">3600</property> <property name="testConnectionOnCheckout">true</property> <!-- C3P0 working threads --> <property name="numHelperThreads">15</property> </default-config> <named-config name="mts_historic"> <property name="minPoolSize">50</property> <property name="initialPoolSize">50</property> <property name="maxPoolSize">50</property> <property name="acquireIncrement">50</property> <property name="connectionCustomizerClassName">us.inswitch.jextdb.database.MTSConnectionCustomizer</property> <property name="jdbcUrl">jdbc:oracle:thin:@(DESCRIPTION_LIST= (DESCRIPTION= (ADDRESS = (PROTOCOL = TCP)(HOST =XXXXXXXX)(PORT =1521)) (CONNECT_DATA= (SERVICE_NAME= XXXXXXX))))</property> <property name="driverClass">oracle.jdbc.driver.OracleDriver</property> <property name="user">XXXXXX</property> <property name="password">UQsdKzKfo9wwy0/HnGR50g==</property> </named-config> So, by using this configuration, we have noticed that the connection testing always succeed, no one of the connections are marked as failed or boken. But, after a long period of time (for example during morning) of inactivity when we try to use a connection, it is being checked by the parameter testConnectionOnCheckout and it fails but the query also fails. • The testConnectionOnCheckout is an internal connection testing from DB pool? It and should be transparent for the clients? • It triggers a new connection? The desision of a new connection depends on something else? • If the c3p0 connection is restablished in an automatic way after the testConnectionOnCheckout failure, the issue that we are currently facing could be related to the aquire increment that we are using? (we are using an static pool max = min = acuireIncrement) Thanks you in advance. Kind regards. Luis — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or unsubscribe.