Steve_BZ has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,

There seems to be a memory leak in DBD::Firebird (or one of its dependencies). Every call after the first one seems to leak 1 Scalar Value (SV).

Here is a code sample which will create a table, query it 6 times and drop it.

I have upgraded to the latest version of DBD::Firebird and I'm on Kubuntu 14.04 lts with Firebird 2.5.

Update: Thanks to sierpinski for pointing out that there was no question here. Has anyone seen this before? I imagine it might be the driver, but I guess it could be Firebird too. I'd like to see where the problem is. If it's fixable, we should fix it, otherwise I'll need to workaround it. Suggestions welcome.

Here is the code, you just need to create an empty database file (I use Flamerobin for this) and modify the dsn in the code appropriately.

Thanks for your help

Code follows

Regards

Steve 

#! /usr/bin/perl

package main;
use strict;
use warnings;
use DBI;
use Devel::Leak;
    my $handle;
    my $count_start;
    my $count_stop;
    my $gl_dbh;
    
    # Just do this 5 times to make sure there is no contribution to $h
+andle count from Devel::Leak
    for (1..10){
    print "Handle init: ", Devel::Leak::NoteSV($Launch::handle),"\n";
    }
my $loc_dsn = <<DSN;
dbi:Firebird:dbname=/home/image/Documents/Endoscopia/DB/endoNew.fdb;
ib_dialect=3;
DSN
    $Launch::gl_dbh=DBI->connect($loc_dsn,"SYSDBA","masterkey", {
        PrintError => 1,                    # Report errors via warn
        RaiseError => 1                    # Report errors via Die
    }
    ) or die;

    my @loc_sql_string =();
    $loc_sql_string[0]="CREATE TABLE TBL_TEST_LEAK (  ATTR_RECORD_ID_T
+XT VARCHAR(10) NOT NULL, ATT_RECORD_NAME_TXT VARCHAR(255), CONSTRAINT
+ PK_TBL_TEST_LEAK PRIMARY KEY (ATTR_RECORD_ID_TXT) ); ";
    $loc_sql_string[1]="GRANT DELETE, INSERT, REFERENCES, SELECT, UPDA
+TE ON TBL_TEST_LEAK TO  SYSDBA WITH GRANT OPTION";
    $loc_sql_string[2]="INSERT INTO TBL_TEST_LEAK (ATTR_RECORD_ID_TXT,
+ ATT_RECORD_NAME_TXT) VALUES ('206', 'Delay Test 1' )";
    $loc_sql_string[3]="select ATT_RECORD_NAME_TXT from TBL_TEST_LEAK;
+ ";                                
    $loc_sql_string[4]= $loc_sql_string[3];                           
+     
    $loc_sql_string[5]= $loc_sql_string[3];                           
+     
    $loc_sql_string[6]= $loc_sql_string[3];                           
+     
    $loc_sql_string[7]= $loc_sql_string[3];                           
+     
    $loc_sql_string[8]= $loc_sql_string[3];                           
+     
    $loc_sql_string[9]="drop table TBL_TEST_LEAK; ";                  
+      

    for (my $i=0;$i<=9;$i++){
    $count_start=Devel::Leak::NoteSV($Launch::handle);
    print "DBD start: ", $count_start,"\n";
    print $loc_sql_string[$i], "\n";
    dbd_select($loc_sql_string[$i]);
    # You can use 
    #$count_stop=Devel::Leak::CheckSV($Launch::handle);
    $count_stop=Devel::Leak::NoteSV($Launch::handle);
    print "Handle stop: ", $count_stop,"\n";
    print "Count difference: ", $count_stop-$count_start,"\n";
    }
    $Launch::gl_dbh->disconnect;
    
sub dbd_select{
    my $loc_sql_string=shift;
    my $loc_sth=$Launch::gl_dbh->prepare($loc_sql_string) or die; 
    $loc_sth->execute() or die;
    $loc_sth->finish();
    return;
}

1;

[download]

Replies are listed 'Best First'.
Re: Memory leaks in DBD::Firebird
by sierpinski (Chaplain) on May 22, 2015 at 16:45 UTC
    What is your question? Are you looking for a workaround, or for someone to fix the bug, or to validate your code, or what?
[reply]

      The more I investigate this issue, the more your question has become a methodology to guide my endeavours. I started with checking my code, I wrote to the author and now I am working back through my prioritised workarounds.

      Thanks.

