Pt-online-schema-change replication-filters information required

I have read the below statement in the pt-online-schema-change documentation. So percona wont support schema change if master is having replication filters enabled? I have master instance which is having replication cluster with read and write only instance. Please advise your tool will help us or not?

  • The tool refuses to operate if it detects replication filters. See --[no]check-replication-filters for details.

-- [no]check-replication-filters

default: yes

Abort if any replication filter is set on any server. The tool looks for server options that filter replication, such as binlog_ignore_db and replicate_do_db. If it finds any such filters, it aborts with an error.

If the replicas are configured with any filtering options, you should be careful not to modify any databases or tables that exist on the master and not the replicas, because it could cause replication to fail. For more information on replication rules, see MySQL :: MySQL 8.0 Reference Manual :: 17.2.5 How Servers Evaluate Replication Filtering Rules.

Hello, this is just a safety precaution as depending on the existing filters replication could break. If you are sure your filters are not related to the tables you want to alter then it should be ok for you to use it.

1 Like

Checked my parameter group the below parameter all are false…Also in replication cluster level i didn’t see any of the parameter. Please confirm now? Also we need to stop replication whenever we need to use pt-online-schema-change?
replicate-do-db
replicate-do-table
replicate-ignore-db
replicate-ignore-table
replicate-wild-do-table
replicate-wild-ignore-table

1 Like

You don’t need to stop replication. pt-online-schema-change will throttle itself to avoid issues with replication. Double check if there are any filters at both master and replicas servers just to be safe

1 Like

We do have our db in AWS RDS. I have checked all parameter group in primary and replication cluster instances, didn’t see any of the parameters i have mentioned earlier.

1 Like

To access rds instance we do have mfa enabled. in this case how i can use percona tool kit to connect to mfa enabled instance…Please help?

1 Like

The percona toolkit does not support MFA to RDS. You will need to disable MFA or create a user specifically for RDS without MFA to use with percona toolkit.

1 Like

Got this link while googling…Is this wont help me?

1 Like

No, because that is not RDS.

1 Like

id is primary key in transaction_log table. Is there anyway i can create the partition without modifying the primary key (id , created) ? If not in the same command (mentioned below) i can drop primary key, modify primary key and add partition?
Please advise on this.
pt-online-schema-change D=subscription,t=transaction_log,h=stm-partition-poc.us-east-2.rds.amazonaws.com,u=root --dry-run --alter “PARTITION BY RANGE(YEAR(created))(PARTITION p2014 VALUES LESS THAN (2015) ,PARTITION p2015 VALUES LESS THAN (2016) , PARTITION p2016 VALUES LESS THAN (2017) ,PARTITION p2017 VALUES LESS THAN (2018) ,PARTITION p2018 VALUES LESS THAN (2019) , PARTITION p2019 VALUES LESS THAN (2020) ,PARTITION p2020 VALUES LESS THAN (2021) ,PARTITION p2021 VALUES LESS THAN (2022) ,PARTITION p2022 VALUES LESS THAN (2023) ,PARTITION pMAXVALUE VALUES LESS THAN (MAXVALUE) );” --no-drop-new-table --no-drop-old-table --ask-pass
Enter MySQL password:

A software update is available:

Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Starting a dry run. subscription.transaction_log will not be altered. Specify --execute instead of --dry-run to alter the table.
Creating new table…
Created new table subscription._transaction_log_new OK.
Altering new table…
Not dropping the new table subscription._transaction_log_new because --no-drop-new-table was specified. To drop the new table, execute:
DROP TABLE IF EXISTS subscription._transaction_log_new;
Dry run complete. subscription.transaction_log was not altered.
(in cleanup) Error altering new table subscription._transaction_log_new: DBD::mysql::db do failed: A PRIMARY KEY must include all columns in the table’s partitioning function [for Statement “ALTER TABLE subscription._transaction_log_new PARTITION BY RANGE(YEAR(created))(PARTITION p2014 VALUES LESS THAN (2015) ,PARTITION p2015 VALUES LESS THAN (2016) , PARTITION p2016 VALUES LESS THAN (2017) ,PARTITION p2017 VALUES LESS THAN (2018) ,PARTITION p2018 VALUES LESS THAN (2019) , PARTITION p2019 VALUES LESS THAN (2020) ,PARTITION p2020 VALUES LESS THAN (2021) ,PARTITION p2021 VALUES LESS THAN (2022) ,PARTITION p2022 VALUES LESS THAN (2023) ,PARTITION pMAXVALUE VALUES LESS THAN (MAXVALUE) );”] at /usr/bin/pt-online-schema-change line 9425.

Error altering new table subscription._transaction_log_new: DBD::mysql::db do failed: A PRIMARY KEY must include all columns in the table’s partitioning function [for Statement “ALTER TABLE subscription._transaction_log_new PARTITION BY RANGE(YEAR(created))(PARTITION p2014 VALUES LESS THAN (2015) ,PARTITION p2015 VALUES LESS THAN (2016) , PARTITION p2016 VALUES LESS THAN (2017) ,PARTITION p2017 VALUES LESS THAN (2018) ,PARTITION p2018 VALUES LESS THAN (2019) , PARTITION p2019 VALUES LESS THAN (2020) ,PARTITION p2020 VALUES LESS THAN (2021) ,PARTITION p2021 VALUES LESS THAN (2022) ,PARTITION p2022 VALUES LESS THAN (2023) ,PARTITION pMAXVALUE VALUES LESS THAN (MAXVALUE) );”] at /usr/bin/pt-online-schema-change line 9425.

1 Like

All unique keys of a mysql table must contain the partition column. Since PRIMARY KEY is a UNIQUE KEY, then the PRIMARY KEY must include the partitioning column.

1 Like