How to fix "C3P0: A PooledConnection that has already signalled a Connection error is still in use"

Question

We have a web application with the stack Spring, Hibernate, C3P0, Oracle DB Driver (having an Oracle DB behind). From time to time we experience blocking locks over a longer period of time which then get killed on the DB end. (we know this is caused by bad application design and we will fix it, but it's not the point of this question). After the DB session was killed by DB it seems that the connection pool reuses the now broken connection which results in the error:

A PooledConnection that has already signalled a Connection error is still in use!

Another error has occurred [ java.sql.SQLRecoverableException: Closed Connection ] which will not be reported to listeners!

On the DataSource we configured

dataSource.setTestConnectionOnCheckin(true);
dataSource.setTestConnectionOnCheckout(true);

But it did not help. We expected that the connections fail these tests and then get renewed. But this does not happen.

Any hints for us how to recreate the broken connections?

Emelineemelita

Related: #43105618 – Stonemason

Answer 1

This warning is given when a Connection that is already checked out experiences an Exception that causes c3p0 to treat it as invalid (so it will not be reincorporated back into the pool on close()), but the Connection continues to be used and experiences an Exception again. These are not broken Connections in the pool. They are broken Connections in-use by the application. So testing them on checkout (or checkin) doesn't do anything about them.

To get rid of this, you need to examine the Exception handling within your application code. Are there circumstances where an invalid Connection might have thrown an Exception, but that Exception was caught and the Connection reused?

The warning itself is harmless. It's just saying c3p0 already knows the Connection is bad, it won't emit an event to signal that again.

Source Code Reference: https://github.com/swaldman/c3p0/blob/c666965e1780a45d4f5aed6367fb43c6c87d8632/src/com/mchange/v2/c3p0/impl/NewPooledConnection.java#L530

Onondaga

As we use Hibernate to create and execute any SQL statements wouldn't it be Hibernate's responsibility to handle the exceptions properly? – Emelineemelita

maybe! there are lots of ways to use and abuse hibernate. after the bit you've quoted above, an Exception stack trace should have been logged. you might want to examine the code path in that stack trace, and think about how you might have gotten there after a prior Exception. – Onondaga

What are the consequences of this warning? I mean: sure it means something is off, but does it tells us that, for example, at least something you tried to do was rollbacked because done on such a connection? Or it might be that all transactions went fine and is just saying that you should keep track of the connections more carefully? BTW: I get this warning even though I never access the connection directly, only via hibernate's entitymanager. Could this be a bug in an old hibernate version? – Taneka