Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

LP #1340649: pt-table-sync checks changing binlog_format on slaves and exists if that does not work. (RDS)

Description

**Reported in Launchpad by Kenny Gryp last update 28-07-2015 16:53:29

pt-table-sync connects to all machines and changes the binlog_format=STATEMENT. However, binlog_format cannot be changed when using Amazon RDS (there is no SUPER privilege).

This breaks pt-table-sync to work with RDS.

In the example below, checksumming is being done between a regular database system replicating to a RDS system.
No slaves are attached to the RDS slave.

Then it should be possible for pt-table-sync to work successfully, as long as the master binlog_format can become STATEMENT (which is the case in this example)
This might need a new advanced option to disable this check.

pt-table-sync error (PTDEBUG=1)

  1. pt_table_sync:10819 12841 DBI::db=HASH(0x1306750) SELECT @@binlog_format

  2. pt_table_sync:10821 12841 Original binlog_format: MIXED

  3. pt_table_sync:10825 12841 DBI::db=HASH(0x1306750) /!50108 SET @@binlog_format := 'STATEMENT'/
    Failed to /!50108 SET @@binlog_format := 'STATEMENT'/: DBD::mysql::db do failed: Access denied; you need (at least one of) the SUPER privilege(s) for this operation [for Statement "/!50108 SET @@binlog_format := 'STATEMENT'/"] at /usr/local/bin/pt-table-sync line 10826.

This tool requires binlog_format=STATEMENT, but the current binlog_format is set to MIXED 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.

Issuing rollback() due to DESTROY without explicit disconnect() of DBD::mysql::db handle ;host=***;mysql_read_default_group=client at /usr/local/bin/pt-table-sync line 10829.

Environment

None

Smart Checklist

Activity

Show:

lpjirasync January 24, 2018 at 8:19 PM

**Comment from Launchpad by: Jay Janssen on: 28-07-2015 16:53:28

AFAICT 5.6 RDS (at least) doesn't let you set STATEMENT (at least via a parameter group), so you can't get pt-table-checksum to work either. I'd suggest that at a minimum we clearly document this on the respective man pages for pt-t-c- and pt-t-s.

lpjirasync January 24, 2018 at 8:19 PM

**Comment from Launchpad by: Alexander Moskaliov on: 11-02-2015 10:09:51

It also strange to check it if '--print' option used

lpjirasync January 24, 2018 at 8:19 PM

**Comment from Launchpad by: Frank Cizmich on: 28-07-2014 19:14:09

Hi Kenny,
pt-table-checksum has the "--[no]check-binlog-format" option, but in the case of pt-table-sync it's a little less trivial because it actually changes data.
Although if the user knows what he's doing, this option would solve the problem. Maybe with an added warning, just in case.

Details

Assignee

Reporter

Priority

Labels

Smart Checklist

Created January 24, 2018 at 8:19 PM
Updated December 21, 2019 at 2:32 PM