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
