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

2018-06-23 13:24:24来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

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

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:SQL复杂查询和视图

下一篇:mysql_建立索引的优缺点 #转自Starzm#