[reply]
Re: Memory leaks in DBD::Firebird
by Anonymous Monk on May 22, 2015 at 21:20 UTC
[reply]
Re: Memory leaks in DBD::Firebird
by Steve_BZ (Chaplain) on May 23, 2015 at 12:20 UTC

    So if it is not possible to fix the DBD::Firebird, workarounds might be (most attractive first):

    1. Use odbc (there used to be an odbc driver called libOdbcFb.so) - I hope I wouldn't need to change the version of FIrebird;
    2. Revert to DBD::InterBase, I don't know that it works with Firebird 2.5 and I might have to revert to Firebird 2.1;
    3. The least attractive option would be to change database to another engine and convert the schema and database.

    What others might there be and what views do you have on these ones?

    Regards

    Steve.

[reply]
Re: Memory leaks in DBD::Firebird [SOLVED]
by Steve_BZ (Chaplain) on May 26, 2015 at 14:06 UTC

    Hi

    Here is Tim Bunce's reply:

    Note that the ChildHandles array holds weak references and that 'from time to time' the old slots get freed up. This isn't a leak, it just appears to be if you're not familiar with the caching that DBI does internally. You can rest assured that if the DBI did have a real leak a) a great many people would be affected and b) it would get fixed very quickly. I think 'from time to time' is every 120 or so newly created child handles.

    Changing the looping of the test code to (1..500) neatly illustrates this.

    Regards

    Steve

[reply]

      Thanks Steve for providing the update here.

[reply]
Re: Memory leaks in DBD::Firebird
by Tux (Canon) on May 26, 2015 at 16:26 UTC

    ++ to your perseverance. It is now a section in the DBI documentation.


    Enjoy, Have FUN! H.Merijn
[reply]
Re: Memory leaks in DBD::Firebird
by Steve_BZ (Chaplain) on May 24, 2015 at 19:34 UTC

    Ok, now for the bad news, ODBC gives the same result, (do ODBC and DBD::Firebird use the same driver?): 

    
my $loc_dsn = <<DSN;
dbi:ODBC:Driver=Firebird;Dbname=/home/image/Documents/Endoscopia/DB/ne
+wEndo.fdb;
ib_dialect=3;
DSN

    [download]

    Gives: 

    
select ATT_RECORD_NAME_TXT from TBL_TEST_LEAK; 
Handle stop: 52407
Count difference: 1
DBD start: 52407
select ATT_RECORD_NAME_TXT from TBL_TEST_LEAK; 
Handle stop: 52408
Count difference: 1
DBD start: 52408
select ATT_RECORD_NAME_TXT from TBL_TEST_LEAK; 
Handle stop: 52409
Count difference: 1
DBD start: 52409
select ATT_RECORD_NAME_TXT from TBL_TEST_LEAK; 
Handle stop: 52410
Count difference: 1

    [download]

    So maybe I need to back up a version!!

    I'll try Firebird 2.1 next.

[reply]
[d/l]
[select]
      Ok, now for the bad news, ODBC gives the same result, (do ODBC and DBD::Firebird use the same driver?):

      No, DBD::ODBC and DBD::Firebird are completely different things. But they share common code from DBI. Perhaps you should post your problem on the dbi-users mailing list.

      Alexander

      --
      Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
[reply]

        Thanks. I'll try that.

[reply]
Re: Memory leaks in DBD::Firebird
by Steve_BZ (Chaplain) on May 26, 2015 at 10:52 UTC

    I found a general DBI memory leak solution here: http://stackoverflow.com/questions/13338308/perl-dbi-memory-leak

    The answer seems to be the 2 lines: 

        print Dumper(  $DBI::lasth  );
    $DBI::lasth ->{ChildHandles} = []; # <-- reset leaking data struct
+ure

    [download]

    but a) it doesn't work without the first print Dumper line. Why is that? And b) it seems to be a bit sporadic. Sometimes it works and sometimes it doesn't.

    Any thoughts?

    I'll update you when I know more.

    Regards

    Steve

[reply]
[d/l]
[select]
Re: Memory leaks in DBD::Firebird
by Steve_BZ (Chaplain) on May 26, 2015 at 09:58 UTC

    Amazingly I am still finding leaks even in in dbi::mysql.

    I find it hard to believe there are leaks in such an established piece of code.

    I have logged a query on the dbi user list, but I have to ask, am I testing this correctly? Are you getting the same results as me? It's very strange.

[reply]

Back to Seekers of Perl Wisdom