pt-table-checksum doesn't work on pxc 5.6

[Ben Mildren]

- pt-table-checksum --recursion-method=cluster
- 2.2.7
- pxc 5.6.15

https://bugs.launchpad.net/percona-xtradb-cluster/+bug/1263991

With PXC 5.6 the session binlog_format cannot be changed to STATEMENT:

mysql> /*!50108 SET @@binlog_format := 'STATEMENT'*/;
ERROR 1231 (42000): Variable 'binlog_format' can't be set to the value of 'STATEMENT'
mysql>

Therefore pt-table-checksum fails with the message:

Failed to /*!50108 SET @@binlog_format := 'STATEMENT'*/: DBD::mysql::db do failed: Variable 'binlog_format' can't be set to the value of 'STATEMENT' [for Statement "/*!50108 SET @@binlog_format := 'STATEMENT'*/"] at /usr/bin/pt-table-checksum line 9101, <STDIN> line 1.

This tool requires binlog_format=STATEMENT, but the current binlog_format is set to ROW and an error occurred while attempting to change it. If running MySQL 5.1.29 or newer, setting binlog_format requires the SUPER privilege. You will need to manually set binlog_format to 'STATEMENT' before running this tool.

There doesn't seem to be any workaround for this using the current options.

[Marc Castrovinci (marc-castrovinci)]

Is anyone going to look at this?? The tool is basically useless for percona cluster now.

[Marc Castrovinci (marc-castrovinci)]

If you want a work around comment out the following in /usr/bin/pt-table-checksum. It seems to work fine.

Starting at line 9202

     if ( VersionParser->new($dbh) >= '5.1.5' ) {
         $sql = 'SELECT @@binlog_format';
         PTDEBUG && _d($dbh, $sql);
         my ($original_binlog_format) = $dbh->selectrow_array($sql);
         PTDEBUG && _d('Original binlog_format:', $original_binlog_format);
         if ( $original_binlog_format !~ /STATEMENT/i ) {
            $sql = q{/*!50108 SET @@binlog_format := 'STATEMENT'*/};
            eval {
               PTDEBUG && _d($dbh, $sql);
               $dbh->do($sql);
            };
            if ( $EVAL_ERROR ) {
               die "Failed to $sql: $EVAL_ERROR\n"
                  . "This tool requires binlog_format=STATEMENT, "
                  . "but the current binlog_format is set to "
                  ."$original_binlog_format and an error occurred while "
                  . "attempting to change it. If running MySQL 5.1.29 or newer, "
                  . "setting binlog_format requires the SUPER privilege. "
                  . "You will need to manually set binlog_format to 'STATEMENT' "
                  . "before running this tool.\n";
            }
         }
      }

[Ben Mildren (ben-mildren)]

I think this will require more thought, as mentioned here: https://bugs.launchpad.net/percona-xtradb-cluster/+bug/1263991, in 5.6 the abillity to dynamically change the binlog_format at the session level has been removed. pt-table-checksum has previously relied on statement based replication to allow a crc to be calculated separately on the slave for comparison.

By simply commenting out the code and allowing the tool to use row based replication, it won't calculate a separate crc on the slave and therefore the checksum comparison will be redundant.

I think the fix will likely have to be within PXC, maybe by allowing the session binlog_format to be dynamically changed again, or maybe by utilizing the wsrep_consistency_check to force the sessions binlog_format to be statement based.

[Marc Castrovinci (marc-castrovinci)]

Thanks for the feedback. Can you suggest any alternate tools in the meantime?

[Joel (joel-8)]

I am also looking for an alternative for this tool. Is there anything else out there? Any plans for a fix? Having the same problem here.

[Marc Castrovinci (marc-castrovinci)]

Is no one else concerned there is no way to actually verify cluster consistency???

[Frank Cizmich (frank-cizmich)]

pt-table-checksum as it stands now relies on being able to set binlog_format = "STATEMENT". It cannot work without this.
PXC 5.6 has disabled this while PXC 5.5 permitted it, though issuing a warning.
We are looking at various alternatives for getting them to work together again, but it is necessary to be careful since galera library is relying on binlog_format=ROW to ensure consistency.

That being said, there is a "dirty" workaround for this which involves commenting out the code mentioned by Marc and setting the global variable wsrep_forced_binlog_format = statement.

But this is very risky and so defeats the purpose. So ultimately not recommended.

If you DO try it remember to set the variable back to the value "NONE" afterwards.

[Frank Cizmich (frank-cizmich)]

UPDATE

I've commited a branch that implements the workaround I mentioned before.

setting the --pxc56 option signals pt-table-checksum to set wsrep_forced_binlog_format = 'statement' at start .

It reverts the setting when it finishes or on any other exit condition.

https://code.launchpad.net/~percona-toolkit-dev/percona-toolkit/pt-table-checksum-pxc56-workaround

This is highly unsafe, meant to be used experimentally only!

[Frank Cizmich (frank-cizmich)]

PXC 5.6.20 has been released and is now once again compatible with pt-table-checksum, allowing the setting of binlog_format at runtime.

[Shahriyar Rzayev (rzayev-sehriyar)]

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