I should point out that this was working fine for years on perl 5.6.1, MySQL 3.23.x and some older versions of DBI and the DBD driver. I had a day off Thursday and I decided it was time to bring my server up to date and installed perl 5.8.3, MySQL 4.0.17, and updated my kernel from 2.4.18 to .24. Frankly I'm please that this is the only real problem that has resulted, but I sill want to understand.

I'll also say that I've done a bunch of searching here and on google for this error. As you'll see below I'm not inserting with a large ammount of data... these are simple selects. MySQL server has the default 8 hour timeout for a connection and I encounter the error on the first wakeup() poll.

Some particulars. The program massively simplified:

#!/usr/bin/perl

######################################################################
#       General use and inits
######################################################################

# Standard CPAN modules
use POSIX;
use Carp;

# Home Grown
use vgapas; # containing GenericDB and others
use TEST;

use strict;

my $time_to_die = 0; # I omitted the SIG handlers here
my $dbq;

######################################################################
#
#       call_host
#
######################################################################

sub call_host
{
  my $tag       = shift;

  my $sql = qq/SELECT number,trncnt,status FROM positions WHERE tag = 
+'$tag' AND status != 'D'/;
  foreach my $gr (@{$dbq->fetchall($sql)}) {
    # stuph
  }

  # more stuph

  &remote_exported_sub($tag);

}

######################################################################
#
#       wakeup
#
######################################################################

sub wakeup
{

  # Object to DB handle
  $dbq = vgapas::GenericDB->new();
  unless  (ref $dbq) {
    print "dbq is not a ref in wakeup\n";
    exit(1);
  }

  my $sql = qq/SELECT tag,name,schedule,next FROM games WHERE active =
+ 'Y'/;
  foreach my $rr (@{$dbq->fetchall($sql)}) {

    if (#condition) {
      &call_host($rr->[0]);
    }

  }

}

######################################################################
#
#       MAIN
#
######################################################################

# Fork into background
my $pid;
if ($pid = fork) {
  # Mark startup
  my $pt = localtime();
  print "$0 start: pid = $pid\n";
}
elsif (defined $pid) {
  # Detach and set up loop
  POSIX::setsid() || croak "Can't start a new session: $!\n";
  until ($time_to_die) {
    &wakeup();
    sleep(300);
  }
}
else {
  print "Unable to Fork: $!\n";
}
[download]

The OO DB connection (GenericDB)

######################################################################
#
#       Package:        MyDBI
#                               MyDBI::db
#                               MyDBI::st
#
#       Purpose:        Subclassing the DBI module
#
######################################################################

package vgapas::MyDBI;
@ISA = qw(DBI);

# inherit connect etc
package vgapas::MyDBI::db;
@ISA = qw(DBI::db);

sub prepare {
    my($dbh, @args) = @_;
    my $sth = $dbh->SUPER::prepare(@args);
    return $sth;
}

package vgapas::MyDBI::st;
@ISA = qw(DBI::st);

sub fetch {
    my($sth, @args) = @_;
    my $row = $sth->SUPER::fetch(@args);
    return $row;
}

######################################################################
#
#       Package:        vgapas::GenericDB
#
#       Purpose:        A class for cgi field retrieval and storage
#
######################################################################

package vgapas::GenericDB;
@ISA = qw(vgapas::MyDBI);
$VERSION = 1.00;
use Carp;
use Carp qw(cluck);
use Data::Dumper;

sub new
{
  my ($class, @argz) = @_;

  # Eval connection block and croak on failure
  my $dbh = eval {
     vgapas::MyDBI->connect("DBI:mysql:<DB>:localhost:3306",
        '<USER>', '<PASS>',
        {PrintError => 0, RaiseError => 0} );
  };
  if ($dbh->err) {croak "Error connecting to db: $dbh->errstr\n";}

  bless {_dbh=>$dbh}, $class;

}

sub fetchall {
    my($self, $sql, @args) = @_;
    my $dbh = $self->{_dbh};

    my @row_data;
    eval {
      my $sth = $dbh->prepare($sql);
      $sth->execute();
      while ((my @row) = $sth->fetchrow()) {
        push(@row_data, \@row);
      }
    };
    if ($@) {croak "fetchall failed".join("\n", $@)."\n$sql\n";}

    return \@row_data;
}

# other methods for retrieving data here
[download]

And the second non OO TEST module

package TEST;
@ISA = qw(Exporter);
@EXPORT  = qw( remote_exported_sub );

use Data::Dumper;
use Carp;
use strict;

use vgapas;

# Initilize some objects
my $local_dbq = vgapas::GenericDB->new();
unless (ref($local_dbq)) {croak "db handle not ref: $local_dbq\n";}

######################################################################
#
#       remote_exported_sub
#
######################################################################

sub remote_exported_sub {

  my $tag               = shift;

  # Get Player Info
  my $sql = qq/SELECT positions.number,positions.handle,
        player_pref.primary_email,player_pref.secondary_email,player_p
+ref.icq,
        player.email,player.altemail,player.icq
        FROM positions
        LEFT JOIN player_pref ON positions.number = player_pref.number
+ and positions.tag = player_pref.tag
        LEFT JOIN player ON positions.handle = player.handle
        WHERE positions.tag = '$tag' AND status != 'D'/;
  foreach my $row (@{$local_dbq->fetchall($sql)}) {
    # do stuph with @row
  }

}

1

__END__
[download]

My croaks tell me that TEST::remote_exported_sub caught an exception calling fetchall in the eval $sth->prepare. Again error is

fetchall failedDBD::mysql::st execute failed: MySQL server has gone aw
+ay at /vgap/vgapas/vgapas.pm line ...
[download]

Now I have other applications that build mutiple objects in themselves and by calling other modules that work just fine. They don't fork. So can anyone help me understand why my program seem to be loosing it's connection of why it's being timed out?