How to configure C3P0 or BoneCP Datasources to withstand database failover/outage

Question

I have a Spring/Hibernate based application which I need to modify to withstand a database outage up to 1 minute. Instead of writing my own hack I took a look at C3P0 and BoneCP datasources to see if they could be configured for this purpose. Unfortunently I was not able to get this to work for either of the datasources. My test program terminated with various exceptions depending on which datasource was used:

With the c3p0 datasource

9558 [main] ERROR org.hibernate.util.JDBCExceptionReporter - connection exception: connection failure: java.net.SocketException: Broken pipe
Exception in thread "main" org.springframework.dao.DataAccessResourceFailureException: could not inspect JDBC autocommit mode; nested exception is      org.hibernate.exception.JDBCConnectionException: could not inspect JDBC autocommit mode

With the c3p0 connectionprovider

9341 [main] ERROR org.hibernate.util.JDBCExceptionReporter - connection exception: connection failure: java.io.EOFException
Exception in thread "main" org.springframework.dao.DataAccessResourceFailureException: Cannot open connection; nested exception is org.hibernate.exception.JDBCConnectionException: Cannot open connection
    at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:627)

With the BoneCP datasource

12250 [main] ERROR org.hibernate.util.JDBCExceptionReporter - connection exception: connection failure: java.net.SocketException: Broken pipe
Exception in thread "main" org.springframework.dao.DataAccessResourceFailureException: could not inspect JDBC autocommit mode; nested exception is org.hibernate.exception.JDBCConnectionException: could not inspect JDBC autocommit mode
    at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:627)

With the BoneCP connectionprovider

19356 [main] ERROR org.hibernate.util.JDBCExceptionReporter - connection exception: connection failure: java.io.EOFException
Exception in thread "main" org.springframework.dao.DataAccessResourceFailureException: Cannot open connection; nested exception is org.hibernate.exception.JDBCConnectionException: Cannot open connection
    at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:627)
    at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
    at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:424)
    at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)
    at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:921)
    at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:913)
    at my.db.failover.CustomerDao.list(CustomerDao.java:14)

The C3P0 documentation states that it can handle a situation like this while the BoneCP documentation don't mention this directly.

To test this I wrote a little program which reads a table from a HSQLDB server and sleeps for a second before repeating the process again. It can be configured to run with following configurations: c3p0_datasource.xml

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass" value="org.hsqldb.jdbcDriver" />
    <property name="jdbcUrl" value="jdbc:hsqldb:hsql://localhost:9002" />
    <property name="user" value="sa" />
    <property name="password" value="" />
    <property name="acquireIncrement" value="2" />
    <property name="minPoolSize" value="3" />
    <property name="maxPoolSize" value="25" />
    <property name="idleConnectionTestPeriod" value="3000" />
    <property name="acquireRetryAttempts" value="30" />
    <property name="acquireRetryDelay" value="1001" />
    <property name="breakAfterAcquireFailure" value="false" />
    <property name="maxIdleTime" value="0" />
    <property name="maxConnectionAge" value="0" />
    <property name="maxIdleTimeExcessConnections" value="0" />
    <property name="automaticTestTable" value="C3P0_TEST" />
</bean>
<bean id="sessionFactory" parent="abstractSessionFactory">
        <property name="dataSource" ref="dataSource"/>
</bean>

c3p0_connectionprovider.xml

<bean id="sessionFactory" parent="abstractSessionFactory">
        <property name="hibernateProperties">
            <props>
                . . .
                <prop key="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</prop>
                <prop key="hibernate.c3p0.acquire_increment">2</prop>
                <prop key="hibernate.c3p0.idle_test_period">300</prop>
                <prop key="hibernate.c3p0.timeout">1800</prop>
                <prop key="hibernate.c3p0.max_size">25</prop>
                <prop key="hibernate.c3p0.min_size">1</prop>
                <prop key="hibernate.c3p0.max_statement">0</prop>
                <prop key="hibernate.c3p0.preferredTestQuery">select 1;</prop>
                <prop key="hibernate.c3p0.validate">true</prop>     
            </props>
        </property>
</bean>

bonecp_datasource.xml

<bean id="dataSource" class="com.jolbox.bonecp.BoneCPDataSource" destroy-method="close">
   <property name="driverClass" value="org.hsqldb.jdbcDriver" />
   <property name="jdbcUrl" value="jdbc:hsqldb:hsql://localhost:9002" />
   <property name="username" value="sa"/>
   <property name="password" value=""/>
   <property name="idleConnectionTestPeriod" value="60"/>
   <property name="idleMaxAge" value="240"/>
   <property name="maxConnectionsPerPartition" value="30"/>
   <property name="minConnectionsPerPartition" value="10"/>
   <property name="partitionCount" value="3"/>
   <property name="acquireIncrement" value="5"/>
   <property name="statementsCacheSize" value="100"/>
   <property name="releaseHelperThreads" value="3"/>
</bean>
<bean id="sessionFactory" parent="abstractSessionFactory">
    <property name="dataSource" ref="dataSource"/>
</bean>

bonecp_connectionprovider.xml

<bean id="sessionFactory" parent="abstractSessionFactory">
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.connection.provider_class">com.jolbox.bonecp.provider.BoneCPConnectionProvider</prop>
                . . .
                <prop key="bonecp.idleMaxAgeInMinutes">2</prop>
                <prop key="bonecp.idleConnectionTestPeriodInMinutes">3</prop>
                <prop key="bonecp.partitionCount">3</prop>
                <prop key="bonecp.acquireIncrement">10</prop>
                <prop key="bonecp.maxConnectionsPerPartition">60</prop>
                <prop key="bonecp.minConnectionsPerPartition">20</prop>
                <prop key="bonecp.statementsCacheSize">50</prop>
                <prop key="bonecp.releaseHelperThreads">3</prop>
            </props>
        </property>
</bean>

Does anyone know if this could be done?

PS! You can download the test project from this link if anyone needs to look deeper into this :)

Here are the steps to build and run the stuff.

1. Build the project with Maven:  
  mvn clean install package appassembler:assemble 
2. Set the start scripts as executable (Unix/linux)
  chmod +x target/appassembler/bin/*  
3. Run the DBServer 
  target/appassembler/bin/dbServer 
4. Run the test client from another shell 
  target/appassembler/bin/client
5. Select one of following configurations to use for the client 
    0 : c3p0_datasource.xml
    1 : c3p0_connectionprovider.xml
    2 : bonecp_datasource.xml
    3 : bonecp_connectionprovider.xml
6. Terminate the dbServer with ctrl c
7. Start it again and the client should survive the DB outage

But it does not :(

score 4 · Answer 1 · answered Oct 01 '13 at 21:15

c3p0 will recover from a database outage of arbitrary duration, as long as breakOnAcquireFailure is not set to true. however, that does not mean that clients will never see an Exception on acquire failure. by default, c3p0 will throw Exceptions to clients after a full round of acquisition attempts has failed, which will take 30030 ms (~30 secs) under your config. if you want c3p0 to continue trying to acquire Connections for longer before throwing Exceptions to clients, either set acquireRetryAttempts higher or acquireRetryDelay longer. the total length of a round of acquisition attempts is acquireRetryAttempts * acquireRetryDelay

if you want clients to wait indefinitely for recovery upon a database outage, set acquireRetryAttempts to 0. with this setting, when c3p0 cannot acquire a Connection, it will keep trying indefinitely every acquireRetryDelay milliseconds, and let clients hang until it succeeds or the sun burns out.

Please see http://www.mchange.com/projects/c3p0/#configuring_recovery

p.s. the Exceptions you are providing are very superficial. you'll need to look into log files to see and provide better information about what's going on.

How to configure C3P0 or BoneCP Datasources to withstand database failover/outage

1 Answers1