Oracle connection timeouts

2019-05-07 - Progress - Tony Finch

Last week while I was beating mod_perl code into shape, I happily deleted a lot of database connection management code that I had inherited from Jackdaw's web server. Today I had to put it all back again.


There is a neat module called Apache::DBI which hooks mod_perl and DBI together to provide a transparent connection cache: just throw in a use statement, throw out dozens of lines of old code, and you are pretty much done.

Connection hangs

Today the clone of Jackdaw that I am testing against was not available (test run for some maintenance work tomorrow, I think) and I found that my dev web server was no longer responding. It started OK but would not answer any requests. I soon worked out that it was trying to establish a database connection and waiting at least 5 minutes (!) before giving up.

DBI(3pm) timeouts

There is a long discussion about timeouts in the DBI documentation which specifically mentions DBD::Oracle as a problem case, with some lengthy example code for implementing a timeout wrapper around DBI::connect.

This is a terrible documentation anti-pattern. Whenever I find myself giving lengthy examples of how to solve a problem I take it as a whacking great clue that the code should be fixed so the examples can be made a lot easier.

In this case, DBI should have connection timeouts as standard.


If you read past the examples in DBI(3pm) there's a reference to a more convenient module which provides a timeout wrapper that can be used like this:

if (timeout_call($connect_timeout, sub {
    $dbh = DBI->connect(@connect_args);
    moan $DBI::errstr unless $dbh;
})) {
    moan "database connection timed out";


The problem is that there isn't a convenient place to put this timeout code where it should be, so that Apache::DBI can use it transparently.

So I resurrected Jackdaw's database connection cache. But not exacly - I looked through it again and I could not see any extra timeout handling code. My guess is that hung connections can't happen if the database is on the same machine as the web server.