We are developing a web based tool that needs access to the database for keeping track of users logging into our website. For this we used Perl independent DBI interface. We face an issue where even after calling $dbh->disconnect we still see connections to ORACLE from our Web Server remaining in ESTABLISHED state. Could you please give a clue on why this may happen? Please note that we are not useing APACHE::DBI persistent connection, since we donot need to do so at this time.
Thanks .. and Wish you a happy new year 2006.
Vivek AN
Have you established connection with RaiseError=>1 or PrintError=>1 attribute?
Reason: [BrowserUk]: Personal attack
You may view the original node and the consideration vote tally.
Reason: [BrowserUk]: racism
You may view the original node and the consideration vote tally.
Reason: [Corion]: (reap) Dupe of Reaped: Re^2: Perl Oracle Database Connections remain ESTABLISHED!! (which is also an attack and got reaped as well)
You may view the original node and the consideration vote tally.
Reason: [jZed]: purposely offensive
You may view the original node and the consideration vote tally.
Reason: [jdporter]: inflammatory troll
You may view the original node and the consideration vote tally.
Reason: [tirwhan]: Reap, troll
You may view the original node and the consideration vote tally.
Reason: [BrowserUk]: Troll
You may view the original node and the consideration vote tally.
Reason: [jdporter]: inflammatory troll
You may view the original node and the consideration vote tally.
Reason: [jZed]: purposely offensive
You may view the original node and the consideration vote tally.
Reason: [jdporter]: inflammatory troll
You may view the original node and the consideration vote tally.
Reason: [tirwhan]: Reap, same old troll
You may view the original node and the consideration vote tally.
This is the correct answer.
I tried it from the command-line. The disconnect was
almost instantaneous. The TCP connection to the Oracle
server moved from 'ESTABLISHED' to 'TIME_WAIT' state as
soon as $dbh->disconnect was called.
So am facing this problem after mod_perl integration with
my snip below.
I think mod_perl is not letting the database connection to
be torn down!! I am not using Apache DBI though, so
persistent connection concept can be ruled out.
Any more clues that I may want to take a look at?
Here's my Perl snip for reference:
use DBI;
my $dbh = 0;
sub connect_db {
if (defined $dbh) {
eval {
$dbh->ping;
};
if (!$@) {
#success return preserver database handle
return $dbh;
}
}
#ok, create a new database handle
my $retry_count = 5;
while ((!$dbh) && ($retry_count > 0)) {
$dbh = DBI->connect('dbi:Oracle:linuxdb3','scott', 'tiger');
$retry_count -= 1;
sleep(1);
}
if (!$dbh) {
# Database connection failed
return 0;
}
# Database connection OK
return $dbh;
}
sub disconnect_db {
if (defined $dbh) {
eval {
$dbh->ping;
};
if ($@) {
# Database handle invalid as ping failed
return 0;
}
}
# Disconnect Database connection
$dbh->disconnect;
return 1;
}
--
Thanks,
Vivek AN
The eval check around $dbh->ping is pointless, DBD::Oracle does it's own eval internally. That subroutine seems weird anyhow, because you first check for definedness of $dbh, then try to call disconnect on it regardless of whether it was defined or not, then return 1 regardless of whether the disconnect was successful or not.
Not sure that any of this will help with your actual problem, but I thought I'd point it out.
A computer is a state machine. Threads are for people who can't program state machines. -- Alan Cox
Your problem is that you've declared $dbh outside of your subroutine, but reference it inside your subroutine. You should pass it into the subroutine instead. Have a look at this mod_perl doco
(P.S. If you don't wnat to pass you database handle around, just connect and disconnect in the main trunk of your code)
#!/usr/local/bin/perl -w
use DBI;
use strict;
use diagnostics;
sub emulate_sub {
my $dbh;
sub connect {
$dbh = DBI->connect('dbi:Oracle:rdb1', 'scott', 'tiger') ||
die DBI::errstr;
}
}
I suspect that there's there's a copy with a connection, but the diconnect is not available.
UPDATE: I was updating the thread while perrin was replying. It probably won't affect what he says, as his answer still applies to the updated version
Incidentally, your connect_db() sub will always sleep for at least 1 second, even if it connects right away. That's probably not what you want.
Ultimately, I recommend you get rid of this code and use $dbh->connect_cached() instead. It is similar to Apache::DBI, but doesn't prevent disconnects.
Reason: [jdporter]: inflammatory troll
You may view the original node and the consideration vote tally.
Reason: [graff]: reap - more useless trolling
You may view the original node and the consideration vote tally.
Reason: [jdporter]: inflammatory troll
You may view the original node and the consideration vote tally.
Reason: [planetscape]: Reap, inflammatory, personal attack
You may view the original node and the consideration vote tally.