What does pt-online-schema-change tool do if it aborts?

Question

I am planning to use pt-online-schema-change tool for a table alter on a production server in a replication environment. Wanted to understand what steps are executed when pt-online-schema-change tool aborts due to server tool. Is it possible to resume after it aborts due to server load ? will it drop the temp table and the triggers it created? Will i need to start all over again?

Answer 1

pt-online-schema-change is very verbose with exactly what it's doing. So, when something fails I've always been able to read the last lines and it'll tell me what to do.

Specifically when migrations have failed due to load, triggers have not been dropped. In that case the output has stated exactly what I need to execute to drop them. It's possible that they are kept to be able to resume. I don't know about that. In my case I've always dropped the triggers and started from scratch after tweaking parameters or lowering database usage. I guess my way of doing it works if you aren't scripting pt-online-schema-change executions.

Regarding automating dropping temp table and triggers; By default it drops them (see documentation). However, you can always specify --nodrop-old-table, --nodrop-new-table and --nodrop-triggers. For larger tables (when I have the disk space) I generally always specify --nodrop-old-table. This enables me to quickly roll back to the old table in case something goes wrong when it's swapped in by simply issuing

RENAME TABLE mytable TO mytable_failedmigration, old-table TO mytable;

If the migration succeeds I drop my giant table by following the instructions in https://serverfault.com/a/566710/37237.