Personal blog of Yzmir Ramirez

pt-online-schema-change is an amazing tool for assisting in table modifications in cases where ONLINE ALTER is not an option. But if you have foreign keys, this could be an interesting and important read for you. Tables with foreign keys are always complicated, and they have to be handled with care. The use case I am […]

You probably missed the news, but… PT-1751: Adds –where param to pt-online-schema-change This brings the possibility to perform what I would call an “inverted purge” because you are not actually purging rows from your multi-terabyte table, but rather, you copy the small percentage of rows you want to keep to a new table and then […]

Starting from Percona Toolkit 3.6.0, pt-online-schema-change supports the option –where, so you can not only alter your table definition live but copy only rows that satisfy certain criteria. It may happen that you may not need to copy all the data when changing table definition. For example, if the table is too big and you […]

Starting from Percona Toolkit 3.6.0, you can resume pt-online-schema-change if it was interrupted. This blog describes the prerequisites and usage of the new –resume option. To restart the job, you need to know where it failed. This is why the first option you must use is –history. It instructs pt-online-schema-change to store its progress in […]

Table modifications are a routine task for database administrators. The blog post Using Percona Toolkit to Alter Database Tables Online: A Controlled Approach provides insights into the process of altering tables online in a controlled manner, ensuring uninterrupted access for application users and preventing application downtime. We will focus here on utilizing the powerful “pt-online-schema-change” […]

Percona Toolkit 3.5.5 was released on October 3, 2023. This blog post covers the main changes in this release.

pt-online-schema-change improvements

After nibble hook

This was contributed by Marcelo HP Ferreira at https://github.com/percona/percona-toolkit/pull/645. The feature allows you to define a custom plugin that will be triggered after copying each nibble.

Here are a few examples of what you can print with this hook:

• Copy rate
• Number of rows per nibble
• Run time per nibble
• Chunk size when using dynamic chunks

You can find an example plugin in the sample file
t/pt–online–schema–change/samples/plugins/on_copy_rows_after_nibble.pm

Using option –skip-check-slave-lag multiple times

pt–online–schema–change  always supported multiple invocations of option
—skip–check–slave–lag  but practically used only the first one; other replicas are still checked for the lag as reported at PT-2241. Contribution by PinoCao (PR-622) fixes this issue. Now, this option works as expected. You can specify multiple replicas for which you do not want to check lag.

No failure on replica disconnect

This is one more feature that was broken before. As described in the user manual for the
—max–lag  option:

The tool waits forever for replicas to stop lagging. If any replica is stopped, the tool waits forever until the replica is started. The data copy continues when all replicas are running and not lagging too much.

However, this was not the case. In case of replica disconnect,
pt–online–schema–change  just stopped working. This was especially unfortunate if this happened after a few hours of data copying. This behavior was reported at PT-2168 and PT-2096

The reason why this feature did not work lies in a few places. One is a debugging code for bug fix PT-1760 that was not removed. As a result, the tool died in case of replica disconnect instead of handling the error.

But even after removing this debugging code, there exist multiple places in the code where the tool can die. Fix for PT-2168 adds checks for these situations and, depending on the option
—fail–on–stopped–replication , either dies or continues waiting when the replica is available again. Now,
pt–online–schema–change  should die less frequently.

pt–online–schema–change  was failing at replica loss for many years, and users may rely on this behavior. Therefore, the option
—fail–on–stopped–replication  is enabled by default. This means that you need to disable it (
—nofail–on–stopped–replication ) if you want
pt–online–schema–change  to continue copying data after one of the replicas is down.

pt–online–schema–change  also updates the replica list while waiting. If you use option
—recursion–method , set to processlist or hosts, this happens automatically. If you use
—recursion–method=dsn , you need to update the DSN table manually, so the tool re-reads it and updates the list of replicas it monitors.

K8 support

Percona Toolkit is shipped with the
pt–k8s–debug–collector  tool that collects diagnostic data from Percona Kubernetes operators. It collects system information together with the output of
pt–mysql–summary ,
pt–mongodb–summary , or
pg_gather  tools, depending on the operator. In version 3.5.5, support for PostgreSQL operator version two has been added. We are planning to add new features to this tool for every release. It will become a Swiss army knife for Percona Kubernetes Operators troubleshooting.

Quality improvements

Perl tools in Percona Toolkit come with a regression test suite that uses its own module that fires up MySQL sandbox (
lib/Sandbox.pm ) and extended Perl module
Test::More  (
lib/PerconaTest.pm ). Unfortunately, in the last few years, not all tests were stable, and the suite was not run at each code change. After noticing this, we started working on fixing existing tests so they do not fail due to improvements in new MySQL versions or environmental issues. Fix for PT-2156: Fix tests for lib finishes this effort. We now run tests locally before releasing the product. We plan to make regression tests part of the release process starting from the next version.

Another move in this direction is standardizing the output of the common options, such as format of the option
—version . This is done as a bug fix for PT-2235: pt-mongodb-index-check does not support option –version and PT-2236: pt-secure-collect, pt-pg-summary do not follow PT standard for option –version.

Percona Toolkit also fixed typos and trailing whitespace issues in this release. Viktor Szépe contributed to these changes. He also updated our GitHub workflows, so such issues should not show up in the future.

Quality control is not only about code fixes but also about testing. In the latest releases, we get CVE reports for Go tools from Kushal Haldar. They include more vulnerabilities than Open Source tools that we use, can find, and help us improve the quality of Go-based tools.

Community contributions

Percona Toolkit continuously receives contributions. In the latest releases, we try to accept as many contributions as possible. At the same time, we started requiring minimal quality control, such as adding test cases and running regression tests before accepting the patch. There are still a few pull requests waiting for final resolution, but we hope we will process all of them by the end of the year.

For this release, we want to thank:

