Bug #44278 mysqlhotcopy unable to backup schema with a hyphen in its name
Submitted: 15 Apr 2009 3:26 Modified: 4 Aug 2009 19:00
Reporter: technical services division TSD Email Updates:
Status: Patch pending Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S2 (Serious)
Version:1.23, 5.1, 6.0 bzr OS:Linux (RHEL4)
Assigned to: Assigned Account CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[15 Apr 2009 3:26] technical services division TSD
Description:
mysqlhotcopy included in distribution 5.1.32 (and possibly previous) fails to backup MyISAM schemas which names include '-'. These schemas are stored in directories which are encoded to be file system neutral, thus those names differ. 

Executing mysqlhotcopy for such schemas results in following message:

Cannot open dir '/data/mysql/a-b-c': No such file or directory at /usr/bin/mysqlhotcopy line 289.

This breaks current backup process 100% and requires new regime to be devised.

How to repeat:
Create schema: a-b-c

Execute: mysqlhotcopy -u backup -p xxxxx a-b-c /var/tmp
[15 Apr 2009 5:56] Sveta Smirnova
Thank you for the report.

Verified as described. Bug exists since version 5.1
[4 Aug 2009 19:00] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/80079

3055 Jim Winstead	2009-08-04
      mysqlhotcopy did not handle the encoding of schema names in the filesystem, so
      schemas with most non-alphanumeric characters in their name could not be
      backed up. (Bug #44278).
[12 Nov 2010 12:05] Gabor Funk
I downloaded mysql-5.5.6-rc.tar.gz and I found out that this - more than a year old - patch not applied there.

I was able to apply the patch to mysqlhotcopy out of the above package - 1 reject added manually, rest was OK -, but after applying the patch, I still got an error:

"Unable to retrieve list of tables in xxx@002dhu: DBD::mysql::db selectall_arrayref failed: Unknown database 'xxx@002dhu' at /usr/bin/mysqlhotcopy line 792."

It is/was because the database previously named "xxx-hu" now called xxx@002dhu, and the "sub encode_identifier_as_filename" creates a xxx@0040002dhu "$filename" out of the tablename ($ident) xxx@002dhu, then mysqlcopy still doesn't/wouldn't work.

Putting an un-elegant but fast regex (  $filename =~ s/\@0040/\@/g;  ) let us continue here, but then "sub get_list_of_tables" needs another regex (  $db =~ s/\@002d/-/g;  ) to continue.

Finally, the "$dbh->do("FLUSH TABLES $hc_tables WITH READ LOCK");" needs "WITH READ LOCK" to be removed, likely this is a new feature above 5.1 - better way here would be a version checking and use "WITH READ LOCK" only if supported.

So mysqlhotcopy (with the patch + hacks) works for me now - but since I am pretty much a newbie to perl and sql - I patched only the parts "I experienced problems with", the way I could, so it would need some professional assistance to make it better and commit to upstream (and make sure it get's there :-), etc.

I also found others who also ran into this problem, (like http://forums.zmanda.com/showthread.php?p=9930 ), so this will likely causes problem elsewhere.

For convenience, here is my "working" version: http://www.maques.hu/mysqlhotcopy/ - haven't changed "version number", but since we're here, a proper versioning would be helpful there...
I mean it is still at 1.23 but I believe there were some changes to it in the last few years. I diffed to a debian testing "v1.23" (size 31715)... At least to see what to diff/patch to, but otherwise a "used" versioning and maybe some changelog would be sensational, if anyone could do anything about that....
[27 Mar 2011 23:13] Mike Reid
Any update on this pending patch?

I am very happy to see that the non-lockable "apply_status|schema|general_log|slow_log" tables are now being excluded from the table list, but I too am shocked that the mysqlhotcopy packaged with latest mysql server cannot even backup it's own databases based on naming convention used on the file system.

I understand that as of MySQL 5.5 InnoDB is the new default storage engine, which I believe mysqlhotcopy may not support...however, for those of us still on MyISAM and MySQL 5.1, approving this patch would be much appreciated. :)
[11 May 2011 14:29] J Wilson
This bug significantly impacts the ability to back up databases. I'm very disappointed to see that this issue was reported more than 2 years ago and has not yet been addressed. mysqldump works alright, but I have a need to very frequently and quickly pull and push databases out of and into mysql servers and mysqlhotcopy just fits this need so much better.

Please fix this issue quickly.
[22 Dec 2013 15:32] Elan Ruusamäe
Just informing that this is still needed as of 5.6.15. and the patch works with minor patch

you can grab updated patch from here:
http://git.pld-linux.org/?p=packages/mysql.git;a=history;f=bug-44278.patch;h=b5d680cdec473...
[2 Jul 2014 11:56] Daniël van Eeden
From http://dev.mysql.com/doc/refman/5.6/en/mysqlhotcopy.html
"This utility is deprecated in MySQL 5.6.20 and removed in MySQL 5.7"