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

Bug #1316863 reported by Ben Mildren on 2014-05-07
28
This bug affects 5 people
Affects Status Importance Assigned to Milestone
Percona Toolkit
Undecided
Unassigned

Bug Description

- 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.

description: updated
Changed in percona-toolkit:
status: New → Confirmed
tags: added: pt-table-checksum

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

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) wrote :

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.

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

Joel (joel-8) wrote :

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.

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

Frank Cizmich (frank-cizmich) wrote :

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) wrote :

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) wrote :

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.

Changed in percona-toolkit:
status: Confirmed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers