My favorites | Sign in
Project Home Downloads Wiki Issues Source
New issue   Search
  Advanced search   Search tips   Subscriptions
Issue 95: Make mk-table-sync force statement-based binlog format on 5.1 and greater
5 people starred this issue and may be notified of changes. Back to list
Reported by, Oct 7, 2008
If this is a problem report, what steps will reproduce the problem?
1. If using the --replicate or --synctomaster options, statements to change
the slave are done via replication.
2. If the diff algorithm determines that there are records on slave to be
deleted it generates the proper delete statement and executes it on the master.
3. The statement is not replicated because no changes occur on the master.

What is the expected output? What do you see instead?


What information do you get from mk-<toolname>--version?

mk-table-sync  Ver 1.0.9 Distrib 2325 Changeset 2311

What is your MySQL, Perl, DBI, and DBD::mysql version?


What is your operating system/distribution?


If you think it'll be useful, please run the tool with MKDEBUG=1 and
capture the full output in a file, then attach the file.  For example,
MKDEBUG=1 mk-<toolname> [options] > debug.txt.  Is there anything in this
file that you think we should pay special attention to?  Please gzip the
file if it's large.

Please provide any additional information below.

I've been able to work around the issue by patching the code.  The patch is
not really the appropriate fix, but does allow it to work for my specific
situation.  The appropriate fix appears to be a bit more complex due to the
closure that is being used to make the changes in
TableSyncer::sync_table().  I've attached the patch.
624 bytes   View   Download
Oct 8, 2008
Project Member #1 baron.schwartz
Is the issue related to row-based replication being used?  Can this be solved just by
setting the logging type to statement?  I believe that should work.
Labels: Tool-mk_table_sync
Oct 8, 2008
I believe that I had the server explicitly set to use statement based replication at
the time, but I cannot be positive about that.  I will test again and see.  
That being said, is there any reason not to explicitly run the delete statements
against the slave server rather than have them replicate?
Oct 8, 2008
Project Member #3 baron.schwartz
The biggest reason to run all the statements on the master is to avoid race
conditions and messing up the slave's data because of out-of-sync writes.  Even if
everything is perfectly synced with locks, which adds a lot of cost and delay, the
other statements happening on the master at the same time may interleave and cause
problems once they replicate to the slave.  (Assuming syncing is happening on a live
server that can't be taken down, which is the usual case at Percona when someone asks
us to fix their critical production server.) There are also a bunch of nasty cases
where the slave could get slightly delayed and the constant locking and waiting could
make the whole process unbearably slow.
Oct 8, 2008
Very well, that sounds reasonable.  Perhaps the solution would be for the application
to explicitly set the session variable binlog_format = 'STATEMENT' when applying
changes?  I know that they have just modified that as of 5.1.28 to only allow a user
with the SUPER priv to be able to set the binlog_format session variable, but that
should be ok as the dba is most likely the one to be using this tool.
Oct 8, 2008
Project Member #5 baron.schwartz
That's good to know about SUPER.  That's a very odd change, is there a bug report on or something to justify this?

mk-table-checksum already sets the binlog_format to accomplish this.  I guess I've
not used mk-table-sync on 5.1 enough to have noticed this.
Status: Accepted
Oct 8, 2008
I misquoted the version that the SUPER privilege is needed.  It appears in 5.1.29
(not yet released, but I have been doing testing with the bzr code).
Aug 27, 2009
Revisiting this issue: so was the problem/solution as Baron said in comment #1: set
statement-based replication instead of row-based?  Or is there still an issue here?
Status: Started
Aug 27, 2009
I think that setting statement replication did fix the issue.
Aug 28, 2009
Ok.  In that case I'll close this issue for now.  If it's found later that there
really is a bug, please let us know.  Thanks.
Status: NotBug
Aug 28, 2009
Still, mk-table-sync should set the binlog_format to statement for the duration of
the run to ensure that changes take effect, I think?
Aug 28, 2009
Project Member #11 baron.schwartz
I agree, mk-table-sync relies on statement-based logging.

Summary was: mk-table-sync: DELETE statements do not work for replication
Summary: Make mk-table-sync force statement-based binlog format on 5.1 and greater
Status: Accepted
Labels: -Type-Defect Type-Enhancement
Sep 2, 2009
(No comment was entered for this change.)
Labels: Milestone-2009_09
Jan 23, 2010
Project Member #13 baron.schwartz
 Issue 817  has been merged into this issue.
Jan 30, 2010
Project Member #14 baron.schwartz
(No comment was entered for this change.)
Labels: Tag-binlog
Jan 31, 2010
Project Member #15 baron.schwartz
(No comment was entered for this change.)
Labels: -Milestone-2009_09 PatchContributed
Jan 31, 2010
Project Member #16 baron.schwartz
(No comment was entered for this change.)
Labels: QuickWin
Feb 19, 2010
This issue was closed by revision r5824.
Status: Fixed
Mergedinto: -
Mar 23, 2011
I'm not sure this is or should be considered fixed at this point, as it now has required SUPER privileges which is not always desirable.  Yes this tool CAN be used to sync replication slaves, but it could also be used to sync data from one table to another on a timed interval (ie: production database versus statistical database) and can replicate individual tables (which MySQL replication can not).

