mysqlhotcopy: error in SQL syntax

Bug #1450803 reported by leonick
10
This bug affects 1 person
Affects Status Importance Assigned to Milestone
mysql-5.5 (Ubuntu)
High
leonick
Trusty
High
Unassigned
Utopic
High
Unassigned

Bug Description

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

Revision history for this message
leonick (leonick) wrote :
Revision history for this message
Morgan Tocker (morgo) wrote :

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

Revision history for this message
Ubuntu Foundations Team Bug Bot (crichton) wrote :

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

tags: added: patch
Revision history for this message
Clint Byrum (clint-fewbar) wrote :

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.

Changed in mysql-5.5 (Ubuntu Trusty):
status: New → Triaged
Changed in mysql-5.5 (Ubuntu Utopic):
status: New → Triaged
Changed in mysql-5.5 (Ubuntu Trusty):
importance: Undecided → High
Changed in mysql-5.5 (Ubuntu Utopic):
importance: Undecided → High
Changed in mysql-5.5 (Ubuntu):
status: New → Invalid
importance: Undecided → High
Revision history for this message
Clint Byrum (clint-fewbar) wrote :

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.

Changed in mysql-5.5 (Ubuntu):
status: Invalid → Incomplete
assignee: nobody → leonick (leonick)
Revision history for this message
leonick (leonick) wrote :

(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

Revision history for this message
Rolf Leggewie (r0lf) wrote :

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

Changed in mysql-5.5 (Ubuntu Utopic):
status: Triaged → Won't Fix
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers