{"id":125,"date":"2013-05-26T01:50:33","date_gmt":"2013-05-25T23:50:33","guid":{"rendered":"http:\/\/catyellow.net\/blog\/?p=125"},"modified":"2013-06-04T21:31:01","modified_gmt":"2013-06-04T19:31:01","slug":"syncing-mysql-tables-by-pt-table-checksum-when-there-is-no-unique-key","status":"publish","type":"post","link":"https:\/\/catyellow.net\/blog\/?p=125","title":{"rendered":"Syncing MySQL tables by pt-table-checksum when there is no unique key"},"content":{"rendered":"

Today I needed to checksum and sync tables in simple master-slave replication.
\nSo I used percona-toolkit<\/a> for this.<\/p>\n

Following checksumming (command should be running on master) of tables shows that repl.t9 and repl.t1 tables have differences:<\/p>\n

\r\n$ pt-table-checksum --replicate=percona.checksums --create-replicate-table --empty-replicate-table --ask-pass h=localhost,u=root\r\n            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE\r\n05-26T01:50:30      0      1        4       1       0   0.016 repl.t1\r\n05-26T01:50:30      0      1        6       1       0   0.016 repl.t9\r\n<\/pre>\n
Let’s run sync and check result:
\n<\/p>\n
\r\n$ pt-table-sync --dry-run --replicate=percona.checksums --ask-pass --sync-to-master h=localhost,u=root\r\n# NOTE: --dry-run does not show if data needs to be synced because it\r\n#       does not access, compare or sync data.  --dry-run only shows\r\n#       the work that would be done.\r\n# Syncing via replication h=localhost,p=...,u=root in dry-run mode, without accessing or comparing data\r\n# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE\r\nCan't make changes on the master because no unique index exists at \/usr\/bin\/pt-table-sync line 10591.  while doing repl.t1 on localhost\r\n#      0       0      0      0 0         02:09:50 02:09:50 1    repl.t1\r\nCan't make changes on the master because no unique index exists at \/usr\/bin\/pt-table-sync line 10591.  while doing repl.t9 on localhost\r\n#      0       0      0      0 0         02:09:50 02:09:50 1    repl.t9\r\n<\/pre>\n
The latter error shows that it can’t sync data because both tables repl.t1 and repl.t9 has no unique index. Let’s see their structure and data (on Master) because on slave they both are empty (I used delete * from “tablename”):<\/p>\n
\r\nmysql> show create table repl.t1\\G;\r\n*************************** 1. row ***************************\r\n       Table: t1\r\nCreate Table: CREATE TABLE `t1` (\r\n  `t1` int(11) DEFAULT NULL\r\n) ENGINE=InnoDB DEFAULT CHARSET=latin1\r\n1 row in set (0.00 sec)\r\n\r\nERROR: \r\nNo query specified\r\n\r\nmysql> show create table repl.t9\\G;\r\n*************************** 1. row ***************************\r\n       Table: t9\r\nCreate Table: CREATE TABLE `t9` (\r\n  `a` int(11) DEFAULT NULL\r\n) ENGINE=InnoDB DEFAULT CHARSET=latin1\r\n1 row in set (0.00 sec)\r\n\r\nERROR: \r\nNo query specified\r\n\r\nmysql> select * from repl.t1;\r\n+------+\r\n| t1   |\r\n+------+\r\n|    1 |\r\n|    2 |\r\n|    3 |\r\n|    1 |\r\n+------+\r\n4 rows in set (0.00 sec)\r\n\r\nmysql> select * from repl.t9;\r\n+------+\r\n| a    |\r\n+------+\r\n|    1 |\r\n|    1 |\r\n|    1 |\r\n|    2 |\r\n|    3 |\r\n|    3 |\r\n+------+\r\n6 rows in set (0.00 sec)\r\n<\/pre>\n
So how to avoid that quickly?
\nThe answer is: create new auto-increment field (unique index).<\/p>\n
repl.t1 + repl.t9 (on Master):<\/p>\n
\r\nmysql> alter table repl.t1 add auto_id Int NOT NULL AUTO_INCREMENT key;\r\nQuery OK, 4 rows affected (0.01 sec)\r\nRecords: 4  Duplicates: 0  Warnings: 0\r\n\r\nmysql> alter table repl.t9 add auto_id Int NOT NULL AUTO_INCREMENT key;\r\nQuery OK, 6 rows affected (0.61 sec)\r\nRecords: 6  Duplicates: 0  Warnings: 0\r\n<\/pre>\n
Let’s check data and structure again on Master:<\/p>\n
\r\nmysql> show create table repl.t1\\G;\r\n*************************** 1. row ***************************\r\n       Table: t1\r\nCreate Table: CREATE TABLE `t1` (\r\n  `t1` int(11) DEFAULT NULL,\r\n  `auto_id` int(11) NOT NULL AUTO_INCREMENT,\r\n  PRIMARY KEY (`auto_id`)\r\n) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1\r\n1 row in set (0.00 sec)\r\n\r\nERROR: \r\nNo query specified\r\n\r\nmysql> show create table repl.t9\\G;\r\n*************************** 1. row ***************************\r\n       Table: t9\r\nCreate Table: CREATE TABLE `t9` (\r\n  `a` int(11) DEFAULT NULL,\r\n  `auto_id` int(11) NOT NULL AUTO_INCREMENT,\r\n  PRIMARY KEY (`auto_id`)\r\n) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1\r\n1 row in set (0.00 sec)\r\n\r\nERROR: \r\nNo query specified\r\n\r\nmysql> select * from repl.t1;\r\n+------+---------+\r\n| t1   | auto_id |\r\n+------+---------+\r\n|    1 |       1 |\r\n|    2 |       2 |\r\n|    3 |       3 |\r\n|    1 |       4 |\r\n+------+---------+\r\n4 rows in set (0.00 sec)\r\n\r\nmysql> select * from repl.t9;\r\n+------+---------+\r\n| a    | auto_id |\r\n+------+---------+\r\n|    1 |       1 |\r\n|    1 |       2 |\r\n|    1 |       3 |\r\n|    2 |       4 |\r\n|    3 |       5 |\r\n|    3 |       6 |\r\n+------+---------+\r\n6 rows in set (0.00 sec)\r\n<\/pre>\n
and Slave:<\/p>\n
\r\nmysql> show create table repl.t1\\G;\r\n*************************** 1. row ***************************\r\n       Table: t1\r\nCreate Table: CREATE TABLE `t1` (\r\n  `t1` int(11) DEFAULT NULL,\r\n  `auto_id` int(11) NOT NULL AUTO_INCREMENT,\r\n  PRIMARY KEY (`auto_id`)\r\n) ENGINE=InnoDB DEFAULT CHARSET=latin1\r\n1 row in set (0.00 sec)\r\n\r\nERROR: \r\nNo query specified\r\n\r\nmysql> show create table repl.t9\\G;\r\n*************************** 1. row ***************************\r\n       Table: t9\r\nCreate Table: CREATE TABLE `t9` (\r\n  `a` int(11) DEFAULT NULL,\r\n  `auto_id` int(11) NOT NULL AUTO_INCREMENT,\r\n  PRIMARY KEY (`auto_id`)\r\n) ENGINE=InnoDB DEFAULT CHARSET=latin1\r\n1 row in set (0.00 sec)\r\n\r\nERROR: \r\nNo query specified\r\n\r\nmysql> select * from repl.t1;\r\nEmpty set (0.00 sec)\r\n\r\nmysql> select * from repl.t9;\r\nEmpty set (0.00 sec)\r\n<\/pre>\n
And let’s try to sync data (on Slave):<\/p>\n
\r\n$ pt-table-sync --dry-run --replicate=percona.checksums --ask-pass --sync-to-master h=localhost,u=root\r\n# NOTE: --dry-run does not show if data needs to be synced because it\r\n#       does not access, compare or sync data.  --dry-run only shows\r\n#       the work that would be done.\r\n# Syncing via replication h=localhost,p=...,u=root in dry-run mode, without accessing or comparing data\r\n# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE\r\n#      0       0      0      0 Nibble    02:38:40 02:38:40 0    mysql.proxies_priv\r\n#      0       0      0      0 Nibble    02:38:40 02:38:40 0    mysql.user\r\n#      0       0      0      0 Nibble    02:38:40 02:38:40 0    repl.checksums\r\n#      0       0      0      0 Chunk     02:38:40 02:38:40 0    repl.t1\r\n#      0       0      0      0 Chunk     02:38:40 02:38:40 0    repl.t9\r\n$ pt-table-sync --execute --replicate=percona.checksums --sync-to-master h=localhost,u=root,p=root\r\n<\/pre>\n
Let’s check again checksum on Master and then check data on Slave:<\/p>\n
\r\n$ pt-table-checksum --replicate=percona.checksums --create-replicate-table --empty-replicate-table --ask-pass h=localhost,u=root\r\nEnter MySQL password: \r\n            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE\r\n05-26T02:41:06      0      0        4       1       0   0.015 repl.t1\r\n05-26T02:41:06      0      0        6       1       0   0.015 repl.t9\r\n<\/pre>\n
\r\nmysql> select * from repl.t1;\r\n+------+---------+\r\n| t1   | auto_id |\r\n+------+---------+\r\n|    1 |       1 |\r\n|    2 |       2 |\r\n|    3 |       3 |\r\n|    1 |       4 |\r\n+------+---------+\r\n4 rows in set (0.00 sec)\r\n\r\nmysql> select * from repl.t9;\r\n+------+---------+\r\n| a    | auto_id |\r\n+------+---------+\r\n|    1 |       1 |\r\n|    1 |       2 |\r\n|    1 |       3 |\r\n|    2 |       4 |\r\n|    3 |       5 |\r\n|    3 |       6 |\r\n+------+---------+\r\n6 rows in set (0.00 sec)\r\n<\/pre>\n
it works!<\/p>\n","protected":false},"excerpt":{"rendered":"
Today I needed to checksum and sync tables in simple master-slave replication. So I used percona-toolkit for this. Following checksumming (command should be running on master) of tables shows that repl.t9 and repl.t1 tables have differences: Let’s run sync and check result:<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[3,35,36],"_links":{"self":[{"href":"https:\/\/catyellow.net\/blog\/index.php?rest_route=\/wp\/v2\/posts\/125"}],"collection":[{"href":"https:\/\/catyellow.net\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/catyellow.net\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/catyellow.net\/blog\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/catyellow.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=125"}],"version-history":[{"count":7,"href":"https:\/\/catyellow.net\/blog\/index.php?rest_route=\/wp\/v2\/posts\/125\/revisions"}],"predecessor-version":[{"id":145,"href":"https:\/\/catyellow.net\/blog\/index.php?rest_route=\/wp\/v2\/posts\/125\/revisions\/145"}],"wp:attachment":[{"href":"https:\/\/catyellow.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=125"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/catyellow.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=125"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/catyellow.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=125"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}