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]

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.

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)

can you show a bit more code?

This is the correct answer.