What this has done is prevent an ordinary user (on a shared hosting or managed hosting environment) from using this tool to sync one table with another.

As disabling it has little effect if replication is not used, this should be a toggled option (with a friendly help message) to not do this is the user is running as an ordinary user.  The inclusion of an option to run without administrative privs and an if statement wrapping around this is quite necessary.

Why exclude the market to only replication?
Mar 23, 2011
Project Member #19 baron.schwartz
I think you are right, we forgot about the diversity of use cases for this tool.  We need to make it change the binlog format and ask for SUPER privilege only when replication-based syncing is used.
Status: Started
Apr 3, 2011
barron.., on that note, a very related bug (that likely copies the same code)

Access denied; you need (at least one of) the SUPER,REPLICATION CLIENT privilege(s) for this operation [for Statement "SHOW SLAVE STATUS"] at line 6070 while doing ...

The command is simply 
# mk-table-sync --dry-run --execute h=localhost,D=db,u=user,p=pw,t=table h=host2,D=db2,u=user,p=pw

In the following:
sub get_slave_status {
   my ( $self, $dbh ) = @_;
   if ( !$self->{not_a_slave}->{$dbh} ) {
      my $sth = $self->{sths}->{$dbh}->{SLAVE_STATUS}
            ||= $dbh->prepare('SHOW SLAVE STATUS');
      MKDEBUG && _d($dbh, 'SHOW SLAVE STATUS');
      my ($ss) = @{$sth->fetchall_arrayref({})};

      if ( $ss && %$ss ) {
         $ss = { map { lc($_) => $ss->{$_} } keys %$ss }; # lowercase the keys
         return $ss;

      MKDEBUG && _d('This server returns nothing for SHOW SLAVE STATUS');

It should be treated the same way.  If you're doing a simple table sync, it should just sync the table.

By commenting out the line for the SHOW SLAVE STATUS and the one for the binlog format as previously mentioned, it now runs perfectly on syncing a table between two standalone databases...

Aug 19, 2011
#21 NorthernRavenNWT
Nothing further was done on this?  I was going to use mk-table-sync to do some simple non-replication syncing to a database in a CPanel shared-hosting environment, where replication and SUPER privileges are not possible.

I'm not a Perl programmer, but I commented out the lines between the two MKDEBUGs in get_slave_status, and the SET @@binlog_format execution line in get_cxn, and a simple sync seemed to work.  But it would be nice if this were baked in... :)
Apr 25, 2012
Work around for getting a table sync issue when super privileges are not available and not syncing between a master and a slave.  This code change adds an option called "--no-bin-log-format" to tell pt-table-sync that is is okay to avoid using "SET @@binlog_format=STATEMENT" .

I added the following code to the pt-table-sync (yes I know that this is a maatkit forum) - the link in the code references this forum, so maybe this will help someone.

This addresses only one of many possible places where the super privilege is needed

In pt-table-sync there is original code looking like this:

sub get_cxn {
   # Ensure statement-based replication.                                                                                                 
   $sql = '/*!50105 SET @@binlog_format="STATEMENT"*/';
   PTDEBUG && _d($dbh, $sql);

The changed code is:

   # Ensure statement-based replication.                                                                                                 
   if ( $o->get('bin-log-format') ) {
       $sql = '/*!50105 SET @@binlog_format="STATEMENT"*/';
       PTDEBUG && _d($dbh, $sql);

which requires that the bin-log-format option be added to the POD section,  So I added this:


=item --[no]bin-log                                                                                                                      
default: yes                                                                                                                             
Log to the binary log (C<SET SQL_LOG_BIN=1>).                                                                                            
Specifying C<--no-bin-log> will C<SET SQL_LOG_BIN=0>.                                                                                    
=item --buffer-in-mysql                                                                                                                  

Changed code:

=item --[no]bin-log                                                                                                                      
default: yes                                                                                                                             
Log to the binary log (C<SET SQL_LOG_BIN=1>).                                                                                            
Specifying C<--no-bin-log> will C<SET SQL_LOG_BIN=0>.                                                                                    
=item --[no]bin-log-format                                                                                                               
default: yes                                                                                                                             
Force binlog_format to STATEMENT (C<SET @@binlog_format=STATEMENT>).                                                                     
Specifying C<--no-bin-log> will prevent binlog_format modification.                                                                      
=item --buffer-in-mysql                                                                                                                  

I'll be sending this change to the percona folks when I figure out their build environment and create a test case.  Depending on their workload, merit of the change, and quality of the change, they may or may not include it.

Sign in to add a comment

Powered by Google Project Hosting