pt-online-schema-change breaks slave replication

MySQL & MariaDB Other MySQL® Questions
,
1

Our database configuration is very simple with one master database and one slave database. Whenever a pt-online-schema-change of any kind (seemingly independent of table size or table alteration being performed), replication ALWAYS breaks on our slave instance. The most recent pt-online-schema-change to break replication was: 

pt-online-schema-change --alter "ADD COLUMN <column>;” 
--critical-load="Threads_running=100" 
--no-check-unique-key-change 
--alter-foreign-keys-method=drop_swap 
--recursion-method=none 
--execute D=<db>,t=<t>,u=<u>,p=<p>,h=<host>

Almost all of our pt-osc queries are constructed with these flag settings, which must create this problem (or additional flags need to be used to mitigate it). Please let me know if there is anything obvious in why this would cause slave replication to break. Any help is greatly appreciated. Thank you.

2

It would be best if you can provide us with the table structure (SHOW CREATE TABLE tblname\G) and the SHOW SLAVE STATUS\G output with the error.

3

It has nothing to do with the table structure, as replication instantly breaks with the same exception no matter what table is being modified or how it is being altered. The exception is as follows: 

Altering `<db>`.`<table>`...
Creating new table...
Created new table <db>._<table>_new OK.
Altering new table...
Altered `<db>`.`_<table>_new` OK.
2020-08-21T21:00:00 Creating triggers...
2020-08-21T21:00:00 Created triggers OK.
2020-08-21T21:00:00 Copying approximately 6162 rows...
(2) Replica h=<host>,P=<port> is stopped. Waiting.
(1) Replica h=<host>,P=<port> is stopped. Waiting.
(1) Replica h=<host>,P=<port> is stopped. Waiting.
(1) Replica h=<host>,P=<port> is stopped. Waiting.
(1) Replica h=<host>,P=<port> is stopped. Waiting.
(1) Replica h=<host>,P=<port> is stopped. Waiting.
(1) Replica h=<host>,P=<port> is stopped. Waiting.
^C# Exiting on SIGINT.<br>


As you can see, replication breaks presumably right after creating triggers or once it tries to start copying data. Once it breaks, it is seemingly in an infinite loop in which the patch will never finish. This has happened on countless tables all of different structures and alter operations, so it is absolutely not anything to do with the table structure, but rather a MySQL database configuration or replica configuration.

4

Unfortunately, with the amount of information we have it would be difficult to reproduce this. Can you provide output of SHOW SLAVE STATUS\G, and config file for both source and replica?

5

Thank you, but I have since solved the problem. This appeared to have occurred because the binlog_format was set to ‘STATEMENT’. After switching it to ‘MIXED’ our problems were resolved.