Possible bug in the in MySQL + Galera and PXC when running PT-table-checksum on a slave.
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MySQL patches by Codership |
New
|
Undecided
|
Unassigned | ||
Percona Toolkit moved to https://jira.percona.com/projects/PT |
New
|
Undecided
|
Daniel Nichter |
Bug Description
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:/
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`
SELECT /*!99997*/ 'sakila', 'staff', '1', NULL, NULL, NULL, COUNT(*) AS cnt,
COALESCE(
`store_id`, `active`, `username`, `password`, `last_update` + 0, CONCAT(
AS UNSIGNED)), 10, 16)), 0) AS crc FROM `sakila`.`staff` /*checksum table*/
But then the information is passed as ROW format as UPDATE:
BINLOG '
SH40UxMjAAAAT
/gMHDf7A/
SH40UxgjAAAAe
OGFlAgAAAEh+
AAAASH40Uw==
'/*!*/;
### UPDATE percona.checksums
### WHERE
### @1='sakila' /* STRING(192) meta=65216 nullable=0 is_null=0 */
### @2='staff' /* STRING(192) meta=65216 nullable=0 is_null=0 */
### @3=1 /* INT meta=0 nullable=0 is_null=0 */
### @4=NULL /* INT meta=4 nullable=1 is_null=1 */
### @5=NULL /* INT meta=600 nullable=1 is_null=1 */
### @6=NULL /* INT meta=2 nullable=1 is_null=1 */
### @7=NULL /* INT meta=2 nullable=1 is_null=1 */
### @8='233668ae' /* STRING(120) meta=65144 nullable=0 is_null=0 */
### @9=2 /* INT meta=0 nullable=0 is_null=0 */
### @10=NULL /* INT meta=65144 nullable=1 is_null=1 */
### @11=NULL /* INT meta=0 nullable=1 is_null=1 */
### @12=1395949128 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
### SET
### @1='sakila' /* STRING(192) meta=65216 nullable=0 is_null=0 */
### @2='staff' /* STRING(192) meta=65216 nullable=0 is_null=0 */
### @3=1 /* INT meta=0 nullable=0 is_null=0 */
### @4=0.166211 /* FLOAT meta=4 nullable=1 is_null=0 */
### @5=NULL /* FLOAT meta=600 nullable=1 is_null=1 */
### @6=NULL /* FLOAT meta=2 nullable=1 is_null=1 */
### @7=NULL /* FLOAT meta=2 nullable=1 is_null=1 */
### @8='233668ae' /* STRING(120) meta=65144 nullable=0 is_null=0 */
### @9=2 /* INT meta=0 nullable=0 is_null=0 */
### @10='233668ae' /* STRING(120) meta=65144 nullable=1 is_null=0 */
### @11=2 /* INT meta=0 nullable=1 is_null=0 */
### @12=1395949128 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
# at 37510
Given that there is no checksum execution on the SLAVE.
pt-table-checksum code related to the patch
# #######
# Checksum args and the DMS part of the checksum query for each table.
# #######
my %crc_args = $rc->get_
my $checksum_dml = "REPLACE INTO $repl_table "
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.
dsns table:
(root@
+----
| id | parent_id | dsn |
+----
| 6 | NULL | h=192.168.
+----
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;
Changed in percona-toolkit: | |
assignee: | nobody → Daniel Nichter (daniel-nichter) |
tags: |
added: pxc rbr removed: replication row |
Forgot to mention that changing: binlog_ format= 'STATEMENT' ;
(root@localhost:pm) [percona]>set global wsrep_forced_
Query OK, 0 rows affected (0.00 sec)
It obviously work, but this mean changing a global value and all the operations are then write in statement format