pt-table-checksum updates percona.checksum inconsistently

[Aleksandr Kuzminsky]

pt-table-checksum does two basic operations:

1. "UPDATE $repl_table SET chunk_time = ?, master_crc = ?, master_cnt = ? "
2. "REPLACE INTO $repl_table ... this_cnt, this_crc'

i.e. the first sets master_* fields and the second - this_* fields.

Due to a fact that these two are done as separate transactions for short period of time the row on a slave is inconsistent.
master_* fields are set, but this_* are not yet.

This may cause false positive triggers of monitoring software.

As a solution UPDATE and REPLACE may be wrapped into BEGIN/COMMIT, so the change of a row on a slave is atomic.

[Jaime Sicam (jssicam)]

The false positives appear when pt-table-checksum is running.

To test:
1. On Slave, run script:
#!/bin/bash
while [ 1 ]
do
date
mysql -h <slave ip> --port=<slave port> -e "SELECT db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunks
FROM percona.checksums
WHERE (
 master_cnt <> this_cnt
 OR master_crc <> this_crc
 OR ISNULL(master_crc) <> ISNULL(this_crc))
GROUP BY db, tbl;"
sleep 1
done

2. On master, run pt-table-checksum

3. Monitor script output of #1.

Tue Nov 11 01:26:24 EST 2014
Tue Nov 11 01:26:25 EST 2014
+-----------+----------+------------+--------+
| db | tbl | total_rows | chunks |
+-----------+----------+------------+--------+
| employees | dept_emp | 1218 | 1 |
+-----------+----------+------------+--------+
Tue Nov 11 01:26:26 EST 2014
Tue Nov 11 01:26:27 EST 2014
Tue Nov 11 01:26:28 EST 2014
Tue Nov 11 01:26:29 EST 2014
Tue Nov 11 01:26:30 EST 2014
+-----------+-----------+------------+--------+
| db | tbl | total_rows | chunks |
+-----------+-----------+------------+--------+
| employees | employees | 43743 | 1 |
+-----------+-----------+------------+--------+
Tue Nov 11 01:26:31 EST 2014
Tue Nov 11 01:26:32 EST 2014
Tue Nov 11 01:26:33 EST 2014
Tue Nov 11 01:26:34 EST 2014
Tue Nov 11 01:26:35 EST 2014
Tue Nov 11 01:26:36 EST 2014
Tue Nov 11 01:26:37 EST 2014
Tue Nov 11 01:26:38 EST 2014
Tue Nov 11 01:26:39 EST 2014
Tue Nov 11 01:26:40 EST 2014
Tue Nov 11 01:26:41 EST 2014
Tue Nov 11 01:26:42 EST 2014
+-----------+--------+------------+--------+
| db | tbl | total_rows | chunks |
+-----------+--------+------------+--------+
| employees | titles | 0 | 1 |
+-----------+--------+------------+--------+
Tue Nov 11 01:26:43 EST 2014
+-------+--------------+------------+--------+
| db | tbl | total_rows | chunks |
+-------+--------------+------------+--------+
| mysql | columns_priv | 0 | 1 |
+-------+--------------+------------+--------+
Tue Nov 11 01:26:44 EST 2014
Tue Nov 11 01:26:45 EST 2014
+-------+------+------------+--------+
| db | tbl | total_rows | chunks |
+-------+------+------------+--------+
| mysql | func | 0 | 1 |
+-------+------+------------+--------+
Tue Nov 11 01:26:46 EST 2014
Tue Nov 11 01:26:47 EST 2014
Tue Nov 11 01:26:48 EST 2014
Tue Nov 11 01:26:49 EST 2014
+-------+------+------------+--------+
| db | tbl | total_rows | chunks |
+-------+------+------------+--------+
| mysql | proc | 0 | 1 |
+-------+------+------------+--------+
Tue Nov 11 01:26:50 EST 2014
+-------+--------------+------------+--------+
| db | tbl | total_rows | chunks |
+-------+--------------+------------+--------+
| mysql | proxies_priv | 2 | 1 |
+-------+--------------+------------+--------+
Tue Nov 11 01:26:51 EST 2014
Tue Nov 11 01:26:52 EST 2014
Tue Nov 11 01:26:53 EST 2014
Tue Nov 11 01:26:54 EST 2014
Tue Nov 11 01:26:55 EST 2014
Tue Nov 11 01:26:56 EST 2014
Tue Nov 11 01:26:57 EST 2014
Tue Nov 11 01:26:58 EST 2014

[Shahriyar Rzayev (rzayev-sehriyar)]

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