pt-online-schema-change makes bad DELETE trigger if PK is re-created with new columns

Reported by Daniel Nichter on 2013-01-23
12
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Percona Toolkit
High
Daniel Nichter

Bug Description

This is a continuation of bug 1062324, see comments #2 and onward.

Daniel Nichter (daniel-nichter) wrote :

From the first bug:

This bug is still reproducible in percona-toolkit version 2.1.8:

[root@ovaistariq-net msb_5_5_29]# pt-online-schema-change --version
pt-online-schema-change 2.1.8

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@ovaistariq-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_sandbox5529.sock,u=msandbox,p=msandbox --execute
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_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.

Note, how DELETE trigger is referencing the wrong column from the original table.

Daniel Nichter (daniel-nichter) wrote :

This is a new limitation, not a bug; here's why:

For the original bug 1062324, the columns in the new, re-created PK also exist in the original table, and there's a unique key on those cols in the new table, too. So this is like promoting a unique key to the PK, and the tool handles this.

This issue here is doing something fundamentally different: it's creating a new PK with a new column (_id). Furthermore, the original PK column (id) changes from unique to non-unique because no unique index is added to it in the new table.

So it's not the same bug; it's a question of whether or not we can and should handle this new issue. I'll have to think about how we might do this, i.e. if the magic is worth the effort given that there's a workaround.

tags: added: triggers
tags: added: percona-28581
Ovais Tariq (ovais-tariq) wrote :

Hi Daniel,

The workaround means doing two ALTERs which might not be feasible in certain situations with big tables as it will increase the alter time to twice of what it should be. Since the old PK column will essentially be unique and each record will have a unique value for each row that comes into the new table till pt-osc is running, so I do not see how this can be an issue, or may be I am missing something :)

Daniel Nichter (daniel-nichter) wrote :

Ok, I have implemented this, but as a tradeoff, I added a check for "DROP PRIMARY KEY" to --check-alter to help make the user take pause and hopefully have them --dry-run --print and test first.

Tested and working.

summary: - pt-online-schema bug with DELETE trigger and changing PK
+ pt-online-schema makes bad DELETE trigger if PK is re-created with new
+ columns
Changed in percona-toolkit:
status: In Progress → Fix Committed
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.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers