MySQL Bugs: #44278: mysqlhotcopy unable to backup schema with a hyphen in its name

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

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

Thank you for the report.

Verified as described. Bug exists since version 5.1

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).

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

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

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.

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

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"