LP #1298689: Possible bug in the in MySQL + Galera and PXC when running PT-table-checksum on a slave.

**Reported in Launchpad by Marco Tusa last update 17-06-2014 10:30:49

Current nodes setup

node1 <> node2 <> node3

\/
Slave

The issue is that when running pt-table-checksum on node1 all the statement that pt-table-checksum execute to test the data on the nodes, are replicated with ROW replication.
To note that I am testing on percona toolkit 2.2.7 and the version of pt-table-checksum is implementing the /!99997/ trick as describe at https://groups.google.com/forum/#!msg/codership-team/kKqigq31dVQ/TV-DTUI0YYYJ.

From MySQL point of view I have tested with PXC 5.5.34 and Codership 5.5.43 (wsrep 25.9)

When pt-table-checksum is executed both the servers print the DEBUG information,
140327 15:38:48 [Note] WSREP: consistency check: REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc)
SELECT /!99997/ 'sakila', 'staff', '1', NULL, NULL, NULL, COUNT AS cnt,
COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `staff_id`, `first_name`, `last_name`, `address_id`, `picture`, `email`,
`store_id`, `active`, `username`, `password`, `last_update` + 0, CONCAT(ISNULL(`picture`), ISNULL(`email`), ISNULL(`password`))))
AS UNSIGNED)), 10, 16)), 0) AS crc FROM `sakila`.`staff` /checksum table/

But then the information is passed as ROW format as UPDATE:
BINLOG '
SH40UxMjAAAATAAAAA2SAAAAACQAAAAAAAEAB3BlcmNvbmEACWNoZWNrc3VtcwAM/v4DBA/8/P4D
/gMHDf7A/sAEWAICAv54/nh4Bg==
SH40UxgjAAAAeQAAAIaSAAAAACQAAAAAAAEADP////949gZzYWtpbGEFc3RhZmYBAAAACDIzMzY2
OGFlAgAAAEh+NFNw8AZzYWtpbGEFc3RhZmYBAAAANzMqPggyMzM2NjhhZQIAAAAIMjMzNjY4YWUC
AAAASH40Uw==
'/!/;

1. 1. 1. UPDATE percona.checksums
      2. WHERE
      3. @1='sakila' /* STRING(192) meta=65216 nullable=0 is_null=0 */
      4. @2='staff' /* STRING(192) meta=65216 nullable=0 is_null=0 */
      5. @3=1 /* INT meta=0 nullable=0 is_null=0 */
      6. @4=NULL /* INT meta=4 nullable=1 is_null=1 */
      7. @5=NULL /* INT meta=600 nullable=1 is_null=1 */
      8. @6=NULL /* INT meta=2 nullable=1 is_null=1 */
      9. @7=NULL /* INT meta=2 nullable=1 is_null=1 */
      10. @8='233668ae' /* STRING(120) meta=65144 nullable=0 is_null=0 */
      11. @9=2 /* INT meta=0 nullable=0 is_null=0 */
      12. @10=NULL /* INT meta=65144 nullable=1 is_null=1 */
      13. @11=NULL /* INT meta=0 nullable=1 is_null=1 */
      14. @12=1395949128 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
      15. SET
      16. @1='sakila' /* STRING(192) meta=65216 nullable=0 is_null=0 */
      17. @2='staff' /* STRING(192) meta=65216 nullable=0 is_null=0 */
      18. @3=1 /* INT meta=0 nullable=0 is_null=0 */
      19. @4=0.166211 /* FLOAT meta=4 nullable=1 is_null=0 */
      20. @5=NULL /* FLOAT meta=600 nullable=1 is_null=1 */
      21. @6=NULL /* FLOAT meta=2 nullable=1 is_null=1 */
      22. @7=NULL /* FLOAT meta=2 nullable=1 is_null=1 */
      23. @8='233668ae' /* STRING(120) meta=65144 nullable=0 is_null=0 */
      24. @9=2 /* INT meta=0 nullable=0 is_null=0 */
      25. @10='233668ae' /* STRING(120) meta=65144 nullable=1 is_null=0 */
      26. @11=2 /* INT meta=0 nullable=1 is_null=0 */
      27. @12=1395949128 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
2. at 37510

Given that there is no checksum execution on the SLAVE.

pt-table-checksum code related to the patch

1. ########################################################################
2. Checksum args and the DMS part of the checksum query for each table.
3. ########################################################################
   my %crc_args = $rc->get_crc_args(dbh => $master_dbh);
   my $checksum_dml = "REPLACE INTO $repl_table "
   . "(db, tbl, chunk, chunk_index,"
   . " lower_boundary, upper_boundary, this_cnt, this_crc) "
   . "SELECT"
   . ($cluster->is_cluster_node($master_cxn) ? ' /!99997/' : '')
   . " ?, ?, ?, ?, ?, ?,";
   my $past_cols = " COUNT, '0'";

HOW to replicate:
================================
1) install a cluster using either PXC or MySQL/Galera both(5.5)
2) connect another MySQL (simple) as slave to node1
3) load a test schema
4) remove some records on the SLAVE
5) run pt-table-checksum as /pt-table-checksum -u<user> -p<pw> -h 192.168.0.35 -P3306 --databases sakila,test --recursion-method dsn=h=192.168.0.35,P=3306,u=<user>,p=<pw>,D=percona,t=dsns --no-check-binlog-format
dsns table:
([email protected]:pm) [percona]>select * from dsns;
--------------------------------------------------

--------------------------------------------------

--------------------------------------------------
1 row in set (0.00 sec)
6) review data from the pt-table-checksum and from the query on master/slave
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;