pt-online-schema-change is stuck when the table that is being altered is filtered out in the slave

[Jaime Sicam]

pt-online-schema-change --execute --alter="engine=innodb" --no-check-replication-filters h=127.0.0.1,P=20594,u=root,p=msandbox,D=employees,t=titles --skip-check-slave-lag=h=127.0.0.1,P=20595,u=root,p=msandbox
Found 2 slaves:
kits-desktop -> SBslave1:20595
kits-desktop -> SBslave2:20596
Will check slave lag on:
kits-desktop -> SBslave1:20595
kits-desktop -> SBslave2:20596
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `employees`.`titles`...
Creating new table...
Created new table employees._titles_new OK.
Waiting forever for new table `employees`.`_titles_new` to replicate to kits-desktop...
Waiting for kits-desktop: 0% 00:00 remain
Waiting for kits-desktop: 0% 00:00 remain
Waiting for kits-desktop: 0% 00:00 remain
Waiting for kits-desktop: 0% 00:00 remain
Waiting for kits-desktop: 0% 00:00 remain
Waiting for kits-desktop: 0% 00:00 remain
Waiting for kits-desktop: 0% 00:00 remain
Waiting for kits-desktop: 0% 00:00 remain
Waiting for kits-desktop: 0% 00:00 remain

To reproduce, add a filter on the database of the slave's my.cnf config and restart:
replicate-wild-ignore-table=employees.%

Then try running pt-online-schema-change on a table of the filtered database on the master:
pt-online-schema-change --execute --alter="engine=innodb" --no-check-replication-filters h=127.0.0.1,P=20594,u=root,p=msandbox,D=employees,t=titles

[Carlos Salguero (carlos-salguero)]

What's the expected behavior?
The documentation says:

If the replicas are configured with any filtering options, you should be careful not to modify any databases or tables that exist on the master and not the replicas, because it could cause replication to fail. For more information on replication rules, see http://dev.mysql.com/doc/en/replication-rules.html.

[Jaime Sicam (jssicam)]

Carlos,

Is it feasible to ignore if the table exists on the slave and still check for slave lag? The workaround is recursion-method=none but slave will lag since it's no longer checked.

Based on the code, it looks like if the tool detects if slave(s) exist, it will check if the created table is on the slave:

   if ( $slaves && scalar @$slaves ) {
      foreach my $slave (@$slaves) {
         my ($pr, $pr_first_report);
         if ( $o->get('progress') ) {
            $pr = new Progress(
               jobsize => scalar @$slaves,
               spec => $o->get('progress'),
               name => "Waiting for " . $slave->name(),
            );
            $pr_first_report = sub {
               print "Waiting forever for new table $new_tbl->{name} to replicate "
                  . "to " . $slave->name() . "...\n";
            };
         }
         $pr->start() if $pr;
         my $has_table = 0;
         while ( !$has_table ) {
            $has_table = $tp->check_table(
               dbh => $slave->dbh(),
               db => $new_tbl->{db},
               tbl => $new_tbl->{tbl}
            );
            last if $has_table;
            $pr->update(
               sub { return 0; },
               first_report => $pr_first_report,
            ) if $pr;
            sleep 1;
         }
      }
   }

But when checking for replication lag it only checks "Seconds behind master".

sub get_slave_lag {
   my ( $self, $dbh ) = @_;
   my $stat = $self->get_slave_status($dbh);
   return unless $stat; # server is not a slave
   return $stat->{seconds_behind_master};
}

So, is it possible to just ignore if the table exists on the slave or is this difficult to fix?

[Carlos Salguero (carlos-salguero)]

Hi,

Please give me a couple of days to analyze this before giving you an answer.

Regards

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-1455