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:
- 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.
- 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.
The pt-online-schema-change tool also provides a third option, “auto.” When selecting “auto,” the tool makes a decision between “rebuild_constraints” and “drop_swap” based on the size of the child table involved in the alteration process. This allows for flexibility in choosing the appropriate option based on the specific scenario and the size of the affected child table.
Here, pt-online-schema-change knows the time taken for the row copy operation from the old table to the new table. It can estimate if the child table can be altered within –chunk-time (that’s considered fast). If child table ALTERs are fast, pt-online-schema-change uses rebuild_constraints; otherwise, use the drop_swap approach.
To error is human, and hence I could be wrong. Luckily the Percona tools are open source. I encourage you to review the source at the pt-online-schema-change’s git. Following is the code block that describes the “auto” behavior and then calls the determine_alter_fk_method.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | # 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.
