Debian Codegraph @

mysql-test/suite/binlog/r/binlog_innodb.result

raw
1 SET BINLOG_FORMAT=MIXED;
2 RESET MASTER;
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;
6 BEGIN;
7 UPDATE t1 SET b = 2*a WHERE a > 1;
8 COMMIT;
9 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
10 BEGIN;
11 UPDATE t1 SET b = a * a WHERE a > 3;
12 COMMIT;
13 SET BINLOG_FORMAT=STATEMENT;
14 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
15 BEGIN;
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.
18 COMMIT;
19 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
20 BEGIN;
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.
23 COMMIT;
24 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
25 BEGIN;
26 UPDATE t1 SET b = 3*a WHERE a > 3;
27 COMMIT;
28 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
29 BEGIN;
30 UPDATE t1 SET b = 4*a WHERE a > 4;
31 COMMIT;
32 SET BINLOG_FORMAT=MIXED;
33 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
34 BEGIN;
35 UPDATE t1 SET b = 1*a WHERE a > 1;
36 COMMIT;
37 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
38 BEGIN;
39 UPDATE t1 SET b = 2*a WHERE a > 2;
40 COMMIT;
41 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
42 BEGIN;
43 UPDATE t1 SET b = 3*a WHERE a > 3;
44 COMMIT;
45 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
46 BEGIN;
47 UPDATE t1 SET b = 4*a WHERE a > 4;
48 COMMIT;
49 SET BINLOG_FORMAT=ROW;
50 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
51 BEGIN;
52 UPDATE t1 SET b = 1*a WHERE a > 1;
53 COMMIT;
54 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
55 BEGIN;
56 UPDATE t1 SET b = 2*a WHERE a > 2;
57 COMMIT;
58 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
59 BEGIN;
60 UPDATE t1 SET b = 3*a WHERE a > 3;
61 COMMIT;
62 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
63 BEGIN;
64 UPDATE t1 SET b = 4*a WHERE a > 4;
65 COMMIT;
66 include/show_binlog_events.inc
67 Log_name Pos Event_type Server_id End_log_pos Info
68 master-bin.000001 # Gtid # # GTID #-#-#
69 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=INNODB
70 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
71 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6)
72 master-bin.000001 # Xid # # COMMIT /* XID */
73 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
74 master-bin.000001 # Query # # use `test`; UPDATE t1 SET b = 2*a WHERE a > 1
75 master-bin.000001 # Xid # # COMMIT /* XID */
76 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
77 master-bin.000001 # Annotate_rows # # UPDATE t1 SET b = a * a WHERE a > 3
78 master-bin.000001 # Table_map # # table_id: # (test.t1)
79 master-bin.000001 # Update_rows_v1 # # table_id: # flags: STMT_END_F
80 master-bin.000001 # Xid # # COMMIT /* XID */
81 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
82 master-bin.000001 # Query # # use `test`; UPDATE t1 SET b = 3*a WHERE a > 3
83 master-bin.000001 # Xid # # COMMIT /* XID */
84 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
85 master-bin.000001 # Query # # use `test`; UPDATE t1 SET b = 4*a WHERE a > 4
86 master-bin.000001 # Xid # # COMMIT /* XID */
87 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
88 master-bin.000001 # Annotate_rows # # UPDATE t1 SET b = 1*a WHERE a > 1
89 master-bin.000001 # Table_map # # table_id: # (test.t1)
90 master-bin.000001 # Update_rows_v1 # # table_id: # flags: STMT_END_F
91 master-bin.000001 # Xid # # COMMIT /* XID */
92 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
93 master-bin.000001 # Annotate_rows # # UPDATE t1 SET b = 2*a WHERE a > 2
94 master-bin.000001 # Table_map # # table_id: # (test.t1)
95 master-bin.000001 # Update_rows_v1 # # table_id: # flags: STMT_END_F
96 master-bin.000001 # Xid # # COMMIT /* XID */
97 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
98 master-bin.000001 # Query # # use `test`; UPDATE t1 SET b = 3*a WHERE a > 3
99 master-bin.000001 # Xid # # COMMIT /* XID */
100 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
101 master-bin.000001 # Query # # use `test`; UPDATE t1 SET b = 4*a WHERE a > 4
102 master-bin.000001 # Xid # # COMMIT /* XID */
103 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
104 master-bin.000001 # Annotate_rows # # UPDATE t1 SET b = 1*a WHERE a > 1
105 master-bin.000001 # Table_map # # table_id: # (test.t1)
106 master-bin.000001 # Update_rows_v1 # # table_id: # flags: STMT_END_F
107 master-bin.000001 # Xid # # COMMIT /* XID */
108 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
109 master-bin.000001 # Annotate_rows # # UPDATE t1 SET b = 2*a WHERE a > 2
110 master-bin.000001 # Table_map # # table_id: # (test.t1)
111 master-bin.000001 # Update_rows_v1 # # table_id: # flags: STMT_END_F
112 master-bin.000001 # Xid # # COMMIT /* XID */
113 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
114 master-bin.000001 # Annotate_rows # # UPDATE t1 SET b = 3*a WHERE a > 3
115 master-bin.000001 # Table_map # # table_id: # (test.t1)
116 master-bin.000001 # Update_rows_v1 # # table_id: # flags: STMT_END_F
117 master-bin.000001 # Xid # # COMMIT /* XID */
118 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
119 master-bin.000001 # Annotate_rows # # UPDATE t1 SET b = 4*a WHERE a > 4
120 master-bin.000001 # Table_map # # table_id: # (test.t1)
121 master-bin.000001 # Update_rows_v1 # # table_id: # flags: STMT_END_F
122 master-bin.000001 # Xid # # COMMIT /* XID */
123 DROP TABLE t1;
124 flush global status;
125 show status like "binlog_cache_use";
126 Variable_name Value
127 Binlog_cache_use 0
128 show status like "binlog_cache_disk_use";
129 Variable_name Value
130 Binlog_cache_disk_use 0
131 create table t1 (a int) engine=innodb;
132 show status like "binlog_cache_use";
133 Variable_name Value
134 Binlog_cache_use 1
135 show status like "binlog_cache_disk_use";
136 Variable_name Value
137 Binlog_cache_disk_use 1
138 begin;
139 delete from t1;
140 commit;
141 show status like "binlog_cache_use";
142 Variable_name Value
143 Binlog_cache_use 2
144 show status like "binlog_cache_disk_use";
145 Variable_name Value
146 Binlog_cache_disk_use 1
147 drop table t1;
148 CREATE TABLE `t1` (
149 `a` int(11) NOT NULL auto_increment,
150 `b` int(11) default NULL,
151 PRIMARY KEY (`a`)
152 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
153 CREATE TABLE `t2` (
154 `a` int(11) NOT NULL auto_increment,
155 `b` int(11) default NULL,
156 PRIMARY KEY (`a`)
157 ) ENGINE=INNODB DEFAULT CHARSET=latin1 ;
158 insert into t1 values (1,1),(2,2);
159 insert into t2 values (1,1),(4,4);
160 reset master;
161 UPDATE t2,t1 SET t2.a=t1.a+2;
162 ERROR 23000: Duplicate entry '3' for key 'PRIMARY'
163 select * from t2 /* must be (3,1), (4,4) */;
164 a b
165 1 1
166 4 4
167 # There must no UPDATE in binlog;
168 include/show_binlog_events.inc
169 delete from t1;
170 delete from t2;
171 insert into t1 values (1,2),(3,4),(4,4);
172 insert into t2 values (1,2),(3,4),(4,4);
173 reset master;
174 UPDATE t2,t1 SET t2.a=t2.b where t2.a=t1.a;
175 ERROR 23000: Duplicate entry '4' for key 'PRIMARY'
176 # There must be no UPDATE query event;
177 include/show_binlog_events.inc
178 drop table t1, t2;
179 *** MDEV-11937: InnoDB flushes redo log too often ***
180 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
181 SET @old_flush = @@GLOBAL.innodb_flush_log_at_trx_commit;
182 SET GLOBAL innodb_flush_log_at_trx_commit=1;
183 SELECT IF(@num_sync < 100*1.5, "OK",
184 CONCAT("ERROR: More than 1 fsync per commit (saw ", @num_sync/100, ")")) AS status;
185 status
186 OK
187 DROP TABLE t1;
188 SET GLOBAL innodb_flush_log_at_trx_commit=@old_flush;
189 End of tests