PERL, mySQL, and fork() ?

[Talleyrand]

Hey,

I'm wondering if someone can help me with some irregularities I'm encountering.

I have a script which acts as a daemon, which calls get_pending_records() to poll a mySQL DB for records where the 'processed' field = 'N' (for No). If there aren't any records that have been written, I sleep for a few moments and try again.

However, if there are records, they are returned to me in a passed-in array reference that is loaded with anonymous hash references, each hash reference being a returned record.

So I do something like this:

while ( 1 )
{
  my @records = ();
  get_pending_record( \@records );

  foreach my $record ( @records )
  {
    my $locked = lock_record( $record );
    if ( ! $locked )
    {
       ## Throw some error;
       next;
    }
	
    my $pid = fork();

    if ( ! defined $pid )
    {
      ## Throw some error
    }
    elsif ( $pid == 0 )
    {
      process_record( $record );
      exit;
    }
    else
    {
      print "Message from parent: process $pid handling record $record->{'record_id'}\n";
    }
  }

  sleep $sleep;
}

The lock_record function writes to the DB, updating the record to set the processed field = 'P' (for Processing). This should prevent the record from being retrieved a second time in the main outer loop while the process_record() function performs potentially lengthy operations.

Now the problem is this: When the script starts up and fetches the first batch of records for processing, I get some mySQL write failure errors--they always say "Lost connection while performing query". My database handle still contains an active connection, though.
It gets stranger. This is frequently the printout, for say three records that it wants to process:

[SHELL]
Message from parent: process 12345 handling record 1
Message from parent: process 12346 handling record 1
Message from parent: process 12347 handling record 2
Message from parent: process 12348 handling record 1
Message from parent: process 12349 handling record 3
[/SHELL]

Sometimes it's sane, and there are only three processes forked, one for each unique record.

The above code seems straightforward to me -- I can't figure out why three different children might be receiving the same hashref or why mySQL would be wigging out. I'm running this on a BSDi machine capable of handling ~1000 mySQL questions/minute -- the most I topped out at was 85 during testing. Would the DBI module handling the write during lock *return* a success before it was actually done writing? If that were the case, surely the sleep would take care of that and it would be finished by the time the main loop started over and get_pending_records() was called again.

Thirdly and finally, when I test this script extensively, the free memory on the server drops significantly--almost like there's a memory leak. fork() is a scary beast, but I'm exiting each child (or so I think).

Any help from the PERL/mySQL/UNIX gurus out there?

Thanks!

[Talleyrand]

I figured it out... thanks for all the help

A globally scoped database connection handle is not a good thing to be sharing amongst children.

Talleyrand