MySQL Bugs: #43594: mysqlhotcopy does not ignore log tables and others in mysql database

Bug #43594	mysqlhotcopy does not ignore log tables and others in mysql database
Submitted:	12 Mar 2009 13:15	Modified:	11 May 2010 4:14
Reporter:	Geert Vanderkelen	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	mysql-5.1-telco-6.3	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	5.1.32

Description:
mysqlhotcopy does currently not ignore log tables and apply_status for example. This should be in sync with mysqldump

How to repeat:
shell> mysqlhotcopy mysql
DBD::mysql:db do failed: You can't use locks with log tables....

Suggested fix:
Ignore tables: general_log, slow_log, apply_status (cluster) and schema (seen in mysqldump.c)

Uploading patch..

Workaround:  mysqlhotcopy mysql./~.+_log$/

Patch for mysqlhotcopy for ignoring some tables in mysql database

Attachment: mysqlhostcopy.bug43594 (application/octet-stream, text), 829 bytes.

Verified using 5.1.32, and fix using latest bzr pull provided.

This bug looks like duplicate of Bug#42465

No, sorry I don't think this is duplicate as there is version difference

I do not know how to apply this patch. I also installed from RPM's. Can anyone suggest how I can get a patched binary of mysqlhotcopy running on CentOS 5.3 x64? Thanks :)

The proposed patch is for apply against the source code, but it should be pretty easy to change it in the MySQL distributions.
Open mysqlhotcopy in your favorite editor, then look the function get_list_of_tables and change it like this (without guarantee, etc.. etc..):

sub get_list_of_tables {
    my ( $db ) = @_;

    my $tables =
        eval {
            $dbh->selectall_arrayref('SHOW TABLES FROM ' .
                                     $dbh->quote_identifier($db))
        } || [];
    warn "Unable to retrieve list of tables in $db: $@" if $@;

     my @ignore_tables = ();

     # Ignore tables for the mysql database
     if ($db eq 'mysql') {
         @ignore_tables = qw(general_log slow_log schema apply_status);
     }
 
     my @res = ();
     if ($#ignore_tables > 1) {
        my @tmp = (map { $_->[0] } @$tables);
        for my $t (@tmp) {
            push(@res, $t) if not exists { map { $_=>1 } @ignore_tables }->{$t};
        }
     } else {
        @res = (map { $_->[0] } @$tables);
     }
 
     return @res;
 }

Geert, could you clarify if the workaround:

mysqlhotcopy mysql./~.+_log$/

works by passing "mysql./~.+_log$/" into mysqlhotcopy (thereby always working), or by excluding the files shell/os-wise (thereby possibly not working depending on what type of shell is being used)

Geert, also, is there a known workaround to use when trying to perform backups using the ZRM management console (instead of a command line)?

To clarify: the regex workaround on command line is shell independent as it is past on to the Perl script.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/105222

3449 Georgi Kodinov	2010-04-08
      Bug #43594: mysqlhotcopy does not ignore log tables and others in mysql database
      
      Added a filter to mysqlhotcopy to filter out the same tables in the 'mysql' 
      database that mysqldump filters out.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/105387

3449 Georgi Kodinov	2010-04-12
      Bug #43594: mysqlhotcopy does not ignore log tables and others in mysql database
      
      Added a filter to mysqlhotcopy to filter out the same tables in the 'mysql' 
      database that mysqldump filters out.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/105388

3449 Georgi Kodinov	2010-04-12
      Bug #43594: mysqlhotcopy does not ignore log tables and others in mysql database
      
      Added a filter to mysqlhotcopy to filter out the same tables in the 'mysql' 
      database that mysqldump filters out.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/105389

3451 Georgi Kodinov	2010-04-12
      Bug #43594: mysqlhotcopy does not ignore log tables and others in mysql database
      
      Added a filter to mysqlhotcopy to filter out the same tables in the 'mysql' 
      database that mysqldump filters out.

the patch is matching substrings of the database and table names, so it could end up catching more tables than intended. better to do "lc $db eq 'mysql'" and "lc $_ ne 'apply_status'", etc, i think.

sorry, never mind, i was looking at an earlier patch. the final patch looks great.

Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:kristofer.pettersson@sun.com-20100416145616-vdcdyz9eu7j86lp7) (merge vers: 5.1.47) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:alik@sun.com-20100422150658-fkhgnwwkyugtxrmu) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:alexey.kopytov@sun.com-20100429203306-tg0wz4y2xyx8edrl) (merge vers: 5.5.5-m3) (pib:16)

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Fix works for log tables, but it fails ignoring NDB tables in the mysql database:

shell> ./bin/mysqlhotcopy mysql -u root /tmp/foo/
DBD::mysql::db do failed: Incorrect information in file: './mysql/ndb_apply_status.frm' at ./bin/mysqlhotcopy line 460.

{ !/^(apply_status|schema|general_log|slow_log)$/ } @dbh_tables

should be, at least:

{ !/^(ndb_apply_status|ndb_schema|general_log|slow_log)$/ } @dbh_tables

The push to Cluster source is not useful thus.

Any updates?

mysqldump and mysqlhotcopy still look for "apply_status" instead of "ndb_apply_status" in 5.6.10, cluster-7.2.10, cluster-7.3.0 ...

Somewhat related:
Bug #82851 	mysqldump excludes only the old ndb apply and schema tables

But mysqlhotcopy is deprecated, so I think this bug should be set to Won't Fix.