Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW

DBI, fork, and clone.

by kyle (Abbot)
on Jan 11, 2007 at 15:22 UTC ( #594175=perlmeditation: print w/ replies, xml ) Need Help??

Some time ago, I wanted to fork a script that had an open DBI connection and a running statement and let the child use the connection the parent had (or an identical connection). This turned out to have more pitfalls than I expected. Everything that is necessary to do this is documented, but I haven't seen one code sample that brings it all together, so I'm documenting here the method I used in the form of a Test::More script.

I'm using DBI 1.53, Perl 5.8.8, and PostgreSQL.

#!/usr/bin/perl use strict; use warnings; use Test::More 'no_plan'; use DBI; my @connect_parameters = ( 'DBI:Pg:dbname=template1', 'user', 'pass', { ShowErrorStatement => 1, AutoCommit => 1, RaiseError => 1, PrintError => 0, } ); # An earlier version leaked socket connections # and would eventually fail (or cause "Too many # open files" errors elsewhere). I loop a while # here to detect that bug. foreach my $iteration ( 1 .. 2_000 ) { warn "iteration $iteration\n"; my $dbh = DBI->connect( @connect_parameters ); isa_ok( $dbh, 'DBI::db' ); my $one; ($one) = $dbh->selectrow_array( 'SELECT 1' ); is( $one, 1, 'can select 1' ); # this is fetched later my $sth = $dbh->prepare( 'SELECT 1' ); $sth->execute; ok( ! $dbh->{InactiveDestroy}, 'dbh InactiveDestroy is off before fork' ); my $pid = fork(); if ( ! defined $pid ) { die "Can't fork: $!\n"; } if ( $pid ) { # parent isa_ok( $dbh, 'DBI::db' ); ($one) = $dbh->selectrow_array( 'SELECT 1' ); is( $one, 1, 'parent can select 1 before child exits' ); is( wait(), $pid, 'waited for child' ); ($one) = $dbh->selectrow_array( 'SELECT 1' ); is( $one, 1, 'parent can select 1 after child exits' ); } else { # child my $child_dbh = $dbh->clone(); isa_ok( $dbh, 'DBI::db' ); isa_ok( $child_dbh, 'DBI::db' ); ok( ! $dbh->{InactiveDestroy}, 'dbh InactiveDestroy is off in child after fork' ); ok( ! $child_dbh->{InactiveDestroy}, 'child_dbh InactiveDestroy is off in child after fork' ); $dbh->{InactiveDestroy} = 1; ok( $dbh->{InactiveDestroy}, 'dbh InactiveDestroy is on in child after fork' ); ok( ! $child_dbh->{InactiveDestroy}, 'child_dbh InactiveDestroy is off in child after fork' ); undef $dbh; ok( ! $dbh, 'death to dbh in child' ); ($one) = $child_dbh->selectrow_array( 'SELECT 1' ); is( $one, 1, 'child can select 1' ); exit; } ($one) = $sth->fetchrow_array; is( $one, 1, 'select running before fork still works' ); }

The clone call produces one warning that doesn't seem to have any consequence:

Can't set DBI::db=HASH(0x8424abc)->{User}: unrecognised attribute name or invalid value at /usr/lib/perl5/ line 675.

In production code, I handle this with a $SIG{__WARN__} handler like so:

# Save existing handler. my $saved_warn_handler = $SIG{__WARN__}; # Suppress warnings. $SIG{__WARN__} = sub {}; my $child_dbh = $dbh->clone(); # Restore saved handler. $SIG{__WARN__} = $saved_warn_handler;

In summary, the general procedure here is as follows:

  1. You have a $dbh.
  2. fork
  3. Parent goes about its business.
  4. Child creates a new $child_dbh with $dbh->clone().
  5. Child sets $dbh->{InactiveDestroy} = 1. This tells DBI that the parent's connection should not be closed when $dbh is destroyed.
  6. Child destroys parent's $dbh (don't use it on accident!)
  7. Child goes about its business (using $child_dbh as it likes).

Earlier nodes that touch on this subject:

Comment on DBI, fork, and clone.
Select or Download Code
Re: DBI, fork, and clone.
by qbxk (Friar) on Jan 12, 2007 at 18:17 UTC
    I ran into this problem just the other day. I had a time consuming database operation i wanted to happen right after a certain page call, but didn't want to make the client wait while i did it. So, fork, close STDOUT and STDERR and do my business. I remember when it dawned on me that the parent was outrunning the child to completion (as was my intention of course) and I'd lose the $dbh when it got there. After a brief affair with $dbh->clone() I decided to punt: just set a flag and made a cron job...

    but maybe we'll go look at that again....

    Thanks kyle!

    It's not what you look like, when you're doin' what you’re doin'.
    It's what you’re doin' when you’re doin' what you look like you’re doin'!
         - Charles Wright & the Watts 103rd Street Rhythm Band, Express yourself