• Kushal Haldar for his reports about vulnerabilities in Go-based tools
• Viktor Szépe for pull requests, improving the quality of our code
• Marcelo HP Ferreira for after nibble hook to
  pt–online–schema–change
• PinoCao for
  —skip–check–slave–lag  bug fix

Percona Toolkit is a collection of advanced open source command-line tools, developed and used by the Percona technical staff, that are engineered to perform a variety of MySQL, MariaDB, MongoDB, and PostgreSQL server and system tasks that are too difficult or complex to perform manually.

 

Download Percona Toolkit

During the early stages of my career, I was captivated by the theories and concepts surrounding foreign keys and how they empowered us to maintain data integrity. However, in practical application, I have found them to be quite challenging to manage. I am sure you’re a champion DBA, but I keep my distance from foreign keys.

With that short story as a background, this post aims to address the dilemma that arises when utilizing the pt-online-schema-change tool on tables that contain foreign keys.

We already know what one of the most used Percona tools pt-online-schema-change is and how pt-online-schema-change handles foreign keys.

When utilizing the pt-online-schema-change tool to alter a table, such as renaming the original table to be replaced as a new one, it’s important to consider how foreign keys are affected. The tool offers two options, rebuild_constraints, and drop_swap, to ensure that foreign keys continue to reference the correct table during the alteration process. Here are the high-level steps involved:

1. Drop and re-add foreign key constraints (rebuild_constraints)

• Rename Current Table T1 to T1_old (FKs are pointing to T1_old now)
• Rename New table with changes, _T1_new to T1 (T1 doesn’t have any tables referencing it)
• ALTER on child tables to adjust the foreign key to point to T1 rather than T1_old.

2. Drop the original table and rename the new table as original (drop_swap)

• Disable foreign key checks for the session (FOREIGN_KEY_CHECKS=0)
• Drop the original table
• Rename the new table _T1_new to T1

It’s worth noting that when using the “drop_swap” option, toward the end of the process, the child table’s foreign key will be pointing to the updated table. However, this approach carries some risks due to the following facts:

• For a short time between dropping the original table and renaming the temporary table, the table to be altered simply does not exist.
• In the event of an error during the rename operation, rollback is impossible, as the original table has already been dropped. This can result in data loss or other unintended consequences.

# XXX Auto-choose the alter fk method BEFORE swapping/renaming tables
# else everything will break because if drop_swap is chosen, then we
# most NOT rename tables or drop the old table.
if ( $alter_fk_method eq 'auto' ) {
# If chunk time is set, then use the average rate of rows/s
# from copying the orig table to determine the max size of
# a child table that can be altered within one chunk time.
# The limit is a fudge factor. Chunk time won't be set if
# the user specified --chunk-size=N on the cmd line, in which
# case the max child table size is their specified chunk size
# times the fudge factor.
my $max_rows = $o->get('dry-run') ? $o->get('chunk-size') * $limit
: $chunk_time && $avg_rate ? $avg_rate * $chunk_time * $limit
: $o->get('chunk-size') * $limit;
PTDEBUG && _d('Max allowed child table size:', $max_rows);

$alter_fk_method = determine_alter_fk_method(
child_tables => $child_tables,
max_rows => $max_rows,
Cxn => $cxn,
OptionParser => $o,
);

Now, the tool will use drop_swap for the child tables which are larger in data size or a number of rows. 

I want to bring something important to your attention, and that is rebuilding the constraints will be slower for large tables unless it is done “ONLINE.” The online operation will not really need the data copy, and it will be faster.

The documentation reads:

    The INPLACE algorithm is supported when foreign_key_checks is disabled. Otherwise, only the COPY algorithm is supported.

This has already been identified and improved in the pt-online-schema-change version 3.3.1, as noted in the bug report https://jira.percona.com/browse/PT-1327

Thus, in my opinion, while using pt-online-schema-change, it is both safe and efficient to use “rebuild_constraints” as long as the tool is version 3.3.1 or higher. Avoid using “drop_swap,” and surely it makes less sense to use “auto.” Don’t “auto” the pt-online-schema-change for tables with foreign keys; understand the options and take control in your hands. That said, it is really easy to upgrade to the latest version of Percona Toolkit.

Percona Toolkit

Table modifications are a common task for database administrators. In this blog, I’ll explain how to alter tables online in a controlled manner that does not disrupt application users or cause application downtime.

One of the tools in Percona Toolkit is pt-online-schema-change, a utility that alters the structure of a table without interfering with the reads or writes. The tool creates an empty copy of the table to alter and modify as desired before copying the rows from the original table into the new one.

When the copying is finished, it removes the original table and replaces it with the new one. Any changes made to data in the original tables during the copy process will be reflected in the new table as the tool creates triggers on the original table to update the corresponding rows in the new table.

How to test the pt-online-schema-change command?

Before running the actual alter using the tool, perform a dry run to ensure the pt-online-schema-change command is functional. The –dry-run option creates and modifies the new table without adding triggers, copying data, or replacing the existing table.

The basic command for modifying a table is as follows, which may need to be tweaked as needed using the variables like –critical-load threads_running –max-load Threads_running –chunk-size –max-lag, –max-flow-ctl (Percona XtraDB Cluster) and so on running in a production environment.

I’m using “ENGINE=InnoDB” for the first test case, which rebuilds the table; this is useful for removing fragmented spaces from the table.

Dry-run test:

$ pt-online-schema-change --dry-run --alter "ENGINE=InnoDB" h=172.31.92.72,D=mytestdb,t=authors;
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Starting a dry run.  `mytestdb`.`authors` will not be altered.  Specify --execute instead of --dry-run to alter the table.
Creating new table...
Created new table mytestdb._authors_new OK.
Altering new table...
Altered `mytestdb`.`_authors_new` OK.
Not creating triggers because this is a dry run.
Not copying rows because this is a dry run.
Not swapping tables because this is a dry run.
Not dropping old table because this is a dry run.
Not dropping triggers because this is a dry run.
2022-12-09T05:44:23 Dropping new table...
2022-12-09T05:44:23 Dropped new table OK.
Dry run complete.  `mytestdb`.`authors` was not altered.
$

How to run the ALTER TABLE?

It is recommended that you read the documentation before performing the task.

To run the alter, replace the –dry-run option with –execute.

$ pt-online-schema-change --execute --alter "ENGINE=InnoDB" h=172.31.92.72,D=mytestdb,t=authors;
Found 1 slaves:
ip-172-31-90-216.ec2.internal -> ip-172-31-90-216.ec2.internal:socket
Will check slave lag on:
ip-172-31-90-216.ec2.internal -> ip-172-31-90-216.ec2.internal:socket
..
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `mytestdb`.`authors`...
Creating new table...
Created new table mytestdb._authors_new OK.
Altering new table...
Altered `mytestdb`.`_authors_new` OK.
2022-12-09T05:57:10 Creating triggers...
2022-12-09T05:57:10 Created triggers OK.
2022-12-09T05:57:10 Copying approximately 10023 rows...
2022-12-09T05:57:10 Copied rows OK.
2022-12-09T05:57:10 Analyzing new table...
2022-12-09T05:57:10 Swapping tables...
2022-12-09T05:57:10 Swapped original and new tables OK.
2022-12-09T05:57:10 Dropping old table...
2022-12-09T05:57:10 Dropped old table `mytestdb`.`_authors_old` OK.
2022-12-09T05:57:10 Dropping triggers...
2022-12-09T05:57:10 Dropped triggers OK.
Successfully altered `mytestdb`.`authors`.
$

Can we pause the pt-online-schema-change execution? Yes!

The –pause-file=/tmp/pt-osc.pause option helps you to pause the execution. While the file specified by this parameter is present, execution will be paused and resumed when it is removed.

Note: I shortened the pt-osc log to make the result more readable.

$ pt-online-schema-change --pause-file=/tmp/pt-osc.pause --execute --alter "ENGINE=InnoDB" h=172.31.92.72,D=mytestdb,t=authors;
….
2022-12-10T15:42:01 Copying approximately 10023 rows...
Sleeping 60 seconds because /tmp/pt-osc.pause exists
Sleeping 60 seconds because /tmp/pt-osc.pause exists
Copying `mytestdb`.`authors`:  73% 00:44 remain
2022-12-10T15:44:04 Copied rows OK.
...
Successfully altered `mytestdb`.`authors`.

Can we review the data and tables before swapping them? Yes!

The —no-swap-tables —no-drop-old-table —no-drop-new-table —no-drop-triggers options allow us to do the alter in a controlled manner. 

That is, we will let tools handle the majority of the tasks, such as creating the new table, altering, copying the records, and the remaining table swapping and trigger dropping will be done manually.

Caution:  The —no-swap-tables option does not work if the table has foreign keys with child tables associated.

$ pt-online-schema-change --no-swap-tables --no-drop-triggers --no-drop-old-table --no-drop-new-table --execute --alter "CHARACTER SET = utf8mb4, COLLATE = utf8mb4_general_ci, MODIFY email varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,MODIFY name  varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL" h=172.31.92.72,D=mytestdb,t=authors2;
Found 1 slaves:
ip-172-31-90-216.ec2.internal -> ip-172-31-90-216.ec2.internal:socket
Will check slave lag on:
ip-172-31-90-216.ec2.internal -> ip-172-31-90-216.ec2.internal:socket
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `mytestdb`.`authors2`...
Creating new table...
Created new table mytestdb._authors2_new OK.
Altering new table...
Altered `mytestdb`.`_authors2_new` OK.
2022-12-09T09:16:28 Creating triggers...
2022-12-09T09:16:28 Created triggers OK.
2022-12-09T09:16:28 Copying approximately 10067 rows...
2022-12-09T09:16:29 Copied rows OK.
Not dropping old table because --no-drop-triggers was specified.
Not dropping triggers because --no-drop-triggers was specified.  To drop the triggers, execute:
DROP TRIGGER IF EXISTS `mytestdb`.`pt_osc_mytestdb_authors2_del`
DROP TRIGGER IF EXISTS `mytestdb`.`pt_osc_mytestdb_authors2_upd`
DROP TRIGGER IF EXISTS `mytestdb`.`pt_osc_mytestdb_authors2_ins`
Not dropping the new table `mytestdb`.`_authors2_new` because --no-drop-new-table was specified.  To drop the new table, execute:
DROP TABLE IF EXISTS `mytestdb`.`_authors2_new`;
Successfully altered `mytestdb`.`authors2`.
$

pt-online-schema-change has done the job and we now have two tables and three triggers. So we can safely review the table structure and data in the _authors2_new table, and once we’re sure everything is in order, we can swap and drop the triggers.

mysql> show tables like '%authors2%';
+---------------------------------+
| Tables_in_mytestdb (%authors2%) |
+---------------------------------+
| _authors2_new                   |
| authors2                        |
+---------------------------------+
2 rows in set (0.00 sec)

Find the TRIGGERS:

mysql> SELECT TRIGGER_NAME,EVENT_MANIPULATION FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA='mytestdb' and EVENT_OBJECT_TABLE like '%authors%' \G
*************************** 1. row ***************************
      TRIGGER_NAME: pt_osc_mytestdb_authors2_del
EVENT_MANIPULATION: DELETE
*************************** 2. row ***************************
      TRIGGER_NAME: pt_osc_mytestdb_authors2_upd
EVENT_MANIPULATION: UPDATE
*************************** 3. row ***************************
      TRIGGER_NAME: pt_osc_mytestdb_authors2_ins
EVENT_MANIPULATION: INSERT
3 rows in set (0.00 sec)

Run the following SQL to swap the tables and remove the triggers.

RENAME TABLE
mytestdb.authors2 TO mytestdb._authors2_old,
mytestdb._authors2_new TO mytestdb.authors2;

Lastly, remove the triggers and the old table:

DROP TRIGGER IF EXISTS `mytestdb`.`pt_osc_mytestdb_authors2_del`;
DROP TRIGGER IF EXISTS `mytestdb`.`pt_osc_mytestdb_authors2_upd`;
DROP TRIGGER IF EXISTS `mytestdb`.`pt_osc_mytestdb_authors2_ins`;
DROP TABLE IF EXISTS mytestdb._authors2_old;

Wrap up

pt-online-schema-change is a part of the Percona Toolkit for altering tables online, and we can customize it with various options available based on our needs. MySQL’s online DDL with the direct alter is an option, particularly for dropping indexes and changing metadata, among other things. Where online DDL is not a choice, we can use the pt-online-schema-change.

Caution: It is not recommended to run the tool directly in the replica instance as the PT-OSC operations will not produce a consistent table on the replicas. The statements coming via binlog will not be processed by triggers, so whatever new data is coming in via replication, will be missing in the new table.

One thing I commonly hear when working with my clients is “I want to change my DDL strategy in order to avoid locking in my database! The last time I used the same old method I ended up in a metadata lock situation!”

I agree that metadata locks can be painful, but unfortunately, it’s completely unavoidable, and changing from one tool to another won’t help with this. That said, it’s still worth it to examine how metadata locks work and what the impact is for each of the common tools and processes. In doing so we will see that all these tools will require metadata locks, but knowing more about how they work and how the use locking can help us determine the right tool for your specific use case.

Any time you make a change to a table a metadata lock is needed to ensure consistency between the table itself and MySQL’s data dictionary. In order for MySQL to establish this lock it has to wait for any query against the table in question to finish, and in turn, any query on the table that occurs after the lock request has to wait for the long-running query and the lock to process before continuing. This will become a lot more apparent in our first example where we’re going to cover the online DDL method using the instant algorithm. I will also illustrate how this works with other tools such as pt-online-schema-change and gh-ost.

Lab setup

In order to demonstrate each technology, the first thing we have to do is set up the environment. I’ve used my VM lab to create a single host using Percona Server for MySQL 8.0. I created a test database and an example table and then created three dummy records.

root@centos7-1 ~]# mysql

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed

