1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
|
SET BINLOG_FORMAT=MIXED;
RESET MASTER;
CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=INNODB;
INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE t1 SET b = 2*a WHERE a > 1;
COMMIT;
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE t1 SET b = a * a WHERE a > 3;
COMMIT;
SET BINLOG_FORMAT=STATEMENT;
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
UPDATE t1 SET b = 1*a WHERE a > 1;
ERROR HY000: Binary logging not possible. Message: Transaction level 'READ-UNCOMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'
COMMIT;
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE t1 SET b = 2*a WHERE a > 2;
ERROR HY000: Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'
COMMIT;
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE t1 SET b = 3*a WHERE a > 3;
COMMIT;
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE t1 SET b = 4*a WHERE a > 4;
COMMIT;
SET BINLOG_FORMAT=MIXED;
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
UPDATE t1 SET b = 1*a WHERE a > 1;
COMMIT;
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE t1 SET b = 2*a WHERE a > 2;
COMMIT;
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE t1 SET b = 3*a WHERE a > 3;
COMMIT;
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE t1 SET b = 4*a WHERE a > 4;
COMMIT;
SET BINLOG_FORMAT=ROW;
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
UPDATE t1 SET b = 1*a WHERE a > 1;
COMMIT;
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE t1 SET b = 2*a WHERE a > 2;
COMMIT;
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE t1 SET b = 3*a WHERE a > 3;
COMMIT;
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE t1 SET b = 4*a WHERE a > 4;
COMMIT;
show binlog events from <binlog_start>;
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=INNODB
master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6)
master-bin.000001 # Xid # # COMMIT /* XID */
master-bin.000001 # Query # # use `test`; BEGIN
master-bin.000001 # Query # # use `test`; UPDATE t1 SET b = 2*a WHERE a > 1
master-bin.000001 # Xid # # COMMIT /* XID */
master-bin.000001 # Query # # use `test`; BEGIN
master-bin.000001 # Table_map # # table_id: # (test.t1)
master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F
master-bin.000001 # Xid # # COMMIT /* XID */
master-bin.000001 # Query # # use `test`; BEGIN
master-bin.000001 # Query # # use `test`; UPDATE t1 SET b = 3*a WHERE a > 3
master-bin.000001 # Xid # # COMMIT /* XID */
master-bin.000001 # Query # # use `test`; BEGIN
master-bin.000001 # Query # # use `test`; UPDATE t1 SET b = 4*a WHERE a > 4
master-bin.000001 # Xid # # COMMIT /* XID */
master-bin.000001 # Query # # use `test`; BEGIN
master-bin.000001 # Table_map # # table_id: # (test.t1)
master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F
master-bin.000001 # Xid # # COMMIT /* XID */
master-bin.000001 # Query # # use `test`; BEGIN
master-bin.000001 # Table_map # # table_id: # (test.t1)
master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F
master-bin.000001 # Xid # # COMMIT /* XID */
master-bin.000001 # Query # # use `test`; BEGIN
master-bin.000001 # Query # # use `test`; UPDATE t1 SET b = 3*a WHERE a > 3
master-bin.000001 # Xid # # COMMIT /* XID */
master-bin.000001 # Query # # use `test`; BEGIN
master-bin.000001 # Query # # use `test`; UPDATE t1 SET b = 4*a WHERE a > 4
master-bin.000001 # Xid # # COMMIT /* XID */
master-bin.000001 # Query # # use `test`; BEGIN
master-bin.000001 # Table_map # # table_id: # (test.t1)
master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F
master-bin.000001 # Xid # # COMMIT /* XID */
master-bin.000001 # Query # # use `test`; BEGIN
master-bin.000001 # Table_map # # table_id: # (test.t1)
master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F
master-bin.000001 # Xid # # COMMIT /* XID */
master-bin.000001 # Query # # use `test`; BEGIN
master-bin.000001 # Table_map # # table_id: # (test.t1)
master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F
master-bin.000001 # Xid # # COMMIT /* XID */
master-bin.000001 # Query # # use `test`; BEGIN
master-bin.000001 # Table_map # # table_id: # (test.t1)
master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F
master-bin.000001 # Xid # # COMMIT /* XID */
DROP TABLE t1;
show status like "binlog_cache_use";
Variable_name Value
Binlog_cache_use 13
show status like "binlog_cache_disk_use";
Variable_name Value
Binlog_cache_disk_use 0
create table t1 (a int) engine=innodb;
show status like "binlog_cache_use";
Variable_name Value
Binlog_cache_use 14
show status like "binlog_cache_disk_use";
Variable_name Value
Binlog_cache_disk_use 1
begin;
delete from t1;
commit;
show status like "binlog_cache_use";
Variable_name Value
Binlog_cache_use 15
show status like "binlog_cache_disk_use";
Variable_name Value
Binlog_cache_disk_use 1
drop table t1;
End of tests
|
