hibernate c3p0 broken pipe

Question

Hi,

I'm using hibernate 3 with c3p0 for a program which constantly extracts data from some source and writes it to a database. Now the problem is, that the database might become unavailable for some reasons (in the simplest case: i simply shut it down).

If anything is about to be written to the database there should not be any exception - the query should wait for all eternity until the database becomes available again. If I'm not mistaken this is one of the things the connection pool could do for me: if there is a problem with the db, just retry to connect - in the worst case for infinity.

But instead i get a broken pipe exception, sometimes followed by connection refused and then the exception is passed to my own code, which shouldn't happen.

Even if I catch the exception, how could i cleanly reinitialize hibernate again? (So far without c3p0 i simply built the session factory again, but i wouldn't be surprised if that could leak connections (or is it ok to do so?)).

The database is Virtuoso open source edition.

My hibernate.xml.cfg c3p0 config:

<property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>  
<property name="hibernate.c3p0.breakAfterAcquireFailure">false</property>
<property name="hibernate.c3p0.acquireRetryAttempts">-1</property>
<property name="hibernate.c3p0.acquireRetryDelay">30000</property>
<property name="hibernate.c3p0.automaticTestTable">my_test_table</property>

<property name="hibernate.c3p0.initialPoolSize">3</property>
<property name="hibernate.c3p0.minPoolSize">3</property>
<property name="hibernate.c3p0.maxPoolSize">10</property>

btw: The test table is created and i get tons of debug output- so it seems it actually reads the config.

Answer 1

If I'm not mistaken this is one of the things the connection pool could do for me: if there is a problem with the db, just retry to connect - in the worst case for infinity.

You're mistaken. A connection pool is just... a connection pool, it contains some already established physical connections to the database and is used to avoid the overhead of creating these connections when you need one of them.

That said, these connections can become stale (e.g. if you restart the database). Luckily, most connection pools can be configured to test if a connection is still valid and to renew them before to distribute them. c3p0 support this feature as documented in Configuring Connection Testing and you're actually already using one of the various options. So your connections should be renewed when the database comes back.

But don't expect your application to be magically suspended when the database goes down, a pool won't do this.

Answer 2

Thank you for the answer. It seems i just haven't really understood the last paragraph of the section http://www.mchange.com/projects/c3p0/index.html#configuring%5Frecovery

Because at first it seems c3p0 could do it (detect stale connections and retry to acquire connections for all eternity without ever throwing an exception to the application code (unless of course when its an error related to the sql statement and not to the connection), but in the last paragraph - which is written in pretty confusing way - it seems as if c3p0 can't ensure that to 100%.

So my solution was to make a small wrapper for the methods i needed from the jdbc Connection interface, which attempt to reconnect if a query fails due to a connection error. Of course its a bit hacky, because i'd rather have my components use the standard Connection-interface instead of my own interface, but at least its working cleanly now.

Answer 3

You're forgetting:

• What about your transactions that have been started?
• What about any preparedstatements that have been sent to the DB already?
• etc

Your application will therefore have to restart the transaction. The only possible way I can think of is for the connection pool to keep track of every call to the connection handle and replay those in case of error but this would greatly slow down the connection pool.

In the case of BoneCP (http://jolbox.com), the pool detects a failure has occured by catching the exception thrown by the JDBC driver first and handling it by either flagging that connection as faulty or else by recreating the entire connection pool.

Edit: It's being handled now.

Answer 4

Well it seems that BoneCP has actually implemented this. It can be set to record the transaction and replay it upon network or database failure:

http://jolbox.com/bonecp/downloads/site/apidocs/com/jolbox/bonecp/BoneCPConfig.html#setTransactionRecoveryEnabled(boolean)