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

GeneQ picture GeneQ · Feb 12, 2009 · Viewed 12.8k times · Source

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.

Answer

Paul Tomblin picture Paul Tomblin · Feb 12, 2009

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.