实验：基于语句复制的不安全性《MySQL排错指南Page83-85》
- 2019 年 10 月 4 日
- 筆記
环境：
主、从库的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)
