sql_log_bin not supported in new pt-online-schema-change?

Polyglot Projects Percona Toolkit
1

We often use --set-vars=“sql_log_bin=0” in our OSC’s in order to only perform schema changes on master due to issues with replication, then do the OSC’s again on the slaves after the master has been completed.

Since updating to percona-toolkit 2.2 I’m getting an error when trying to use sql_log_bin in that it’s not a supported --set-vars option, has this changed in the new version? Is there another way to stop OSC from binlogging?

2

Hi,

Are you still facing the same issue? Can you please provide command line that how you are running pt-osc and the error message which you are getting?

3

I’m still seeing the same issue in the latest version of pt-online-schema-change (2.2.2). The command I’m using is:


$ pt-online-schema-change --progress time,10 --no-drop-old-table --recursion-method=none --no-check-replication-filters D=mydb,t=mytable --alter "ADD mydatetime DATETIME NULL DEFAULT NULL" --set-vars="SQL_LOG_BIN=0" --dry-run
Invalid --set-vars value: SQL_LOG_BIN=0



$ pt-online-schema-change --version
pt-online-schema-change 2.2.2
4

This is actually a bug, I get the same error when I try to assign 0 to any variable. I’ve reported it here:
[url]https://bugs.launchpad.net/percona-toolkit/+bug/1182856[/url]

As a workaround, you may just use

–set-vars=“SQL_LOG_BIN=OFF”

5

all, in trying to understand the best way to run pt-online-schema-change in a replicated environment, what would be best practices for a simple master + 2 slave topology?

specifically…

  • what replication binlog_formats are supported? (i see thousands of errors when using statement)
  • should pt-osc be run on slaves before master?
  • should SQL_LOG_BIN be set to 0 or OFF when run against slaves or masters?
  • i’ve read about issues with row-based replication, where incoming RBR updates from master won’t execute pt-osc triggers on a slave while pt-osc is running. should replication be stopped while pt-osc is run on a slave?
  • if pt-osc triggers allow rows to be copied to the new temporary table out of order between or during chunking copies, won’t table row order get out of sync between master and slaves? see following for more… [url]http://www.percona.com/forums/questions-discussions/percona-toolkit/10524-pt-online-schema-change-update-delete-query[/url]
  • are there issues with running pt-osc on a table with auto-increment (again for out-of-order rows)?

thank you in advance for more details on pt-osc and replication.

-ives