pt-online-schema-change makes bad DELETE trigger if PK is re-created with new columns
Bug #1103672 reported by
Daniel Nichter
This bug affects 2 people
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Toolkit moved to https://jira.percona.com/projects/PT |
Fix Released
|
High
|
Daniel Nichter |
Bug Description
This is a continuation of bug 1062324, see comments #2 and onward.
Related branches
lp:~percona-toolkit-dev/percona-toolkit/pk-del-bug-1103672-encore
- Daniel Nichter: Approve
-
Diff: 322 lines (+218/-16)4 files modifiedbin/pt-online-schema-change (+104/-15)
t/pt-online-schema-change/bugs.t (+29/-1)
t/pt-online-schema-change/check_alter.t (+65/-0)
t/pt-online-schema-change/samples/del-trg-bug-1103672.sql (+20/-0)
summary: |
- pt-online-schema makes bad DELETE trigger if PK is re-created with new - columns + pt-online-schema-change makes bad DELETE trigger if PK is re-created + with new columns |
Changed in percona-toolkit: | |
status: | Fix Committed → Fix Released |
To post a comment you must log in.
From the first bug:
This bug is still reproducible in percona-toolkit version 2.1.8:
[root@ovaistari q-net msb_5_5_29]# pt-online- schema- change --version schema- change 2.1.8
pt-online-
mysql [localhost] {msandbox} (test) > show create table test\G ******* ******* ****** 1. row ******* ******* ******* ******
*******
Table: test
Create Table: CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL,
`x` char(3) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
When trying to alter the above table to add a new primary key column, pt-osc fails as below:
[root@ovaistari q-net msb_5_5_29]# pt-online- schema- change --alter "drop primary key, add column _id int unsigned not null primary key auto_increment FIRST" D=test, t=test, S=/tmp/ mysql_sandbox55 29.sock, u=msandbox, p=msandbox --execute test_test_ del` AFTER DELETE ON `test`.`test` FOR EACH ROW DELETE IGNORE FROM `test`.`_test_new` WHERE `test`. `_test_ new`.`_ id` <=> OLD.`_id`"] at /usr/bin/ pt-online- schema- change line 9613.
Altering `test`.`test`...
Creating new table...
Created new table test._test_new OK.
Altering new table...
Altered `test`.`_test_new` OK.
Creating triggers...
Dropping triggers...
Dropped triggers OK.
Dropping new table...
Dropped new table OK.
`test`.`test` was not altered.
Error creating triggers: DBD::mysql::db do failed: Unknown column '_id' in 'OLD' [for Statement "CREATE TRIGGER `pt_osc_
Note, how DELETE trigger is referencing the wrong column from the original table.