pt-online-schema-change中update触发器的bug

时间：2023-11-22 20:07:56

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

  copy_rows, , 0.25

  create_triggers, ,

  drop_triggers, ,

  swap_tables, ,

  update_foreign_keys, ,

Altering `test`.`t2`...

Creating new table...

CREATE TABLE `test`.`___t2_new` (

  `id` int() NOT NULL AUTO_INCREMENT,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT= 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.

--23T20:: 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`)

--23T20:: Created triggers OK.

--23T20:: Copying approximately  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 ?,  /*next chun

k boundary*/
Copying `test`.`t2`:  % : remain

Copying `test`.`t2`:  52% 00:54 remain

Copying `test`.`t2`:  % : remain

--23T20:: Copied rows OK.

--23T20:: Analyzing new table...

--23T20:: Swapping tables...

RENAME TABLE `test`.`t2` TO `test`.`_t2_old`, `test`.`___t2_new` TO `test`.`t2`

--23T20:: Swapped original and new tables OK.

--23T20:: Dropping old table...

DROP TABLE IF EXISTS `test`.`_t2_old`

--23T20:: Dropped old table `test`.`_t2_old` OK.

--23T20:: 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`;

--23T20:: 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 '' 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过程中，有针对主键的更新操作，则会导致记录的新增。这点需引起注意。

相关文章