Re: DBI, fork, and clone.
by mr_mischief (Monsignor) on Jan 16, 2007 at 01:11 UTC
    Great work. I don't even get the warning you mention on my test box.

    Note that DBI's clone() method is labelled as new and subject to change. Also worth noting is that the username argument to DBI's connect is just that -- 'Username', to be exact.

    I mentioned that I don't get the warning you noted in your node. I was thinking that things sounded odd considering the 'User'/'Username' business, so I tried changing the $dbh->clone() call like this:
    $db->clone( {'User' => 'foo'} );
    ... and I got exactly the warning you mentioned!

    My guess is that your particular DBI (or DBI/DBD combination) is connect()'ing with a 'Username' attribute and then re-issuing it with something additional called 'User'. I'm using MySQL, and it looks like you're using Postgres from the driver info you had in the test. I'll give you my info for comparison:


    The perl binary I tested against does have Mandriva-applied patches, but I doubt that has anything to do with the matter.

    ActiveState perl 5.8.7 on Windows with DBI 1.4.2 and DBD::mysql 3.0002 fails to create a clone, BTW. It gives the following complaint: "DBD::mysql::db clone failed: handle 2 is owned by thread 2244a4 not current thread 1cccad4 (handles can't be shared between threads and your driver may need a CLONE method added) a t db_clone line 59". Probably has to do with Windows not supporting fork() and the perl installation having to make do with threads, smoke, and mirrors.

    Christopher E. Stith

      I have DBD::Pg version 1.49, and in its connect method, I see:

      152 my ($dbh) = DBI::_new_dbh($drh, { 153 'Name' => $Name, 154 'User' => $user, 'CURRENT_USER' => $user, 155 });

      Maybe that has something to do with it. I don't see User used anywhere else (not that I've done an exhaustive search of DBI-related code), so maybe that's why the warning seems to be toothless.

      Thanks for letting us know how it works on your setup!

Re: DBI, fork, and clone.
by Anonymous Monk on Apr 24, 2007 at 10:52 UTC
    I've had this exact same issue for ages now and tried to come up with all sorts of workarounds. I've tried your method in some test code and I'm still seeing that issue you had in previous article regarding all forked connections still use the same handle. from my test: sol::kenjim$ ./ 4326 parent: DBI::db=HASH(0x823d6ec) :1 4327 child: DBI::db=HASH(0x832c4a0) :1 4326 parent: DBI::db=HASH(0x823d6ec) :1 4328 child: DBI::db=HASH(0x832c4a0) :1 4326 parent: DBI::db=HASH(0x823d6ec) :1 4329 child: DBI::db=HASH(0x832c4a0) :1 4326 parent: DBI::db=HASH(0x823d6ec) :1 4330 child: DBI::db=HASH(0x832c4a0) :1 4326 parent: DBI::db=HASH(0x823d6ec) :1 4331 child: DBI::db=HASH(0x832c4a0) :1 the first value is the pid, but each child has the same memory address, so I assume they still are sharing connections. I need each child to have its own connection as well... a little stumped.

      A few brief comments...

      1. I'd be interested to see the code you say is not working. Without seeing it, it's hard to tell what might be going on.
      2. The problem really concerns shared connections (and avoiding them). I'm not sure that same memory addresses (in different processes) implies same connections.
      3. Letting each child have its own connection is easiest if you can close the parent's connection first. In my case, that wasn't feasible, but maybe it is in your case.

      Thanks for your comments.

Re: DBI, fork, and clone.
by Anonymous Monk on Apr 09, 2008 at 11:40 UTC
    Thanks for this. I'm doing basically the same, but it's reassuring to see other's solutions. The error message can be avoided by passing an empty hash ref to the clone method like so: $dbh->clone({}); I guess they have forgotten to do an my $args = shift || {} or somesuch, but I'm too busy to check now :-) Regards -- t
      I have been using the above code successfully since 2007 when it was first posted, but recently I upgraded my Perl to 5.8.8, DBI 1.604, and DBD::Pg 2.10.7.

      I'm having the same issue as before where the main process will go haywire and report random query errors. Did something change recently or is there a new workaround?

        Here is the code I was using that is now broken. I have defined $dbh as a global variable using 'our' pragma.
        if (my $pid = fork) { l("D","$restr [$tbn] [se_id:$se->{scenario_exec_id}] forking ($p +id) $se->{suite_name}:$se->{scenario_name}"); $V{scenario_exec_childs}->{$se->{scenario_exec_id}}{pid} = $pid; $V{scenario_exec_childs}->{$se->{scenario_exec_id}}{start_time} += $before_exec; # prepopulate before next cycle for state consistency, $se is re +ferenced in the $ses array ref in main loop $se->{status} = "RUNNING"; set_scenario_exec_state($se,{ -testbed_id=>$tb_id, -pid=>$pid, - +start=>"now()", status=>"RUNNING" }); sleep 2; } elsif (defined $pid) { $0 = "$restr $tbn SE:$se->{scenario_exec_id}"; l("D", "top of se fork: " . $dbh); # once and for all, I think I have figured out the forking + DBI + connection handling issue! 2007-04-23, # we clone the handle, undef it, then copy over the old variable + in the new process # suppress warning from clone call... I think something is broke +n with DBI # produces: Can't set DBI::db=HASH(0x82cf704)->{User}: unrecogni +sed attribute or invalid value at # /volume/perl/lib/site_perl/5.8.5/i386-freebsd/ line 648. my $saved_warn_handler = $SIG{__WARN__}; $SIG{__WARN__} = sub {} +; my $child_dbh = $dbh->clone(); $SIG{__WARN__} = $saved_warn_hand +ler; l("D", "cloned child_dbh: " . $child_dbh); $dbh->{InactiveDestroy} = 1; undef $dbh; $dbh = $child_dbh;

        20090108 Janitored by Corion: Changed PRE tags to CODE tags, as per Writeup Formatting Tips

Re: DBI, fork, and clone.
by mrhackerott (Initiate) on Apr 11, 2010 at 15:42 UTC
    Thanks for this information! I was going insane trying to figure out workarounds before finding this. Excellent tidbit.
Re: DBI, fork, and clone.
by Anonymous Monk on Dec 17, 2010 at 15:22 UTC
    I just found this posting, over 3 years since it was written, from a Google search for "DBI fork". It was *exactly* what I needed. I can't thank you enough for not only figuring it out, but having the foresight to document your efforts for us future programmers. -- Mr.Itty

Log In?

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

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (15)
As of 2014-09-30 19:12 GMT
Find Nodes?
    Voting Booth?

    How do you remember the number of days in each month?

    Results (381 votes), past polls