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

[Rolf]

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.

[Ives Stoddard (ives-stoddard)]

@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?

http://dev.mysql.com/doc/refman/5.5/en/replication-features-triggers.html
http://dev.mysql.com/doc/refman/5.5/en/replication-features-auto-increment.html

[quote]
"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)"
[/quote]

http://dev.mysql.com/doc/refman/5.5/en/faqs-triggers.html

[quote]
"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."
[/quote]

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
http://dev.mysql.com/doc/refman/5.5/en/replication-features-sql-mode.html

[Przemek (pmalkowski)]

This scenario leads to breaking data consistency if you use pt-online-schema-change on a slave, so I consider this as serious issue. Also, regardless of the default binlog_format on the master, client can still set ROW mode per session. So, the tool should abort with error when it detects the slave threads exist. I think though it should be still possible to run pt-osc on slave by enforcing with some additional option, like --force-slave-run or similar.

The same problem seems to not apply to PXC/Galera, as triggers are created on all the nodes.

[Carlos Salguero (carlos-salguero)]

The fix has been commited.
There is a new parameter, --force-slave-run to be able to bypass the check introduced in this feature.

[Shahriyar Rzayev (rzayev-sehriyar)]

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