[sakai2-tcc] Improving Database Connection Support

[David Adams]

David Adams wrote:
> 5. Lack of support for database failover except through driver indirection.

Okay, so this is an issue close to my heart, and is probably an easier
fix than introducing an entirely new component to the architecture.

Here's the scenario: We have two Oracle database servers set up in an
active-passive failover structure. The config works great, and we can
failover in a few seconds. However, Sakai doesn't cooperate, and you
have to reconfigure and restart the app servers to regain
connectivity. Luckily, Oracle's full JDBC driver supports the
failover completely transparently, right? Oh, except Sakai doesn't do
JDBC the standard way, so no dice.

I've been toying around inside the SakaiBasicDataSource class and the
various other hacks around commons-pool on and off for the past year
trying to find a safe workaround to allow me to either watch for
busted connections and restart them on the standby db host, or just to
force a complete rebuild of the database connection pool. This latter
is what I've settled on by providing a resetDataSource method in my
extension class that I can call through our custom REST-RPC servlet,
which just trashes the connection pool and updates the bean properties
so that the next call to createDataSource from the app will regenerate
the pool with the new settings.

I've yet to move my hacky code out to production, and honestly I'd rather
not have to resort to such a messy solution.

Here's what Noah proposed:

Noah Botimer wrote:
> First step: undo our weird binding to a very old DBCP and allow a real
> DataSource to be plugged in -- whatever pool or config you need.

> Second step: pick the Tomcat pool by default and document how to switch
> if you need something else (hint: should be very standard and basically
> link to JDK/Tomcat docs).

So are you talking about, eg, setting up a Resource in server.xml, and
binding to that, or something else? I admit I'm unfamiliar with Tomcat
SOPs as I've been so immersed in Sakai for so long that "correct" Tomcat
usage is often foreign to me.

> Third step: add some better connection/query/transaction/request metrics
> that work OOTB so implementors can better see what's going on and decide
> what to do about it.

This is very important. Ideally, we need to be able to peek into the
status of connections, find out what those hung active connections are
up to, and selectively kill/restart/tweak them on the fly.

I'm definitely on board with this project if there's someone to
partner up with who understands exactly how Sakai has hijacked DBCP
and where all the tendrils lead. Obviously, my situation is just one
scenario, but it seems like any legal JDBC connection ought to be
valid for Sakai. If we can configure it in the "standard" way as well
that's another win.

Anyone have any other thoughts?

-dave