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

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.

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.

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 :)

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.

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-350