pt-table-sync to detect galera nodes

Registered by Muhammad Irfan

I noticed pt-table-sync is not able to detect galera nodes. To illustrate this problem, I intentionally unsync one node in three node Percona XtraDB Cluster to see how it works to sync node with data drift but it fails. See below.

root@node1:~# pt-table-sync --dry-run h=localhost,u=checksum_user,p=checksum_password,D=test,t=dummy h=node2
# NOTE: --dry-run does not show if data needs to be synced because it
# does not access, compare or sync data. --dry-run only shows
# the work that would be done.
# Syncing D=test,h=node2,p=...,t=dummy,u=checksum_user in dry-run mode, without accessing or comparing data
# DELETE REPLACE INSERT UPDATE ALGORITHM START END EXIT DATABASE.TABLE
# 0 0 0 0 Chunk 03:22:40 03:22:40 0 test.dummy

In dry-run it shows 0 for DELETE/REPLACE and so on.

root@node1:~# pt-table-sync --print h=localhost,u=checksum_user,p=checksum_password,D=test,t=dummy h=node2
INSERT INTO `test`.`dummy`(`id`, `name`) VALUES ('5', 'e') /*percona-toolkit src_db:test src_tbl:dummy src_dsn:D=test,h=localhost,p=...,t=dummy,u=checksum_user dst_db:test dst_tbl:dummy dst_dsn:D=test,h=node2,p=...,t=dummy,u=checksum_user lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:6538 user:root host:node1*/;
UPDATE `test`.`dummy` SET `name`='d' WHERE `id`='4' LIMIT 1 /*percona-toolkit src_db:test src_tbl:dummy src_dsn:D=test,h=localhost,p=...,t=dummy,u=checksum_user dst_db:test dst_tbl:dummy dst_dsn:D=test,h=node2,p=...,t=dummy,u=checksum_user lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:6538 user:root host:node1*/;

pt-table-sync --print option prints actual INSERT/UPDATE statements instead REPLACE. I thought it's because pt-table-sync fails to detect PXC nodes.

root@node1:~# pt-table-sync --print --replace h=localhost,u=checksum_user,p=checksum_password,D=test,t=dummy h=node2
REPLACE INTO `test`.`dummy`(`id`, `name`) VALUES ('4', 'd') /*percona-toolkit src_db:test src_tbl:dummy src_dsn:D=test,h=localhost,p=...,t=dummy,u=checksum_user dst_db:test dst_tbl:dummy dst_dsn:D=test,h=node2,p=...,t=dummy,u=checksum_user lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:6541 user:root host:node1*/;
REPLACE INTO `test`.`dummy`(`id`, `name`) VALUES ('5', 'e') /*percona-toolkit src_db:test src_tbl:dummy src_dsn:D=test,h=localhost,p=...,t=dummy,u=checksum_user dst_db:test dst_tbl:dummy dst_dsn:D=test,h=node2,p=...,t=dummy,u=checksum_user lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:6541 user:root host:node1*/;

As soon as I use --replace with --print option it prints REPLACE statements.

root@node1:~# pt-table-sync --execute h=localhost,u=checksum_user,p=checksum_password,D=test,t=dummy h=node2

Again, If i use --execute it will execute actual INSERT/UPDATE statements instead REPLACE and crashes already synchronized nodes with "Duplicate Key error" To workaround this problem, I have to use --execute along --replace option to execute REPLACE statements so those are "no-op" changes for already synchronized nodes as it will be for master in replication environment. However, --execute along --replace option also prints below warning/error but actually synchronizes the differences.

root@node1:~# pt-table-sync --execute --replace h=localhost,u=checksum_user,p=checksum_password,D=test,t=dummy h=node2
DBD::mysql::db commit failed: Deadlock found when trying to get lock; try restarting transaction at /usr/bin/pt-table-sync line 11048.
Issuing rollback() due to DESTROY without explicit disconnect() of DBD::mysql::db handle test;host=localhost;mysql_read_default_group=client at /usr/bin/pt-table-sync line 11048.

Blueprint information

Status:
Not started
Approver:
None
Priority:
Undefined
Drafter:
Muhammad Irfan
Direction:
Needs approval
Assignee:
None
Definition:
New
Series goal:
None
Implementation:
Unknown
Milestone target:
None

Related branches

Sprints

Whiteboard

(?)

Work Items

This blueprint contains Public information 
Everyone can see this information.

Subscribers

No subscribers.