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

I use MySQL with the DBI Module for my perl programming database connections.

I see this all over the error_log that apache generates...

[Sun Nov 15 02:59:17 2009] [error] [client 0.0.0.1] DBI::db=HASH(0xc40
+d5a0)->disconnect invalidates 2 active statement handles (either dest
+roy statement handles or call finish on them before disconnecting) at
+ /usr/lib/perl5/site_perl/5.8.8/Apache/Session/Lock/MySQL.pm line 90.
[Sun Nov 15 02:59:20 2009] [error] [client 0.0.0.1] DBI::db=HASH(0xb1a
+def0)->disconnect invalidates 2 active statement handles (either dest
+roy statement handles or call finish on them before disconnecting) at
+ /usr/lib/perl5/site_perl/5.8.8/Apache/Session/Lock/MySQL.pm line 90.

[download]


I always call $sth->finish() on every statement where I do a prepare statement on the database: 
$sth = $dbh->prepare(qq{select * from `table`});
$sth->execute();
my $_var = $sth->fetchrow_hashref();
$sth->finish(); #<--- like this here...

[download]
however when I just pull a single Item I don't call any kind of finish: 
my $_var = $dbh->selectrow_array(qq{select `column` from `table` where
+ `something` = ?}, undef, "somethingelse");
# Done getting $_var no database connection right, to close right?

[download]
at the end of the page loading I call a close to the html page which does this for every page load: 
if($sess_ref) {
$sess_ref->close(); # If there is a session open, close it
}
if($dbh) {
$dbh->disconnect();
}
print end_html();

[download]
The close command that the sess_ref uses is here:

sub close
{
my $self = shift;
    untie (%{$self});
}

[download]

So there is a close to the apache mysql session and a close to the database connection that is using DBI...
So I don't know how there are anything open that is causing a active statement handle to be left open...

Any idea why this happens?
I'd appreciate any feedback.

Thanks,
Rich

Replies are listed 'Best First'.
Re: DBI disconnect database errors
by ikegami (Patriarch) on Nov 16, 2009 at 05:33 UTC
    Just ruling out one possible cause:

    however when I just pull a single Item I don't call any kind of finish

    That's not the problem. selectrow_* calls finish.

[reply]
[d/l]
[select]
      Yeah, I expected that, but was not sure...

      What if I just do not call the close and disconnect and let perl do it when it exits? Would that cause problems?

      Thanks,
      Rich
[reply]
        Perl would implicitly close/disconnect the database handle on exit (or, more accurately, when the handle goes out of scope, which would be prior to program exit if it were lexical to a block) and produce the same errors. The messages would, IIRC, be worded a little differently, but that's pretty much all that would change.
[reply]
[d/l]
[select]
Re: DBI disconnect database errors
by EvanCarroll (Chaplain) on Nov 16, 2009 at 06:54 UTC
    Upgrade Apache::Session, your version is old. This was reported in ticket rt://32148 fixed in 1.85_01 and noted in the CHANGES per CPAN convention. Also, for the love of god don't call finish on handles which you intend to read all the data from (it will be called for you). Please review the DBI docs on this.


    Evan Carroll
    The most respected person in the whole perl community.
    www.evancarroll.com
[reply]
[d/l]
      I got this code from Paul, the author of Perl and MySQL for the web...

      I did scan the DBI module documentation, and do see the finish calls are different than what I do...

      However, I am not reading from the database while it is connected, I store the data in a hashref: 
      $sth = $dbh->prepare(qq{select * from `table`});
$sth->execute();
my $_somevar = $sth->fetchrow_hashref();
$sth->finish();

      [download]
      or: 
      $sth = $dbh->prepare(qq{select * from `table`});
$sth->execute();
while(my $_somevar = $sth->fetchrow_hashref()) {
my $_someid = $_somvar->{primarykeyfield};
$sth2 = $dbh->prepare(qq{select * from `table1` where `primarykeyfield
+` = ?});
$sth2->execute($_someid);
$_thisvar = $sth2->fetchrow_hashref();
$sth2->finish();
# now I have data from table in $_somvar
# and data from table1 in $_thisvar
}
$sth->finish();

      [download]
      So, I call finish on them after I'm done reading from there, but not before. That way I don't leave any connections open, is that not correct?

      Thanks,
      Rich
[reply]
[d/l]
[select]

        If you want to track down which handles DBI is warning about see visit_handles which would allow you to loop through them and see which are Active and what SQL they are running.

[reply]

        OK, being able to look at that code, I wold explicitly NOT to do that. I would not explicitly prepare something for a single select, use selectrow_hashref instead, and put in the sql an explicit `LIMIT 1`. Also, using '*' should be reserved, when you want all the rows in the table. This should hold true if one of those rows is a 50 meg block of text.

        Also, in your second example you could just as well do that in one query right? Through a join. This saves work on the DB. 

        $dbh->selectrow_hashref(
    'SELECT t1.* FROM table AS t0'
    . ' JOIN table1 AS t1'
    . ' USING ( primarykeyfield )'
);

        [download]
        Or, if you wanted precisely the same as above, which will render the row from table1 random because the lack of a sort order on table, you can still do that in one statement 
        $dbh->selectrow_hashref(
    'SELECT t1.* FROM table AS t0'
    . ' WHERE t1.primarykeyfield IN ( SELECT primarykeyfield FROm tabl
+e LIMIT 1 )
);

        [download]



        Evan Carroll
        The most respected person in the whole perl community.
        www.evancarroll.com
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom