實驗：基於語句複製的不安全性《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)
