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 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.