scope and undef

[Ryszard]

I have a daemon process which runs every n minutes. The daemon is persistent in memory and is executed via a while loop.

During each iteration of the main code body, it must connect to a database, grab some data and do something with it (the details are not important for this discussion).

i was contacted this morning by our DBAs wrt to my application having 200 connections to the database, so i went and checked the code to see if i could find the connection leak.

to my utter surprise it was due to an undef statement.

to illustrate the way the daemon is created:

my $app = Daemon->new(sleep => 60, mailLimit => 200);

while (1) {
    $app->doSomething();
    print scalar(localtime)." Sleeping 300 seconds...\n";
    sleep 300;
}

inside the Daemon object is a call to create a database object (which creates the db connection) and assigns it to $self->{dbo}

the code i use to create the database object checks for the existence of $self->{dbo}, and if it exists re-uses it, if not, creates it.

to force the creation of a new database object each time (for reasons i cant remember now - yes i should have used comments in the code :-) ) i did an undef on the $self->{dbo} variable.

the expected result is that once i undef the $self->{dbo} variable, it would fall out of scope and the database connection would be implicitly released.

in practice this is not the case. it seams that once the variable has been undef'd it is left dangling in memory and not cleaned up.

while this seems pretty black and white based on what i've observed, as mentioned, it was totally unexpected.

can anyone explain why?

[atemon]

Existance of a circular reference can cause this problem. i.e. even if you undef $self->{dbo}, there exist some reference to same object. So its not removed from memory.

Here is a very simple problematic case for circular reference.

    foreach (1..5) {  
        my $a;  
        my $b;  
        $a->{b} = $b;  
        $b->{a} = $a;  
    } # since both are pointing to each other they will never get coll
+ected.

Please check your code for any circular reference.

you may try disconnecting the database handle just before you undef the $self->{dbo} as an immediate fix.

Cheers !

--VC

---


There are three sides to any argument.....
your side, my side and the right side.

[lodin]

An aid in finding any circular references could be Devel::Cycle.

lodin

[bart]

VC has a point, it might involve a circular reference, the phrase "a call to create a database object (which creates the db connection) and assigns it to $self->{dbo}" surely sound circular-referency. Could you try using delete instead of undef? Maybe it helps.

However, deep in my heart, I don't really believe it, this has the fault smell of a bug in Perl.

For debugging purposes, to see if any attempt is being made to clean up the object, you can add print statements in a DESTROY handler — use Hook::LexWrap or another similar module if one already exists and you don't want to edit the source; or subclass the class of the database connection.

[Ryszard]

cool bart, and thanks for the reply. I'll show a bit more detail into what i'm doing which hopefully will make what i see with Hook::LexWrap a little clearer to you.

my $app = Daemon->new(sleep => 60, mailLimit => 200);

while (1) {
    $app->mailout();
    print scalar(localtime)." Sleeping 300 seconds...\n";
    sleep 10;
}

is how the Daemon is called.

inside Daemon is a call to an object i've made myself to manage database connections, which you can find here, which is not particularly complicated, or fancy, just an abstraction to the DBI which i find handy. we'll call this DBhandler. on creating a Daemon object, DBhandler is put into $self->{dbo}

Since Daemon is persistant, i wrapped DESTROY in DBhandler, and told it to print Dumper($self).

This is where i see things that are contradictory. when i undef $self->{dbo} DESTROY is called in DBhandler, and i see $self being dumped to standard out, however upon checking the sessions in the database (ie via v$session) i see the number of connections from that user increasing!

oh, for the record, the delete, while not tested in this specific context, (ie with Hook::LexWrap), symptoms are the same.

[eric256]

In that code you do a foreach on a hashref. I don't think that destroy is actualy looping at all. Shouldn't it be foreach (keys %{$self->{_db_handle}})?

sub DESTROY {
       my $self = shift;
       foreach ($self->{_db_handle}) {
           $self->{_db_handle}{$_}->disconnect;
       }
   }

[Anonymous Monk]

You can try an explicit disconnect instead of simply assigning undef to the "database object". I assume you use DBI:

$rc = $dbh->disconnect  or warn $dbh->errstr;

I would recommend you to include which database engine you are using, what modules you use to access it, and a small code sample that illustates your problem.

--

[Ryszard]

thanks for your answer, however, my initial question isnt about the database per se. the database object is just a symptom of the actual problem about why the object is not collected when it "goes out of scope" using the undef.

removal of the undef statement fixed the problem, i now have a single persistent database connection.

[girarde]

Assuming DBI, a disconnect would solve the problem, without necessarily answering your underlying question, I know.

I think Bart is on to something in that you want a delete rather than an undef, because the former removes the dbo key from the tied hash that is the $self object, while the latter leaves it there with an undefined value.

[Rhandom]

At first I thought that it could be a circular ref problem, but then I read his description and that isn't what it sounds like. I would first go with the ->disconnect suggestion. I would then peruse through the code to look to see what other methods the dbh could be getting passed to. Undefing {dbo} or deleting it won't cause a copied reference elsewhere to get DESTROYED. I would guess another object or data structure contains a copy of the dbh - so that you will not be able to rely on implicit object destruction.

You could always call $self->{dbo}->DESTROY but I'm not sure that is best practice. I'd stick with the ->disconnect.

[pilcrow]

the expected result is that once i undef the $self->{dbo} variable, it would fall out of scope and the database connection would be implicitly released.
in practice this is not the case. it seams that once the variable has been undef'd it is left dangling in memory and not cleaned up.

What does DBI->trace reveal? That will show whether the underlying DBI handles are themselves actually destroyed or not. IIRC, the DBD::Oracle driver under high tracing will dump OCI calls -- so you should see DESTROYs followed by OCISessionEnd (I think) on successful implicit disconnects.

Is InactiveDestroy set to true on the DBI handles?

Does netstat/lsof/whatever confirm that your process has redundant DB connections open?

Your suspicion may be correct, but the symptoms are also consistent with at least a bug holding on to DBI handles, undesirable "Activeness" attributes, or a faulty proxy (circuit or DB-aware) between the daemon and the DB.

[Ryszard]

Thanks to everyone who has replied. After a nights rest and fresh eyes, i've found what is causing the problem.

a difference in the DBhandler module i'm using to the one posted here is the ability for it to use multiple dbd's. i've added support for both oracle and mysql.

the $self reference that holds the actual db reference becomes $self->{_db_handle}{$arg{engine}}{$arg{handle}}.

when i've gone about disconnecting the database connection in the destructor i've ommited the engine variable.

if i perform an explicit disconnect in the DESTROY method, correctly the database connection is dropped when the object goes out of scope (ie with a delete or an undef. the reverse is also true, if you dont perform an explicit disconnect when the object goes out of scope, the database connection remains, which i think could be considered a bug in the DBI/DBD/perl/whatever.

to those who have spent their valuable time on this, i apologise, i've lead you down the garden path, making reference to code that is different to what i've been actually using. lesson learnt.

to those who would like to have a crack at reproducing the bug, this is what i used below.

#!/usr/local/bin/perl -w

use Daemon;

my $app = Daemon->new();

while (1) {
    $app->do_something();
    print scalar(localtime)." Sleeping 5 seconds...\n";
    sleep 5;
}

-------8<--------------------------------

package Daemon;

use Data::Dumper;

use Handle;

{

    sub _createConnection {

        my $self = shift;
        my %args = @_;
        my $retval;

        $self->{dbo} = Handle->new( handle => 'handle', user   => 'jwi
+lliams', pwd => 'type895', sid => 'ossp1',) if (! $self->{dbo} );

    }
}

sub new {
    my ($caller, %arg) = @_;
    my $caller_is_obj = ref($caller);
    my $class = $caller_is_obj  || $caller;
    my $self  = bless {}, $class;


    return $self;
}

sub do_something {

    my $self = shift;
    my %args = @_;

    $self->_createConnection();
    undef $self->{dbo};

    return 'status message';
}
1

-------8<--------------------------------

package Handle;

use Data::Dumper;

$VERSION = 1.00;
use strict;
use DBI;
use Carp;
use Hook::LexWrap;

{

    sub _get_db_handle {

        my ($self, %args) = @_;
        croak "Must supply username" unless ($args{user});
        croak "Must supply password" unless ($args{pwd});
        croak "Must supply SID"      unless ($args{sid});

        my $dsn  = "DBI:Oracle:".$args{sid};           #set the datase
+t name
        my %attr = ( RaiseError => 1, PrintError => 0, AutoCommit => 1
+ || $self->{_AutoCommit} ); #set error raising and printing

        # lets go get the handle
        my $handle = DBI->connect($dsn,$args{user},$args{pwd}, \%attr)
+;
        $handle->{InactiveDestroy} = 1;

        croak "Unable to connect to ".$args{sid} unless ($handle);
        return $handle;
    }


}

sub new {
    my ($caller, %arg) = @_;
    my $caller_is_obj = ref($caller);
    my $class = $caller_is_obj  || $caller;
    my $self  = bless {}, $class;

    $arg{engine} = 'oracle' if (!defined $arg{engine} );
    $self->{_db_handle}{$arg{engine}}{$arg{handle}} = $self->_get_db_h
+andle(%arg);

    wrap 'DESTROY', pre => \&predestroy, post=> \&postdestroy;

    return $self;
}

sub predestroy {
    print STDERR "predestroy: ".Dumper(@_);
}

sub postdestroy {
    print STDERR "postdestroy: ".Dumper(@_);
}


sub DESTROY {
    my $self = shift;
    foreach my $handle ( keys %{$self->{_db_handle}{'oracle'}} ) {

       # non bug condition, the object is correctly referenced
       #$self->{_db_handle}{'oracle'}{$handle}->disconnect;

       # bug condition, the object is incorrectly referenced
       $self->{_db_handle}{$handle}->disconnect;
    }
}
1

-------8<--------------------------------

SQL> l
  1  select username, to_char(logon_time, 'dd-mon-yyyy hh24:mi:ss') as
+ logon from v$session
  2* where username = '<username>'
SQL>

[girarde]

Quelle moment d'éscalier. I almost recommended undefing the whole $self object between runs, which would have solved your problem while also obscuring the root cause.

Just as well I didn't.