MySQL Bugs: #33640: mysqlhotcopy v1.22 does not work

Bug #33640	mysqlhotcopy v1.22 does not work
Submitted:	2 Jan 2008 23:31	Modified:	31 Jan 2008 22:08
Reporter:	Cengiz Gunay	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Linux (openSuSE 10.3)
Assigned to:		CPU Architecture:	Any
Tags:	Backup, Contribution, dbi, mysqlhotcopy, perl, table names

Description:
mysqlhotcopy v1.22 fails because it repeats the database name twice when looking for tables using perl-DBD-mysql 4.005-26.

How to repeat:
Assume a test.mytable table exists. Running the folowing command fails:

# mysqlhotcopy test
Using copy suffix '_copy'
Invalid db.table name 'test.test`.`mytable' at /usr/bin/mysqlhotcopy line 861.

(line numbers shifted because I edited mysqlhotcopy)

Suggested fix:
I figured this is happening because the return format of the $dbh->tables() Perl command has changed at some point, returning table names that includes the database name. Somehow mysqlhotcopy did not expect this, so it has the database name appearing twice in the final name. $dbh is the database handle returned by DBI->connect from the DBI Perl module, see below.

The following patch fixes the problem (not a real diff output, just look for lines starting with '+'):
========
sub get_list_of_tables {
    my ( $db ) = @_;

    # "use database" cannot cope with database names containing spaces
    # so create a new connection

    my $dbh = DBI->connect("dbi:mysql:${db}${dsn};mysql_read_default_group=mysqlhotcopy",
                            $opt{user}, $opt{password},
    {
        RaiseError => 1,
        PrintError => 0,
        AutoCommit => 1,
    });

    my @dbh_tables = eval { $dbh->tables() };

    ## Remove quotes around table names
    my $quote = $dbh->get_info(29); # SQL_IDENTIFIER_QUOTE_CHAR
    if ($quote) {
      foreach (@dbh_tables) {
        s/^$quote(.*)$quote$/$1/;
        s/$quote$quote/$quote/g;
+       ## Cengiz: remove db name prefix from table names:
+       s/$quote\.$quote/./;
+       s/^$db\.//;
      }
    }
    print "DB tables: @dbh_tables\n" if $opt{debug};

    $dbh->disconnect();
    return @dbh_tables;
}
=====

This is not a problem with DBD::mysql.

This is a problem with DBD::MySQL. The previous version of
tables() used to give out only the table names. The recent version
also prefixes the names with database names.

Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Duplicate of bug #27694

Sorry, my initial search did not give the existing bug. It's true, this is a duplicate.

Seems like the bug isn't fix...

DBD::mysql::db do failed: Can't find file: './dbname/mytable.frm' (errno: 13) at /usr/bin/mysqlhotcopy line 467.