I'm not sure how to test this. The problem isn't reflected in that select query. Whenever ptc finds an error, it exits with a non-zero exit code. But if I immediately query the replicate table, no differences are present. Here's the latest:
1. Ran the following in a while loop:
do
/sso/sfw/percona/bin/pt-table-checksum --user=root --password=blah --chunk-size-limit=50.0 --replicate-check --recursion-method=DSN=D=percona,t=dsns_ssodb03 --databases=vsp_jira_current --tables=customfieldoption --no-check-replication-filters --replicate=percona.checksums_ssodb03
if [ "$?" != "0" ]; then
break;
fi
done
2. Loop ran 9 times with a 0 exit code, and on the 10th try, it exited with the following output:
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
12-12T13:51:14 0 1 1734 1 0 0.018 vsp_jira_current.customfieldoption
So it thinks there is a difference in vsp_jira_current.customfieldoption.
3. Examine replicate table on master:
mysql> select * from checksums_ssodb03 where tbl='customfieldoption';
+------------------+-------------------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
| db | tbl | chunk | chunk_time | chunk_index | lower_boundary | upper_boundary | this_crc | this_cnt | master_crc | master_cnt | ts |
+------------------+-------------------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
| vsp_jira_current | customfieldoption | 1 | 0.006716 | NULL | NULL | NULL | a83c0464 | 1734 | a83c0464 | 1734 | 2012-12-12 13:51:14 |
+------------------+-------------------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
1 row in set (0.00 sec)
mysql> SELECT CONCAT(db, '.', tbl) AS `table`, chunk, chunk_index, lower_boundary, upper_boundary, COALESCE(this_cnt-master_cnt, 0) AS cnt_diff, COALESCE(this_crc <> master_crc OR ISNULL(master_crc) <> ISNULL(this_crc), 0) AS crc_diff, this_cnt, master_cnt, this_crc, master_crc FROM `percona`.`checksums_ssodb03` WHERE (master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)) AND (db='vsp_jira_current' AND tbl='customfieldoption');
Empty set (0.00 sec)
mysql> SELECT CONCAT(db, '.', tbl) AS `table`, chunk, chunk_index, lower_boundary, upper_boundary, COALESCE(this_cnt-master_cnt, 0) AS cnt_diff, COALESCE(this_crc <> master_crc OR ISNULL(master_crc) <> ISNULL(this_crc), 0) AS crc_diff, this_cnt, master_cnt, this_crc, master_crc FROM `percona`.`checksums_ssodb03` WHERE (master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)) AND (db='vsp_jira_current' AND tbl='customfieldoption');
Empty set (0.00 sec)
5. So there aren't any differences in the replicate table between the master and slave. The only indication there is a difference is in pt-table-checksum's output and its exit code.
I'm not sure how to test this. The problem isn't reflected in that select query. Whenever ptc finds an error, it exits with a non-zero exit code. But if I immediately query the replicate table, no differences are present. Here's the latest:
1. Ran the following in a while loop: sfw/percona/ bin/pt- table-checksum --user=root --password=blah --chunk- size-limit= 50.0 --replicate-check --recursion- method= DSN=D=percona, t=dsns_ ssodb03 --databases= vsp_jira_ current --tables= customfieldopti on --no-check- replication- filters --replicate= percona. checksums_ ssodb03
do
/sso/
if [ "$?" != "0" ]; then
break;
fi
done
2. Loop ran 9 times with a 0 exit code, and on the 10th try, it exited with the following output: current. customfieldopti on
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
12-12T13:51:14 0 1 1734 1 0 0.018 vsp_jira_
So it thinks there is a difference in vsp_jira_ current. customfieldopti on.
3. Examine replicate table on master: doption' ; ------- -----+- ------- ------- ----+-- -----+- ------- ----+-- ------- ----+-- ------- ------- +------ ------- ---+--- ------- +------ ----+-- ------- ---+--- ------- --+---- ------- ------- ---+ ------- -----+- ------- ------- ----+-- -----+- ------- ----+-- ------- ----+-- ------- ------- +------ ------- ---+--- ------- +------ ----+-- ------- ---+--- ------- --+---- ------- ------- ---+ ------- -----+- ------- ------- ----+-- -----+- ------- ----+-- ------- ----+-- ------- ------- +------ ------- ---+--- ------- +------ ----+-- ------- ---+--- ------- --+---- ------- ------- ---+ this_cnt- master_ cnt, 0) AS cnt_diff, COALESCE(this_crc <> master_crc OR ISNULL(master_crc) <> ISNULL(this_crc), 0) AS crc_diff, this_cnt, master_cnt, this_crc, master_crc FROM `percona` .`checksums_ ssodb03` WHERE (master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)) AND (db='vsp_ jira_current' AND tbl='customfiel doption' );
mysql> select * from checksums_ssodb03 where tbl='customfiel
+------
| db | tbl | chunk | chunk_time | chunk_index | lower_boundary | upper_boundary | this_crc | this_cnt | master_crc | master_cnt | ts |
+------
| vsp_jira_current | customfieldoption | 1 | 0.006716 | NULL | NULL | NULL | a83c0464 | 1734 | a83c0464 | 1734 | 2012-12-12 13:51:14 |
+------
1 row in set (0.00 sec)
mysql> SELECT CONCAT(db, '.', tbl) AS `table`, chunk, chunk_index, lower_boundary, upper_boundary, COALESCE(
Empty set (0.00 sec)
4. Examine replicate on slave: doption' ; ------- -----+- ------- ------- ----+-- -----+- ------- ----+-- ------- ----+-- ------- ------- +------ ------- ---+--- ------- +------ ----+-- ------- ---+--- ------- --+---- ------- ------- ---+ ------- -----+- ------- ------- ----+-- -----+- ------- ----+-- ------- ----+-- ------- ------- +------ ------- ---+--- ------- +------ ----+-- ------- ---+--- ------- --+---- ------- ------- ---+ ------- -----+- ------- ------- ----+-- -----+- ------- ----+-- ------- ----+-- ------- ------- +------ ------- ---+--- ------- +------ ----+-- ------- ---+--- ------- --+---- ------- ------- ---+
mysql> select * from checksums_ssodb03 where tbl='customfiel
+------
| db | tbl | chunk | chunk_time | chunk_index | lower_boundary | upper_boundary | this_crc | this_cnt | master_crc | master_cnt | ts |
+------
| vsp_jira_current | customfieldoption | 1 | 0.006716 | NULL | NULL | NULL | a83c0464 | 1734 | a83c0464 | 1734 | 2012-12-12 13:51:14 |
+------
1 row in set (0.00 sec)
mysql> SELECT CONCAT(db, '.', tbl) AS `table`, chunk, chunk_index, lower_boundary, upper_boundary, COALESCE( this_cnt- master_ cnt, 0) AS cnt_diff, COALESCE(this_crc <> master_crc OR ISNULL(master_crc) <> ISNULL(this_crc), 0) AS crc_diff, this_cnt, master_cnt, this_crc, master_crc FROM `percona` .`checksums_ ssodb03` WHERE (master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)) AND (db='vsp_ jira_current' AND tbl='customfiel doption' );
Empty set (0.00 sec)
5. So there aren't any differences in the replicate table between the master and slave. The only indication there is a difference is in pt-table-checksum's output and its exit code.