实验：基于语句复制的不安全性《MySQL排错指南Page83-85》
实验：基于语句复制的不安全性《MySQL排错指南Page83-85》
环境：
主、从库的binlog_format = statement
隔离级别： RC
主库会话1 | 主库会话2 | 主库会话3 | 从库会话 |
|---|---|---|---|
use test;create table f1(char(2))engine=InnoDB; | |||
begin;insert into t1 select 1;insert into t1 select 2;insert into t1 select 3;insert into t1 select 4;insert into t1 select 5; | |||
begin;insert into t1 select 'a';insert into t1 select 'b';insert into t1 select 'c';insert into t1 select 'd';insert into t1 select 'e';insert into t1 select 'f';commit; | |||
select * from t1;+------+| f1 |+------+| a || b || c || d || e || f |+------+ | select * from t1;+------+| f1 |+------+| a || b || c || d || e || f |+------+ | ||
commit | |||
select * from t1;+------+| f1 |+------+| 1 || 2 || 3 || 4 || 5 || a || b || c || d || e || f |+------+ | select * from t1;+------+| f1 |+------+| a || b || c || d || e || f || 1 || 2 || 3 || 4 || 5 |+------+ |
可以看到上图中，最后一行，主从库的数据排列顺序变化了。如果这时候我们在主库再执行一个类似 update t1 set f1='A' limit 4; 这种操作，则从库的数据彻底乱了。
下面是执行update时候 主库提示的warnings告警。
master [localhost] {root} (test) > master [localhost] {root} (test) > show warnings \G
*************************** 1. row ***************************
Level: Note
Code: 1592
Message: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.
1 row in set (0.00 sec)