madorb has asked for the wisdom of the Perl Monks concerning the following question:

Using perl 5.8.8 on Cygwin with DBD::Oracle 1.2.3 and DBI 1.609 I have some code attempting to fork and then establish a new DB connection using the process as defined at DBI, Fork, and clone. It is currently behaving quite strangely though: 
sub some_method
{
    ...
    print "CALLING FORKER\n";
    my $pid = db_forkin($dbh, \@some_array_of_files);
    waitpid($pid, 0);
    print "KID'S DONE\n";
    ...
}

sub db_forkin
{
    my $dbh = $_[0];
    #dereference array ref
    my @scripts_to_execute = @{$_[1]};

    my $pid = fork();
    if($pid == 0) #i'm a child!
    {
        Print("*****************CHILD******************\n");

        #idiom via http://www.perlmonks.org/?node_id=594175
        my $child_dbh = $dbh->clone();
        $dbh->{InactiveDestroy} = 1;
        $dbh = undef;

        my $q = qq(INSERT INTO TEST_TABLE 
            (FIRST_NAME, LAST_NAME, AGE, GENDER) 
            VALUES ('ISTHIS', 'WORKING', 30, 'X'));
        
        print("*****************A******************\n\n");
        my $sth = $child_dbh->prepare($q);
        print("*****************B******************\n\n");
        $sth->execute();
        print("*****************C******************\n\n");

        foreach my $script_name (@scripts_to_execute)
        {
            print("Executing $script_name within a the fork\n");
        }

        #once the child process is done, kill it.
        print("\n************CHILD IS DYING*************\n\n");

      POSIX:_exit(0);
    }
    else
    {
        return $pid;
    }
}

[download]

The output of this is: 
CALLING FORKER
*****************CHILD******************
<long pause...>
KID'S DONE

[download]

It also appears that the insert statement doesn't occur. Now if I comment out the new steps to create the $child_dbh and just use $dbh, i am able to see ...*A*... Basically as soon as anything database related executes, the whole process seems to be lost.

Has anyone seen this behaviour before? Any suggestions?

Replies are listed 'Best First'.
Re: Forking and DBD::Oracle
by mzedeler (Pilgrim) on Jul 16, 2009 at 22:57 UTC

    I would be very surprised if this technique is usable with any DBD driver. After all, when you have created a DBI connection, there is usually just one (and only one) database session available using the instantiated database handle with some driver specific resources allocated. Providing a universal method to clone such anything-structure seems next to impossible, but I'd be happy to be proven wrong.

    Reading the posting you refer to, it also seems to be very experimental and some other posters had trouble with it.

    Since you are not using transactions, why not just use two database connections?

[reply]
      That's in essence what i'm doing... the clone() creates a new connection. I also tried not using clone and manuallyc reating a new connection and it exhibits the exact same behaviour.
[reply]

        So I guess that the reason you try cloning the database handle is to reuse the connection parameters?

        In that case, you can just write a sub that returns a closure which in turn creates a database handle with all the right parameters.

        sub get_dbh_factory {
    my @config = @_;
    return sub {
        return DBI->connect(@config);
    }
}

sub somewhere_else {

    my $dbh_factory = get_dbh_factory( ... );

    if(fork == 0) {
        my $dbh = $dbh_factory->();
        ...
    } else {
        my $dbh = $dbh_factory->();
        ...
    }
}

        [download]

        Code above is sloppy and just a sketch, but the principle behind should work.

[reply]
[d/l]
Re: Forking and DBD::Oracle
by jethro (Monsignor) on Jul 16, 2009 at 22:52 UTC

    No idea about your main problem, but there is a bug in your call of the sub. It should be:

    my $pid = db_forkin($dbh, \@some_array_of_files);

    [download]

    If you had warnings on, you would have gotten a notice about it so I guess you don't. Big mistake

    AFAIK there is no fork on Windows so it is simulated with threads. Maybe you could test your script on linux to see whether that changes anything

[reply]
[d/l]
      sorry, that was a typo (i'm very prone to these lately!) it's fixed. the real code doesn't have that problem.
[reply]

Back to Seekers of Perl Wisdom