How pt-online-schema-change Handles Foreign Keys


pt-online-schema-changeForeign key related issues are very common when dealing with DDL changes in MySQL using Percona toolkit. In this blog post, I will explain how the tool (pt-online-schema-change) handles foreign key constraints when executing a DDL change.

First of all, I would like to explain why foreign keys have to be handled at all before writing more about the “How”. Foreign key constraints are aware of table rename operations. In other words, if the parent table is renamed, the child table automatically knows it and changes the foreign key constraint accordingly. Please have a look at the below example, and you can see the table name is automatically updated in the child table after the rename operation on the parent table:

mysql> show create table prd_details \G
*************************** 1. row ***************************
       Table: prd_details
Create Table: CREATE TABLE `prd_details` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_catalog_id` int(11) unsigned NOT NULL,
  CONSTRAINT `prd_details_ibfk_1` FOREIGN KEY (`product_catalog_id`) REFERENCES `product_catalog` (`catalog_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)i
mysql> RENAME TABLE product_catalog TO product_cat ;
Query OK, 0 rows affected (0.15 sec)
mysql> show create table prd_details \G
*************************** 1. row ***************************
       Table: prd_details
Create Table: CREATE TABLE `prd_details` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_catalog_id` int(11) unsigned NOT NULL,
  CONSTRAINT `prd_details_ibfk_1` FOREIGN KEY (`product_catalog_id`) REFERENCES `product_cat` (`catalog_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Well, that is indeed very nice and to be expected. But please allow me to explain how this becomes a problem when dealing with DDL changes using pt-online-schema-change. The tool implements the DDL changes as mentioned below. Please keep in mind that these are just to give an idea of how the tool works, as there would be more internal things going on in reality.

Let’s take an example ALTER for this case:



pt-online-schema-change steps for the above alter:

  1. Create a similar table _T1_new
  2. Modify the column c1 to BIGINT in the table _T1_new
  3. Define triggers on table T1 so that changes to data on the original table will be applied to _T1_new as well.
  4. Copy the data from table T1 to _T1_new.
  5. Swap the tables
  6. Drop triggers.

All looks good so far. Now let’s see why these steps create a problem, with a close look at Step #5 (Swap the tables).

Without foreign keys: Swapping of these tables is done as below, which looks nice.

  • Rename T1 —> T1_old
  • Rename _T1_new –> T1
  • If everything right, drop the table T1_old
  • Drop triggers on the new T1 table

With foreign keys: As I mentioned earlier, if there are any child tables with foreign keys to table T1, after renaming, they would automatically point to T1_old but not the new T1. Pt online schema change has to ensure the child table refers to a correct parent table by the end of this DDL change.

  • Rename T1 —> T1_old       =====? The child table refers to T1_old automatically.
  • Rename _T1_new —> T1

In this case, the foreign keys in the child table are still referring to the old table T1_old which don’t have the schema change in place. If you drop T1_old, child table CT1 ends up pointing to a table that doesn’t exist. That’s a very bad situation. Now let’s talk about how the tool handles this.

How does pt-online-schema-change handle this?

The tool comes up with an option named --alter-foreign-keys-method This option supports two values at a high level and below you can see what are those and how they will work.


With this value, it won’t swap as mentioned in the steps. Rather, it drops the old table and then renames the new table with the change in place.

  • Disable foreign key checks for the session (FOREIGN_KEY_CHECKS=0)
  • Drop the table T1_old
  • Rename the new table _T1_new –> T1

The good thing here is that it is quick, but the bad thing is that it’s not reliable. If something goes wrong with renaming, it ends up with the same problem of referring to an unexisting table.


This is the preferred approach for the reason it maintains the consistency of the schema and its relations. In this approach, before dropping the old table, it runs ALTER on all the child tables to drop existing FK and re-add new FK constraints that points to the columns from the new table (with the schema change in place).  Below sequence of bullet points explains the same.

  • Rename T1 –> T1_old
  • Rename _T1_new –> T1
  • ALTER on child table to adjust the foreign key so that it points to T1 rather T1_old.
ALTER TABLE child_table DROP FOREIGN KEY `fk_name`, ADD CONSTRAINT `_fk_name` FOREIGN KEY  (`child_table_column`) REFERENCES _T1_new (`parent_table_column`)

  • Drop the table T1_old
  • Drop triggers from the new T1 table.

I would like to mention that the current implementation to rebuild the child table can be improved by making use of the INPLACE ALTER which I hope would probably be available in upcoming releases. You can see more information about this in the existing bug report here. I will discuss in brief about the two other options available, which are derived based on the above two. Let’s have a quick look.

auto:  If this value is used, it leaves the decision up to the tool itself to choose from the two (drop_swap/rebuild_constraints) options available. If the number of rows in the child table is small, it uses rebuild_constraints; otherwise, it goes with the drop_swap approach. For this reason, this option should always be chosen carefully as it can end up with unexpected results when choosing drop_swap. Below is an example log snippet which explains this behavior:

# pt-online-schema-change --user=root --password=xxxxxxx --alter-foreign-keys-method=auto  --alter "MODIFY COLUMN header_id BIGINT unsigned NOT NULL AUTO_INCREMENT" D=DB1,t=T1 --execute
Copying `DB1`.`T1`:  75% 00:18 remain
2019-05-28T12:49:41 Copied rows OK.
2019-05-28T12:49:41 Max rows for the rebuild_constraints method: 5588
Determining the method to update foreign keys...
2019-05-28T12:49:41   `DB1`.`child_of_T1`: too many rows: 197076; must use drop_swap
2019-05-28T12:49:41 Drop-swapping tables...

