Pt-online-schema-change failing for a table with triggers

MySQL & MariaDB Other MySQL® Questions
1

Hi,

I am trying to add an index to a table which already has two triggers (INSERT and UPDATE)…Now I am using --preserve-triggers to preserve the eisting triggers on new table but still getting below error.
““Check if all fields referenced by the trigger still exists after the operation you are trying to apply at /usr/local/Cellar/percona-toolkit/3.5.5/libexec/bin/pt-online-schema-change line 11558.””

Our MySQL version is 8.0.32 and Aurora version is 3.05.2.

Command used :
pt-online-schema-change --recursion-method=none --chunk-size=100000 --max-load Threads_running=300 --critical-load Threads_running=1000 --alter-foreign-keys-method=auto --alter=“ADD INDEX <Index_name> (row_stamp)” h=“XXXXXXXXXXX”,P=3306,u=${PTOSC_USER},p=“${PTOSC_PASSWORD}”,D=XXXXXXXX,t=XXXXXXXX --print --pause-file /tmp/pause-file --preserve-triggers --dry-run

I checked the existing triggers on table and the columns in both the triggers are same…Here is the trigger definition :

CREATE DEFINER=xxxxxxxx@% TRIGGER mysqldb.ohgdrsxozvltfmh AFTER UPDATE ON _actual_cash_new
FOR EACH ROW
INSERT INTO actual_cash_history (
cash_flow_id,
created_at,
changed_on,
public_id,
currency,
amount,
amount_scale,
account_id,
execution_time,
capture_time,
settlement_time,
cancellation_time,
estimated_settlement_time,
earliest_settlement_time,
latest_settlement_time,
business_transaction_type,
business_transaction_public_id,
algorithm_version,
collision_hash,
content_hash,
forecasted_settlement_time,
cash_flow_type
)
SELECT
acf.cash_flow_id,
acf.created_at,
NOW(),
acf.public_id,
acf.currency,
acf.amount,
acf.amount_scale,
acf.account_id,
acf.execution_time,
acf.capture_time,
acf.settlement_time,
acf.cancellation_time,
acf.estimated_settlement_time,
acf.earliest_settlement_time,
acf.latest_settlement_time,
acf.business_transaction_type,
acf.business_transaction_public_id,
acf.algorithm_version,
acf.collision_hash,
acf.content_hash,
acf.forecasted_settlement_time,
acf.cash_flow_type
FROM actual_cash AS acf
WHERE acf.cash_flow_id = NEW.cash_flow_id"] at /usr/local/Cellar/percona-toolkit/3.5.5/libexec/bin/pt-online-schema-change line 11552.

Any help will be greatly appreciated…

2

You don’t need pt-osc for adding indexes. ADD INDEX is already an online, non-blocking background operation. Adding the index with a normal ALTER TABLE will not block other writers.