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: Bernd Ocklin CPU Architecture:Any
Tags: 5.1.32
Triage: Triaged: D3 (Medium) / R2 (Low) / E2 (Low)

[12 Mar 2009 13:15] Geert Vanderkelen
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$/
[12 Mar 2009 13:16] Geert Vanderkelen
Patch for mysqlhotcopy for ignoring some tables in mysql database

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

[12 Mar 2009 13:35] Geert Vanderkelen
Verified using 5.1.32, and fix using latest bzr pull provided.
[12 Mar 2009 19:26] Hema Sridharan
This bug looks like duplicate of Bug#42465
[12 Mar 2009 19:27] Hema Sridharan
No, sorry I don't think this is duplicate as there is version difference
[9 Jun 2009 16:59] Johnny Stork
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 :)
[24 Jun 2009 8:49] Geert Vanderkelen
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;
 }
[30 Mar 2010 1:08] Roel Van de Paar
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)
[30 Mar 2010 2:39] Roel Van de Paar
Geert, also, is there a known workaround to use when trying to perform backups using the ZRM management console (instead of a command line)?
[5 Apr 2010 8:00] Geert Vanderkelen
To clarify: the regex workaround on command line is shell independent as it is past on to the Perl script.
[8 Apr 2010 13:24] Bugs System
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.
[12 Apr 2010 8:52] Bugs System
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.
[12 Apr 2010 8:56] Bugs System
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.
[12 Apr 2010 8:59] Bugs System
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.
[14 Apr 2010 18:56] Jim Winstead
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.
[14 Apr 2010 19:12] Jim Winstead
sorry, never mind, i was looking at an earlier patch. the final patch looks great.
[5 May 2010 15:09] Bugs System
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)
[28 May 2010 5:47] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:17] Bugs System
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)
[28 May 2010 6:44] Bugs System
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)
[17 Jun 2010 11:48] Bugs System
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)
[17 Jun 2010 12:25] Bugs System
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)
[17 Jun 2010 13:12] Bugs System
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)
[23 Jun 2010 7:40] Geert Vanderkelen
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.
[29 Nov 2010 1:08] Roel Van de Paar
Any updates?
[4 Apr 2013 7:38] Hartmut Holzgraefe
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 ...
[27 Oct 2017 11:27] Daniƫl van Eeden
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.