pt-online-schema change and replication

[dunnjoe]

Hello,

Sorry if this is a repeat issue. Over the weekend I used pt-online-schema-change to alter a 2.5 billion row table on one of our slaves. I did not switch the tables or drop the original table. The triggers are still inplace to change the new table when the original table is updated. There is only replication traffic to this server. pt-osc was chosen because a straight alter table would take 72 hours to run. When replication was turned back on new rows are seen in the old table but they are not making it to the new table. The plan is to promote this slave to a new master so that the outage window can be shortened significantly. Then the old master will become a slave and synched from the new master. So, there are no triggers on the master, only on the slave. Replication is mixed mode on the master. The slave does not have binary logging enabled.

We are running Percona 5.6.21-70 and Toolkit is 2.2.15-1.

How can I get the triggers on the slave to fire when the original table is updated by replication?

Thank you,

Joe

[scott.nemes]

Hi dunnjoe;

The problem that you appear to be hitting is that the replication events coming through for the table(s) in question are likely in a ROW binlog format, which is being chosen by the server since you have the binlog format set to MIXED. Triggers will fire on the slave table if the replication event is in STATEMENT format, but not ROW. You can read about this caveat in the manual:

https://dev.mysql.com/doc/refman/5.6/en/replication-features-triggers.html

So the direct/literal answer to your question would be that your master would need to be set to a binlog format of STATEMENT, or you need to find out why the queries inserting/updating the table(s) in question are causing the binlog format to switch to ROW instead of STATEMENT given the choice. See the below link to the manual regarding a binlog format of MIXED and why/when MySQL will choose ROW over STATEMENT (mainly non-deterministic things). If switching to a STATEMENT binlog format is an option for you, then that is an alternative option. But that is not a switch to be taken lightly, so make sure to test it thoroughly if you do go that route.

https://dev.mysql.com/doc/refman/5.6/en/binary-log-mixed.html

All of that aside, are you not wanting to perform the PTOSC straight on the master because you are afraid of the potential slave lag? Or is the master overworked to the point that having PTOSC running on it would adversely affect performance? If it’s the former, then you can modify the PTOSC settings to reduce potential slave lag. If it’s the latter, then I would also start to worry about the future load and start scaling your environment accordingly. Running PTOSC on the master would mean no outage at all other than a brief potential lock during the addition of triggers and the final table swap, which would vary in length depending on how busy the table being altered is.

-Scott