In this blog post, we’ll look at how to get rid of the unused Foreign Key (FK) constraint and/or related columns/keys with the help of pt-online-schema-change and the power of its plugins.
Before we proceed, here is a useful blog post written by Peter Zaitsev on Hijacking Innodb Foreign Keys.
If you are trying to get rid of an unused foreign key (FK) constraint and related columns from versions older than MySQL 5.6, or tables that cannot be executed with ALTER TABLE ... ALGORITHM=INPLACE because of limitations mentioned here (specifically, tables with 5.5 TIMESTAMP formats), you can use pt-online-schema-change.
For DROP FOREIGN KEY constraint_name with pt-online-schema-change requires specifying _constraint_name rather than the real constraint_name. This is due to a limitation in MySQL: pt-online-schema-change adds a leading underscore to foreign key constraint names when creating the new table. Here’s is a simple example of one such case:
|
1 2 3 4 5 6 7 |
CREATE TABLE `test3` ( `Id` int(11) NOT NULL DEFAULT '0', `Firstname` varchar(32) DEFAULT NULL, `City` varchar(32) DEFAULT NULL, PRIMARY KEY (`Id`), CONSTRAINT `FKID` FOREIGN KEY (`Id`) REFERENCES `test4` (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
To drop the constraint, we are supposed to add an underscore prior to constraint_name FKID:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
[root@siddhant ~]# pt-online-schema-change --user=root --execute --set-vars=foreign_key_checks=0 --alter-foreign-keys-method=rebuild_constraints --alter="DROP FOREIGN KEY _FKID" D=apps02,t=test3 --socket=/tmp/mysql-master5520.sock Operation, tries, wait: analyze_table, 10, 1 copy_rows, 10, 0.25 ……...Altering `apps02`.`test3`... Creating new table... Created new table apps02._test3_new OK. Altering new table….... ……. 2017-02-11T12:45:12 Dropped old table `apps02`.`_test3_old` OK. 2017-02-11T12:45:12 Dropping triggers... 2017-02-11T12:45:12 Dropped triggers OK. Successfully altered `apps02`.`test3`. |
Below is one case where if, for some reason, you already have an FK constraint with an underscore the above method of adding an additional underscore to already underscored _FK will fail with an error while dropping it:
|
1 |
Error altering new table `apps02`.`_test3_new`: DBD::mysql::db do failed: Error on rename of './apps02/_test3_new' to './apps02/#sql2-697-19' (errno: 152) [for Statement "ALTER TABLE `apps02`.`_test3_new` DROP FOREIGN KEY ___FKID"] at /usr/bin/pt-online-schema-change line 9069. |
In such cases, we will have to make use of the --plugin option used along with a file that calls the pt_online_schema_change_plugin class and a hook after_alter_new_table to drop the FK constraint. For example, a table with the FK constraint with an underscore is:
|
1 2 3 4 5 6 7 |
CREATE TABLE `test` ( `Id` int(11) NOT NULL DEFAULT '0', `Firstname` varchar(32) DEFAULT NULL, `City` varchar(32) DEFAULT NULL, PRIMARY KEY (`Id`), CONSTRAINT `<strong>___fkId</strong>` FOREIGN KEY (`Id`) REFERENCES `test2` (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
Here we have a table with foreign key ___fkid using three underscores. Our plugin for dropping the constraint should be as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
[root@siddhant ~]# cat ptosc_plugin_drop_fk.pl package pt_online_schema_change_plugin; use strict; sub new { my ($class, %args) = @_; my $self = { %args }; return bless $self, $class; } sub after_alter_new_table { my ($self, %args) = @_; my $new_tbl = $args{new_tbl}; my $dbh = $self->{cxn}->dbh; my $sth = $dbh->prepare("ALTER TABLE $new_tbl->{name} DROP FOREIGN KEY __fkId"); $sth->execute(); } 1; |
NOTE: DROP FOREIGN KEY CONSTRAINT in the plugin has one underscore less than original foreign key constraint, __fkId vs. ___fkId. Also, the alter statement will be NOOP alter (i.e., --alter ="ENGINE=INNODB").
Here is the pt-online-schema-change execution example with the plugin.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
[root@siddhant ~]# pt-online-schema-change --user=root --execute --set-vars=foreign_key_checks=0 --alter-foreign-keys-method=rebuild_constraints --alter="ENGINE=INNODB" --plugin=/root/ptosc_plugin_drop_fk.pl D=apps01,t=test --socket=/tmp/mysql-master5520.sock Created plugin from /root/ptosc_plugin_drop_fk.pl. 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 `apps01`.`test`... Creating new table... Created new table apps01._test_new OK. Altering new table... Altered `apps01`.`_test_new` OK. 2017-02-11T11:26:14 Creating triggers... 2017-02-11T11:26:14 Created triggers OK. 2017-02-11T11:26:14 Copied rows OK. 2017-02-11T11:26:14 Swapping tables... 2017-02-11T11:26:14 Swapped original and new tables OK. 2017-02-11T11:26:14 Dropping old table... 2017-02-11T11:26:14 Dropped old table `apps01`.`_test_old` OK. 2017-02-11T11:26:14 Dropping triggers... 2017-02-11T11:26:14 Dropped triggers OK. Successfully altered `apps01`.`test`. |
Also for knowing proper FK constraint name to be added in PT-OSC command ,
Execute the PT-OSC command with PTDEBUG=1 and –dry-run option, and look for FK constraint name which the PT-OSC is trying to create table with and used that FK name in your final PT-OSC command .