none: If this value is used, it is similar to drop_swap but without swapping. In other words, it just drops the original table and leaves the child tables in a state which they point to a table that doesn’t exist. In this case, DBA’s have need to fix the leftover job.

Photo by Silas Köhler on Unsplash


Schema changes in MySQL for OpenStack Trove users

People using OpenStack Trove instances can hit a common issue in the MySQL world: how to perform schema change operations while minimizing the impact on the database server? Let’s explore the options that can allow online schema changes.


Schema changes in MySQL for OpenStack Trove usersWith MySQL 5.5, pt-online-schema-change from Percona Toolkit is your best option for large tables while regular ALTER TABLE statements are only acceptable for small tables. Also beware of metadata locks.

With MySQL 5.6, almost all types of schema changes can be done online. Metadata locks can also be an issue. pt-online-schema-change can still be worth using as it is also online on read replicas.

Regular ALTER TABLE with MySQL 5.5

If you are still using MySQL 5.5, almost all schema changes will require a table rebuild and MySQL will set a write lock. Therefore all writes to the table that gets modified will be blocked. As soon as the table gets large or if you cannot afford to have a maintenance window, using ALTER TABLE becomes tricky.

The only exception is that secondary indexes can be added or removed without rebuilding the whole table. The table is still write locked during the operation but it is much faster.

You can spot this ‘fast index creation’ process by looking at SHOW PROCESSLIST (see manage keys in the State field):

| Id | User | Host      | db     | Command | Time | State       | Info                                    | Rows_sent | Rows_examined | Rows_read |
|  1 | root | localhost | sbtest | Query   |    4 | manage keys | alter table sbtest2 add index idx_k (k) |         0 |             0 |         0 |

It is helpful for small or even medium tables, but it is not fast enough for large tables.

A common workaround when you have a master-slave configuration is to perform the schema change on the slave first, promote the slave and then perform the schema change on the old master. However replication is not only available if you are using Icehouse and even with Juno, replicas are read-only. So this workaround is not usable with Trove.


A good alternative is to use pt-online-schema-change. In a nutshell the tool creates a new table with the desired structure, creates triggers on the original table, and copies rows in chunks from the original table to the new table.

Nice features of the tool include being able to write to the original table while the schema change is being performed (hence the name of the tool), monitoring replication lag on slaves and automatic throttling to keep lag under a reasonable threshold.

Let’s assume you want to add a new field called ‘city’ in the ‘user’ table. Instead of running:

mysql> ALTER TABLE mydb.user ADD city VARCHAR(50) NOT NULL DEFAULT '';

you will run:

# pt-online-schema-change --progress=percentage,10 --alter="ADD city VARCHAR(50) NOT NULL DEFAULT ''" h=localhost,u=root,D=mydb,t=user --execute

All ALTER TABLE statements can be converted to an equivalent pt-online-schema-change command.

Is there any tradeoff? The first one is that pt-online-schema-change will be slower than a plain ALTER TABLE. This is expected as the tool has extra code to throttle itself if it adds too much load.

How much slower is it? As always it depends. On a lightly loaded server the difference will be minimal, for instance I measured 3mn24s for ALTER TABLE and 3mn30s for pt-online-schema-change on a test table. However on a heavily loaded server, it can be like 5x slower.

The second tradeoff is that pt-online-schema-change adds triggers on the original table to capture data change. So if you already have triggers, you can’t use the tool (this will be lifted in MySQL 5.7).

Metadata Locks

Metadata locks were introduced in MySQL 5.5 for better transaction isolation.

But one side effect is: if you start a schema change operation on a table while another transaction is reading or writing on the same table, ALTER TABLE will be stuck in the Waiting for metadata lock state.

Another negative side effect of metadata locks is that in some situations ALTER TABLE will also block all queries to the table that is being altered, even reads. In the worst cases, this may create a query pileup situation that will freeze the whole server. For more on this topic, have a look at this post.

What about pt-online-schema-change and metadata locks? It is of course hit by this issue/feature. However the good thing is that the timeout for metadata locks is set by the tool to 60s instead of the default 1 year. So after 60s, pt-online-schema-change will simply retry the operation that failed because of metadata locks.

MySQL 5.6: Online Schema Changes?

Metadata locks also exist with MySQL 5.6, the same issues as described above can then happen.

However the good news with MySQL 5.6 is that most schema changes can be done online. Now the question is: should you use pt-online-schema-change or a regular online ALTER TABLE statement?

Both have pros and cons:

  • ALTER TABLE is easy to use while being confident with pt-online-schema-change requires some time.
  • There is no way for ALTER TABLE to know if it’s overloading a replica while pt-online-schema-change monitors replication lag and throttles itself if needed.
  • ALTER TABLE is only online for the master, not for replicas while pt-online-schema-change is online for all servers.

The last point can be a bit confusing: why an online ALTER TABLE on the master wouldn’t be online on the slave as well? The truth is that unless you are using multi-threaded replication, writes on slaves are serialized. So while the ALTER TABLE is running, the slave will not process any other write, which is not much different from an offline ALTER TABLE.

So if you are running a single server (all Icehouse users for instance), ALTER TABLE is probably the right choice. But if you have read replicas and specifically if the application cannot tolerate replication lag, pt-online-schema-change is definitely a better choice.


Performing schema changes is becoming easier with newer versions of MySQL. However potential issues can be found with any version, so always be careful when you need to run ALTER TABLE.

The post Schema changes in MySQL for OpenStack Trove users appeared first on MySQL Performance Blog.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com