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.
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.