Not the answer you need?
Register and ask your own question!

pt-table-checksum binlog_format=ROW issue

EfrainEfrain EntrantInactive User Role Beginner
Hi all,

Please help, I am not able to make pt-table-checksum tool work.
I have a simple master-slave environment and I am trying to check data consistency between them.

I am issuing the next command
pt-table-checksum --user=xxxxxx --password=xxxxx

And I get the next error message
01-09T11:54:08 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 9148.

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.
[root@hades admin]# [SSH] ERROR: An existing connection was forcibly closed by the remote host.
[SSH] FAIL: Write failed: An existing connection was forcibly closed by the remote host.

Our master is a cluster running under MariaDB Galera 10 with binlog format = ROW and I cannot change that because is mandatory as you can read here https://mariadb.com/kb/en/mariadb/documentation/managing-mariadb/replication/galera/getting-started-with-mariadb-galera-cluster/

I have been googling how to run this command without any success. Some people recommend to disable the check binlog format with the --nocheck-binlog-format, but that didn t do nothing
[# pt-table-checksum --user=xxxxx --password=xxxxxx --nocheck-binlog-format
01-09T12:52:26 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 9148.

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.

Before giving up, I would like to know if anybody has faced the same issue and how they solved it?
Thanks

More info
I am using Percona Toolkit version 2.2.12
Redhat 6.5
MariaDB 10

Comments

  • jrogersmithjrogersmith Entrant Inactive User Role Beginner
    I have examined the same contradtiction. As well, --nocheck-binlog-format appears to be ignored. Using MariaDB 10.0.13-wsrep_25.10 and percona toolkit 2.2.12.
  • jriverajrivera Percona Support Engineer Percona Staff Role
    Try it with --no-check-binlog-format, not --nocheck-binlog-format. See if that works.
  • lkthomaslkthomas Entrant Current User Role Supporter
    jrivera wrote: »
    Try it with --no-check-binlog-format, not --nocheck-binlog-format. See if that works.

    may I know what's the disadvantage if I use no check binlog format?
  • carlos.salguerocarlos.salguero Percona Toolkit Developer Percona Staff Role
    pt-table-checksum requires statement-based replication, and it sets binlog_format=STATEMENT on the master, but due to a MySQL limitation replicas do not honor this change. Therefore, checksums will not replicate past any replicas using row-based replication that are masters for further replicas.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.