1

I used pt-online-schema-change but the process died in the middle.

The script makes a copy of the original table with the name followed by "_new".

Since the process died in the middle, the table "_new" wasn't deleted and when I try to delete it my application crashes.

It seems whenever I alter the original table this _new table is also changed so if it is not found it crashes.

I would like to know how can I remove this _new table.

2
  • 1
    As you can see in the documentation (percona.com/doc/percona-toolkit/2.1/…), pt-online-schema-change creates trigger to do the magic of a "lockless" schema change. If this process crashed, you might have to delete the triggers yourself and then the table from a mysql command prompt. Commented May 16, 2017 at 14:25
  • Thanks! I just needed to know the name of the magic. Now that you indicated that it was a trigger I was able to find them and kill them all.
    – carla
    Commented May 16, 2017 at 14:56

1 Answer 1

5

Like many other solutions designed for an almost lockless schema change, pt-online-schema-change works by creating triggers on your table and creating a _new table. As stated in the documentation:

Any modifications to data in the original tables during the copy will be reflected in the new table, because the tool creates triggers on the original table to update the corresponding rows in the new table

If this utility crashed, you might have to do the cleanup yourself. This might include:

  • Removing the triggers: have a look at the SHOW TRIGGERS syntax to see the ones pt-online-schema-change has just created;
  • Delete the _new table.
1
  • When I try to re-run pt-online-schema-change, the error says "tablespace _mytable_new exists". However, when I view all the tables, this underscore-prepended table is nowhere to be found, and thus can't be deleted. Where do I find it? Commented Apr 19, 2023 at 20:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.