failed pt-online-schema-change left behind triggers. How to delete?

Question

We had a failed pt-osc - the server ran out of disk space. And now the triggers are left behind

SHOW TRIGGERS;

pt_osc_xxx_production_orders_ins
pt_osc_xxx_production_orders_upd
pt_osc_xxx_production_orders_del

How can these be deleted?

Doing a DROP TRIGGER seems to lock the table (it has 150 million rows) and looks like it will take some hours.

We cannot use pt-osc on this table anymore because it fails with a Trigger already exists error.

We are running on mysql Ver 8.0.32-24 for Linux on x86_64 (Percona Server (GPL), Release 24, Revision e5c6e9d2) under CentOS8 (if that makes any difference)

Answer 1

I had to do this operation many times at my last job, because we used pt-online-schema-change dozens of times per week, and occasionally something would cause it to fail.

You must use DROP TRIGGER. The operation itself is nearly instantaneous, and it is not longer no matter what size the table is. But it must acquire a metadata lock before it can execute the drop. So it can in theory wait for a long time to acquire that lock.

Any transaction against that table, even ones that only do a read-only query, will inhibit a metadata lock. So if there are transactions outstanding that had done a query on the table, your DROP TRIGGER will wait. The timeout for a metadata lock is lock_wait_timeout, which defaults to 31536000 seconds (1 year!).

The best solution is to code your application so that you don't have such long-running transactions that last for hours.

You could also kill the threads that are holding on to their transactions. But if you have many threads with long-running transactions, this could cause some problems with your app's operation.

You may need to shut down the app briefly, as Rolando says. Once the app is shut down and there are no transactions inhibiting the DROP TRIGGER operations, they will be very quick.

Once you DROP TRIGGER for the three triggers created by pt-osc, remember to also drop the table they were copying data into. That's the only way you can free up space.

And next time, be aware that pt-osc needs a lot of disk space. Not only for the copy table, but also for binary logs. ALTER TABLE doesn't add a lot to the binary logs, because DDL is always just a single statement-based log event. But pt-osc writes the data-copy steps to the binary log incrementally, so replicas can perform the same data-copy operation in parallel. Assume this will grow the binary log proportionally to the data_length of your table, at least.

You can free up more space with PURGE BINARY LOGS, up to the log that is still needed for replication or recovery.

This occasionally created an awkward situation where a database grew to the point where we could no longer perform any schema changes to the largest table.

Answer 2

There is no escaping the locking feature.

You could attempt to rename the table

USE xxx
RENAME TABLE _production_orders_old TO _production_orders_zap;
DROP TABLE IF EXISTS _production_orders_zap;

That way, the triggers will do nothing but you still need to address dropping the triggers.

If the writes are too high, you must pause the app, drop the table and drop the triggers.

USE xxx
DROP TRIGGER IF EXISTS pt_osc_xxx_production_orders_ins;
DROP TRIGGER IF EXISTS pt_osc_xxx_production_orders_upd;
DROP TRIGGER IF EXISTS pt_osc_xxx_production_orders_del;