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