Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

Parallel::ForkManager and DBD::mysql, the easy way

by samtregar (Abbot)
on Mar 21, 2009 at 20:10 UTC ( #752289=perlmeditation: print w/ replies, xml ) Need Help??

I love Parallel::ForkManager and I love DBD::mysql but they don't always get along so well. If you have DBI connections open in the parent process you'll get errors when the child processes exit. There's a few ways to deal with the problem, but here's the easiest. Just disconnect all handles in the parent and reconnect later after your parallel work is done:

# disconnect all database handles my %drivers = DBI->installed_drivers(); my @all_dbh = grep { defined } map { @{$_->{ChildHandles}} } value +s %drivers; $_->disconnect for @all_dbh; # now use Parallel::ForkManager as usual foreach my $job (@work) { $pm->start and next; # do the fork # connect in the child $dbh = DBH->connect(...); $pm->finish; # do the exit in the child process } $pm->wait_all_children; # now safe to reconnect in the parent $dbh = DBI->new(...);

The alternative is to keep the parent handles connected but set InactiveDestroy on then in the children. I don't prefer this because you also have to be sure the children won't accidently use the parent handles or explicitely disconnect() them. This may seem easy for simple scripts but code using ORMs like Class::DBI or Rose::DB::Object can hide DBI connections deep in their bowels. You might worry about the slowdown from all this disconnecting and reconnecting but I've yet to see it show up on a profiling run.

So there you have it. I wrote this note mostly for my own future reference since I've had to rediscover this a couple times now. Maybe it will help you too!

-sam

PS: This probably works for any DBI driver that uses sockets to talk to the database, but I can't say for sure.

Comment on Parallel::ForkManager and DBD::mysql, the easy way
Download Code
Re: Parallel::ForkManager and DBD::mysql, the easy way
by ruzam (Curate) on Mar 21, 2009 at 20:21 UTC
      Nicely documented. Aside from the use of clone() instead of connect() this is pretty much the other option I mentioned, using InactiveDestroy to disable disconnect() on parent handles in the kid. My experience with doing it this way was not great - inevitably something in a child process would stumble on a parent handle and either try to use it or disconnect it. Either will cause unpredictable mayhem, which is the very worst kind.

      -sam

        Ya, you've got to keep track of your handles and where they're going to be used.
Re: Parallel::ForkManager and DBD::mysql, the easy way
by sundialsvc4 (Monsignor) on Mar 22, 2009 at 01:38 UTC
    Excellent wisdom! Thanks for sharing!
Re: Parallel::ForkManager and DBD::mysql, the easy way
by kubrat (Scribe) on Mar 27, 2009 at 13:20 UTC
    The bottom line is every distinct process has to have its own database handle - they cannot be shared.
Re: Parallel::ForkManager and DBD::mysql, the easy way
by Anonymous Monk on Jul 17, 2009 at 13:55 UTC
    U saved me a lot of time Raj

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlmeditation [id://752289]
Approved by zwon
Front-paged by Arunbear
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (7)
As of 2014-07-29 12:09 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (217 votes), past polls