Not the answer you need?
Register and ask your own question!

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

WheresWardyWheresWardy ContributorInactive User Role Beginner
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?

Comments

  • niljoshiniljoshi MySQL Sage Inactive User Role Beginner
    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?
  • WheresWardyWheresWardy Contributor Inactive User Role Beginner
    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
    
  • przemekprzemek Percona Support Engineer Percona Staff Role
    This is actually a bug, I get the same error when I try to assign 0 to any variable. I've reported it here:
    https://bugs.launchpad.net/percona-toolkit/+bug/1182856

    As a workaround, you may just use


    --set-vars="SQL_LOG_BIN=OFF"
  • neurogenesisneurogenesis Entrant Inactive User Role Beginner
    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... http://www.percona.com/forums/questions-discussions/percona-toolkit/10524-pt-online-schema-change-update-delete-query
    - 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
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.