Heyho,

I spent ages trying to track down a mysql/Apache::DBI
bug, and whilst I'm not sure what's going wrong with
your system, I found it useful to add the PID of the
individual mysql process (not sure if you can do this
with postgresql) to the $dbh and then implement a
error handler which logs the pid. Then (again not sure
if you can do this in postgres) trace that pid to a DB
log file. in mysql you can get the last run command of
that PID, and then see if something is amiss on the DB
end...

Anyway here's a quick brain dump, you'll probably have
to tailor this to your needs..


Regards

Marty


something like this:-

my $dbh;

    my %attrs=();
    $attrs{RaiseError}=1;

    if ($DBI::VERSION >= 1.21)
    {
      #cos old DBI doesn't support this and we had
many versions running
      #cluck ("Adding ErrorHandler as DBI (version
$DBI::VERSION) Supports this method\n");
      $attrs{HandleError}=\&DBIErrorHandler;
    }
    else
    {
      #cluck ("Not Adding ErrorHandler Support as DBI
version ($DBI::VERSION) Does not support this
method\n")
;
    }

    eval
    {
        $dbh = DBI->connect(
            $dsn,
            $db_user,
            $db_password,
            \%attrs
        );
    }; #because our bug was in the connect, you may
not need this

my $c_id;
    eval
    {
      my $stm_id = qq{SELECT connection_id()};
      ($c_id) = $dbh->selectrow_array($stm_id);
    };

    if ($@)
    {
      #Probably older version of mysql, ignore
      $c_id='UNKNOWN - PROBABLY AN OLD DB Server';
    }

    $dbh->{private_MY_HASH}->{connection_id}=$c_id;



sub DBIErrorHandler
{
  my $sql = shift;
  my $sth = shift;
  my $value = shift;

  my $dbh;
  my $ref=ref($sth);

  print STDERR "In DBIErrorHandler\n";
  print STDERR "Incoming handle is '$ref'\n";
  print STDERR Dumper(@_)."\n";

  if ($ref =~ /DBI::st$/)
  {
    #print STDERR "IS a STH\n";
    $dbh=$sth->{Database};
  }
  elsif($ref =~ /DBI::db$/)
  {
    #print STDERR "IS a DBH\n";
    $dbh=$sth;
  }
  else
  {
    print STDERR "Unable to Determine db/sth from ".
Dumper($sth);
    $dbh=$sth;
  }

  if (ref($dbh) eq 'HASH')
  {
    print STDERR "\nConnection ID is
'".$dbh->{private_MY_HASH}->{connection_id}."'\n";
  }
  else
  {
    print STDERR '\nConnection ID cannot be
established, $dbh is not a hashref';
  }

  return 0; #Return false so RaiseError is now
executed

}



 --- Joe Thomas <[EMAIL PROTECTED]> wrote: 
