Uploaded image for project: 'Percona Toolkit'
  1. Percona Toolkit
  2. PT-1582

Foreign key name on child table grows with multiple underscores

    XMLWordPrintable

    Details

      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 

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            stevenmalin Steven Malin
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:

                Smart Checklist