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:
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

[30 May 2006 20:03] Christian Hammers
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.



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;
 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 ---
[30 May 2006 20:49] Valeriy Kravchuk
Thank you for a bug report and suggested fix.
[19 Jun 2006 17:54] Sveta Smirnova
Similar bug: http://bugs.mysql.com/bug.php?id=20518
[27 Jun 2006 18:38] Sveta Smirnova
Another duplicate: http://bugs.mysql.com/bug.php?id=20727
[27 Jun 2006 18:39] Sveta Smirnova
Another duplicate: http://bugs.mysql.com/bug.php?id=20727
[8 Aug 2006 21:33] Timothy Smith
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'");
while (defined(my $row = $sth->fetchrow_arrayref())) {
    print uc($row->[0]), ": $row->[1]\n";


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.


[15 Aug 2006 0:04] Jim Winstead
Patch to DBD::mysql 3.0006 to clean up statements when they finish

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

[15 Aug 2006 0:08] Jim Winstead
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.
[1 Sep 2006 15:02] Jim Winstead
Patch including test case

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

[6 Sep 2006 0:11] Jim Winstead
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).