Search code examples
javaspringtomcatc3p0

c3p0 reconnect after network outage

The application connects to MS SQL server. It uses the c3p0 ComboPooledDataSource in Tomcat and Spring environment.

When the application loses database connection and gets it back few seconds later, the application recovers the connection and can continue querying the db quickly (as soon as the network is back). But it the network outage is longer, the application needs more than 10 minutes to recover a db connection after network came back.

I see these logs when the db connection is back after 10 minutes:

[WARNING] Exception on close of inner statement.java.sql.SQLException: Invalid state, the Connection object is closed.
at net.sourceforge.jtds.jdbc.TdsCore.checkOpen(TdsCore.java:481)     
[WARNING] [c3p0] A PooledConnection that has already signalled a Connection error is still in use! 
[WARNING] [c3p0] Another error has occurred [ java.sql.SQLException: Invalid state, the Connection object is closed. ] which will not be reported to listeners!java.sql.SQLException: Invalid state, the Connection object is closed.

Here is the spring-config.xml configuration:

<bean id="CommonDataSource" abstract="true" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass" value="net.sourceforge.jtds.jdbc.Driver" />
    <property name="minPoolSize" value="${db.minPoolSize}" />
    <property name="maxPoolSize" value="${db.maxPoolSize}" />
    <property name="acquireRetryAttempts" value="0" />
    <property name="checkoutTimeout" value="0" />
    <property name="testConnectionOnCheckout" value="true" />
    <property name="testConnectionOnCheckin" value="false" />
    <property name="idleConnectionTestPeriod" value="10" />
    <property name="preferredTestQuery" value="select 1" />
</bean>

I tried other configurations, with a non-zero checkoutTimeout, testConnectionOnCheckout=false and testConnectionOnCheckin=true, the recovery still is very long.

What is wrong with my configuration? I would like to recover the db connection as soon as network issues are fixed.

Many thanks for you help

EDIT with Hakari configuration as suggested by M. Deinum

Hi,

I tried with this Hakari configuration:

<bean id="CommonDataSource" abstract="true" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
    <property name="maximumPoolSize" value="${db.maxPoolSize}" />
    <property name="connectionTestQuery" value="select 1"/>
    <property name="allowPoolSuspension" value="true"/>
</bean>

But the behaviour is similar: I have to wait for 10-15 minutes before getting the database connection back.

Would you have any suggestion please?

Solution

  • The issue was not related to c3p0 nor HikariCP. I had to modify the jdbc url and add these properties:

    loginTimeout=60;socketTimeout=60

    Maybe only one is enough but I could do the job with both of these. This link helps a lot http://jtds.sourceforge.net/faq.html