pt-online-schema-change在对表进行表结构变更时，会创建三个触发器。
如下文测试案例中的t2表，表结构如下：
mysql> show create table t2\G*************************** 1. row *************************** Table: t2Create Table: CREATE TABLE `t2` ( `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.07 sec)
只有一个自增列字段id。
创建的触发器如下：
CREATE TRIGGER `pt_osc_test_t2_del` AFTER DELETE ON `test`.`t2` FOR EACH ROW DELETE IGNORE FROM `test`.`__t2_new` WHERE `test`.`__t2_new`.`id` <=> OLD.`id` CREATE TRIGGER `pt_osc_test_t2_upd` AFTER UPDATE ON `test`.`t2` FOR EACH ROW REPLACE INTO `test`.`__t2_new` (`id`) VALUES (NEW.`id`)CREATE TRIGGER `pt_osc_test_t2_ins` AFTER INSERT ON `test`.`t2` FOR EACH ROW REPLACE INTO `test`.`__t2_new` (`id`) VALUES (NEW.`id`)
DELETE触发器和INSERT触发器逻辑上没有任何问题。
但对于UPDATE触发器来说，如果某条记录已经拷贝到中间表中，此时，有针对该记录的UPDATE操作，且修改的是主键，此时，针对中间表触发的“REPLACE INTO `test`.`__t2_new` (`id`) VALUES (NEW.`id`)”操作只会插入一条新的记录，而不会删除原来的记录。
下面重现该场景
创建触发器构造测试数据
delimiter //create procedure p1()begin declare v1 int default 1; set autocommit=0; while v1 <=10000000 do insert into test.t2(id) values(null); set v1=v1+1; if v1%1000 =0 then commit; end if; end while;end //delimiter ;call p1;
此时，会生成1千万的数据
mysql> select count(*),min(id),max(id) from t2;+----------+---------+----------+| count(*) | min(id) | max(id) |+----------+---------+----------+| 10000000 | 1 | 10000000 |+----------+---------+----------+1 row in set (4.29 sec)
利用pt-online-schema-change对t2表添加一列
# pt-online-schema-change --execute --alter "ADD COLUMN c1 DATETIME" --print D=test,t=t2
No slaves found. See --recursion-method if host localhost.localdomain has slaves.Not checking slave lag because no slaves were found and --check-slave-lag was not specified.Operation, tries, wait: analyze_table, 10, 1 copy_rows, 10, 0.25 create_triggers, 10, 1 drop_triggers, 10, 1 swap_tables, 10, 1 update_foreign_keys, 10, 1Altering `test`.`t2`...Creating new table...CREATE TABLE `test`.`___t2_new` ( `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8Created new table test.___t2_new OK.Altering new table...ALTER TABLE `test`.`___t2_new` ADD COLUMN c1 DATETIMEAltered `test`.`___t2_new` OK.2016-10-23T20:24:13 Creating triggers...CREATE TRIGGER `pt_osc_test_t2_del` AFTER DELETE ON `test`.`t2` FOR EACH ROW DELETE IGNORE FROM `test`.`___t2_new` WHERE `test`.`___t2_new`.`id` <=> OLD.`id`CREATE TRIGGER `pt_osc_test_t2_upd` AFTER UPDATE ON `test`.`t2` FOR EACH ROW REPLACE INTO `test`.`___t2_new` (`id`) VALUES (NEW.`id`)CREATE TRIGGER `pt_osc_test_t2_ins` AFTER INSERT ON `test`.`t2` FOR EACH ROW REPLACE INTO `test`.`___t2_new` (`id`) VALUES (NEW.`id`)2016-10-23T20:24:13 Created triggers OK.2016-10-23T20:24:13 Copying approximately 9429750 rows...INSERT LOW_PRIORITY IGNORE INTO `test`.`___t2_new` (`id`) SELECT `id` FROM `test`.`t2` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 2456 copy nibble*/SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `test`.`t2` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/ Copying `test`.`t2`: 29% 01:12 remainCopying `test`.`t2`: 52% 00:54 remainCopying `test`.`t2`: 76% 00:27 remain2016-10-23T20:26:22 Copied rows OK.2016-10-23T20:26:22 Analyzing new table...2016-10-23T20:26:23 Swapping tables...RENAME TABLE `test`.`t2` TO `test`.`_t2_old`, `test`.`___t2_new` TO `test`.`t2`2016-10-23T20:26:24 Swapped original and new tables OK.2016-10-23T20:26:24 Dropping old table...DROP TABLE IF EXISTS `test`.`_t2_old`2016-10-23T20:26:24 Dropped old table `test`.`_t2_old` OK.2016-10-23T20:26:24 Dropping triggers...DROP TRIGGER IF EXISTS `test`.`pt_osc_test_t2_del`;DROP TRIGGER IF EXISTS `test`.`pt_osc_test_t2_upd`;DROP TRIGGER IF EXISTS `test`.`pt_osc_test_t2_ins`;2016-10-23T20:26:24 Dropped triggers OK.Successfully altered `test`.`t2`.
当输出到上述红色信息时，打开另外一个终端窗口，执行如下命令
mysql -e 'update test.t2 set id=-1 where id=1' mysql -e 'update test.t2 set id=-2 where id=2' mysql -e 'update test.t2 set id=-3 where id=3' mysql -e 'update test.t2 set id=-4 where id=4' mysql -e 'update test.t2 set id=-5 where id=5' mysql -e 'update test.t2 set id=-6 where id=6' mysql -e 'update test.t2 set id=-7 where id=7' mysql -e 'update test.t2 set id=-8 where id=8' mysql -e 'update test.t2 set id=-9 where id=9' mysql -e 'update test.t2 set id=-10 where id=10'
查看t2表修改完表结构后的数据情况
mysql> select count(*),min(id),max(id) from t2;+----------+---------+----------+| count(*) | min(id) | max(id) |+----------+---------+----------+| 10000010 | -10 | 10000000 |+----------+---------+----------+1 row in set (3.00 sec)mysql> select * from t2 order by id limit 20;+-----+------+| id | c1 |+-----+------+| -10 | NULL || -9 | NULL || -8 | NULL || -7 | NULL || -6 | NULL || -5 | NULL || -4 | NULL || -3 | NULL || -2 | NULL || -1 | NULL || 1 | NULL || 2 | NULL || 3 | NULL || 4 | NULL || 5 | NULL || 6 | NULL || 7 | NULL || 8 | NULL || 9 | NULL || 10 | NULL |+-----+------+20 rows in set (0.08 sec)
可见，在执行pt-online-schema-change命令的过程中，针对原表执行的update操作并没有理所当然的反应到中间表上。
总结
1. 上述测试使用的pt-online-schema-change是2.2.19版本。
2. 欲进行表结构变更的表中必须存在主键或者唯一索引。
体现在以下方面：
1> 针对DELETE触发器
CREATE TRIGGER `pt_osc_test_t2_del` AFTER DELETE ON `test`.`t2` FOR EACH ROW DELETE IGNORE FROM `test`.`_t2_new` WHERE `test`.`_t2_new`.`id` <=> OLD.`id`
DELETE触发器是基于主键或者唯一索引进行删除的。如果id是普通索引，则原表中可能只有一行记录的删除（譬如delete from t where id=1 and name='victor'）,导致中间表中所有id为1的记录的删除。
2> 针对UPDATE触发器
如果原表中不存在主键或者唯一索引，则replace操作会直接插入，而不会进行替换。
mysql> create table t3(id int,name varchar(10));Query OK, 0 rows affected (0.08 sec)mysql> insert into t3 values(1,'a');Query OK, 1 row affected (0.05 sec)mysql> replace into t3 values(1,'b');Query OK, 1 row affected (0.06 sec)mysql> select * from t3;+------+------+| id | name |+------+------+| 1 | a || 1 | b |+------+------+2 rows in set (0.00 sec)mysql> alter table t3 modify id int primary key;ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'mysql> delete from t3 where id=1 and name='b';Query OK, 1 row affected (0.07 sec)mysql> alter table t3 modify id int primary key;Query OK, 0 rows affected (0.24 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> select * from t3;+----+------+| id | name |+----+------+| 1 | a |+----+------+1 row in set (0.00 sec)mysql> replace into t3 values(1,'b');Query OK, 2 rows affected (0.01 sec)mysql> select * from t3;+----+------+| id | name |+----+------+| 1 | b |+----+------+1 row in set (0.01 sec)
3. 即便欲进行表结构变更的表中存在主键或者唯一索引，如果在利用pt-online-schema-change进行online ddl过程中，有针对主键的更新操作，则会导致记录的新增。这点需引起注意。