pt-table-sync generates lots of errors in MariaDB logs

[Jonathan Tullett]

When running pt-table-sync, MariaDB outputs lots of errors like the following:
Jan 13 15:01:01 server mysqld: 140113 15:01:01 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.
 The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE xyz set [...] LIMIT 1

Version: pt-table-sync 2.2.6

cmdline: /usr/bin/pt-table-sync --execute --verbose --function MD5 --database $db_name --tables SPC A=utf8,u=****,p=****,h=H1 h=h2

Server: MariaDB 5.5.34.

Update clauses which don't use order by shouldn't have limit specified as the order of the row returned, thus the rows which will be affected, can't be determined. Though this is reported on MariaDB, it'll be the same issue with MySQL.

[Reddox (gopiaravind)]

I fixed this by removing the "LIMIT" clause from pt-table-sync code.

To do this on Ubuntu, open:
nano /usr/bin/pt-table-sync

Search for "UPDATE $self->{dst_db_tbl} SET" and remove "LIMIT" from this statement.

[Jaime Sicam (jssicam)]

This bug can be reproduced in Percona Server and MySQL as well. This bug is triggered when pt-table-sync is used to sync two separate MySQL servers where the target server is using STATEMENT as binary log format. The problem is, as per http://dev.mysql.com/doc/refman/5.5/en/replication-features-limit.html , UPDATE-LIMIT without ORDER BY is not replication safe.

Test case:

Server h1
use test;
create table t1(a int not null primary key, b int not null) engine=innodb;
insert into t1 values (0,0),(1,1),(2,2),(3,3);

Server h2
use test;
create table t1(a int not null primary key, b int not null) engine=innodb;
insert into t1 values (0,0),(1,1),(2,2),(3,4);

Note: binary log must be enabled on h2 and binary log format is STATEMENT

pt-table-sync --execute h=h1 h=h2

Warning:
141111 13:34:57 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE `test`.`t1` SET `b`='3' WHERE `a`='3' LIMIT 1 /*percona-toolkit src_db:test src_tbl:t1 src_dsn:P=24394,h=127.0.0.1 dst_db:test dst_tbl:t1 dst_dsn:P=24395,h=127.0.0.1 lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:3553 user:user host:localhost.localdomain*/

[Shahriyar Rzayev (rzayev-sehriyar)]

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