MySQL Bugs: #20153: Fetching all data from a statement handle does not mark it as finished

Bug #20153	Fetching all data from a statement handle does not mark it as finished
Submitted:	30 May 2006 20:03	Modified:	6 Sep 2006 0:11
Reporter:	Christian Hammers (Silver Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connectors: DBD::mysql ( Perl )	Severity:	S3 (Non-critical)
Version:	4.1, 5.0.21	OS:	Linux (Debian GNU/Linux Sid)
Assigned to:	Jim Winstead	CPU Architecture:	Any
Tags:	finish

Description:
As reported as Debian Bug #369394 by Max Bowsher <maxb1@ukf.net> and confirmed by me:
After upgrading DBD::MySQL from 3.0002 to 3.0004, mysqlhotcopy starts throwing warnings.
The first part of the following diff solves the problem, the second one just seems to be right, too.

bye,

-christian-

How to repeat:
$ sudo mysqlhotcopy -u root -p root mysql --allowold
Using copy suffix '_copy'
Existing hotcopy directory renamed to '/var/lib/mysql/mysql_copy_old'
Locked 17 tables in 0 seconds.
Flushed tables (`mysql`.`columns_priv`, `mysql`.`db`, `mysql`.`func`, `mysql`.`help_category`, `mysql`.`help_keyword`, `mysql`.`help_relation`, `mysql`.`help_topic`, `mysql`.`host`, `mysql`.`proc`, `mysql`.`procs_priv`, `mysql`.`tables_priv`, `mysql`.`time_zone`, `mysql`.`time_zone_leap_second`, `mysql`.`time_zone_name`, `mysql`.`time_zone_transition`, `mysql`.`time_zone_transition_type`, `mysql`.`user`) in 0 seconds.
Copying 51 files...
Copying indices for 0 files...
Unlocked tables.
DBI::db=HASH(0xa937f0)->disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting) at /usr/bin/mysqlhotcopy line 541.
mysqlhotcopy copied 17 tables (51 files) in 0 seconds (0 seconds overall).

Suggested fix:
--- /usr/bin/mysqlhotcopy       2006-05-28 19:02:47.000000000 +0200
+++ mysqlhotcopy        2006-05-30 22:02:45.000000000 +0200
@@ -216,6 +216,7 @@
 while ( my ($var,$value) = $sth_vars->fetchrow_array ) {
     $mysqld_vars{ $var } = $value;
 }
+$sth_vars->finish();
 my $datadir = $mysqld_vars{'datadir'}
     || die "datadir not in mysqld variables";
     $datadir= $opt{chroot}.$datadir if ($opt{chroot});
@@ -259,6 +260,7 @@
        next if $db_name =~ m/^information_schema$/i;
        push @db_desc, { 'src' => $db_name, 't_regex' => $t_regex } if ( $db_name =~ m/$opt{regexp}/o );
     }
+    $sth_dbs->finish();
 }
 
 # --- get list of tables to hotcopy ---

Thank you for a bug report and suggested fix.

Similar bug: http://bugs.mysql.com/bug.php?id=20518

Another duplicate: http://bugs.mysql.com/bug.php?id=20727

Another duplicate: http://bugs.mysql.com/bug.php?id=20727

Hi.  This is a bug in DBD::mysql, not in mysqlhotcopy.

If all rows are fetched from a statement handle, then the warning should not be printed.  The statement handle is to be considered inactive after the last row is fetched from it.

I'm changing the Category to DBD::mysql, and assigning it to the Connectors team lead.

Here's a smaller test case which demonstrates the problem:

#! /usr/bin/perl -w

use strict;
use DBI;
use DBD::mysql;

print "DBI $DBI::VERSION, DBD::mysql $DBD::mysql::VERSION\n";

my $dsn = "mysql_read_default_group=client";
my $dbh = DBI->connect("dbi:mysql:$dsn", undef, undef,
        { RaiseError => 1, PrintError => 1, AutoCommit => 1 });

my $sth = $dbh->prepare("show variables like 'datadir'");
$sth->execute;
while (defined(my $row = $sth->fetchrow_arrayref())) {
    print uc($row->[0]), ": $row->[1]\n";
}

$dbh->disconnect();

exit 0;

When run with DBD::mysql version 3.0006, it produces the warning on disconnect:

15:30 ~$ ./t.pl                                                    
DBI 1.51, DBD::mysql 3.0006
DATADIR: /usr/home/tim/m/bk/41/run/data/
DBI::db=HASH(0x81bd52c)->disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting) at ./t.pl line 19.

I don't want to modify mysqlhotcopy to work around this bug in DBD::mysql.

Regards,

Timothy

Patch to DBD::mysql 3.0006 to clean up statements when they finish

Attachment: bug20153.diff (application/octet-stream, text), 753 bytes.

I've attached a patch for DBD::mysql that automatically "finishes" statement handles when the last row is fetched, so that these warnings are prevented.

This (new) behavior is consistent with the documentation of the finish method in the DBI documentation.

Patch including test case

Attachment: bug20153.diff (application/octet-stream, text), 2.52 KiB.

Fix committed to the dev-3.0 branch of DBD::mysql, so it will be in the next release (either 3.0006_2 or 3.0007).