pt-online-schema-change should error if server is a slave in row based replication

Bug #1221372 reported by Rolf on 2013-09-05
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Percona Toolkit

Bug Description

It would be nice if pt-online-schema-change tool would check to see if there is a master of the current server and if that server is using row based replication. Example, converting a table down stream to another engine, replicated data will not fire the triggers used by pt-osc.

tags: added: binlog-format pt-online-schema-change
Changed in percona-toolkit:
status: New → Triaged
Ives Stoddard (ives-stoddard) wrote :

@rolf, wouldn't this also be true for mixed-mode replication as well? (where non-deterministic inserts / updates use row-based replication instead of statement-based replication)?

and wouldn't this also impact same-engine replication?

"A statement invoking a trigger (or function) that causes an update to an AUTO_INCREMENT column is not replicated correctly using statement-based replication. MySQL 5.5 marks such statements as unsafe [and replicates using row-based replication]. (Bug #45677)"

"A.5.14: How are actions carried out through triggers on a master replicated to a slave?
Row-based replication. When you use row-based replication, the changes caused by executing the trigger on the master are applied on the slave. However, the triggers themselves are not actually executed on the slave under row-based replication. This is because, if both the master and the slave applied the changes from the master and—in addition—the trigger causing these changes were applied on the slave, the changes would in effect be applied twice on the slave, leading to different data on the master and the slave.

In most cases, the outcome is the same for both row-based and statement-based replication. However, if you use different triggers on the master and slave, you cannot use row-based replication. (This is because the row-based format replicates the changes made by triggers executing on the master to the slaves, rather than the statements that caused the triggers to execute, and the corresponding triggers on the slave are not executed.) Instead, any statements causing such triggers to be executed must be replicated using statement-based replication."

on a related note, SQL_MODE should also be checked on both slave and master. if they differ, row ordering could be an issue...

Replication and Server SQL Mode

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

Other bug subscribers