pt-online-schema-change loses foreign keys

Bug #969726 reported by Baron Schwartz on 2012-03-31
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit moved to https://jira.percona.com/projects/PT
Fix Released
High
Daniel Nichter
2.0
Won't Fix
Undecided
Unassigned
2.1
Fix Released
High
Daniel Nichter

Bug Description

The use of CREATE TABLE LIKE is causing foreign keys to disappear.

Before:

CREATE TABLE `film_actor` (
  `actor_id` smallint(5) unsigned NOT NULL,
  `film_id` smallint(5) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`,`film_id`),
  KEY `idx_fk_film_id` (`film_id`),
  CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`actor_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_film_actor_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

After:

CREATE TABLE `film_actor` (
  `actor_id` smallint(5) unsigned NOT NULL,
  `film_id` smallint(5) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `baron` int(11) DEFAULT NULL,
  PRIMARY KEY (`actor_id`,`film_id`),
  KEY `idx_fk_film_id` (`film_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I believe that the solution needs to be retrieving the SHOW CREATE and sending it back to the server again with a different name.

Related branches

Baron Schwartz (baron-xaprb) wrote :

I've pushed some code that solves this into the branch linked to this bug.

Changed in percona-toolkit:
status: Confirmed → In Progress
assignee: nobody → Daniel Nichter (daniel-nichter)
Daniel Nichter (daniel-nichter) wrote :

I added a test for your code and, in the process, found, tested, and fixed a drop_swap bug: if a child table was specified to which no other tables referenced, the tool would unset --alter-fk-method, but if --alter-fk-method drop_swap was specified, then the tool also unset --swap-tables and --drop-old-table but it did not re-set these when it unset --alter-fk-method, so the table was never modified.

Daniel Nichter (daniel-nichter) wrote :

This certainly affects 2.0 too, but we're not going to fix that version since 2.1 replaces it completely (there's no reason to use 2.0 instead of 2.1).

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-304

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers