pt-online-schema-change在對表進行表結構變更時，會創建三個觸發器。
如下文測試案例中的t2表，表結構如下：
mysql> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 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, 1 Altering `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=utf8 Created new table test.___t2_new OK. Altering new table... ALTER TABLE `test`.`___t2_new` ADD COLUMN c1 DATETIME Altered `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`.`___t 2_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 chun k boundary*/
Copying `test`.`t2`: 29% 01:12 remain Copying `test`.`t2`: 52% 00:54 remain Copying `test`.`t2`: 76% 00:27 remain 2016-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只是普通的索引，
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: 0 mysql> 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過程中，有針對主鍵的更新操作，則會導致記錄的新增。這點需引起注意。
