Bug of update trigger in pt-online-schema-change, onlineschemachange

Bug of update trigger in pt-online-schema-change, onlineschemachange

When pt-online-schema-change changes the table structure, Three triggers are created.

Table t2 in the following test case. The table structure is as follows:

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)

Only one auto-incrementing column id exists.

The created trigger is as follows:

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`)

 

The DELETE trigger and INSERT trigger have no logic problems.

 

However, for an UPDATE trigger, if a record has been copied to an intermediate table, an UPDATE operation is performed on the record and the primary key is modified, replace into 'test' triggered for the intermediate table '. '_ t2_new' ('id') VALUES (NEW. 'id') "operation inserts only one new record without deleting the original record.

 

The following scenario is reproduced:

Create a trigger to construct Test Data

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;

 

In this case, 10 million data records are generated.

mysql> select count(*),min(id),max(id) from t2;+----------+---------+----------+| count(*) | min(id) | max(id)  |+----------+---------+----------+| 10000000 |       1 | 10000000 |+----------+---------+----------+1 row in set (4.29 sec)

 

Use pt-online-schema-change to add a column to table 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`.

When the above red information is output, open another terminal window and execute the following command

 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'

 

View data after table structure modification in Table 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)

 

It can be seen that during the execution of the pt-online-schema-change command, the update operation for the original table does not take it for granted to the intermediate table.

 

Summary

1. the pt-online-schema-change used in the above test is version 2.2.19.

2. the table to be changed must have a primary key or a unique index.

It is reflected in the following aspects:

1> DELETE 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`

The DELETE trigger is deleted based on the primary key or unique index. If the ID is only a common index,

2> UPDATE trigger

If the original table does not have a primary key or a unique index, the replace operation is inserted directly without replacement.

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. even if the table to be changed has a primary key or a unique index, if you use pt-online-schema-change to update the primary key during the online ddl process, this will lead to new records. This should be noted.