Syncing MySQL tables by pt-table-checksum when there is no unique key

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:

$ pt-table-checksum --replicate=percona.checksums --create-replicate-table --empty-replicate-table --ask-pass h=localhost,u=root
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
05-26T01:50:30      0      1        4       1       0   0.016 repl.t1
05-26T01:50:30      0      1        6       1       0   0.016 repl.t9

Let’s run sync and check result:

$ pt-table-sync --dry-run --replicate=percona.checksums --ask-pass --sync-to-master h=localhost,u=root
# 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 via replication h=localhost,p=...,u=root in dry-run mode, without accessing or comparing data
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
Can'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
#      0       0      0      0 0         02:09:50 02:09:50 1    repl.t1
Can'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
#      0       0      0      0 0         02:09:50 02:09:50 1    repl.t9

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”):

mysql> show create table repl.t1\G;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `t1` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> show create table repl.t9\G;
*************************** 1. row ***************************
       Table: t9
Create Table: CREATE TABLE `t9` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR: 
No query specified

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

mysql> select * from repl.t9;
+------+
| a    |
+------+
|    1 |
|    1 |
|    1 |
|    2 |
|    3 |
|    3 |
+------+
6 rows in set (0.00 sec)

So how to avoid that quickly?
The answer is: create new auto-increment field (unique index).

repl.t1 + repl.t9 (on Master):

mysql> alter table repl.t1 add auto_id Int NOT NULL AUTO_INCREMENT key;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> alter table repl.t9 add auto_id Int NOT NULL AUTO_INCREMENT key;
Query OK, 6 rows affected (0.61 sec)
Records: 6  Duplicates: 0  Warnings: 0

Let’s check data and structure again on Master:

mysql> show create table repl.t1\G;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `t1` int(11) DEFAULT NULL,
  `auto_id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`auto_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> show create table repl.t9\G;
*************************** 1. row ***************************
       Table: t9
Create Table: CREATE TABLE `t9` (
  `a` int(11) DEFAULT NULL,
  `auto_id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`auto_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR: 
No query specified

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

mysql> select * from repl.t9;
+------+---------+
| a    | auto_id |
+------+---------+
|    1 |       1 |
|    1 |       2 |
|    1 |       3 |
|    2 |       4 |
|    3 |       5 |
|    3 |       6 |
+------+---------+
6 rows in set (0.00 sec)

and Slave:

mysql> show create table repl.t1\G;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `t1` int(11) DEFAULT NULL,
  `auto_id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`auto_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> show create table repl.t9\G;
*************************** 1. row ***************************
       Table: t9
Create Table: CREATE TABLE `t9` (
  `a` int(11) DEFAULT NULL,
  `auto_id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`auto_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> select * from repl.t1;
Empty set (0.00 sec)

mysql> select * from repl.t9;
Empty set (0.00 sec)

And let’s try to sync data (on Slave):

$ pt-table-sync --dry-run --replicate=percona.checksums --ask-pass --sync-to-master h=localhost,u=root
# 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 via replication h=localhost,p=...,u=root in dry-run mode, without accessing or comparing data
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      0       0      0      0 Nibble    02:38:40 02:38:40 0    mysql.proxies_priv
#      0       0      0      0 Nibble    02:38:40 02:38:40 0    mysql.user
#      0       0      0      0 Nibble    02:38:40 02:38:40 0    repl.checksums
#      0       0      0      0 Chunk     02:38:40 02:38:40 0    repl.t1
#      0       0      0      0 Chunk     02:38:40 02:38:40 0    repl.t9
$ pt-table-sync --execute --replicate=percona.checksums --sync-to-master h=localhost,u=root,p=root

Let’s check again checksum on Master and then check data on Slave:

$ pt-table-checksum --replicate=percona.checksums --create-replicate-table --empty-replicate-table --ask-pass h=localhost,u=root
Enter MySQL password: 
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
05-26T02:41:06      0      0        4       1       0   0.015 repl.t1
05-26T02:41:06      0      0        6       1       0   0.015 repl.t9

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

mysql> select * from repl.t9;
+------+---------+
| a    | auto_id |
+------+---------+
|    1 |       1 |
|    1 |       2 |
|    1 |       3 |
|    2 |       4 |
|    3 |       5 |
|    3 |       6 |
+------+---------+
6 rows in set (0.00 sec)

it works!

cat-yellow

My main background for a recent years is MySQL DEV/DBA engineer but I’m also participating in wider range of tasks which could be likely called as DataOps/DevOps tasks. You can see my online CV here: http://catyellow.net

catyellow.net – blog

useful IT info

Syncing MySQL tables by pt-table-checksum when there is no unique key