in reply to Re: Perl Oracle Database Connections remain ESTABLISHED!!
in thread Perl Oracle Database Connections remain ESTABLISHED!!

Thanks Perrin, for your valuable inputs.

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. <this same snip works well on command-line>.
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', 'ti
+ger');
                $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;
}

[download]

--
Thanks,

Vivek AN

Replies are listed 'Best First'.
Re^3: Perl Oracle Database Connections remain ESTABLISHED!!
by perrin (Chancellor) on Jan 03, 2006 at 14:43 UTC
    Have you tried use DBI_TRACE or putting in a warn statement to see if the disconnect() really gets called? Maybe the ping above it is failing and causing it to get skipped.

    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.

[reply]
[d/l]
Re^3: Perl Oracle Database Connections remain ESTABLISHED!!
by tirwhan (Abbot) on Jan 03, 2006 at 08:04 UTC

    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
[reply]
[d/l]
[select]
Re^3: Perl Oracle Database Connections remain ESTABLISHED!!
by astroboy (Chaplain) on Jan 03, 2006 at 08:17 UTC

    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)

[reply]
      That would cause a scoping problem, but wouldn't prevent an actual disconnect() call from working.
[reply]
        It's may not be just a scoping problem. Apache::Registry will cache the script inside a subroutine, similar to 
        #!/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;
    }
}

        [download]
        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
[reply]
[d/l]
Re^3: Perl Oracle Database Connections remain ESTABLISHED!!
by astroboy (Chaplain) on Jan 04, 2006 at 13:45 UTC
    can you show a bit more code?
[reply]

In Section Seekers of Perl Wisdom