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
139
140
141
142
143
144
145
146
147
148
|
SET BINLOG_FORMAT=MIXED;
Warnings:
Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
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);
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
UPDATE t1 SET b = 2*a WHERE a > 1;
COMMIT;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
UPDATE t1 SET b = a * a WHERE a > 3;
COMMIT;
SET BINLOG_FORMAT=STATEMENT;
Warnings:
Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
UPDATE t1 SET b = 1*a WHERE a > 1;
ERROR HY000: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
COMMIT;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
UPDATE t1 SET b = 2*a WHERE a > 2;
ERROR HY000: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
COMMIT;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
UPDATE t1 SET b = 3*a WHERE a > 3;
COMMIT;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
UPDATE t1 SET b = 4*a WHERE a > 4;
COMMIT;
SET BINLOG_FORMAT=MIXED;
Warnings:
Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
UPDATE t1 SET b = 1*a WHERE a > 1;
COMMIT;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
UPDATE t1 SET b = 2*a WHERE a > 2;
COMMIT;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
UPDATE t1 SET b = 3*a WHERE a > 3;
COMMIT;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
UPDATE t1 SET b = 4*a WHERE a > 4;
COMMIT;
SET BINLOG_FORMAT='ROW';
Warnings:
Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
UPDATE t1 SET b = 1*a WHERE a > 1;
COMMIT;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
UPDATE t1 SET b = 2*a WHERE a > 2;
COMMIT;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
UPDATE t1 SET b = 3*a WHERE a > 3;
COMMIT;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
UPDATE t1 SET b = 4*a WHERE a > 4;
COMMIT;
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
binlog.000001 # Query # # use `test`; CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=INNODB
binlog.000001 # Query # # BEGIN
binlog.000001 # Query # # use `test`; INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6)
binlog.000001 # Xid # # COMMIT /* XID */
binlog.000001 # Query # # BEGIN
binlog.000001 # Query # # use `test`; UPDATE t1 SET b = 2*a WHERE a > 1
binlog.000001 # Xid # # COMMIT /* XID */
binlog.000001 # Query # # BEGIN
binlog.000001 # Table_map # # table_id: # (test.t1)
binlog.000001 # Update_rows # # table_id: # flags: STMT_END_F
binlog.000001 # Xid # # COMMIT /* XID */
binlog.000001 # Query # # BEGIN
binlog.000001 # Query # # use `test`; UPDATE t1 SET b = 3*a WHERE a > 3
binlog.000001 # Xid # # COMMIT /* XID */
binlog.000001 # Query # # BEGIN
binlog.000001 # Query # # use `test`; UPDATE t1 SET b = 4*a WHERE a > 4
binlog.000001 # Xid # # COMMIT /* XID */
binlog.000001 # Query # # BEGIN
binlog.000001 # Table_map # # table_id: # (test.t1)
binlog.000001 # Update_rows # # table_id: # flags: STMT_END_F
binlog.000001 # Xid # # COMMIT /* XID */
binlog.000001 # Query # # BEGIN
binlog.000001 # Table_map # # table_id: # (test.t1)
binlog.000001 # Update_rows # # table_id: # flags: STMT_END_F
binlog.000001 # Xid # # COMMIT /* XID */
binlog.000001 # Query # # BEGIN
binlog.000001 # Query # # use `test`; UPDATE t1 SET b = 3*a WHERE a > 3
binlog.000001 # Xid # # COMMIT /* XID */
binlog.000001 # Query # # BEGIN
binlog.000001 # Query # # use `test`; UPDATE t1 SET b = 4*a WHERE a > 4
binlog.000001 # Xid # # COMMIT /* XID */
binlog.000001 # Query # # BEGIN
binlog.000001 # Table_map # # table_id: # (test.t1)
binlog.000001 # Update_rows # # table_id: # flags: STMT_END_F
binlog.000001 # Xid # # COMMIT /* XID */
binlog.000001 # Query # # BEGIN
binlog.000001 # Table_map # # table_id: # (test.t1)
binlog.000001 # Update_rows # # table_id: # flags: STMT_END_F
binlog.000001 # Xid # # COMMIT /* XID */
binlog.000001 # Query # # BEGIN
binlog.000001 # Table_map # # table_id: # (test.t1)
binlog.000001 # Update_rows # # table_id: # flags: STMT_END_F
binlog.000001 # Xid # # COMMIT /* XID */
binlog.000001 # Query # # BEGIN
binlog.000001 # Table_map # # table_id: # (test.t1)
binlog.000001 # Update_rows # # table_id: # flags: STMT_END_F
binlog.000001 # Xid # # COMMIT /* XID */
DROP TABLE t1;
flush status;
show status like "binlog_cache_use";
Variable_name Value
Binlog_cache_use 0
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 2
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 3
show status like "binlog_cache_disk_use";
Variable_name Value
Binlog_cache_disk_use 1
drop table t1;
End of tests
|
