forking and InactiveDestroy

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


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: forking and InactiveDestroy

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
>
>
>
>



--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org