DDL Queries on Foreign Key Columns in MySQL/PXC

Recently, 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:

mysql> show create table product_catalog \G
*************************** 1. row ***************************
       Table: product_catalog
Create Table: CREATE TABLE `product_catalog` (
  `catalog_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  .....
  PRIMARY KEY (`catalog_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)
 
mysql> show create table product_details \G
*************************** 1. row ***************************
       Table: product_details
Create Table: CREATE TABLE `product_details` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_catalog_id` int(11) unsigned NOT NULL,
  .....
  PRIMARY KEY (`product_id`),
  KEY `fk_audit_detail_audit_header_idx` (`product_catalog_id`),
  CONSTRAINT `product_catalog_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)

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.

mysql> ALTER TABLE product_details MODIfY COLUMN product_catalog_id BIGINT UNSIGNED NOT NULL  ;
ERROR 1832 (HY000): Cannot change column 'product_catalog_id': used in a foreign key constraint 'product_details_ibfk_1'

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

mysql> ALTER TABLE product_catalog MODIfY COLUMN catalog_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT  ;
ERROR 1833 (HY000): Cannot change column 'catalog_id': used in a foreign key constraint 'product_details_ibfk_1' of table 'DB255525.product_details'

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 tothis 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

# pt-online-schema-change --user=root --password=root --host=172.23.0.2 --alter-foreign-keys-method=auto --alter "MODIFY COLUMN product_catalog_id BIGINT UNSIGNED NOT NULL" D=DB1,t=product_details --execute
....
....
2019-05-28T09:19:16 Dropping new table...
2019-05-28T09:19:16 Dropped new table OK.
`DB255525`.`product_details` was not altered.
Error altering new table `DB1`.`_product_details_new`: DBD::mysql::db do failed: Cannot change column 'product_catalog_id': used in a foreign key constraint '_product_details_ibfk_1' [for Statement "ALTER TABLE `DB255525`.`_product_details_new` MODIFY COLUMN product_catalog_id BIGINT UNSIGNED NOT NULL"] at /usr/bin/pt-online-schema-change line 9271.

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

# pt-online-schema-change --user=root --password=root --host=172.23.0.2 --alter-foreign-keys-method=rebuild_constraints --alter "MODIFY COLUMN product_catalog_id BIGINT UNSIGNED NOT NULL" D=DB1,t=product_details --execute
.....
.....
Created new table DB1._product_details_new OK.
Altering new table...
2019-05-28T09:27:24 Dropping new table...
2019-05-28T09:27:25 Dropped new table OK.
`DB255525`.`product_details` was not altered.
Error altering new table `DB1`.`_product_details_new`: DBD::mysql::db do failed: Cannot change column 'product_catalog_id': used in a foreign key constraint '_product_details_ibfk_1' [for Statement "ALTER TABLE `DB255525`.`_product_details_new` MODIFY COLUMN product_catalog_id BIGINT UNSIGNED NOT NULL"] at /usr/bin/pt-online-schema-change line 9271.

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 thesehere andhere. 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.

Step #1: 
mysql> ALTER TABLE product_details DROP FOREIGN KEY product_details_ibfk_1 ;
Query OK, 0 rows affected (0.95 sec)
Records: 0 Duplicates: 0 Warnings: 0
 
Step #2: 
# pt-online-schema-change --user=root --password=XXXXXXX --host=172.23.0.2 --alter "MODIFY COLUMN product_catalog_id BIGINT UNSIGNED NOT NULL" D=DB1,t=product_details --execute
......
Successfully altered `DB1`.`product_details`.
 
Step #3:  
# pt-online-schema-change --user=root --password=XXXXXXX --host=172.23.0.2 --alter "MODIFY COLUMN catalog_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT" D=DB1,t=product_catalog --execute
.......
Successfully altered `DB1`.`product_catalog`.
 
Step #4: 
mysql> ALTER TABLE product_details ADD FOREIGN KEY product_details_ibfk_1 (product_catalog_id) REFERENCES `product_catalog`(`catalog_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ;
Query OK, 0 rows affected (1.55 sec)
Records: 0 Duplicates: 0 Warnings: 0

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