Re: Memory leak in DBI ?

I'm sorry for the delay replying.

On Thu, Apr 10, 2008 at 05:00:57PM +0200, Philippe Bruhat (BooK) wrote:
> On Thu, Apr 10, 2008 at 03:40:55PM +0100, Tim Bunce wrote:
> > I trust the script is destroying the results and, where appropriate,
> > the $sth before calling Devel::Leak::CheckSV.
> 
> Yes.
> 
> The command is run in a block, with lexicals (except the $dbh).
> To avoid counting information that is retained by the DBI, the command
> is run twice, and the leak count is done on the second run only.

> Here it is:

My results for DBI 1.604 with perl v5.8.6 built for darwin-thread-multi-2level
using Text::CSV_XS 0.45 and DBD::SQLite 1.13

ok 1 -  	leak= 0 for csv     my $sanity = 1;
ok 2 -  	leak= 0 for csv     my $s = $dbh->trace(0) 
not ok 3 - 	leak= 9 for csv     my $s = $dbh->prepare($sql) 
not ok 4 - 	leak= 1 for csv     my $s = $dbh->prepare($sql); $s->execute(); 
not ok 5 - 	leak= 2 for csv     my $s = $dbh->prepare( $sql ); $s->execute(); 1 while $s->fetchrow_arrayref(); $s->finish;
not ok 6 - 	leak= 1 for csv     my $s = $dbh->prepare_cached( $sql ); $s->execute(); 1 while $s->fetchrow_arrayref(); $s->finish;
not ok 7 - 	leak= 2 for csv     my $c = $dbh->selectcol_arrayref( $sql, { Column => [1] } )
not ok 8 - 	leak= 1 for csv     my @a = $dbh->selectrow_array( $sql )
not ok 9 - 	leak= 2 for csv     my $a = $dbh->selectrow_arrayref( $sql )
ok 10 - 	leak= 0 for sqlite  my $sanity = 1;
ok 11 - 	leak= 0 for sqlite  my $s = $dbh->trace(0) 
not ok 12 - 	leak= 1 for sqlite  my $s = $dbh->prepare($sql) 
not ok 13 - 	leak= 1 for sqlite  my $s = $dbh->prepare($sql); $s->execute(); 
not ok 14 - 	leak= 1 for sqlite  my $s = $dbh->prepare( $sql ); $s->execute(); 1 while $s->fetchrow_arrayref(); $s->finish;
ok 15 - 	leak= 0 for sqlite  my $s = $dbh->prepare_cached( $sql ); $s->execute(); 1 while $s->fetchrow_arrayref(); $s->finish;
not ok 16 - 	leak= 1 for sqlite  my $c = $dbh->selectcol_arrayref( $sql, { Column => [1] } )
not ok 17 - 	leak= 1 for sqlite  my @a = $dbh->selectrow_array( $sql )
not ok 18 - 	leak= 1 for sqlite  my $a = $dbh->selectrow_arrayref( $sql )

So using DBD::CSV appears to leak rather more than using DBD::SQLite.
(It's odd that for DBD::CSV calling just prepare leaks more than calling
prepare then execute.)

The leak in DBD::SQLite appears to relate to the prepare() method and
appears to be a scalar containing the sql statement.
It's possible that Devel::Leak is being confused by the DBI keeping a
reference (alias) to the statement in the $dbh->{Statement} attribute.

Tim.