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

[Kenny Gryp]

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)
# pt_table_sync:10819 12841 DBI::db=HASH(0x1306750) SELECT @@binlog_format
# pt_table_sync:10821 12841 Original binlog_format: MIXED
# 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.

[Frank Cizmich (frank-cizmich)]

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.

[Alexander Moskaliov (irker)]

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

[Jay Janssen (jay-janssen)]

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.

[Shahriyar Rzayev (rzayev-sehriyar)]

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