pt-table-sync: do not set binlog_format on destination server

[Simon J Mudd]

bug#997155 refers to an issue when using RBR and this triggered setting binlog_format=STATEMENT.

However, this is not quite right.
If syncing a table between 2 different _unconnected_ servers, not linked via replication, then the effect the binlog_format is irrelevant, and additionally if the destination server is already a master which runs in RBR mode you really should not be trying to change the binlog format.

So please provide an option to _not_ set this option on the destination server (potentially a master), when synchronising a table between 2 servers which are not connected by replication.

It's been necessary to work around this by patching the code but that's ugly and under these circumstances the check and setting the binlog_format is really not necessary.

Seen on 2.2.12 (percona-toolkit-2.2.13-1.noarch)
Servers involved are running MySQL 5.6 and MariaDB 10.0

If you need more info please let me know.

[Frank Cizmich (frank-cizmich)]

Hello Simon,

Although not recommended, the change of binlog_format to "statement" happens at the session level, so it doesn't affect the regular operation of the master.
Nevertheless I agree that it's unnecessary in the use cases you mention. Also, the change requires super privilege, so if it's not needed it should be avoided.

I've prepared a fix without adding an extra option, since it's clear from the use cases when binlog_format should be altered or not.

Thanks for reporting
Regards
Frank

[Simon J Mudd (sjmudd)]

Hi Frank,

Thanks for responding to this so promptly. I'll look forward to seeing this when the change is pushed out.

[Shashank Sahni (shredder12)]

Hi Frank,

I am using Percona Toolkit 2.2.17-1 on Ubuntu 14.04 and still see this issue. Was this ever fixed?

[Nicholas Sherlock (n-sherlock)]

There's hints for patching in a --no-check-binlog-format setting here if you don't want to wait:

https://bugs.launchpad.net/percona-toolkit/+bug/982381/comments/5

I still see this problem in 2.2.18 on Ubuntu 16.04, so I'm looking forward to a fix being included!

[Brian Blood (u-brian-c)]

Still seeing this issue as of version 3.0.4 in Debian (8.9) Jessie

[Brian Blood (u-brian-c)]

I have patched my 3.0.4 as follows:

sub get_cxn {
...
   if ( VersionParser->new($dbh) >= '5.1.29' ) {
+ if ( $o->get('check-binlog-format') ) {
      $sql = 'SELECT @@binlog_format';

...

               . "You will need to manually set binlog_format to 'STATEMENT' "
               . "before running this tool.\n";
         }
      }
+ }
   }

and I am using --nocheck-binlog-format on the command line to mirror this parameter from pt-table-checksum

[Brian Blood (u-brian-c)]

also required patch

=item --[no]check-binlog-format

default: yes

Check that the C<binlog_format> is the same on all servers.

See "Replicas using row-based replication" under L<"LIMITATIONS">.

[Shahriyar Rzayev (rzayev-sehriyar)]

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