Bug #46653 mysqlhotcopy fails if db/table name contains '.'
Submitted: 11 Aug 2009 14:50 Modified: 12 May 2011 10:15
Reporter: Scott Noyes Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:5.1 OS:Linux
Assigned to: CPU Architecture:Any

[11 Aug 2009 14:50] Scott Noyes
Description:
mysqlhotcopy's quote_names splits db and table names on the '.' character:

  my ($db, $table, @cruft) = split( /\./, $name );
  die "Invalid db.table name '$name'" if (@cruft || !defined $db || !defined $table );

If the db or table name contains a '.' character, then @cruft is populated and the script fails with "Invalid db.table name"

How to repeat:
mysql -e "CREATE DATABASE `test.period`; CREATE TABLE `test.period`.t1 (id int)"
mysqlhotcopy --regexp=period backupFolder

Suggested fix:
my ($db, $table, @cruft) = $name =~ /`/ ? split( /`\.`/, $name ) : split( /`?\.`?/, $name );
[12 Aug 2009 5:04] Sveta Smirnova
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
[12 Aug 2009 5:13] Sveta Smirnova
Scott,

> his database name does not contain a dot, but is doubled:

This is another case. Please send us output of SHOW CREATE TABLE problem_table and exact name of the database.
[11 May 2011 18:42] J Wilson
This bug was improperly marked as a duplicate and is, in fact, still present in the current version of mysqlhotcopy.

bug #27694 has to do with the database name being duplicated adding an extra dot. The fix for that bug is definitely not the same as a needed fix for this bug in general. 

Specifically, this bug manifests when a table name itself has a '.' in it:

CREATE DATABASE test;
CREATE TABLE test.`dotted.table.name` ( test char(1) );

when doing a backup of the test db, mysqlhotcopy simply concats db and table names with a dot (which is inappropriate) and then splits them on the dot and recombines them as `$db`.`$table` -- the split doesn't work because there are dots in the table name. The symptom of this bug is what revealed bug #27694 but fixing that bug does not fix this bug in general.

Suggested Fix: 

@@ -331,7 +331,7 @@
 
     $rdb->{files}  = [ @db_files ];
     $rdb->{index}  = [ @index_files ];
-    my @hc_tables = map { quote_names("$db`.`$_") } @dbh_tables;
+    my @hc_tables = map { quote_names("$db.$_") } @dbh_tables;
     $rdb->{tables} = [ @hc_tables ];
 
     $hc_locks .= ", "  if ( length $hc_locks && @hc_tables );
@@ -792,7 +792,7 @@
   my ( $name ) = @_;
   # given a db.table name, add quotes
 
-  my ($db, $table, @cruft) = split( /`\.`/, $name );
+  my ($db, $table, @cruft) = split( /\./, $name );
   die "Invalid db.table name '$name'" if (@cruft || !defined $db || !defined $table );
 
   # Earlier versions of DBD return table name non-quoted,
[11 May 2011 18:50] J Wilson
if i could edit that last comment i would -- my diff was in the wrong direction, sorry :-)
[12 May 2011 10:15] Sveta Smirnova
J Wilson,

thank you for the feedback and contribution. I'd say another bug was not properly fixed, but we can go and set this to "Verified" so it will be fixed.

Output in my environment:

$perl ~/host/build/mysql-5.1/bin/mysqlhotcopy  -u root -S /tmp/mysql_ssmirnova.sock test
Using copy suffix '_copy'
Invalid db.table name 'test.dotted.table.name' at /users/ssmirnova/host/build/mysql-5.1/bin/mysqlhotcopy line 813.