Knowing what pt-online-schema-change will do

pt-online-schema-change is simple to use, but internally it is complex.  Baron’s webinar about pt-online-schema-change hinted at several of the tool’s complexities.  Consequently, users often want to know before making changes what pt-online-schema-change will do when it runs.  The tool has two options to help answer this question: –dry-run and –print.

When ran with –dry-run and –print, pt-online-schema-change changes nothing but prints a lot of information about what it will do if ran with –execute instead.  Here’s an example (don’t strain to read all this output; I’ll extract the important parts as I discuss them):

Let’s go through that output part by part to see how it reveals that the tools is going to do something wrong, a bug fixed in v2.1.2.

First, the tool does create the new table, even with –dry-run:

This should not impact the server in any way; it is just a new, empty table.  This allows two things: 1) it verifies that the tool copies the table structure correctly, and 2) it allows the tool to test the –alter statement:

If the syntax of the –alter statement is invalid, the tool will die at this point.  In this example, everything is OK so far: a simple table with a new column named “foo”.  Notice that the table has a PRIMARY key and another non-unique key named “guest_language”–this will be important later.

As the avid reader of documentation or attendee of webinars knows: pt-online-schema-change creates three triggers: