List:MySQL and Perl« Previous MessageNext Message »
From:Stephen Adkins Date:September 5 2005 9:42pm
Subject:Re: forking and InactiveDestroy
View as plain text  
Hi,

We use {InactiveDestroy} effectively with multi-process applications 
with MySQL.

I think the problem is that you are trying to use the same handle in 
both parent
and child processes without using the {InactiveDestroy} attribute properly.

This is what {InactiveDestroy} is for:
When you open a database handle ($dbh) in a process, it contains a 
variety of
database-specific state/connection information in it.  When you fork a 
child process,
you should never use the database handle ($dbh) which you opened in the 
parent
process.  This behavior is *not* defined to work by the DBI.  It does 
work in some cases
on *some* databases, but this is not supported behavior.  Every process 
should have
its own database handle.

The problem with this is that when you close the inherited database 
handle in the
child process, it also shuts it down on the server side, thus rendering 
the handle
unusable even in the parent process.  So here's the logic.

  parent process opens a database handle
  parent forks a child process
  parent can continue using the open database handle safely as long as no
     child process uses it and no child process closes it "ungracefully"
 
  child process immediately sets the {InactiveDestroy} attribute on the 
handle
  child process deallocates the database handle (i.e. $dbh = undef;) but 
does *not*
     explicitly close the connection. This triggers the DESTROY method 
on the
     database handle.  Since the {InactiveDestroy} attribute is set,
     this causes the client-side state and connection information to be
     deallocated and shut down without telling the server to shut down the
     connection.  this allows the parent process to continue to use the 
connection.
  child opens its own connection to the database
  child uses its own connection and closes it normally whenever it wishes

Here is the documentation from the DBI manual page.

   http://search.cpan.org/~timb/DBI/DBI.pm

|InactiveDestroy| (boolean)

    The |InactiveDestroy| attribute can be used to disable the /database
    engine/ related effect of DESTROYing a handle (which would normally
    close a prepared statement or disconnect from the database etc). The
    default value, false, means a handle will be fully destroyed when it
    passes out of scope.

    For a database handle, this attribute does not disable an /explicit/
    call to the disconnect method, only the implicit call from DESTROY
    that happens if the handle is still marked as |Active|.

    Think of the name as meaning 'treat the handle as not-Active in the
    DESTROY method'.

    This attribute is specifically designed for use in Unix applications
    that "fork" child processes. Either the parent or the child process,
    but not both, should set |InactiveDestroy| on all their shared
    handles. Note that some databases, including Oracle, don't support
    passing a database connection across a fork.

    To help tracing applications using fork the process id is shown in
    the trace log whenever a DBI or handle trace() method is called. The
    process id also shown for /every/ method call if the DBI trace level
    (not handle trace level) is set high enough to show the trace from
    the DBI's method dispatcher, e.g. >= 9.

That should do it for you.

Stephen

the hatter wrote:

>I'm having a problem vaguely similar to some in the archives, but I'm not
>convinced it's the same cause.
>
>I have code that, approximately, says:
>
>$dbopts{'InactiveDestroy'} = 0;
>$db = DCP::db_connect("dcp",\%dbopts);
>
>my $query = "SELECT * FROM foo";
>my $dbq = $db->prepare($query); $dbq->execute;
>
>my $forkid;
>while (my ($uid,$ip,$lang)=$dbq->fetchrow_array) {
>    $forkid = fork();
>    next if $forkid != 0;
>
>    $dbm = $db->prepare("SELECT * FROM bar"); $dbm->execute; # Line 63
>    while (my ($port,$proto,$down)=$dbm->fetchrow_array) {   # Line 64
>        #do some stuff
>    }
>
>    $dbq->finish;
>    $db->disconnect;
>    exit(0);
>
>}
>
>It runs on several machines, most a bit old, the database server running
>mysql 3.23.32.  A new machine (installed with mysql client and libs from
>RH FC4) tries to run this script but gives a stack of DBD::mysql::st
>execute failed: MySQL server has gone away at ./myscript line 63.
>DBD::mysql::st fetchrow_array failed: fetch() without execute() at
>./myscript line 64.
>
>There is some mention of using InactiveDestroy in children, rather than in
>the parent, but I'm not sure how that would work in this instance
>(finish/disconn the query and db once inside the main while(), then
>reconnect ?  Doesn't seem to make so much sense)
>
>Any suggestions, or known issues (google and the mailing list archives
>didn't seem to help)
>
>thanks
>
>
>the hatter
>
>
>  
>


Thread
forking and InactiveDestroythe hatter5 Sep
  • Re: forking and InactiveDestroyStephen Adkins5 Sep