Patch for robust JDBC pooling in Hermes H2O SimpleDSDAOFactory

[Martin Kalén]

Greetings Hermes developers and users,
we are using the EbMS module of Hermes in a standard setup (ie with
the default SimpleDSDAOFactory and JDBC connection pooling using
Jakarta Commons DBCP [1]).

On Windows installations of Hermes H2O from 2007-11-09 and PostgreSQL
(v8.2.5.1) we have been experiencing non-robust connectivity between
Hermes and PostgreSQL when the application is activated after it has
been sitting idle for some time.

This is manifested as "PSQLException: An I/O error occured while
sending to the backend" caused by "java.net.SocketException:
Connection reset by peer: socket write error" in the Hermes logs (at
various places, it affects all DB access and both EbMS, AS2 and
respective admin plugins once manifested at runtime).

We have tracked down the problem to Hermes H2O SimpleDSDAOFactory not
utilizing the validation query feature of Jakarta Commons DBCP.
Validation queries which will enable "alive" checks of pooled JDBC
connections before and/or after usage (and even during idle time in
pool with scheduled "eviction runs" configured in DBCP [2]). The
consequence is that once a JDBC connections between java and postgres
processes have been established, it will never be validated as alive
even if it starts failing and H2O can never "heal" after connectivity
regressions (in our case caused either by PG server idle timeout
disconnect or firewalls between app- and rdbms server dropping the
connection).

I have attach a simple patch ("pooling.patch") with an example of how
to remedy this problem for a H2O standard setup to the group. Let me
know if you can read this file or if you want me to mail or post it in
any other way or format.

A work-around with the current H2O codebase is to use appserver
datasources and eg configure a DBCP validation query in a Tomcat DS
and let H2O use that one.

This can easily be verified using the current Hermes deliverable:
1. start postgresql and h2o
2. perform some h2o activity to establish pooled connections between
java and postgres
3. shut down postgresql server
4. perform some h2o activity eg click on links in admin or watch the
logs (failures are of course expected!)
5. start postgresql server again
6. h2o can never recover after failing at #4

With the path, H2O recovers after #5 and #6 starts displaying
successful behaviour in Hermes again.

Any chance this can make it into the codebase?

Best regards,
Martin

[1] http://commons.apache.org/dbcp/
[2] See DBCP configuration documentation at http://commons.apache.org/dbcp/configuration.html

P.S. I choose to hard-code "test on borrow" only and no test on return
to pool after usage and/or eviction runs during idle time in pool. You
may of course choose to change this or make it configurable if you
like. With validation during idle time and possible recovery while
idle, the next active borrow will be faster and H2O can be more
responsive after idle time however idle activity is increased and it
is difficult to choose good default values since this is very
dependent on the runtime usage profile and the application of H2O in
specific cases.

[hoc...@gmail.com]

Hi Martin,

Thank you for your very useful suggestion and code patch.
It will definitely enhance connection stability for H2O.
We will adopt this to the codebase and release it in next version.

From my point of view, "testOnBorrow" somehow achieves the "safety
net" with minimum effort.
"testWhileIdle" & "testOnReturn" will happen to be the same as
"testOnBorrow" very little earlier and just with more overhead.

Many thanks!
Philip

> [2] See DBCP configuration documentation athttp://commons.apache.org/dbcp/configuration.html