pt-online-schema-change fails with duplicate key in table for self-referencing FK
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Toolkit moved to https://jira.percona.com/projects/PT |
Fix Released
|
Medium
|
Carlos Salguero |
Bug Description
When altering a table with a self-refencing foreign key, pt-osc fails with a duplicate key in table.
pt-osc version = 2.2.19+
MySQL version = 5.6.31
Steps to reproduce.
# Setup database and test tables with self referencing FK
drop database if exists fk_test;
create database fk_test;
use fk_test;
CREATE TABLE `person` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `test_table` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`refId` bigint(20) DEFAULT NULL,
`person` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_person` (`person`),
KEY `fk_refId` (`refId`),
CONSTRAINT `fk_person` FOREIGN KEY (`person`) REFERENCES `person` (`id`),
CONSTRAINT `fk_refId` FOREIGN KEY (`refId`) REFERENCES `test_table` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# Add a new column
./liquibase/
No slaves found. See --recursion-method if host C02MM3DMFD57 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
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_
Child tables:
`fk_test`
Will automatically choose the method to update foreign keys.
Altering `fk_test`
Creating new table...
Created new table fk_test.
Altering new table...
Altered `fk_test`
2016-10-11T17:18:50 Creating triggers...
2016-10-11T17:18:50 Created triggers OK.
2016-10-11T17:18:50 Copying approximately 1 rows...
2016-10-11T17:18:50 Copied rows OK.
2016-10-11T17:18:50 Max rows for the rebuild_constraints method: 4000
Determining the method to update foreign keys...
2016-10-11T17:18:50 `fk_test`
2016-10-11T17:18:50 Analyzing new table...
2016-10-11T17:18:50 Swapping tables...
2016-10-11T17:18:50 Swapped original and new tables OK.
2016-10-11T17:18:50 Rebuilding foreign key constraints...
2016-10-11T17:18:50 Dropping triggers...
2016-10-11T17:18:50 Dropped triggers OK.
Altered `fk_test`
Error updating foreign key constraints: 2016-10-11T17:18:50 DBD::mysql::db do failed: Can't write; duplicate key in table '#sql-7db0_9' [for Statement "ALTER TABLE `fk_test`
Changed in percona-toolkit: | |
assignee: | nobody → Carlos Salguero (carlos-salguero) |
milestone: | none → 2.2.20 |
importance: | Undecided → Medium |
Changed in percona-toolkit: | |
milestone: | 2.2.20 → 2.2.21 |
Changed in percona-toolkit: | |
status: | Confirmed → Fix Released |
Changed in percona-toolkit: | |
milestone: | 2.2.21 → 3.0.1 |
Suggested fix: https:/ /github. com/percona/ percona- toolkit/ pull/137