pt-online-schema-change DELETE trigger fails when altering primary key
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Toolkit moved to https://jira.percona.com/projects/PT |
Fix Released
|
Critical
|
Daniel Nichter |
Bug Description
When attempting to change Primary Key + drop old PK column on a table , the DELETE trigger is wrong -- it tries to reference a column in the new table that is no longer there.
CREATE TABLE `t1` (
`c1` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`c2` bigint(20) unsigned DEFAULT NULL,
`c3` binary(20) DEFAULT NULL,
PRIMARY KEY (`c1`),
UNIQUE KEY `2bpk` (`c2`,`c3`),
KEY `c3` (`c3`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
pt-online-
michael@
*******
character_
collation_
Database Collation: latin1_swedish_ci
*******
character_
collation_
Database Collation: latin1_swedish_ci
*******
character_
collation_
Database Collation: latin1_swedish_ci
A workaround is to process this in two passes of pt-online-
pt-online-
pt-online-
This was using 2.1.4 .
Related branches
- Daniel Nichter: Approve
-
Diff: 175 lines (+88/-22)4 files modifiedbin/pt-online-schema-change (+18/-18)
t/pt-online-schema-change/bugs.t (+43/-1)
t/pt-online-schema-change/samples/del-trg-bug-1062324.sql (+24/-0)
t/pt-online-schema-change/sanity_checks.t (+3/-3)
Changed in percona-toolkit: | |
status: | New → Triaged |
tags: | added: pt-online-schema-change triggers |
Changed in percona-toolkit: | |
assignee: | nobody → Daniel Nichter (daniel-nichter) |
importance: | Undecided → Critical |
Changed in percona-toolkit: | |
status: | Triaged → In Progress |
tags: | added: percona-24252 |
summary: |
- pt-online-schema-change sets bad DELETE trigger when changing Primary - Key + pt-online-schema-change DELETE trigger fails when altering primary key |
Changed in percona-toolkit: | |
milestone: | none → 2.1.6 |
Changed in percona-toolkit: | |
status: | In Progress → Fix Committed |
Changed in percona-toolkit: | |
status: | Fix Committed → Fix Released |
For the record, the solution was to choose a key on the _new_ table for the DELETE trigger. I asked everyone at Percona and no one seemed to think this would have weird side-effects, nor did I, because we're still deleting rows based on a unique key.