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

Bug #1062324 reported by Michael Coburn
10
This bug affects 2 people
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-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 .

Related branches

Brian Fraser (fraserbn)
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
Revision history for this message
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)
Changed in percona-toolkit:
status: Fix Committed → Fix Released
Revision history for this message
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
Revision history for this message
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`.

Revision history for this message
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.

Revision history for this message
Daniel Nichter (daniel-nichter) wrote :

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

Revision history for this message
Tegan Snyder (tsnyder) wrote :

While this error message is useful when performing most operations. There are times when someone might want to add an auto_increment primary ID column to a table that doesn't currently have one. Since the trigger would not have a identifier to key a DELETE off of this message is displayed.

It would be nice to allow these types of operations:

pt-online-schema-change --alter "ADD COLUMN _id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY" u=user,p='pass',D=mydatabase,t=mytable --no-drop-new-table --no-swap-tables --no-check-alter --dry-run

Then it would be up to the user to rename and drop tables manually. Thoughts?

Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

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

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

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.