How to prevent replication break while adding index/column using pt-osc

Overview of ISSUE:

This post is regarding a scenario where the pt-osc command breaks replication and how to prevent that. If you are running a pt-osc command on a primary instance and it has replicas under it with existing replication filters checks, the replication can break, if some pre-checks are not done properly and few changes are not made.

We recently came across a scenario, where we received a replication stopped alert. On analyzing further, we noticed that the replication broke with an alert of unknown table as below:

Error 'Table DB._tbl_new' doesn't exist' on query. Default database: DB. Query: 'RENAME TABLE `DB`.`tbl` TO `DB`.`_tbl_old`, `DB`.`_tbl_new` TO `DB`.`tbl`'

This error occurred as a pt-osc was running on a master instance to add a column in table `DB`.`tbl`, and the tables DB._tbl_new and DB._tbl_old did not exist on this replica server. 

So why did this happen? When the pt-osc is running, it works by creating an empty copy of the table (_new) to alter, modifying it as desired, and then copying rows from the original table into the new table. When the copy is complete, it renames the original table to _old and replaces it with the new one. By default, it also drops the original table. Hence the _old and _new, both tables were missing here.

There were multiple replicas under this primary and it worked fine for all, except for this one. We confirmed that the index was added on the primary and all other replicas except this one. 

On further analysis, we noticed that this happened because this replica had a replication filter set as replicate_do_table, and it was replicating only a few tables from the master and the pt-osc command was run with an option of –[no]check-replication-filters.

So what does this command basically do? When you apply this option in pt-osc commands, it will not check the replication filters on any replica and keep running the pt-osc command without any error. If you do not specify this option and a replica has a replication filter, it will abort pt-osc with an error and not let the tool run. So it is advisable to use this option, of course with some pre-checks.

Here the pt-osc ran completely and in its last step, it failed on this replica as it was not replicating the newly created DB._tbl_new table and the original renamed table, which is DB._tbl_old, as they were not on the replicate_do_table list, hence the SQL thread did not create them in the first place and the no-check filter did not let any error generate during the entire process. 

We then skipped the error and started the replication process, but the index creation did not happen on this replica, so we ended up with a different table structure for this table on primary and this replica, which is not a recommended situation.

Let’s understand this better with a test case of this error.

TEST CASE 

Table structure on both master and replica:

CREATE TABLE t (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar (100) CHARACTER SET utf8mb4 DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1

Replication filter on the replica instance:

Replicate_Do_Table: db.t

pt-osc command to add an index:

pt-online-schema-change --print --progress time,10 --pause-file=/tmp/pt-osc-t.pause --alter="ADD INDEX idx_name (name)" --tries create_triggers:10000:0.25,drop_triggers:10000:0.25,copy_rows:10000:0.25,swap_tables:10000:0.25,update_foreign_keys:10000:0.25,analyze_table:10000:0.25 --set-vars lock_wait_timeout=1 h=localhost,S=/tmp/mysql.sock,D=db,t=t --critical-load threads_running=99999 --max-load Threads_running=50 --new-table-name=_%T_new --chunk-time=1 --max-lag 300 --no-check-replication-filters --execute 2>&1 | tee -a /tmp/pt-osc-t.log

Index added successfully on primary. Replication failed with below error on one of the replica instance (which had replication filters):

Last_Error: Error 'Can't find file: './db/_t_new.frm' (errno: 2 - No such file or directory)' on query. Default database: 'db'. Query: 'RENAME TABLE `db`.`t` TO `db`.`_t_old`, `db`.`_t_new` TO `db`.`t`'

This is expected as SQL thread will not create the _t_new table on replica instance because it is not there in the –replicate-do-table list.

SOLUTION

Now let’s see how we can avoid such a situation, where the pt-osc command runs successfully across all replicas and also the table structure is the same across the environment.

Below are the steps to do when you perform any schema change using pt-osc

1. First check all replicas under the primary server and list all replicas that have replication filters like replicate-do-table, check the list and confirm if the table on which you are running pt-osc is added in this list or not. If the table does not exist on this list, there is no issue as we do not need to modify the table anyways. Now if the table is existing in the filter, it means we need to change its schema
2. Now the 2 tables that pt-osc will create during schema change namely, DB._t_new and DB._t_old will not be created here, as they are not in the replication filter. So you need to APPEND them manually in this filter now.

Below are the steps for it:

1. Added _new and _old tables into the –replicate-do-table list. Append _t_new and _t_old tables to the –replicate-do-table list

mysql> stop slave sql_thread;

Query OK, 0 rows affected (0.00 sec)




mysql> change replication filter Replicate_Do_Table=(db.t,db._t_new,db._t_old);

Query OK, 0 rows affected (0.00 sec)




mysql> start slave sql_thread;

Query OK, 0 rows affected (0.00 sec)

NOTE: You need to provide all the existing tables in the list first and then append the _new and _old tables. This is a dynamic change and no mysql restart would be required here.

2. Check the replication status and confirm that the new tables are added to replication filter

Replicate_Do_Table: db._t_old,db._t_new,db.t

3. Execute pt-osc on master, on replica replication will not be broken, and index will be added

mysql> SHOW CREATE TABLE t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar (100) CHARACTER SET utf8mb4 DEFAULT NULL, `new_column` varchar (10) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

4. After the index addition is finished, change the replication filters to the original one, by removing these 2 newly added tables

mysql> stop slave sql_thread;

Query OK, 0 rows affected (0.00 sec)




mysql> change replication filter Replicate_Do_Table=(db.t);

Query OK, 0 rows affected (0.00 sec)




mysql> start slave sql_thread;

Query OK, 0 rows affected (0.00 sec)

5. Check the replication status and confirm that the new tables are removed from the  replication filter

Replicate_Do_Table: db.t