XtraDB Cluster and pt-table-checksum

222 views
Skip to first unread message

Michael Dykman

unread,
Apr 25, 2017, 5:33:13 PM4/25/17
to Percona Discussion
Hi,

I have setup an XtraDB cluster across 4 VMs which appear to be behaving correctly. I have installed percona xtradb and percona-toolkit from the percona apt repository and am using the latest versions of those packages.

root@build-server:~# pt-table-checksum --version
pt-table-checksum 3.0.2

root@build-server:~# mysqld --version
mysqld  Ver 5.7.17-13-57 for debian-linux-gnu on x86_64 (Percona XtraDB Cluster (GPL), Release rel13, Revision f46c657, WSREP version 29.20, wsrep_29.20)


I created the table percona.dsns and populated with DSNs for each node in my cluster but I am unable to get pt-table-checksum past the binlog_format issue.
Here is the command I am using.  

 $ pt-table-checksum  h=192.168.32.11,u=root,p=password   --recursion-method dsn=h=192.168.32.11,u=root,p=password,D=percona,t=dsns

I have attempted the command above with and without 
     --nocheck-binlog-format 


The command is repeated, along with the error message received at runtime near the end of this message.


I found an article authored just over a year ago 


which recommended an older article to me which I tried to follow:


In the comments section of the latter article, there is a complaint from 2014 that the mechanism is broken which refers to the bug report at



The bug purports to describe the exact problem I am having and is marked as 'fix-released' on  2014-09-03, and apparently released as part of PXC 5.6.20. I experimented with the option --pxc56 (and --pxc) as suggested in the bug tracker but they are not valid flags.

I am currently out of ideas as to what to try next.


mysql> show create table percona.dsns;
+-------+--------------------------------------------------------------------------------------------+
| Table | Create Table                                                                         |
+-------+--------------------------------------------------------------------------------------------+
| dsns  | CREATE TABLE `dsns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `dsn` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------+


mysql> select * from percona.dsns;
+----+-----------+-----------------------------------+
| id | parent_id | dsn                               |
+----+-----------+-----------------------------------+
|  1 |      NULL | h=192.168.32.10,u=root,p=password |
|  2 |      NULL | h=192.168.32.11,u=root,p=password |
|  3 |      NULL | h=192.168.32.12,u=root,p=password |
|  4 |      NULL | h=192.168.32.13,u=root,p=password |
+----+-----------+-----------------------------------+
4 rows in set (0.00 sec)

root@build-server:~# pt-table-checksum  h=192.168.32.11,u=root,p=password   --recursion-method dsn=h=192.168.32.11,u=root,p=password,D=percona,t=dsns
04-25T16:19:34 Failed to /*!50108 SET @@binlog_format := 'STATEMENT'*/: DBD::mysql::db do failed: Percona-XtraDB-Cluster prohibits setting binlog_format to STATEMENT or MIXED with pxc_strict_mode = ENFORCING or MASTER [for Statement "/*!50108 SET @@binlog_format := 'STATEMENT'*/"] at /usr/bin/pt-table-checksum line 9312.

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.

ramesh.s...@percona.com

unread,
Apr 30, 2017, 11:25:19 PM4/30/17
to Percona Discussion
Hi Michael,

With 5.7 release PXC added a new feature PXC Strict Mode, this will help to avoid the use of experimental and unsupported features in Percona XtraDB Cluster


This feature supports only the default row-based binary logging format and you cannot set binlog_format to STATEMENT/MIXED on runtime that is why pt-table-checksum fail to execute the command.

 
To avoid this issue you can set pxc_strict_mode=DISABLED/PERMISSIVE before running pt-table-checksum and set it back to pxc_strict_mode=ENFORCING after finishing pt-table-checksum.

You can also start PXC with pxc_strict_mode=DISABLED/PERMISSIVE

Please let me know you need any other info.

Matthew Boehm

unread,
May 1, 2017, 11:51:06 PM5/1/17
to percona-d...@googlegroups.com
Set --recursion-method=cluster and the binlog format is not checked and
pt-table-checksum will run just fine.

-Matthew
> node in my cluster but I am unable to get /pt-table-checksum/ past
> --
> You received this message because you are subscribed to the Google
> Groups "Percona Discussion" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to percona-discuss...@googlegroups.com
> <mailto:percona-discuss...@googlegroups.com>.
> To post to this group, send email to percona-d...@googlegroups.com
> <mailto:percona-d...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/percona-discussion.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/percona-discussion/e91aee5e-1986-422a-9098-9f67ab95c4c1%40googlegroups.com
> <https://groups.google.com/d/msgid/percona-discussion/e91aee5e-1986-422a-9098-9f67ab95c4c1%40googlegroups.com?utm_medium=email&utm_source=footer>.
> For more options, visit https://groups.google.com/d/optout.
>
> --
> You received this message because you are subscribed to the Google
> Groups "Experts" group.
> To post to this group, send email to exp...@percona.com
> <mailto:exp...@percona.com>.
> Visit this group at https://groups.google.com/a/percona.com/group/experts/.

--
Matthew Boehm
Senior Architect / Lead Trainer
Percona, Inc / www.percona.com

Matt Riddell

unread,
Jul 11, 2017, 2:31:25 PM7/11/17
to Percona Discussion, matthe...@percona.com
That doesn't seem to make a difference:

pt-table-sync --recursion-method=cluster 


Failed to /*!50108 SET @@binlog_format := 'STATEMENT'*/: DBD::mysql::db do failed: Percona-XtraDB-Cluster prohibits setting binlog_format to STATEMENT or MIXED with pxc_strict_mode = ENFORCING or MASTER [for Statement "/*!50108 SET @@binlog_format := 'STATEMENT'*/"] at /usr/bin/pt-table-sync line 10918.
Reply all
Reply to author
Forward
0 new messages