mysqlhotcopy failed on table with hyphen in name

[Michael Heča]

In Ubuntu 8.04.1 LTS (in 8.10 seem too):

root@ns1:~# mysqlhotcopy.original --allowold tiskarna-amg /data/mysqlhotcopy
Deleting previous 'old' hotcopy directory ('/data/mysqlhotcopy/tiskarna-amg_old')
Existing hotcopy directory renamed to '/data/mysqlhotcopy/tiskarna-amg_old'
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 '-amg.cz_clanky READ, tiskarna-amg.cz_daystat READ, tiskarna-amg.cz_download READ' at line 1 at /usr/bin/mysqlhotcopy.original line 473.

root@ns1:~# dlocate mysqlhotcopy
mysql-server-5.0: /usr/bin/mysqlhotcopy

root@ns1:~# dpkg -l mysql-server-5.0
...
ii mysql-server-5.0 5.0.51a-3ubuntu5.1 MySQL database server binaries

SOLUTION:
Comment out duplicated lines 871 and 872 "$hc_locks =~ s/\`//g;" in 8.04.1 whitch removes quotation from db/table names.

[Michael Heča (orgoj)]

Version 5.0.38-0ubuntu1.4 in Ubuntu 7.04 is not affected by this bug.

[Andreas Olsson (andol)]

I can replicate exactly the same problem in Hardy as well as in Jaunty.

To comment out the duplicate "$hc_locks =~ s/\`//g;" does seem to fix the problem. I haven't (yet) looked into what use they are supposed to do.

[Andreas Olsson (andol)]

Seems as if the problem is a not-longer-necessary ./patches/56-mysqlhotcopy-invalid-dbtable.dpatch.

I believe the patch was taken from http://bugs.mysql.com/bug.php?id=27303
Based on a comment to http://bugs.mysql.com/bug.php?id=27694 the original problem isn't longer an issue since MySQL 5.0.50. This I can kind of confirm.

Using the Hardy mysqlhotcopy, and removing the double "$hc_locks =~ s/\`//g;", I can not reproduce the problem mentioned in http://bugs.mysql.com/bug.php?id=27303

[Andreas Olsson (andol)]

In short, this is what has been happening to mysqlhotcopy.

(Please correct me if I'm wrong. It's late and I'm tired.)

DBD::mysql changed the behavior of $dbh->tables(), causing mysqlhotcopy to break. This was kind of handled by the $hc_locks-patch in 56-mysqlhotcopy-invalid-dbtable.dpatch

Later mysqlhotcopy was fixed more properly by replacing $dbh->tables() with a direct ""SHOW TABLES FROM `db`".

Our problem is that we still apply 56-mysqlhotcopy-invalid-dbtable.dpatch, causing problem instead of fixing problems.

By the way, http://lists.mysql.com/commits/32765 is a good read.

[Andreas Olsson (andol)]

I'm attaching a debdiff which builds mysql-dfsg-5.0 without using the 56-mysqlhotcopy-invalid-dbtable.dpatch

The resulting mysqlhotcopy seems to fix this bug without causing any other problems.

These are the tests I've (successfully) tried.

# mysqlhotcopy --allowold foo-bar
# mysqlhotcopy --allowold mysql
# mysqlhotcopy --allowold foo-bar mysql /root/dump/
# mysqlhotcopy --allowold mysql./o/ /root/dump/

[Andreas Olsson (andol)]

I'm also attaching a modified mysqlhotcopy, which is the result of a build using recently posted debdiff.

[Andreas Olsson (andol)]

Understood that patches should foremost be submitted to the development release of Ubuntu. Hence, here is a debdiff to Jaunty.

I've run the same tests mentioned above in a Jaunty system.

[Dustin Kirkland  (kirkland)]

Andreas-

Thanks for the debdiff. It's much appreciated when you attach a patch in this format ;-)

I assigned this bug to myself, because I was about to sponsor the upload.

However, I realize now that I don't entirely understand *why* we're able to drop that 56-* patch? Can you give some clear explanation/reasoning why that patch is either no longer applicable, or incorrect?

Thanks,
:-Dustin

[Andreas Olsson (andol)]

Let me see...

Previously mysqlhotcopy used "$dbh->tables()" to get a list of a database's tables. This happens in the subroutine get_list_of_tables. For convenience I'm attaching a mysqlhotcopy from dapper, where this behavior can be seen.

Around DBD::mysql version 4.003 the behavior of "$dbh->tables()" changed. Instead of returning a simple "tablename" it started to return a full "databasename.tablename". This resulted in mysqlhotcopy trying to operate on "databasename.databasename.tablename". That is the issue 56-* deals with. Well, there were also some following issues of quotation.

If you look at the subroutine get_list_of_tables in a more modern mysqlhotcopy you'll see that it get its list of tables from a SQL-query based on "SHOW TABLES FROM...". This again returns the table list in the simple form of "tablename", without any database-prefix.

Hence the 56-patch doesn't apply.

[Launchpad Janitor (janitor)]

This bug was fixed in the package mysql-dfsg-5.0 - 5.1.30really5.0.75-0ubuntu7

---------------
mysql-dfsg-5.0 (5.1.30really5.0.75-0ubuntu7) jaunty; urgency=low

  * Revert 56-mysqlhotcopy-invalid-dbtable.dpatch: The behavior of
    $dbh->tables() has changed. Instead of returning a simple
    "tablename" it returns a full "databasename.tablename". LP: #296952

 -- Andreas Olsson <email address hidden> Sat, 31 Jan 2009 22:34:54 +0100

[Andreas Olsson (andol)]

A SRU for this bug is kind of depending of a SRU for bug #323755.

[Benjamin Drung (bdrung)]

no debdiff for hardy to sponsor, unsubscribing ubuntu-sponsors

[Rolf Leggewie (r0lf)]

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