mysql> CREATE TABLE `t1` (
    -> `c1` int unsigned NOT NULL AUTO_INCREMENT,
    -> `c2` int,
    -> `c3` int,
    -> PRIMARY KEY (`c1`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 (c1, c2, c3) values (1, 1, 1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 (c1, c2, c3) values (2, 2, 2);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 (c1, c2, c3) values (3, 3, 3);
Query OK, 1 row affected (0.00 sec)

Online DDL, Algorithm=INSTANT

In order to demonstrate how metadata locks work I have several terminals running against my host.

Terminal One

In the first terminal, I have created a long-running query that will prevent the formation of a metadata lock on the table in question.

mysql> select * from t1 where 1=1 and sleep(100);

Terminal Two

In terminal two, I attempted to run the direct alter against MySQL, which hung.

mysql> ALTER TABLE t1 ADD COLUMN c4 TINYINT NOT NULL DEFAULT '1', ALGORITHM=INSTANT;
# HUNG

Terminal three

In terminal three we can see the processlist which shows the offending query and the alter that is seeking, but unable to obtain, the metadata lock.

mysql> show processlist;
+----+-----------------+-----------+------+---------+------+---------------------------------+------------------------------------------------------------------------------+---------+-----------+---------------+
| Id | User            | Host      | db   | Command | Time | State                           | Info                                                                         | Time_ms | Rows_sent | Rows_examined |
+----+-----------------+-----------+------+---------+------+---------------------------------+------------------------------------------------------------------------------+---------+-----------+---------------+
|  5 | event_scheduler | localhost | NULL | Daemon  |   90 | Waiting on empty queue          | NULL                                                                         |   89866 |         0 |             0 |
|  8 | root            | localhost | test | Query   |   30 | User sleep                      | select * from t1 where 1=1 and sleep(100)                                    |   30212 |         0 |             0 |
|  9 | root            | localhost | test | Query   |   12 | Waiting for table metadata lock | ALTER TABLE t1 ADD COLUMN c4 TINYINT NOT NULL DEFAULT '1', ALGORITHM=INSTANT |   11630 |         0 |             0 |
| 10 | root            | localhost | NULL | Query   |    0 | init                            | show processlist                                                             |       0 |         0 |             0 |
+----+-----------------+-----------+------+---------+------+---------------------------------+------------------------------------------------------------------------------+---------+-----------+---------------+
4 rows in set (0.00 sec)

Terminal four

And in terminal four I have created another select statement against the table, but it hangs because it’s queued behind the metadata lock.

mysql> select * from t1;
#HUNG

Terminal one

So now we have fully recreated the issue. In order to resolve this I have gone back into terminal one to kill the offending long-running query.

mysql> select * from t1 where 1=1 and sleep(100);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

Terminal two

In terminal two we can see that the alter table completed now that it was able to get the lock it needed.

mysql> ALTER TABLE t1 ADD COLUMN c4 TINYINT NOT NULL DEFAULT '1', ALGORITHM=INSTANT;
Query OK, 0 rows affected (36.13 sec)

Terminal three

In terminal three we can see that the processlist is now clear.

mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+---------+-----------+---------------+
| Id | User            | Host      | db   | Command | Time | State                  | Info             | Time_ms | Rows_sent | Rows_examined |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+---------+-----------+---------------+
|  5 | event_scheduler | localhost | NULL | Daemon  |  120 | Waiting on empty queue | NULL             |  119876 |         0 |             0 |
|  8 | root            | localhost | test | Sleep   |   60 |                        | NULL             |   60222 |         0 |             1 |
|  9 | root            | localhost | test | Sleep   |   42 |                        | NULL             |   41640 |         0 |             0 |
| 10 | root            | localhost | NULL | Query   |    0 | init                   | show processlist |       0 |         0 |             0 |
| 11 | root            | localhost | test | Sleep   |   12 |                        | NULL             |   12092 |         3 |             3 |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+---------+-----------+---------------+

Terminal four

And in terminal four we can see the query that was queued behind the lock request was able to complete.

mysql> select * from t1;

+----+------+------+----+
| c1 | c2   | c3   | c4 |
+----+------+------+----+
|  1 |    1 |    1 |  1 |
|  2 |    2 |    2 |  1 |
|  3 |    3 |    3 |  1 |
+----+------+------+----+
3 rows in set (6.58 sec)

As such we can conclude that Online DDL was impacted by a long-running query given that it was unable to get the metadata lock it needed. Once the long-running query was killed the alter table was able to complete.

Before moving on to the next example, I’ll undo the changes in my lab so we have a fresh start.

Terminal one

mysql> ALTER TABLE t1 DROP COLUMN c4;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int unsigned NOT NULL AUTO_INCREMENT,
  `c2` int DEFAULT NULL,
  `c3` int DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

pt-online-schema-change

Now that we know a little more about metadata locks, I’ll simplify the lab output in this example and the gh-ost example to follow in order to keep things a little more brief and concise.

For pt-online-schema change, there are at least four places where metadata locks are found. One for the creation of each of the three associated triggers, and one for the table swap.

Just as the same as before, in terminal one I created the long-running query against the table in question. In terminal two I ran the following pt-online-schema-change command.

Terminal two

[root@centos7-1 ~]# pt-online-schema-change --print --progress time,10 \
> --no-swap-tables --no-drop-new-table --no-drop-triggers \
> --alter="ADD COLUMN c4 TINYINT NOT NULL DEFAULT '1'" \
> --tries create_triggers:10000:1,drop_triggers:10000:1,copy_rows:10000:1 \
> --set-vars tx_isolation=\'READ-COMMITTED\',lock_wait_timeout=30 \
> h=localhost,D=test,t=t1 \
> --chunk-time=1 \
> --critical-load threads_running=99999 --max-load Threads_running=50 \
> --execute
...
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10000, 1
  create_triggers, 10000, 1
  drop_triggers, 10000, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `test`.`t1`...
Creating new table...
CREATE TABLE `test`.`_t1_new` (
  `c1` int unsigned NOT NULL AUTO_INCREMENT,
  `c2` int DEFAULT NULL,
  `c3` int DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Created new table test._t1_new OK.
Altering new table...
ALTER TABLE `test`.`_t1_new` ADD COLUMN c4 TINYINT NOT NULL DEFAULT '1'
Altered `test`.`_t1_new` OK.
2022-10-18T07:47:57 Creating triggers...
# HUNG

Terminal three

As you can see, this process is hung on the creation of triggers that will keep the original table in sync with the new table that pt-osc has created. This is because creating triggers on a table changes the table definition, which impacts the data dictionary. We can see this in the processlist as shown below…

mysql> show processlist;
+----+-----------------+-----------+------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+---------+-----------+---------------+
| Id | User            | Host      | db   | Command | Time | State                           | Info                                                                                                 | Time_ms | Rows_sent | Rows_examined |
+----+-----------------+-----------+------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+---------+-----------+---------------+
|  5 | event_scheduler | localhost | NULL | Daemon  |  577 | Waiting on empty queue          | NULL                                                                                                 |  576835 |         0 |             0 |
| 10 | root            | localhost | NULL | Query   |    0 | init                            | show processlist                                                                                     |       0 |         0 |             0 |
| 11 | root            | localhost | test | Sleep   |  469 |                                 | NULL                                                                                                 |  469051 |         3 |             3 |
| 16 | root            | localhost | test | Query   |   44 | User sleep                      | select * from t1 where 1=1 and sleep(1000)                                                           |   44129 |         0 |             0 |
| 17 | root            | localhost | test | Query   |    7 | Waiting for table metadata lock | CREATE TRIGGER `pt_osc_test_t1_del` AFTER DELETE ON `test`.`t1` FOR EACH ROW BEGIN DECLARE CONTINUE  |    6567 |         0 |             0 |
| 18 | root            | localhost | test | Sleep   |   38 |                                 | NULL                                                                                                 |   38267 |         1 |             1 |
+----+-----------------+-----------+------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+---------+-----------+---------------+

Terminal two

Once I killed the offending long-running query, the pt-osc process continued:

2022-10-18T07:47:57 Creating triggers...
-----------------------------------------------------------
Event : DELETE
Name  : pt_osc_test_t1_del
SQL   : CREATE TRIGGER `pt_osc_test_t1_del` AFTER DELETE ON `test`.`t1` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; DELETE IGNORE FROM `test`.`_t1_new` WHERE `test`.`_t1_new`.`c1` <=> OLD.`c1`; END
Suffix: del
Time  : AFTER
-----------------------------------------------------------
-----------------------------------------------------------
Event : UPDATE
Name  : pt_osc_test_t1_upd
SQL   : CREATE TRIGGER `pt_osc_test_t1_upd` AFTER UPDATE ON `test`.`t1` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; DELETE IGNORE FROM `test`.`_t1_new` WHERE !(OLD.`c1` <=> NEW.`c1`) AND `test`.`_t1_new`.`c1` <=> OLD.`c1`; REPLACE INTO `test`.`_t1_new` (`c1`, `c2`, `c3`) VALUES (NEW.`c1`, NEW.`c2`, NEW.`c3`); END
Suffix: upd
Time  : AFTER
-----------------------------------------------------------
-----------------------------------------------------------
Event : INSERT
Name  : pt_osc_test_t1_ins
SQL   : CREATE TRIGGER `pt_osc_test_t1_ins` AFTER INSERT ON `test`.`t1` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; REPLACE INTO `test`.`_t1_new` (`c1`, `c2`, `c3`) VALUES (NEW.`c1`, NEW.`c2`, NEW.`c3`);END
Suffix: ins
Time  : AFTER
-----------------------------------------------------------
2022-10-18T07:49:21 Created triggers OK.
2022-10-18T07:49:21 Copying approximately 3 rows...
INSERT LOW_PRIORITY IGNORE INTO `test`.`_t1_new` (`c1`, `c2`, `c3`) SELECT `c1`, `c2`, `c3` FROM `test`.`t1` LOCK IN SHARE MODE /*pt-online-schema-change 7797 copy table*/
2022-10-18T07:49:21 Copied rows OK.
Not dropping old table because --no-drop-triggers was specified.
Not dropping triggers because --no-drop-triggers was specified.  To drop the triggers, execute:
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_t1_del`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_t1_upd`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_t1_ins`
Not dropping the new table `test`.`_t1_new` because --no-drop-new-table was specified.  To drop the new table, execute:
DROP TABLE IF EXISTS `test`.`_t1_new`;
Successfully altered `test`.`t1`.

Terminal two

But you’ll notice in our command that we noted that we would swap tables later using the –no-swap-tables –no-drop-new-table options. So now we have to swap the table. This is impacted by metadata locks as well. In terminal one, I created the long-running query again, and in terminal two I attempted to do the table swap.

mysql> RENAME TABLE test.t1 to test._t1_old,
    -> test._t1_new to test.t1;
# HUNG

Terminal three

This was hung due to waiting for a metadata lock, which we can see in the processlist:

mysql> show processlist;
+----+-----------------+-----------+------+---------+------+---------------------------------+---------------------------------------------------------------+---------+-----------+---------------+
| Id | User            | Host      | db   | Command | Time | State                           | Info                                                          | Time_ms | Rows_sent | Rows_examined |
+----+-----------------+-----------+------+---------+------+---------------------------------+---------------------------------------------------------------+---------+-----------+---------------+
|  5 | event_scheduler | localhost | NULL | Daemon  |  762 | Waiting on empty queue          | NULL                                                          |  762369 |         0 |             0 |
| 10 | root            | localhost | NULL | Query   |    0 | init                            | show processlist                                              |       0 |         0 |             0 |
| 11 | root            | localhost | test | Sleep   |  654 |                                 | NULL                                                          |  654585 |         3 |             3 |
| 16 | root            | localhost | test | Query   |   80 | User sleep                      | select * from t1 where 1=1 and sleep(1000)                    |   79853 |         0 |             0 |
| 20 | root            | localhost | test | Query   |   41 | Waiting for table metadata lock | RENAME TABLE test.t1 to test._t1_old,
test._t1_new to test.t1 |   41043 |         0 |             0 |
+----+-----------------+-----------+------+---------+------+---------------------------------+---------------------------------------------------------------+---------+-----------+---------------+

Terminal three

Once the long-running query was killed, the table swap was able to continue. Further work on this process was not interrupted by long-running queries because all work at this point only happens on the swapped _old table.

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| _t1_old        |
| t1             |
+----------------+
2 rows in set (0.00 sec)

mysql> drop trigger pt_osc_test_t1_ins;
Query OK, 0 rows affected (0.00 sec)

mysql> drop trigger pt_osc_test_t1_upd;
Query OK, 0 rows affected (0.01 sec)

mysql> drop trigger pt_osc_test_t1_del;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table _t1_old;
Query OK, 0 rows affected (0.01 sec)

So, as you can see, pt-osc is impacted by metadata locks as well. In fact, three more than direct alters.

gh-ost

You may think that using gh-ost will help you get around metadata locks, but this isn’t the case. As you will see below, if there is a long-running query, it will hang when it tries to do the table swap similar to how pt-osc swaps tables.

[root@centos7-1 ~]# gh-ost \
> --max-load=Threads_running=25 \
> --critical-load=Threads_running=1000 \
> --chunk-size=1000 \
> --user="root" \
> --password="password" \
> --host=localhost \
> --allow-on-master \
> --database="test" \
> --table="t1" \
> --verbose \
> --alter="ADD COLUMN c4 TINYINT NOT NULL DEFAULT '1'" \
> --switch-to-rbr \
> --allow-master-master \
> --cut-over=default \
> --exact-rowcount \
> --concurrent-rowcount \
> --default-retries=120 \
> --execute
2022-10-18 07:56:38 INFO starting gh-ost 1.1.5
2022-10-18 07:56:38 INFO Migrating `test`.`t1`

...

# Migrating `test`.`t1`; Ghost table is `test`.`_t1_gho`
# Migrating centos7-1.localdomain:3306; inspecting centos7-1.localdomain:3306; executing on centos7-1.localdomain
# Migration started at Tue Oct 18 07:56:38 -0400 2022
# chunk-size: 1000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: Threads_running=25; critical-load: Threads_running=1000; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# Serving on unix socket: /tmp/gh-ost.test.t1.sock
Copy: 0/3 0.0%; Applied: 0; Backlog: 0/1000; Time: 0s(total),

...

2022-10-18 07:56:39 INFO Creating magic cut-over table `test`.`_t1_del`
2022-10-18 07:56:39 INFO Magic cut-over table created
2022-10-18 07:56:39 INFO Locking `test`.`t1`, `test`.`_t1_del`
Copy: 3/3 100.0%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 1s(copy); streamer: centos7-1-bin.000003:20639; Lag: 0.04s, HeartbeatLag: 0.05s, State: migrating; ETA: due
Copy: 3/3 100.0%; Applied: 0; Backlog: 0/1000; Time: 3s(total), 1s(copy); streamer: centos7-1-bin.000003:25267; Lag: 0.04s, HeartbeatLag: 0.05s, State: migrating; ETA: due
Copy: 3/3 100.0%; Applied: 0; Backlog: 0/1000; Time: 4s(total), 1s(copy); streamer: centos7-1-bin.000003:29893; Lag: 0.04s, HeartbeatLag: 0.05s, State: migrating; ETA: due
Copy: 3/3 100.0%; Applied: 0; Backlog: 0/1000; Time: 5s(total), 1s(copy); streamer: centos7-1-bin.000003:34518; Lag: 0.04s, HeartbeatLag: 0.05s, State: migrating; ETA: due
Copy: 3/3 100.0%; Applied: 0; Backlog: 0/1000; Time: 6s(total), 1s(copy); streamer: centos7-1-bin.000003:39149; Lag: 0.04s, HeartbeatLag: 0.05s, State: migrating; ETA: due
Copy: 3/3 100.0%; Applied: 0; Backlog: 0/1000; Time: 7s(total), 1s(copy); streamer: centos7-1-bin.000003:43780; Lag: 0.04s, HeartbeatLag: 0.05s, State: migrating; ETA: due
2022-10-18 07:56:45 ERROR Error 1205: Lock wait timeout exceeded; try restarting transaction
2022-10-18 07:56:45 INFO Looking for magic cut-over table
2022-10-18 07:56:45 ERROR Error 1205: Lock wait timeout exceeded; try restarting transaction

And once this lock clears, the process is able to complete.

2022-10-18 07:56:52 INFO Dropping magic cut-over table
2022-10-18 07:56:52 INFO Dropping table `test`.`_t1_del`
2022-10-18 07:56:52 INFO Table dropped
Copy: 3/3 100.0%; Applied: 0; Backlog: 0/1000; Time: 15s(total), 1s(copy); streamer: centos7-1-bin.000003:81553; Lag: 0.04s, HeartbeatLag: 0.05s, State: migrating; ETA: due

...

# Migrating `test`.`t1`; Ghost table is `test`.`_t1_gho`
# Migrating centos7-1.localdomain:3306; inspecting centos7-1.localdomain:3306; executing on centos7-1.localdomain
# Migration started at Tue Oct 18 07:56:38 -0400 2022
# chunk-size: 1000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: Threads_running=25; critical-load: Threads_running=1000; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# Serving on unix socket: /tmp/gh-ost.test.t1.sock
Copy: 3/3 100.0%; Applied: 0; Backlog: 0/1000; Time: 20s(total), 1s(copy); streamer: centos7-1-bin.000003:106278; Lag: 0.04s, HeartbeatLag: 0.06s, State: migrating; ETA: due
2022-10-18 07:56:58 INFO Setting RENAME timeout as 3 seconds
2022-10-18 07:56:58 INFO Session renaming tables is 41
2022-10-18 07:56:58 INFO Issuing and expecting this to block: rename /* gh-ost */ table `test`.`t1` to `test`.`_t1_del`, `test`.`_t1_gho` to `test`.`t1`
Copy: 3/3 100.0%; Applied: 0; Backlog: 0/1000; Time: 21s(total), 1s(copy); streamer: centos7-1-bin.000003:111137; Lag: 0.04s, HeartbeatLag: 0.05s, State: migrating; ETA: due
2022-10-18 07:56:59 INFO Found atomic RENAME to be blocking, as expected. Double checking the lock is still in place (though I don't strictly have to)
2022-10-18 07:56:59 INFO Checking session lock: gh-ost.40.lock
2022-10-18 07:56:59 INFO Connection holding lock on original table still exists
2022-10-18 07:56:59 INFO Will now proceed to drop magic table and unlock tables
2022-10-18 07:56:59 INFO Dropping magic cut-over table
2022-10-18 07:56:59 INFO Releasing lock from `test`.`t1`, `test`.`_t1_del`
2022-10-18 07:56:59 INFO Tables unlocked
2022-10-18 07:56:59 INFO Tables renamed
2022-10-18 07:56:59 INFO Lock & rename duration: 5.975493692s. During this time, queries on `t1` were blocked
[2022/10/18 07:56:59] [info] binlogsyncer.go:164 syncer is closing...
2022-10-18 07:57:00 INFO Closed streamer connection. err=<nil>
2022-10-18 07:57:00 INFO Dropping table `test`.`_t1_ghc`
[2022/10/18 07:57:00] [error] binlogstreamer.go:77 close sync with err: sync is been closing...
[2022/10/18 07:57:00] [info] binlogsyncer.go:179 syncer is closed
2022-10-18 07:57:00 INFO Table dropped
2022-10-18 07:57:00 INFO Am not dropping old table because I want this operation to be as live as possible. If you insist I should do it, please add `--ok-to-drop-table` next time. But I prefer you do not. To drop the old table, issue:
2022-10-18 07:57:00 INFO -- drop table `test`.`_t1_del`
2022-10-18 07:57:00 INFO Done migrating `test`.`t1`
2022-10-18 07:57:00 INFO Removing socket file: /tmp/gh-ost.test.t1.sock
2022-10-18 07:57:00 INFO Tearing down inspector
2022-10-18 07:57:00 INFO Tearing down applier
2022-10-18 07:57:00 INFO Tearing down streamer
2022-10-18 07:57:00 INFO Tearing down throttler
# Done

Conclusion

As you can see above, all schema change tools have to work with metadata locks. Some more than others, but this is something that we always need to consider when executing changes. Even if you opt to set a table swap for a later date/time, you still need to be aware that metadata locks can interrupt that process. The key here is to monitor for long-running queries at the time that that change occurs and kill them if they are getting in the way, if that’s possible.

This brings me to the final point: selecting a tool to use for a change. Personally, I do not believe there is such a thing as a single best tool for schema changes. You pick the best tool that will be the lightest weight and the least interruptive for the change you want to make.

Recently I was dealing with an unexpected issue raised by our Support customer, in which data became inconsistent after a schema change was applied.

After some investigation, it turned out that affected tables had a special word in the comments of some columns, which triggered an already known (and fixed) issue with the TableParser.pm library of Percona Toolkit.  The problem is that the customer was using an outdated Toolkit version, where pt-online-schema-change was using that buggy parser.

This bug applies only to Percona Toolkit versions up to 3.0.10, so if you have already 3.0.11 or newer installed, you can skip the rest of this post as these are no longer affected.

I am writing this post to warn every user of pt-online-schema-change who has not upgraded the toolkit, as the problem is potentially very dangerous and can lead to the silent loss of data. 

The problem can manifest in two ways. The first one, although confusing, is not really dangerous as the operation is canceled. It happens when the columns with culprit comments do not allow NULL values. For example:

CREATE TABLE `test_not_null` (
`id` int NOT NULL,
`add_id` int NOT NULL COMMENT 'my generated test case',
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Schema change operation on this one will look like this:

$ ./pt-online-schema-change-3.0.10 u=msandbox,p=msandbox,h=localhost,S=/tmp/mysql_sandbox5735.sock,D=test,t=test_not_null --print --alter "engine=InnoDB" --execute
(...)
Altering `test`.`test_not_null`...
Creating new table...
CREATE TABLE `test`.`_test_not_null_new` (
`id` int(11) NOT NULL,
`add_id` int(11) NOT NULL COMMENT 'my generated test case',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Created new table test._test_not_null_new OK.
Altering new table...
ALTER TABLE `test`.`_test_not_null_new` engine=InnoDB
Altered `test`.`_test_not_null_new` OK.
2020-09-30T21:25:22 Creating triggers...
2020-09-30T21:25:22 Created triggers OK.
2020-09-30T21:25:22 Copying approximately 3 rows...
INSERT LOW_PRIORITY IGNORE INTO `test`.`_test_not_null_new` (`id`) SELECT `id` FROM `test`.`test_not_null` LOCK IN SHARE MODE /*pt-online-schema-change 1438 copy table*/
2020-09-30T21:25:22 Dropping triggers...
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_test_not_null_del`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_test_not_null_upd`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_test_not_null_ins`
2020-09-30T21:25:22 Dropped triggers OK.
2020-09-30T21:25:22 Dropping new table...
DROP TABLE IF EXISTS `test`.`_test_not_null_new`;
2020-09-30T21:25:22 Dropped new table OK.
`test`.`test_not_null` was not altered.
2020-09-30T21:25:22 Error copying rows from `test`.`test_not_null` to `test`.`_test_not_null_new`: 2020-09-30T21:25:22 Copying rows caused a MySQL error 1364:
Level: Warning
Code: 1364
Message: Field 'add_id' doesn't have a default value
Query: INSERT LOW_PRIORITY IGNORE INTO `test`.`_test_not_null_new` (`id`) SELECT `id` FROM `test`.`test_not_null` LOCK IN SHARE MODE /*pt-online-schema-change 1438 copy table*/

So the reason for the failed operation may be unclear, but at least no data gets damaged. A much worse result happens when the column with comment allows nulls:

CREATE TABLE `test_null` (
`id` int NOT NULL,
`add_id` int DEFAULT NULL COMMENT 'my generated test case',
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

mysql [localhost:5735] {msandbox} (test) > select * from test_null;
+----+--------+
| id | add_id |
+----+--------+
| 1  |      1 |
| 2  |      2 |
| 3  |      3 |
+----+--------+
3 rows in set (0.01 sec)

For this one, the schema change command runs without any errors:

$ ./pt-online-schema-change-3.0.10 u=msandbox,p=msandbox,h=localhost,S=/tmp/mysql_sandbox5735.sock,D=test,t=test_null --print --alter "engine=InnoDB" --execute
(...)
Altering `test`.`test_null`...
Creating new table...
CREATE TABLE `test`.`_test_null_new` (
`id` int(11) NOT NULL,
`add_id` int(11) DEFAULT NULL COMMENT 'my generated test case',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Created new table test._test_null_new OK.
Altering new table...
ALTER TABLE `test`.`_test_null_new` engine=InnoDB
Altered `test`.`_test_null_new` OK.
2020-09-30T21:28:11 Creating triggers...
2020-09-30T21:28:11 Created triggers OK.
2020-09-30T21:28:11 Copying approximately 3 rows...
INSERT LOW_PRIORITY IGNORE INTO `test`.`_test_null_new` (`id`) SELECT `id` FROM `test`.`test_null` LOCK IN SHARE MODE /*pt-online-schema-change 3568 copy table*/
2020-09-30T21:28:11 Copied rows OK.
2020-09-30T21:28:11 Analyzing new table...
2020-09-30T21:28:11 Swapping tables...
RENAME TABLE `test`.`test_null` TO `test`.`_test_null_old`, `test`.`_test_null_new` TO `test`.`test_null`
2020-09-30T21:28:11 Swapped original and new tables OK.
2020-09-30T21:28:11 Dropping old table...
DROP TABLE IF EXISTS `test`.`_test_null_old`
2020-09-30T21:28:11 Dropped old table `test`.`_test_null_old` OK.
2020-09-30T21:28:11 Dropping triggers...
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_test_null_del`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_test_null_upd`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_test_null_ins`
2020-09-30T21:28:11 Dropped triggers OK.
Successfully altered `test`.`test_null`.

But… the table data is not the same after:

mysql [localhost:5735] {msandbox} (test) > select * from test_null;
+----+--------+
| id | add_id |
+----+--------+
|  1 |   NULL |
|  2 |   NULL |
|  3 |   NULL |
+----+--------+
3 rows in set (0.00 sec)

Summarizing, it is essential to make sure you are using the up to date Percona Toolkit, especially the pt-online-schema-change tool, to avoid potential disaster. The current latest stable release, as of when I am writing the post, is version 3.2.1, and the fixed version for this particular bug, 3.0.11, was released in July 2018.

References:

https://www.percona.com/doc/percona-toolkit/3.0/release_notes.html
https://jira.percona.com/browse/PT-1570