pt-table-checksum is not showing table data mismatch with PXC

[Ramesh Sivaraman]

pt-table-checksum is not showing table data mismatch with PXC

Testcase

Node 1
******

drop database if exists percona;create database percona;
drop table if exists percona.dsns;create table percona.dsns(id int,parent_id int,dsn varchar(100));
insert into percona.dsns (id,dsn) values (1,'h=127.0.0.1,P=14000,u=root'),(2,'h=127.0.0.1,P=14100,u=root'),(3,'h=127.0.0.1,P=14200,u=root');

mysql> use test;
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t1 values (1), (2), (3), (4);
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update test.t1 set id = id + 100;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4 Changed: 4 Warnings: 0

mysql> select * from t1;
+------+
| id |
+------+
| 101 |
| 102 |
| 103 |
| 104 |
+------+
4 rows in set (0.00 sec)

mysql> select @@port;
+--------+
| @@port |
+--------+
| 14000 |
+--------+
1 row in set (0.00 sec)

mysql>

Node 2
******

mysql> select * from test.t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)

mysql> select @@port;
+--------+
| @@port |
+--------+
| 14100 |
+--------+
1 row in set (0.00 sec)

mysql>

pt-table-checksum result

[ramesh@qaserver-01 Percona-XtraDB-Cluster-5.6.30-rel76.3-25.16..Linux.x86_64]$ pt-table-checksum h=127.0.0.1,P=14000,u=root -d test --recursion-method dsn=h=127.0.0.1,P=14000,u=root,D=percona,t=dsns
Not checking replica lag on qaserver-01.ci.percona.com because it is a cluster node.
Not checking replica lag on qaserver-01.ci.percona.com because it is a cluster node.
            TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
06-17T09:35:17 0 0 4 1 0 0.066 test.t1
[ramesh@qaserver-01 Percona-XtraDB-Cluster-5.6.30-rel76.3-25.16..Linux.x86_64]$

When I updated single row it started showing the difference

Node1

mysql> update test.t1 set id = id + 100 where id=101;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from t1;
+------+
| id |
+------+
| 201 |
| 102 |
| 103 |
| 104 |
+------+
4 rows in set (0.00 sec)

mysql>

Node2

mysql> select * from test.t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)

mysql>

[ramesh@qaserver-01 Percona-XtraDB-Cluster-5.6.30-rel76.3-25.16..Linux.x86_64]$ pt-table-checksum h=127.0.0.1,P=14000,u=root -d test --recursion-method dsn=h=127.0.0.1,P=14000,u=root,D=percona,t=dsns
Not checking replica lag on qaserver-01.ci.percona.com because it is a cluster node.
Not checking replica lag on qaserver-01.ci.percona.com because it is a cluster node.
            TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
06-17T09:38:36 0 1 4 1 0 0.074 test.t1
[ramesh@qaserver-01 Percona-XtraDB-Cluster-5.6.30-rel76.3-25.16..Linux.x86_64]$

[Muhammad Irfan (muhammad-irfan)]

I'm able to verify this as described.

percona1> select * from irfan;
+------+
| id |
+------+
| 101 |
| 102 |
| 103 |
| 104 |
+------+
4 rows in set (0.00 sec)

From other cluster nodes:
percona2> select * from test.irfan;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)

[root@percona1 ~]# pt-table-checksum --empty-replicate-table --replicate percona.checksums --ignore-databases=mysql h=192.168.70.2,u=checksum_user,p=checksum_password --recursion-method=cluster
Not checking replica lag on percona4 because it is a cluster node.
Not checking replica lag on percona2 because it is a cluster node.
Not checking replica lag on percona3 because it is a cluster node.
            TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
08-16T14:28:26 0 0 6 1 0 0.753 sys.sys_config
08-16T14:28:26 0 0 2 1 0 0.118 test.consistency
08-16T14:28:26 0 0 4 1 0 0.148 test.irfan
08-16T14:28:27 0 0 1 1 0 0.407 test.m_deliver
08-16T14:28:27 0 0 1 1 0 0.173 test.m_facility
08-16T14:28:27 0 0 1 1 0 0.089 test.m_facility_history_action
08-16T14:28:27 0 0 0 1 0 0.093 test.m_fqdn
08-16T14:28:27 0 0 1 1 0 0.084 test.m_map_class
08-16T14:28:32 0 0 100000 8 0 4.541 test.sbtest1
08-16T14:28:38 0 0 497446 10 0 5.877 test.t1
08-16T14:28:38 0 0 6 1 0 0.184 test.t2
08-16T14:28:38 0 0 1 1 0 0.455 test.t3
08-16T14:28:38 0 1 3 1 0 0.198 test.t4
08-16T14:28:39 0 0 1 1 0 0.097 test.t_facility_history

[root@percona1 ~]# pt-table-checksum --replicate percona.checksums --ignore-databases=mysql h=192.168.70.2,u=checksum_user,p=checksum_password --recursion-method=dsn=D=percona,t=dsns
Not checking replica lag on percona2 because it is a cluster node.
Not checking replica lag on percona3 because it is a cluster node.
Not checking replica lag on percona4 because it is a cluster node.
            TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
08-16T14:39:57 0 0 4 1 0 0.192 percona.dsns
08-16T14:39:57 0 0 6 1 0 0.248 sys.sys_config
08-16T14:39:57 0 0 2 1 0 0.277 test.consistency
08-16T14:39:58 0 0 4 1 0 0.367 test.irfan
08-16T14:39:58 0 0 1 1 0 0.206 test.m_deliver
08-16T14:39:58 0 0 1 1 0 0.211 test.m_facility
08-16T14:39:58 0 0 1 1 0 0.210 test.m_facility_history_action
08-16T14:39:59 0 0 0 1 0 0.152 test.m_fqdn
08-16T14:39:59 0 0 1 1 0 0.185 test.m_map_class
08-16T14:40:01 0 0 100000 9 0 2.276 test.sbtest1
08-16T14:40:07 0 0 497446 11 0 5.487 test.t1
08-16T14:40:07 0 0 ...

[Muhammad Irfan (muhammad-irfan)]

5.7.18-15-57-log Percona XtraDB Cluster

[Shahriyar Rzayev (rzayev-sehriyar)]

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