pt-online-schema-change DELETE trigger fails when altering primary key

Reported by Michael Coburn on 2012-10-05
10
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Percona Toolkit
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-schema-change --execute --alter "drop key 2bpk, drop key c3, drop primary key, drop c1, add primary key (c2, c3(4)), add key (c3(4))"

michael@ubuntu1204:/data/24252$ mysql test -e "show triggers from test\G"
*************************** 1. row ***************************
             Trigger: pt_osc_test_t1_ins
               Event: INSERT
               Table: t1
           Statement: REPLACE INTO `test`.`_t1_new` (`c2`, `c3`) VALUES (NEW.`c2`, NEW.`c3`)
              Timing: AFTER
             Created: NULL
            sql_mode: NO_AUTO_VALUE_ON_ZERO
             Definer: root@localhost
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
             Trigger: pt_osc_test_t1_upd
               Event: UPDATE
               Table: t1
           Statement: REPLACE INTO `test`.`_t1_new` (`c2`, `c3`) VALUES (NEW.`c2`, NEW.`c3`)
              Timing: AFTER
             Created: NULL
            sql_mode: NO_AUTO_VALUE_ON_ZERO
             Definer: root@localhost
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
*************************** 3. row ***************************
             Trigger: pt_osc_test_t1_del
               Event: DELETE
               Table: t1
           Statement: DELETE IGNORE FROM `test`.`_t1_new` WHERE `test`.`_t1_new`.`c1` <=> OLD.`c1`
              Timing: AFTER
             Created: NULL
            sql_mode: NO_AUTO_VALUE_ON_ZERO
             Definer: root@localhost
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci

A workaround is to process this in two passes of pt-online-schema-change (change PK, drop old PK column). Note addition of index on c1 as it is still AUTO_INCREMENT:

pt-online-schema-change --execute --alter "drop key 2bpk, drop key c3, drop primary key, add primary key (c2, c3(4)), add key (c3(4)), add key (c1)"
pt-online-schema-change --execute --alter "drop key c1, drop c1"

This was using 2.1.4 .

Brian Fraser (fraserbn) on 2012-10-08
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
Daniel Nichter (daniel-nichter) wrote :

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.

Brian Fraser (fraserbn) on 2012-11-16
Changed in percona-toolkit:
status: Fix Committed → Fix Released
Ovais Tariq (ovais-tariq) wrote :

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.

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

The workaround currently is to do the alter in two steps:

First add the new column, and optionally add an index if its an auto_increment column:
[root@ovaistariq-net msb_5_5_29]# pt-online-schema-change --alter "add column _id int unsigned not null auto_increment FIRST, add index (_id)" 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...
Created triggers OK.
Copying approximately 2 rows...
Copied rows OK.
Swapping tables...
Swapped original and new tables OK.
Dropping old table...
Dropped old table `test`.`_test_old` OK.
Dropping triggers...
Dropped triggers OK.
Successfully altered `test`.`test`.

Once the above finishes, drop the old primary key and the index added above, and then specify the new primary key:
[root@ovaistariq-net msb_5_5_29]# pt-online-schema-change --alter "drop primary key, add primary key (_id), drop index _id" 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...
Created triggers OK.
Copying approximately 2 rows...
Copied rows OK.
Swapping tables...
Swapped original and new tables OK.
Dropping old table...
Dropped old table `test`.`_test_old` OK.
Dropping triggers...
Dropped triggers OK.
Successfully altered `test`.`test`.

Ovais Tariq (ovais-tariq) wrote :

This was not fixed in 2.1.6 as well, see below.

[root@ovaistariq-net bin]# ./pt-online-schema-change --version
pt-online-schema-change 2.1.6

[root@ovaistariq-net bin]# ./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 ./pt-online-schema-change line 9414.

Daniel Nichter (daniel-nichter) wrote :

I opened a new bug for this: bug 1103672 (since we avoid changing bugs once they've been "released").

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers