pt-online-schema-change中update触发器的bug
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是普通索引，则原表中可能只有一行记录的删除（譬如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: 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过程中，有针对主键的更新操作，则会导致记录的新增。这点需引起注意。
