mysqlhotcopy: error in SQL syntax

[leonick]

When the database contains views in different schemas, the Perl script mysqlhotcopy reports the following syntax error:

DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' `<schema>.<view>` READ, `<schema>.<view2>` READ' at line 1 at /usr/bin/mysqlhotcopy line 526.

(Exact package version: 5.5.43-0ubuntu0.14.04.1)

This error can be fixed by the patch, which I have attached to the bug report.
Please incorporate it as soon as possible into the next official release.

Thanks,
leonick

[Morgan Tocker (morgo)]

Hi!

mysqlhotcopy has been deprecated by upstream (and removed in 5.7). I have a blog post on it here:
http://www.tocker.ca/2014/04/17/proposal-to-deprecate-mysqlhotcopy.html

Most importantly, it will not safely back up InnoDB tables (which is the default storage engine from 5.5+).

[Ubuntu Foundations Team Bug Bot (crichton)]

The attachment "Adapt syntax of 'LOCK TABLES' command to MySQL 5.5" seems to be a patch. If it isn't, please remove the "patch" flag from the attachment, remove the "patch" tag, and if you are a member of the ~ubuntu-reviewers, unsubscribe the team.

[This is an automated message performed by a Launchpad user owned by ~brian-murray, for any issues please contact him.]

[Clint Byrum (clint-fewbar)]

Regardless of its status in 5.6/5.7, we shipped 5.5 to users in Ubuntu 14.04, when it was still undeprecated, so I think it is worth fixing in 14.04.

Note that in Ubuntu 15.04 and later, 5.6 is shipped, and mysqlhotcopy is in fact shown as deprecated:

http://bazaar.launchpad.net/~ubuntu-branches/ubuntu/vivid/mysql-5.6/vivid/view/head:/scripts/mysqlhotcopy.sh#L161

Now, my personal feeling is that users should stop using it _immediately_ because it really is a terrible thing to rely on. But, we take bugs that might affect data integrity, including backups, pretty seriously.

[Clint Byrum (clint-fewbar)]

leonick, can you please provide a clear step by step test case for us to follow so we can be sure the patch works? Also, if you could re-make the patch in unified format with 'diff -u' that would help make it easier to apply, though we can manually apply it if you're unable to provide a unified diff.

[leonick (leonick)]

(1) Added output of 'diff -u' as requested by Clint Byrum (see attachment)

(2) Test case:
-----------------
To reproduce the problem, MySQL database must contain at least 2 schemas with a view definition; say 'schema1.'view1' and 'schema2.view2'

When you run the command 'mysqlhotcopy --user=backup schema1 schema2 <some backup directory>', you get the syntax error as described in bug report.

Problem description:
----------------------------
Script mysqlhotcopy creates internally the following MySQL statement: LOCK TABLES schema1.view1 , schema2.view2 READ
which is invalid. Correct syntax is: LOCK TABLES 'schema1.view1' READ, 'schema2.view2' READ

(3) Question:
-----------------
There was a comment, that mysqlhotcopy should not be used any more in future releases.
Which solution/alternative do you recommend instead for doing _online_ backup?
(by the way: I don't use InnoDB but MyISAM)

Best regards,
leonick

[Rolf Leggewie (r0lf)]

utopic has seen the end of its life and is no longer receiving any updates. Marking the utopic task for this ticket as "Won't Fix".