| Bug #27303 | mysqlhotcopy dies with error Invalid db.table name 'foo.bar`.`baz' | ||
|---|---|---|---|
| Submitted: | 20 Mar 2007 19:53 | Modified: | 14 Dec 2007 9:39 |
| Reporter: | Axel Schwenke | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Backup | Severity: | S1 (Critical) |
| Version: | n/a | OS: | Linux (Linux) |
| Assigned to: | CPU Architecture: | Any | |
[21 Mar 2007 15:25]
idrees darky
This is perl, v5.8.5 built for i386-linux-thread-multi
[21 Mar 2007 15:27]
idrees darky
I had this issue with the perl version 5.8.5. This is perl, v5.8.5 built for i386-linux-thread-multi
[13 Jun 2007 22:53]
[ name withheld ]
the bug description should be" mysqlhotcopy dies with error Invalid db.table name 'foo.foo`.`bar' " as the problem is the repeat of the db name. The fix seems to work although the exact line you have to add it to is 841 on my version of mysqlhotcopy (1.22)
[26 Jul 2007 19:11]
Ronald Bradford
Under RHEL4 with 5.0.30 and Perl DBI 1.40-8 mysqlhotcopy works. Under RHEL4 with 5.0.44 and Perl DBI 1.58 mysqlhotcopy fails with this error. The workaround as described works!
[25 Oct 2007 17:32]
Christopher McCrory
database= was not specified
patch:
diff -u mysqlhotcopy /tmp/mysqlhotcopy
--- mysqlhotcopy 2006-10-23 07:37:26.000000000 -0700
+++ /tmp/mysqlhotcopy 2007-10-25 10:26:30.000000000 -0700
@@ -822,7 +822,7 @@
# "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",
+ my $dbh = DBI->connect("dbi:mysql:database=${db}${dsn};mysql_read_default_group=mysqlhotcopy",
$opt{user}, $opt{password},
{
RaiseError => 1,
[1 Nov 2007 17:33]
Lex Neva
The proposed patch (adding database=) did not fix the problem for me. The map workaround did work. mysqlhotcopy 1.22, mysql-5.0.44, DBI-1.53, gentoo linux x86_64
[16 Nov 2007 19:57]
Michael Moss
Yep, all that.
quote_names was being passed tables already pre-pended with database name. Don't do that.
hc_locks hc_tables really screwy with back ticks. Removed (with s/\`//g)
C comments in SQL commands caused me problems.
Here is my fix for 5.0.45 version
--- /usr/local/mysql5/bin/mysqlhotcopy.orig 2007-11-16 13:36:43.000000000 -0500
+++ /usr/local/mysql5/bin/mysqlhotcopy 2007-11-16 13:34:37.000000000 -0500
@@ -333,7 +333,7 @@
$rdb->{files} = [ @db_files ];
$rdb->{index} = [ @index_files ];
- my @hc_tables = map { quote_names("$db.$_") } @dbh_tables;
+ my @hc_tables = map { quote_names("$_") } @dbh_tables;
$rdb->{tables} = [ @hc_tables ];
$rdb->{raid_dirs} = [ get_raid_dirs( $rdb->{files} ) ];
@@ -464,13 +464,15 @@
}
else {
my $start = time;
+ $hc_locks =~ s/\`//g;
+ $hc_tables =~ s/\`//g;
$dbh->do("LOCK TABLES $hc_locks");
printf "Locked $num_tables tables in %d seconds.\n", time-$start unless $opt{quiet};
$hc_started = time; # count from time lock is granted
# flush tables to make on-disk copy uptodate
$start = time;
- $dbh->do("FLUSH TABLES /*!32323 $hc_tables */");
+ $dbh->do("FLUSH TABLES");
printf "Flushed tables ($hc_tables) in %d seconds.\n", time-$start unless $opt{quiet};
$dbh->do( "FLUSH LOGS" ) if ( $opt{flushlog} );
$dbh->do( "RESET MASTER" ) if ( $opt{resetmaster} );
@@ -478,7 +480,7 @@
if ( $opt{record_log_pos} ) {
record_log_pos( $dbh, $opt{record_log_pos} );
- $dbh->do("FLUSH TABLES /*!32323 $hc_tables */");
+ $dbh->do("FLUSH TABLES");
}
}
[6 Dec 2007 2:48]
Xu Zheng
Thanks , Michael Moss I've resolved this problem with your patch information . So it funcational to the version 5.0.45
[14 Dec 2007 9:39]
Timothy Smith
This is a duplicate of bug #27694 (which is already fixed).
Description: mysqlhotcopy dies with an errormessage like Invalid db.table name 'mysql.mysql`.`columns_priv' at usr/local/mysql/current/bin/mysqlhotcopy line 855. This seems to be an issue with the behaviour of the DBI database handle method tables(). It returns a list of tables in the currently selected database. Newer versions of DBD::mysql seem to prefix the table names with the database name. I see the new behaviour with DBD::mysql version 4.003 and the expected (old) behaviour with DBD::mysql version 2.9004. Sorry I don't have any more versions at my hands but probably this change happened late in DBD::mysql 3.x or even in 4.x. How to repeat: shell> mysqlhotcopy --debug mysql Suggested fix: A quick and dirty fix is to strip the database prefix from the list of tables. This happens in function get_list_of_tables(). Adding the following line right behind line 835 will strip the prefixing schema: map { s/^.*?\.//o } @dbh_tables; A long term fix would be to avoid $dbh->tables() (it is marked deprecated anyway) and use either table_info() or plain SHOW TABLES instead.