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.

Solution 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.

Solution 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.

Comments

[GeneQ]

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

[Paul Tomblin]

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

[GeneQ]

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

[GeneQ]

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.

[wds]

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

[Paul Tomblin]

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

[GeneQ]

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