1
SET BINLOG_FORMAT=MIXED;
3
CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=INNODB;
4
INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
5
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
7
UPDATE t1 SET b = 2*a WHERE a > 1;
9
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
11
UPDATE t1 SET b = a * a WHERE a > 3;
13
SET BINLOG_FORMAT=STATEMENT;
14
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
16
UPDATE t1 SET b = 1*a WHERE a > 1;
17
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.
19
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
21
UPDATE t1 SET b = 2*a WHERE a > 2;
22
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.
24
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
26
UPDATE t1 SET b = 3*a WHERE a > 3;
28
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
30
UPDATE t1 SET b = 4*a WHERE a > 4;
32
SET BINLOG_FORMAT=MIXED;
33
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
35
UPDATE t1 SET b = 1*a WHERE a > 1;
37
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
39
UPDATE t1 SET b = 2*a WHERE a > 2;
41
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
43
UPDATE t1 SET b = 3*a WHERE a > 3;
45
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
47
UPDATE t1 SET b = 4*a WHERE a > 4;
49
SET BINLOG_FORMAT=ROW;
50
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
52
UPDATE t1 SET b = 1*a WHERE a > 1;
54
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
56
UPDATE t1 SET b = 2*a WHERE a > 2;
58
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
60
UPDATE t1 SET b = 3*a WHERE a > 3;
62
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
64
UPDATE t1 SET b = 4*a WHERE a > 4;
66
include/show_binlog_events.inc
67
Log_name Pos Event_type Server_id End_log_pos Info
68
master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=INNODB
69
master-bin.000001 # Query # # BEGIN
70
master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6)
71
master-bin.000001 # Xid # # COMMIT /* XID */
72
master-bin.000001 # Query # # BEGIN
73
master-bin.000001 # Query # # use `test`; UPDATE t1 SET b = 2*a WHERE a > 1
74
master-bin.000001 # Xid # # COMMIT /* XID */
75
master-bin.000001 # Query # # BEGIN
76
master-bin.000001 # Table_map # # table_id: # (test.t1)
77
master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F
78
master-bin.000001 # Xid # # COMMIT /* XID */
79
master-bin.000001 # Query # # BEGIN
80
master-bin.000001 # Query # # use `test`; UPDATE t1 SET b = 3*a WHERE a > 3
81
master-bin.000001 # Xid # # COMMIT /* XID */
82
master-bin.000001 # Query # # BEGIN
83
master-bin.000001 # Query # # use `test`; UPDATE t1 SET b = 4*a WHERE a > 4
84
master-bin.000001 # Xid # # COMMIT /* XID */
85
master-bin.000001 # Query # # BEGIN
86
master-bin.000001 # Table_map # # table_id: # (test.t1)
87
master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F
88
master-bin.000001 # Xid # # COMMIT /* XID */
89
master-bin.000001 # Query # # BEGIN
90
master-bin.000001 # Table_map # # table_id: # (test.t1)
91
master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F
92
master-bin.000001 # Xid # # COMMIT /* XID */
93
master-bin.000001 # Query # # BEGIN
94
master-bin.000001 # Query # # use `test`; UPDATE t1 SET b = 3*a WHERE a > 3
95
master-bin.000001 # Xid # # COMMIT /* XID */
96
master-bin.000001 # Query # # BEGIN
97
master-bin.000001 # Query # # use `test`; UPDATE t1 SET b = 4*a WHERE a > 4
98
master-bin.000001 # Xid # # COMMIT /* XID */
99
master-bin.000001 # Query # # BEGIN
100
master-bin.000001 # Table_map # # table_id: # (test.t1)
101
master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F
102
master-bin.000001 # Xid # # COMMIT /* XID */
103
master-bin.000001 # Query # # BEGIN
104
master-bin.000001 # Table_map # # table_id: # (test.t1)
105
master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F
106
master-bin.000001 # Xid # # COMMIT /* XID */
107
master-bin.000001 # Query # # BEGIN
108
master-bin.000001 # Table_map # # table_id: # (test.t1)
109
master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F
110
master-bin.000001 # Xid # # COMMIT /* XID */
111
master-bin.000001 # Query # # BEGIN
112
master-bin.000001 # Table_map # # table_id: # (test.t1)
113
master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F
114
master-bin.000001 # Xid # # COMMIT /* XID */
117
show status like "binlog_cache_use";
120
show status like "binlog_cache_disk_use";
122
Binlog_cache_disk_use 0
123
create table t1 (a int) engine=innodb;
124
show status like "binlog_cache_use";
127
show status like "binlog_cache_disk_use";
129
Binlog_cache_disk_use 1
133
show status like "binlog_cache_use";
136
show status like "binlog_cache_disk_use";
138
Binlog_cache_disk_use 1
141
`a` int(11) NOT NULL auto_increment,
142
`b` int(11) default NULL,
144
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
146
`a` int(11) NOT NULL auto_increment,
147
`b` int(11) default NULL,
149
) ENGINE=INNODB DEFAULT CHARSET=latin1 ;
150
insert into t1 values (1,1),(2,2);
151
insert into t2 values (1,1),(4,4);
153
UPDATE t2,t1 SET t2.a=t1.a+2;
154
ERROR 23000: Duplicate entry '3' for key 'PRIMARY'
155
select * from t2 /* must be (3,1), (4,4) */;
159
# There must no UPDATE in binlog;
160
include/show_binlog_events.inc
163
insert into t1 values (1,2),(3,4),(4,4);
164
insert into t2 values (1,2),(3,4),(4,4);
166
UPDATE t2,t1 SET t2.a=t2.b where t2.a=t1.a;
167
ERROR 23000: Duplicate entry '4' for key 'PRIMARY'
168
# There must be no UPDATE query event;
169
include/show_binlog_events.inc