On Oct 22, 2008, at 10:39 AM, Michael Peters wrote:
It will probably reconnect if the handle isn't there. But not if it
goes bad. I don't know for sure since I don't use that plugin. But
it's a common problem when dealing with persistant DB connections.
If you want to see how mod_perl folks handle, take a look at
Apache::DBI. Look at it's connect() method and particularly how it
uses ping().
I am sure are all dying for an update.
1) Unable to turn on DBI_TRACE within perl ($ENV{DBI_TRACE} = 2=/path/
to/file). I have had this problem in the past with other scripts, I
suppose I will have to fiddle around more with this.
2) The error is here: DBD::Oracle::st execute failed: ORA-01012: not
logged on (DBD ERROR: OCIStmtExecute) and it will occur after the
IDLE time of the user is exceeded. This was tested with a special user
with 10M idle time.
My review of cgiapp::Plugin::DBH and Apache::DBI
Turns up the following:
my code:
$self->dbh_config(sub { DBI->connect_cached(..) });
Plugin::DBH
(sub dbh)
...
unless( defined($self->{__DBH}{$name}) && $self->{__DBH}{$name}-
>ping ) {
# create DBH object
if(my $config = $self->{__DBH_CONFIG}{$name} ) {
# Use a callback
if (ref $config eq 'CODE') {
$self->{__DBH}{$name} = $config->();
}
# use the parameters the user supplied
else {
require DBI;
$self->{__DBH}{$name} = DBI->connect(@{ $self-
>{__DBH_CONFIG}{$name} });
}
} else {
}
}
sub connect_cached in DBI.pm:
if ($dbh && $dbh->FETCH('Active') && eval { $dbh->ping }) {
# If the caller has provided a callback then call it
if ($cb and $cb = $cb->{"connect_cached.reused"}) {
local $_ = "connect_cached.reused";
$cb->($dbh, $dsn, $user, $auth, $attr);
}
return $dbh;
}
# If the caller has provided a callback then call it
# ... not using Callbacks so deleted for brevity
$dbh = $drh->connect(@_);
$cache->{$key} = $dbh; # replace prev entry, even if connect
failed
return $dbh;
Apache::DBI essentially reimplements connect_cached with a ping only
when necessary:
# do we need to ping the database ?
$PingTimeOut{$dsn} = 0 unless $PingTimeOut{$dsn};
$LastPingTime{$dsn} = 0 unless $LastPingTime{$dsn};
my $now = time;
my $needping = (($PingTimeOut{$dsn} == 0 or $PingTimeOut{$dsn} > 0)
and (($now - $LastPingTime{$dsn}) >= $PingTimeOut{$dsn})
) ? 1 : 0;
print STDERR "$prefix need ping: ", $needping == 1 ? "yes" :
"no", "\n" if $Apache::DBI::DEBUG > 1;
$LastPingTime{$dsn} = $now;
# check first if there is already a database-handle cached
# if this is the case, possibly verify the database-handle
# using the ping-method. Use eval for checking the connection
# handle in order to avoid problems (dying inside ping) when
# RaiseError being on and the handle is invalid.
if ($Connected{$Idx} and (!$needping or eval{$Connected{$Idx}-
>ping})) {
print STDERR "$prefix already connected to '$Idx'\n" if
$Apache::DBI::DEBUG > 1;
return (bless $Connected{$Idx}, 'Apache::DBI::db');
}
# either there is no database handle-cached or it is not valid,
# so get a new database-handle and store it in the cache
delete $Connected{$Idx};
$Connected{$Idx} = $drh->connect(@args);
return undef if !$Connected{$Idx};
# return the new database handle
print STDERR "$prefix new connect to '$Idx'\n" if
$Apache::DBI::DEBUG;
return (bless $Connected{$Idx}, 'Apache::DBI::db');
I note that in the Plugin::DBH code there is no eval{} around the
ping... I will probably try that.
First I am going to try what I have with just connect() instead of
connect_cached.. I am wondering if the dual layer of $dbh checking is
causing system to get confused. I agree oracle connections are
slow... but the FastCGI version of this page takes 3 seconds WHEN
CACHED... so I am not really worried about it (will profile it though)
Ben
--
Ben Hitz
Senior Scientific Programmer ** Saccharomyces Genome Database ** GO
Consortium
Stanford University ** [EMAIL PROTECTED]
##### CGI::Application community mailing list ################
## ##
## To unsubscribe, or change your message delivery options, ##
## visit: http://www.erlbaum.net/mailman/listinfo/cgiapp ##
## ##
## Web archive: http://www.erlbaum.net/pipermail/cgiapp/ ##
## Wiki: http://cgiapp.erlbaum.net/ ##
## ##
################################################################
