Creating new database handles with mod_perl

Question

I've run into an issue when stress testing mod_perl that the database connections are going away. I suspect that processes are sharing database connections, causing the issue.

But I've followed all instructions for Apache::DBI, and can't figure this out.

I'm making the connections in the child process and not in startup.pl. But when I examine the $dbh returned by each child from the DBI->connnect, the address is the same for every httpd process. Firstly, if this is working properly and reconnecting for each process, should the address returned by DBI->connect be different for each child process? I've assumed so, but as far as I can tell the core C code in DBI (dbih_setup_handle) is managing this and is returning the same address. So maybe I'm not understanding what it means to reconnect in the child.

Am I reconnecting properly if the $dbh handles are the same?

Answer 1

It sounds like you are establishing the database connection in a <perl>...</perl> section in your startup config, or in a module loaded at startup, and keeping it around until forked processes attempt to use it.

Unfortunately you can't get away with this. You'll need to somehow make sure that new processes get new connections.

My solution to this problem was a central function to obtain a dbh that kept track of what $$ (the current process id) was when the connection was established. If when handing over the connection, the function found that $$ had changed, it would dispose of any existing connections without closing them, and create a new one:

my $_dbh;
my $pid;

sub dbh {
        if($pid != $$) {
                $_dbh = DBI->connect(...);
                $pid = $$;
        }
        return $_dbh;
}

Any code that wanted to use the database would first call dbh() to obtain a database handle, and let it create a new one if necessary, or hand over a previously established connection if it can be used.