pt-online-schema-change loses foreign keys

Bug #969726 reported by Baron Schwartz
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

Revision history for this message
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)
Revision history for this message
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.

Revision history for this message
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).

Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

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  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.