Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Foreign key name on child table grows with multiple underscores

Description

I have a table named 'pets' with a foreign key referencing an 'owner' table (pets.owner_id => owner.id, constraint name 'owner_fk').  I want to create a new table called 'pet_type' with an 'id' field that my 'pets' table should also reference.  So, after creating my 'pet_type' table, I need to alter my 'pets' table and add a new field (pet_type_id) then add the foreign key so I now have the relationship pets.pet_type_id => pet_type.id.

When I execute the first ALTER to add the field, my first constraint name (owner_fk) gets changed to 'owner_fk'.  When I execute the second ALTER to create the relationship, the fk name gets another underscore: '_owner_fk'.

The '--alter-foreign-keys-method' flag has no impact on this behavior; it only affects how child table foreign keys are handled when we're working on the parent table.  In this instance, we're working on the child table.

System info:
{{$ uname -svr }}
Linux 4.15.0-24-generic #26-Ubuntu SMP Wed Jun 13 08:44:47 UTC 2018
{{$ pt-online-schema-change --version }}
pt-online-schema-change 3.0.11
{{ $ mysql --version }}
mysql  Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using  EditLine wrapper

Here is my table prior to running pt-online-schema-change:

{{CREATE TABLE `pets` ( }}
{{  `name` varchar(30) NOT NULL, }}
{{  `owner_id` bigint(20) unsigned NOT NULL, }}
{{  PRIMARY KEY (`name`,`owner_id`), }}
{{  KEY `owner_fk` (`owner_id`), }}
{{  CONSTRAINT `owner_fk` FOREIGN KEY (`owner_id`) REFERENCES `owners` (`id`) }}
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Here is the table after running pt-online-schema-change:

{{CREATE TABLE `pets` ( }}
{{  `name` varchar(30) NOT NULL, }}
{{  `owner_id` bigint(20) unsigned NOT NULL, }}
{{  `pet_type` bigint(20) unsigned NOT NULL DEFAULT '1', }}
{{  PRIMARY KEY (`name`,`owner_id`), }}
{{  KEY `owner_fk` (`owner_id`), }}
{{  KEY `pet_type_fk` (`pet_type`), }}
{{  CONSTRAINT `__owner_fk` FOREIGN KEY (`owner_id`) REFERENCES `owners` (`id`), }}
{{  CONSTRAINT `pet_type_fk` FOREIGN KEY (`pet_type`) REFERENCES `pet_type` (`id`) }}
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

You can see that owner_fk grew by two underscores.

Here are the commands I'm using.  

/usr/bin/pt-online-schema-change --execute --alter-foreign-keys-method rebuild_constraints --alter 'ADD COLUMN `pet_type` bigint(20) unsigned NOT NULL DEFAULT 1, ADD KEY `pet_type_fk` (`
pet_type`)' D=mydb,t=pets,h=localhost,P=3306,u=root,p=xxxxx{{}}

/usr/bin/pt-online-schema-change --execute --alter-foreign-keys-method rebuild_constraints --alter 'ADD CONSTRAINT `pet_type_fk` FOREIGN KEY (`pet_type`) REFERENCES `pet_type` (`id`)' D=
mydb,t=pets,h=localhost,P=3306,u=root,p=xxxxx

I've attached the debug output below.

I know this is a pretty 

Environment

None

Attachments

2
  • 12 Jul 2018, 02:47 PM
  • 12 Jul 2018, 02:47 PM

Smart Checklist

Activity

Show:

Lalit Choudhary August 17, 2018 at 1:39 PM
Edited

Hi Steven,

Thank you for the report.

I'm able to reproduce describe behavior with the latest version of Percona Toolkit (3.0.11).

Steps to reproduce:

 

create table owners (id bigint(20) unsigned NOT NULL primary key) CHARSET=latin1; CREATE TABLE `pets` ( `name` varchar(30) NOT NULL, `owner_id` bigint(20) unsigned NOT NULL, PRIMARY KEY (`name`,`owner_id`), KEY `owner_fk` (`owner_id`), CONSTRAINT `owner_fk` FOREIGN KEY (`owner_id`) REFERENCES `owners` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;   $ PTDEBUG=1 pt-online-schema-change --execute --alter-foreign-keys-method rebuild_constraints --alter 'ADD COLUMN `pet_type` bigint(20) unsigned NOT NULL DEFAULT 1, ADD KEY `pet_type_fk` (`pet_type`)' D=test,t=pets,u=msandbox,p=msandbox --socket=/tmp/mysql_sandbox5723.sock > pt-add_column.txt 2>&1 mysql [localhost] {msandbox} (test) > show create table pets\G *************************** 1. row *************************** Table: pets Create Table: CREATE TABLE `pets` ( `name` varchar(30) NOT NULL, `owner_id` bigint(20) unsigned NOT NULL, `pet_type` bigint(20) unsigned NOT NULL DEFAULT '1', PRIMARY KEY (`name`,`owner_id`), KEY `owner_fk` (`owner_id`), KEY `pet_type_fk` (`pet_type`), CONSTRAINT `_owner_fk` FOREIGN KEY (`owner_id`) REFERENCES `owners` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql [localhost] {msandbox} (test) > -- mysql [localhost] {msandbox} (test) > create table pet_type(`id` bigint(20) unsigned primary key DEFAULT '1')ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.04 sec) $ PTDEBUG=1 pt-online-schema-change --execute --alter-foreign-keys-method rebuild_constraints --alter 'ADD *CONSTRAINT `pet_type_fk` FOREIGN KEY (`pet_type`) REFERENCES `pet_type`(`id`)' *D=test,t=pets,u=msandbox,p=msandbox --socket=/tmp/mysql_sandbox5723.sock > pt-add_contraint.txt 2>&1 mysql [localhost] {msandbox} (test) > show create table pets\G *************************** 1. row *************************** Table: pets Create Table: CREATE TABLE `pets` ( `name` varchar(30) NOT NULL, `owner_id` bigint(20) unsigned NOT NULL, `pet_type` bigint(20) unsigned NOT NULL DEFAULT '1', PRIMARY KEY (`name`,`owner_id`), KEY `owner_fk` (`owner_id`), KEY `pet_type_fk` (`pet_type`), CONSTRAINT `__owner_fk` FOREIGN KEY (`owner_id`) REFERENCES `owners` (`id`), CONSTRAINT `pet_type_fk` FOREIGN KEY (`pet_type`) REFERENCES `pet_type` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.01 sec) mysql [localhost] {msandbox} (test) >

 

 

Steven Malin July 12, 2018 at 2:58 PM

Accidentally hit 'create' without finishing my thoughts up on this.

I know this is a pretty complex problem.  What I'm thinking though is that maybe the clone table can be created without the foreign key, since the original table can act as a 'filter' for ensuring the keys are correct.  When the swap occurs, the foreign key can be dropped on the old table and recreated on the new one. This might break the whole 'online' paradigm a bit, but it would preserve the constraint name.

Keeping the constraint names consistent is important for use with tools like skeema.

Details

Assignee

Reporter

Priority

Components

Affects versions

Labels

Smart Checklist

Created July 12, 2018 at 2:56 PM
Updated March 4, 2024 at 4:31 PM

Flag notifications