Why does Apache complain that my mod_perl program "disconnect invalidates 1 active statement handle"?

Question

disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting)

The following code which grabs data from MySQL gets executed successfully, but will cause Apache to generate the above message in its error log:

my $driver   = "mysql";
my $server   = "localhost:3306";
my $database = "test";
my $url      = "DBI:$driver:$database:$server";
my $user     = "apache";
my $password = "";

#Connect to database
my $db_handle = DBI->connect( $url, $user, $password ) 
    or die $DBI::errstr;

#SQL query to execute
my $sql = "SELECT * FROM tests WHERE id=?";

#Prepare SQL query
my $statement = $db_handle->prepare($sql)
        or die "Couldn't prepare query '$sql': $DBI::errstr\n";

#Execute SQL Query
$statement->execute($idFromSomewhere)
    or die "Couldn't execute query '$sql': $DBI::errstr\n";

#Get query results as hash
my $results = $statement->fetchall_hashref('id');

$db_handle->disconnect();

• Will there be any dire consequences by ignoring the said error/warning? The code has been running for a week without any ill effects.

• Is there anything wrong with the code or is this just a harmless warning?

Edit

Code is executed via mod_perl.

Mauretta

Hey Brian, thanks for editing the title. I read all your books before. – Mauretta

Answer 1

You should call $statement->finish(); before $db_handle->disconnnect();.

Normally you don't need to call finish, unless you're not getting all the rows. If you get all the results in a loop using fetchrow_array, you don't call finish at the end unless you aborted the loop.

I'm not sure why the MySQL driver isn't finishing the statement after a fetchall_hashref. The manual suggests that your query might be aborting due to an error:

If an error occurs, fetchall_hashref returns the data fetched thus far, which may be none. You should check $sth->err afterwards (or use the RaiseError attribute) to discover if the data is complete or was truncated due to an error.

Anesthetic

Thanks, that did the trick. Although reading the O'Reilly DBI book and the Perl documentation suggest otherwise. Solved in 16 minutes! Go stackoverflow! – Mauretta

Thanks for answering the "why" part of my question Paul. ;-) You deserve your 15k of karma. – Mauretta

The data is fine. It's been pounded by about 1K people continuously for a week. Yes, the documentation says so. Anyway, it could just be the MySQL driver. After calling finish() the warnings stopped appearing. I'm going to upgrade to the latest build of DBI and MYSQL and see what happens. – Mauretta

Did you try printing out $statement->err after the fetchall_hashref? – Anesthetic

Not yet. Will post an update if anything interesting happens. – Mauretta

Answer 2

This is caused by the handle still being active. Normally it should close itself though, but you don't seem to be fetching all the data from it. From the perldoc on DBI:

When all the data has been fetched from a SELECT statement, the driver should automatically call finish for you. So you should not normally need to call it explicitly except when you know that you've not fetched all the data from a statement handle. The most common example is when you only want to fetch one row, but in that case the selectrow_* methods are usually better anyway. Adding calls to finish after each fetch loop is a common mistake, don't do it, it can mask genuine problems like uncaught fetch errors.

Creeps

But he's calling fetchall_hashref - that's supposed to get all the results! – Anesthetic

good point, didn't catch that. The only way to know for sure is check the errors on fetchall I guess. – Creeps

Answer 3

Although probably not the reason you got this warning (which is what the manual claims that it is), I experienced the same warning in slightly different circumstances and wanted to suggest it here rather than opening my own question.

You may find yourself in this scenario if you perform a query to fetch some rows -- but only for the intention of knowing whether or not there are rows that match or not. In my circumstance, we'll update the rows if a match is found and insert otherwise.

Because nothing is done with the rows that are found, I believe this constitutes a scenario where following the warning's lead is appropriate. Therefore, I call finish() on my select handler before I disconnect.

Disclaimer: Being new to DBI, there is potentially a better approach. I would have used ->do() except the documentation pointed out that it should not be used when executed repeatedly -- also discouraged were SELECT statements for some reason, as well!

Here is some perl pseudocode showing what I landed on:

$selectHandler = $dbh->prepare($queryString) or die "Cannot prepare: ".$dbh->errstr;
#Loop through a list of keys to check existence {
    $selectHandler.execute($uniqueID);
    $found = 0;
    $found = $selectHandler->fetch();
    if (!$found) {
        # Do an insert of $uniqueID
    } else {
        # Do an update of $uniqueID
    }
#}
# Having not done anything with the selectHandler's result (when rows were 
# found) close it now that the loop is complete
$selectHandler->finish(); # we don't need you any more select handler!
$dbh->disconnect or warn "Disconnection error: $DBI::errstr\n";

Hope this helps someone else and feel free to correct my approach if I am misleading anyone.

Damle