MySQL Bugs: #62472: mysqlhotcopy errors on databases containing MyISAM views

Bug #62472	mysqlhotcopy errors on databases containing MyISAM views
Submitted:	19 Sep 2011 12:41	Modified:	10 Feb 2012 13:42
Reporter:	Nick Barham	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	5.5.15, 5.5.17	OS:	Any (Ubuntu, Mac OS X)
Assigned to:		CPU Architecture:	Any

Description:
mysqlhotcopy does "SHOW TABLES FROM ..." to get a list of table to flush. This call includes any views. It later calls "FLUSH TABLE <tablename> WITH READ LOCK" on this list of tables, and when it gets to one of the views, it throws the error  "<tablename> is not BASE TABLE" (this error was added in 5.5). This in turn causes the backup to abort. 

Instead, the "SHOW TABLES" call should be modified to exclude views (using "SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'")

How to repeat:
Create a MyISAM database containing one or more views. 
Run mysqlhotcopy on this database - It will error out with the SQL Error Message: "<viewname> is not BASE TABLE".

Suggested fix:
Replace lines mysqlhotcopy:804-807

    my $tables =
        eval {
            $dbh->selectall_arrayref('SHOW TABLES FROM ' . 
                        $dbh->quote_identifier($db))
        } || [];

with

    my $tables =
        eval {
            $dbh->selectall_arrayref('SHOW FULL TABLES FROM ' . $dbh->quote_identifier($db) . ' WHERE Table_type = \'BASE TABLE\'')
        } || [];

(Correcting server version)

mysqlhotcopy tested reports as version 1.23

Thank you for the bug report. Verified just as described on Mac OS X.

Noted in 5.6.5 changelog. 

mysqlhotcopy failed for databases containing views.

Is there any way this can get backported to a 5.5 GA? This is a pretty serious deficiency.

I can confirm that this bug exists in the currently shipping and most recent version of Ubuntu 12.04 running MySQL 5.5.24.

I can also confirm that the change above fixes the issue.