What is the safe way to use fork with Apache::DBI under mod_perl2?

Question

I have a problem when I use Apache::DBI in child processes. The problem is that Apache::DBI provides a single handle for all processes which use it, so I get

DBD::mysql::db selectall_arrayref failed: Commands out of sync; you can't run this command now at /usr/local/www/apache22/data/test-fork.cgi line 20.

Reconnection doesn't help, since Apache::DBI reconnects in all processes, as I understood the following error

The server encountered an internal error and was unable to complete your request.

Error message: DBD driver has not implemented the AutoCommit attribute at /usr/local/lib/perl5/site_perl/5.8.9/Apache/DBI.pm line 283. ,

Here's the origin code:

use Data::Dumper 'Dumper';
use DBI ();

my $dbh = DBI->connect($dsn, $username, $password, {
        RaiseError => 1,
        PrintError => 0,
    });
my $file = "/tmp/test-fork.tmp";

my $pid = fork;
defined $pid or die "fork: $!";

if ($pid) {
    my $rows = eval { $dbh->selectall_arrayref('SELECT SLEEP(1)') };

    print "Content-Type: text/plain\n\n";
    print $rows ? "parent: " . Dumper($rows) : $@;
}
else {
    my $rows = eval { $dbh->selectall_arrayref('SELECT SLEEP(1)') };

    open FH, '>', $file or die "$file: $!";
    print FH $rows ? "child: " . Dumper($rows) : $@;
    close FH;
}

The code I used for reconnection:

...
else {
    $dbh->disconnect;
    $dbh = DBI->connect($dsn, $username, $password, $attrs);
    my $rows = eval { $dbh->selectall_arrayref('SELECT SLEEP(1)') };

    open FH, '>', $file or die "$file: $!";
    print FH $rows ? "child: " . Dumper($rows) : $@;
    close FH;
}

Is there a safe way to use Apache::DBI with forking? Is there a way to make it create a new connection perhaps?

Answer 1

Don't fork under mod_perl2. Use Apache2::Subprocess. See also Is it a bad idea to fork under mod_perl2?

Answer 2

I see a few options:

• Explicitly close your DB handles when you fork, and reopen them as needed.

e.g.:

my $dbh = DBI->connect(...);

my $pid = fork;
defined $pid or die "fork: $!";

if ($pid) {
    # parent...
}
else {
    # child...
    undef $dbh;

This could be made easier by storing the $dbh in an object, and passing around that object as needed to parts of your system. The object would be responsible for reopening the $dbh as needed, so the rest of the application doesn't have to concern itself with the details. Keep code encapsulated and well-separated from other parts of the system.

• Don't use Apache::DBI. I can highly recommend DBIx::Connector, which opens a new connection as needed and doesn't preserve the bad behaviour of either plain DBI or Apache::DBI: see http://search.cpan.org/~dwheeler/DBIx-Connector-0.32/lib/DBIx/Connector.pm#Description for a detailed description of how it differs.

I use DBIx::Connector in my system inside a Moose object, which uses a method delegation to provide the dbh. The application simply does:

my $dbh = $db_dbj->dbh;
my $sth = $dbh->prepare(...);
# more boring DBI code here

...And the dbh is reconnected/regenerated as needed, invisibly.

---

As an aside, you should be really careful of using bare filehandles in a multiprocess environment. You could be very easily clobbering your data. open (my $fh, $file) or die "Cannot open $file: $!" is much safer.

I'm also a little nervous by seeing you using eval {} blocks without checking the contents of $@. You're just masking errors, rather than dealing with them, so there may be more things going on than you are aware of. Check your result values (or better, use an explicit exception-handling module, such as Try::Tiny. use use strict; use warnings;.

PS. I just noticed that you are explicitly including DBI in your code. Don't do that. If you use Apache::DBI in your startup_modperl.pl (or whatever you call your bootstrap file), you should never have to include DBI itself. I can't say for sure but I wouldn't be confident the right package is getting called (it's been a while since I looked at Apache::DBI's guts; it might take care of this for you though).