WARNING c3p0: Another error has occurred: Connection is Closed

Question

We are using c3p0 as the connection pool in our application with Microsoft SQL Database. The connections are tested on checkout with validation query so that application doesn't work with stale connections.

Recently, we have started seeing following warning in the application logs (a lot of these messages are present in sequence). Anyone have seen this sort of exception and what does it mean?

2017-03-29 09:34:24 [WARNING] [c3p0] A PooledConnection that has already signalled a Connection error is still in use!
2017-03-29 09:34:24 [WARNING] [c3p0] Another error has occurred [ com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed. ] which will not be reported to listeners!
2017-03-29 09:34:24 com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
2017-03-29 09:34:24     at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
2017-03-29 09:34:24     at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:388)
2017-03-29 09:34:24     at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(SQLServerConnection.java:2166)
2017-03-29 09:34:24     at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(SQLServerConnection.java:1853)
2017-03-29 09:34:24     at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:1076)

My concerns are:

1. Does this warning (or exception message) mean that the query had actually failed to execute and the code will throw the exception?
2. Is it just a warning message that is logged by c3p0 because we test connection on checkout and since the connection is closed, it will now acquire a new connection from the database and the application will run without any issue?

Any help will be appreciated. Thanks!

Merrymaking

is there more stack trace? is it application code or c3p0 code that is calling prepareStatement(...)? – Rabelais

@SteveWaldman It is the application code. If it helps, then I can provide you its details. Please let me know. – Merrymaking

no, that's enough. (really it had to be, c3p0 doesn't prepare statements on proxy Connections. but i just wanted to be sure.) – Rabelais

Related: #50066926 – Sapheaded

Answer 1

So, there's not enough information here to say what the initial cause of the problem was. Anything could have happened, a network outage, whatever. Testing a Connection on checkout ensures that the Connection worked at the time of checkout, but once in client-land, nothing prevents a break. It should be very, unless you are keeping Connections checked out for long periods of time. (Don't do that! With a Connection pool, adopt a just-in-time, quick checkout, immediate check-in strategy.)

Anyway, some attempt by the application to use the Connection threw an Exception. c3p0 internally checked the Connection then, decided the Connection was broken, and emitted an event (specified by the JDBC spec, but of interest only to internal listeners) indicating a Connection error. c3p0 responds to this by marking the Connection for destruction rather than check-in when the application is done.

The application, despite having seen the first Exception, continued to use the Connection. A second Exception occurred (yes, this Connection really is broken). That's what c3p0 is logging here. It's ignoring the second Exception, not signaling a Connection error, because a Connection error has already been signalled for this Connection. But it's a bit surprised and annoyed to find that the Connection is still in use ;)

All exceptions are relayed to the application. Silently swallowing up problems is the very opposite of c3p0's philosophy. But whatever your application was doing with this Connection triggered an Exception, and your application kept doing other things that triggered more.

That doesn't necessarily mean that anything is wrong. An application may tentatively interpret an Exception as something other than a Connection failure. Perhaps an Exception occurred because of a constraint violation, and if so, there is a workaround? If it were something like that, here the application would find further evidence that, yes, the Connection is broken, because this next use of the Connection, after a previous Exception had been handled, will continue to fail.

If I were you, I'd review the application code that triggers this stack trace, and look particularly for Exception handling in prior steps that might be too forgiving, that might catch an Exception and continue when it should instead abort. Again, that's not necessarily the case -- it could be that your application is doing exactly what it should, it's appropriately retrying or attempting to continue after a potentially recoverable error, and it's robust to the possibility that the retry will fail too, in which case you'll just harmlessly see these stack traces in your logs, hopefully very rarely, when already-checked-out Connections fail. But I'd definitely review your Exception handling logic in this code path, during the step that triggered the stack trace, and importantly during prior steps which would have triggered the first Exception. Usually one Exception aborts a database codepath (except for an eventual rollback() and close()), here you are barreling on to a second, which may well be awesome, but make sure it is what you want to do.

If you are seeing this a lot, make sure Connection testing on checkout really is configured properly, then try to minimize the period during which the Connection is checked out, then try to understand why your network or something at the server side might be failing occasionally.

Rabelais

Thanks, I will review the application code as per your suggestion. One thing that I want to clarify is that this warning (or error message) is not just logged by c3p0 but also relayed back to the application. Correct? – Merrymaking

yes. if you want to verify, the messages you are seeing are caused by the handleThrowable(...) method of NewPooledConnection. that method itself does not rethrow the Exception. it takes an arbitrary Throwable, does c3p0's internal accounting upon detection of a potential error, then returns the Throwable converted to an interface-appropriate SQLException. All codepaths that use handleThrowable(...) throw the returned Exception immediately – Rabelais