DDL Queries on Foreign Key Columns in MySQL/PXC

DDL Queries on Foreign Key ColumnsRecently, I received a support request where the customer wanted to convert an INTEGER column to BIGINT on two tables. These tables are related by a foreign key, and it is a 3 node PXC cluster. These tables are 20GB and 82 GB in size and DDL’s on such tables in a production environment is always a challenge. We have options like direct ALTER or using pt-online-schema-change to get this done, but this is a very special case where none of them will be able to do this DDL. To demonstrate why this is so, please follow the table schema and the example below about DDL queries on foreign key columns. In the end, I will discuss an easy workaround, too.

Please take a look at table schema before reading further. In the below schema, the product_catalog_id column from the product_details table refers to catalog_id column from the product_catalog table. Both these fields are INT(11) and the customer wanted to convert them to BIGINT:

Changing a column from INT to BIGINT is an ONLINE ALTER. But in this case, it is not an ONLINE ALTER as the primary key is defined in this column. I started with a direct ALTER and later with pt-online-schema-change. Let’s have a look at how these tools react to this DDL change.

Direct Alter:

Considering the tables are close to 100GB in size together, a direct ALTER is not a good choice especially with a PXC cluster, and also on a standard deployment, it would block queries on metadata lock. But let’s see how the direct ALTER reacts here.  I will first alter the child table and then the parent table.

It failed with Error 1832. Let’s try changing the column in the parent table first.

This time, it is Error 1833. Both these errors state that it cannot change the column involved in a foreign key. The reason is that a foreign key is defined only between two identical columns. Changing any of those column data types would result in an error.

pt-online-schema-change:

It is always recommended to use pt-online-schema-change for DDL’s in PXC cluster deployments, provided additional disk space is available. You can refer to this blog post to know when to use this tool. pt-osc works by creating a new table with the required change in place, and copies data to the new table. The challenge comes in when there are child tables referring some column in this parent table. Presence of foreign keys complicates the job of pt-osc.

There are two ways the tool handles the foreign key constraints on the child table when the parent table is renamed as part of pt-osc. Below explains each of those cases.

alter-foreign-keys-method=auto

It failed while renaming the table _product_details_new to product_details. The reason is that it would have BIGINT datatype in child table and INT datatype in the parent table if the rename succeeds – which is not allowed in MySQL. Foreign keys between different data types are not allowed.

alter-foreign-keys-method=rebuild_constraints

In this case, the child table is rebuilt to point to the correct parent table using an ALTER, but it failed again for the same reason. So, none of the options like direct ALTER nor pt-online-schema-change are working for this particular change.

DDL Queries on Foreign Key Columns Workaround

Even disabling foreign key checks don’t work here, as that trick only works when it comes to data, not the schema changes. This has been reported independently under Percona Server as well as in PXC branches in JIRA. You can see more information about these here and here. There is one easy yet simple workaround that I would suggest. That is to drop the foreign key constraint on the child table, run the DDL on both the child and the parent tables, and finally redefine the foreign key constraint.

As you can see, the integrity constraint is compromised for the duration for this workaround. Be sure to keep the server in read-only mode and not allow any changes to these tables, as it might lead to inconsistent data between the parent and child tables.

Summary:

Foreign keys can only be defined and operated between two identical columns. Due to this constraint, DDL queries on columns involved in foreign keys are still a problem in MySQL/PXC, especially when the tables are huge. This workaround, with little downtime for writes, is the only quick way to get this done without spending time on complex logic building and implementation that involves changes on both the DB and the application.

Photo by Vanessa Bucceri on Unsplash

Share this post

Leave a Reply