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

Bug #1340649 reported by Kenny Gryp
34
This bug affects 6 people
Affects Status Importance Assigned to Milestone
Percona Toolkit moved to https://jira.percona.com/projects/PT
Confirmed
Undecided
Unassigned

Bug Description

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.

Changed in percona-toolkit:
status: New → Confirmed
Revision history for this message
Frank Cizmich (frank-cizmich) wrote :

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.

Revision history for this message
Alexander Moskaliov (irker) wrote :

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

Revision history for this message
Jay Janssen (jay-janssen) wrote :

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.

Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

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

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.