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)
Incomplete
High
leonick
Trusty
Triaged
High
Unassigned
Utopic
Won't Fix
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

Tags: patch
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  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.