> Brian Dimeler wrote:
> 
> > So anyway, I recompiled mod_perl to ensure these
> kinds of handlers 
> > were enabled, restarted the server with
> $Apache::DBI::DEBUG set to 2, 
> > and sure enough, I started getting debug messages
> about rollbacks 
> > after each script finished executing. Problem is..
> they stopped, after 
> > awhile. Mysteriously, about an hour after
> restarting, the cleanup 
> > handlers either aren't running or aren't printing
> debug info anymore, 
> > and the 'transaction aborted' errors are back.
> Does anyone know what 
> > could cause this, or how I could better diagnose
> my system?
> 
> Do you ever connect to a different database from
> your mod_perl scripts 
> -- or to the same database with different options? 
> Or even use a 
> different form for the database hostname (hostname
> only vs. 
> fully-qualified domain name)?
> 
> If you do, please try this patch (inline and
> attached) against the 
> current Apache::DBI.  I believe it fixes a bug in
> rolling back 
> transactions when more than one database handle is
> used.
> 
> Joe
> 
> --- DBI.pm.orig Tue Feb 17 16:18:50 2004
> +++ DBI.pm      Tue Aug 31 14:48:58 2004
> @@ -21,7 +21,6 @@
>  my %Rollback;     # keeps track of pushed
> PerlCleanupHandler which can 
> do a rollback after the request has finished
>  my %PingTimeOut;  # stores the timeout values per
> data_source, a 
> negative value de-activates ping, default = 0
>  my %LastPingTime; # keeps track of last ping per
> data_source
> -my $Idx;          # key of %Connected and
> %Rollback.
>  
>  
>  # supposed to be called in a startup script.
> @@ -67,7 +66,7 @@
>      my $dsn    = "dbi:$drh->{Name}:$args[0]";
>      my $prefix = "$$ Apache::DBI            ";
>  
> -    $Idx = join $;, $args[0], $args[1], $args[2];
> +    my $Idx = join $;, $args[0], $args[1],
> $args[2];  # key of 
> %Connected and %Rollback.
>  
>      # the hash-reference differs between calls even
> in the same
>      # process, so de-reference the hash-reference
> @@ -96,7 +95,7 @@
>      # TODO - Fix mod_perl 2.0 here
>      if(!$Rollback{$Idx} and $needCleanup and 
> Apache->can('push_handlers')) {
>          print STDERR "$prefix push
> PerlCleanupHandler \n" if 
> $Apache::DBI::DEBUG > 1;
> -        Apache->push_handlers("PerlCleanupHandler",
> \&cleanup);
> +        Apache->push_handlers("PerlCleanupHandler",
> sub { cleanup($Idx) });
>          # make sure, that the rollback is called
> only once for every
>          # request, even if the script calls connect
> more than once
>          $Rollback{$Idx} = 1;
> @@ -155,6 +154,7 @@
>  # Note: the PerlCleanupHandler runs after the
> response has been sent to 
> the client
>  
>  sub cleanup {
> +    my $Idx = shift;
>      my $prefix = "$$ Apache::DBI            ";
>      print STDERR "$prefix PerlCleanupHandler \n" if
> $Apache::DBI::DEBUG 
>  > 1;
>      my $dbh = $Connected{$Idx};
> 
> > 
> --- DBI.pm.orig Tue Feb 17 16:18:50 2004
> +++ DBI.pm      Tue Aug 31 14:48:58 2004
> @@ -21,7 +21,6 @@
>  my %Rollback;     # keeps track of pushed
> PerlCleanupHandler which can do a rollback after the
> request has finished
>  my %PingTimeOut;  # stores the timeout values per
> data_source, a negative value de-activates ping,
> default = 0
>  my %LastPingTime; # keeps track of last ping per
> data_source
> -my $Idx;          # key of %Connected and
> %Rollback.
>  
>  
>  # supposed to be called in a startup script.
> @@ -67,7 +66,7 @@
>      my $dsn    = "dbi:$drh->{Name}:$args[0]";
>      my $prefix = "$$ Apache::DBI            ";
>  
> -    $Idx = join $;, $args[0], $args[1], $args[2];
> +    my $Idx = join $;, $args[0], $args[1],
> $args[2];  # key of %Connected and %Rollback.
>  
>      # the hash-reference differs between calls even
> in the same
>      # process, so de-reference the hash-reference 
> @@ -96,7 +95,7 @@
>      # TODO - Fix mod_perl 2.0 here
>      if(!$Rollback{$Idx} and $needCleanup and
> Apache->can('push_handlers')) {
>          print STDERR "$prefix push
> PerlCleanupHandler \n" if $Apache::DBI::DEBUG > 1;
> -        Apache->push_handlers("PerlCleanupHandler",
> \&cleanup);
> +        Apache->push_handlers("PerlCleanupHandler",
> sub { cleanup($Idx) });
>          # make sure, that the rollback is called
> only once for every 
>          # request, even if the script calls connect
> more than once
>          $Rollback{$Idx} = 1;
> @@ -155,6 +154,7 @@
>  # Note: the PerlCleanupHandler runs after the
> response has been sent to the client
>  
>  sub cleanup {
> +    my $Idx = shift;
>      my $prefix = "$$ Apache::DBI            ";
>      print STDERR "$prefix PerlCleanupHandler \n" if
> $Apache::DBI::DEBUG > 1;
>      my $dbh = $Connected{$Idx};
> 
> > -- 
> Report problems: http://perl.apache.org/bugs/
> Mail list info:
> http://perl.apache.org/maillist/modperl.html
> List etiquette:
http://perl.apache.org/maillist/email-etiquette.html 


        
        
                
___________________________________________________________ALL-NEW Yahoo! Messenger - 
all new features - even more fun!  http://uk.messenger.yahoo.com

-- 
Report problems: http://perl.apache.org/bugs/
Mail list info: http://perl.apache.org/maillist/modperl.html
List etiquette: http://perl.apache.org/maillist/email-etiquette.html

Reply via email to