Debian Codegraph @

mysql-test/suite/engines/funcs/r/rpl_switch_stm_row_mixed.result

raw
1 include/master-slave.inc
2 [connection master]
3 connection slave;
4 connection master;
5 drop database if exists mysqltest1;
6 create database mysqltest1;
7 use mysqltest1;
8 set @my_binlog_format= @@global.binlog_format;
9 set session binlog_format=mixed;
10 show session variables like "binlog_format%";
11 Variable_name Value
12 binlog_format MIXED
13 set session binlog_format=statement;
14 show session variables like "binlog_format%";
15 Variable_name Value
16 binlog_format STATEMENT
17 set session binlog_format=row;
18 show session variables like "binlog_format%";
19 Variable_name Value
20 binlog_format ROW
21 set global binlog_format=DEFAULT;
22 show global variables like "binlog_format%";
23 Variable_name Value
24 binlog_format MIXED
25 set global binlog_format=MIXED;
26 show global variables like "binlog_format%";
27 Variable_name Value
28 binlog_format MIXED
29 set global binlog_format=STATEMENT;
30 show global variables like "binlog_format%";
31 Variable_name Value
32 binlog_format STATEMENT
33 set global binlog_format=ROW;
34 show global variables like "binlog_format%";
35 Variable_name Value
36 binlog_format ROW
37 show session variables like "binlog_format%";
38 Variable_name Value
39 binlog_format ROW
40 select @@global.binlog_format, @@session.binlog_format;
41 @@global.binlog_format @@session.binlog_format
42 ROW ROW
43 CREATE TABLE t1 (a varchar(100));
44 prepare stmt1 from 'insert into t1 select concat(SYS_GUID(),?)';
45 set @string="emergency_1_";
46 insert into t1 values("work_2_");
47 execute stmt1 using @string;
48 deallocate prepare stmt1;
49 prepare stmt1 from 'insert into t1 select ?';
50 insert into t1 values(concat(SYS_GUID(),"work_3_"));
51 execute stmt1 using @string;
52 deallocate prepare stmt1;
53 insert into t1 values(concat("for_4_",SYS_GUID()));
54 insert into t1 select "yesterday_5_";
55 create temporary table tmp(a char(100));
56 insert into tmp values("see_6_");
57 set binlog_format=statement;
58 ERROR HY000: Cannot switch out of the row-based binary log format when the session has open temporary tables
59 insert into t1 select * from tmp;
60 drop temporary table tmp;
61 set binlog_format=statement;
62 show global variables like "binlog_format%";
63 Variable_name Value
64 binlog_format ROW
65 show session variables like "binlog_format%";
66 Variable_name Value
67 binlog_format STATEMENT
68 select @@global.binlog_format, @@session.binlog_format;
69 @@global.binlog_format @@session.binlog_format
70 ROW STATEMENT
71 set global binlog_format=statement;
72 show global variables like "binlog_format%";
73 Variable_name Value
74 binlog_format STATEMENT
75 show session variables like "binlog_format%";
76 Variable_name Value
77 binlog_format STATEMENT
78 select @@global.binlog_format, @@session.binlog_format;
79 @@global.binlog_format @@session.binlog_format
80 STATEMENT STATEMENT
81 prepare stmt1 from 'insert into t1 select ?';
82 set @string="emergency_7_";
83 insert into t1 values("work_8_");
84 execute stmt1 using @string;
85 deallocate prepare stmt1;
86 prepare stmt1 from 'insert into t1 select ?';
87 insert into t1 values("work_9_");
88 execute stmt1 using @string;
89 deallocate prepare stmt1;
90 insert into t1 values("for_10_");
91 insert into t1 select "yesterday_11_";
92 set binlog_format=statement;
93 select @@global.binlog_format, @@session.binlog_format;
94 @@global.binlog_format @@session.binlog_format
95 STATEMENT STATEMENT
96 set global binlog_format=statement;
97 select @@global.binlog_format, @@session.binlog_format;
98 @@global.binlog_format @@session.binlog_format
99 STATEMENT STATEMENT
100 prepare stmt1 from 'insert into t1 select ?';
101 set @string="emergency_12_";
102 insert into t1 values("work_13_");
103 execute stmt1 using @string;
104 deallocate prepare stmt1;
105 prepare stmt1 from 'insert into t1 select ?';
106 insert into t1 values("work_14_");
107 execute stmt1 using @string;
108 deallocate prepare stmt1;
109 insert into t1 values("for_15_");
110 insert into t1 select "yesterday_16_";
111 set global binlog_format=mixed;
112 select @@global.binlog_format, @@session.binlog_format;
113 @@global.binlog_format @@session.binlog_format
114 MIXED STATEMENT
115 set binlog_format=default;
116 select @@global.binlog_format, @@session.binlog_format;
117 @@global.binlog_format @@session.binlog_format
118 MIXED MIXED
119 prepare stmt1 from 'insert into t1 select concat(SYS_GUID(),?)';
120 set @string="emergency_17_";
121 insert into t1 values("work_18_");
122 execute stmt1 using @string;
123 deallocate prepare stmt1;
124 prepare stmt1 from 'insert into t1 select ?';
125 insert into t1 values(concat(SYS_GUID(),"work_19_"));
126 execute stmt1 using @string;
127 deallocate prepare stmt1;
128 insert into t1 values(concat("for_20_",SYS_GUID()));
129 insert into t1 select "yesterday_21_";
130 prepare stmt1 from 'insert into t1 select ?';
131 insert into t1 values(concat(SYS_GUID(),"work_22_"));
132 execute stmt1 using @string;
133 deallocate prepare stmt1;
134 insert into t1 values(concat("for_23_",SYS_GUID()));
135 insert into t1 select "yesterday_24_";
136 create table t2 ENGINE=MyISAM select rpad(SYS_GUID(),100,' ');
137 create table t3 select 1 union select SYS_GUID();
138 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
139 create table t4 select * from t1 where 3 in (select 1 union select 2 union select SYS_GUID() union select 3);
140 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
141 create table t5 select * from t1 where 3 in (select 1 union select 2 union select curdate() union select 3);
142 Warnings:
143 Warning 1292 Truncated incorrect datetime value: '3'
144 insert ignore into t5 select SYS_GUID() from t1 where 3 in (select 1 union select 2 union select 3 union select * from t4);
145 create procedure foo()
146 begin
147 insert into t1 values("work_25_");
148 insert into t1 values(concat("for_26_",SYS_GUID()));
149 insert into t1 select "yesterday_27_";
150 end|
151 create procedure foo2()
152 begin
153 insert into t1 values(concat("emergency_28_",SYS_GUID()));
154 insert into t1 values("work_29_");
155 insert into t1 values(concat("for_30_",SYS_GUID()));
156 set session binlog_format=row; # accepted for stored procs
157 insert into t1 values("more work_31_");
158 set session binlog_format=mixed;
159 end|
160 create function foo3() returns bigint unsigned
161 begin
162 set session binlog_format=row; # rejected for stored funcs
163 insert into t1 values("alarm");
164 return 100;
165 end|
166 create procedure foo4(x varchar(100))
167 begin
168 insert into t1 values(concat("work_250_",x));
169 insert into t1 select "yesterday_270_";
170 end|
171 call foo();
172 call foo2();
173 call foo4("hello");
174 call foo4(SYS_GUID());
175 call foo4("world");
176 select foo3();
177 ERROR HY000: Cannot change the binary logging format inside a stored function or trigger
178 select * from t1 where a="alarm";
179 a
180 drop function foo3;
181 create function foo3() returns bigint unsigned
182 begin
183 insert into t1 values("foo3_32_");
184 call foo();
185 return 100;
186 end|
187 insert into t2 select foo3();
188 prepare stmt1 from 'insert into t2 select foo3()';
189 execute stmt1;
190 execute stmt1;
191 deallocate prepare stmt1;
192 create function foo4() returns bigint unsigned
193 begin
194 insert into t2 select foo3();
195 return 100;
196 end|
197 select foo4();
198 foo4()
199 100
200 prepare stmt1 from 'select foo4()';
201 execute stmt1;
202 foo4()
203 100
204 execute stmt1;
205 foo4()
206 100
207 deallocate prepare stmt1;
208 create function foo5() returns bigint unsigned
209 begin
210 insert into t2 select SYS_GUID();
211 return 100;
212 end|
213 select foo5();
214 foo5()
215 100
216 prepare stmt1 from 'select foo5()';
217 execute stmt1;
218 foo5()
219 100
220 execute stmt1;
221 foo5()
222 100
223 deallocate prepare stmt1;
224 create function foo6(x varchar(100)) returns bigint unsigned
225 begin
226 insert into t2 select x;
227 return 100;
228 end|
229 select foo6("foo6_1_");
230 foo6("foo6_1_")
231 100
232 select foo6(concat("foo6_2_",SYS_GUID()));
233 foo6(concat("foo6_2_",SYS_GUID()))
234 100
235 prepare stmt1 from 'select foo6(concat("foo6_3_",SYS_GUID()))';
236 execute stmt1;
237 foo6(concat("foo6_3_",SYS_GUID()))
238 100
239 execute stmt1;
240 foo6(concat("foo6_3_",SYS_GUID()))
241 100
242 deallocate prepare stmt1;
243 create view v1 as select SYS_GUID();
244 create table t11 (data varchar(255));
245 insert into t11 select * from v1;
246 insert into t11 select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysqltest1' and TABLE_NAME IN ('v1','t11');
247 prepare stmt1 from "insert into t11 select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysqltest1' and TABLE_NAME IN ('v1','t11')";
248 execute stmt1;
249 execute stmt1;
250 deallocate prepare stmt1;
251 create trigger t11_bi before insert on t11 for each row
252 begin
253 set NEW.data = concat(NEW.data,SYS_GUID());
254 end|
255 insert into t11 values("try_560_");
256 insert delayed into t2 values("delay_1_");
257 insert delayed into t2 values(concat("delay_2_",SYS_GUID()));
258 insert delayed into t2 values("delay_6_");
259 insert delayed into t2 values(rand());
260 set @a=2.345;
261 insert delayed into t2 values(@a);
262 connection slave;
263 connection master;
264 create table t20 select * from t1;
265 create table t21 select * from t2;
266 create table t22 select * from t3;
267 drop table t1,t2,t3;
268 create table t1 (a int primary key auto_increment, b varchar(100));
269 create table t2 (a int primary key auto_increment, b varchar(100));
270 create table t3 (b varchar(100));
271 create function f (x varchar(100)) returns int deterministic
272 begin
273 insert into t1 values(null,x);
274 insert into t2 values(null,x);
275 return 1;
276 end|
277 select f("try_41_");
278 f("try_41_")
279 1
280 connection slave;
281 use mysqltest1;
282 insert into t2 values(2,null),(3,null),(4,null);
283 delete from t2 where a>=2;
284 connection master;
285 select f("try_42_");
286 f("try_42_")
287 1
288 connection slave;
289 insert into t2 values(3,null),(4,null);
290 delete from t2 where a>=3;
291 connection master;
292 prepare stmt1 from 'select f(?)';
293 set @string="try_43_";
294 insert into t1 values(null,"try_44_");
295 execute stmt1 using @string;
296 f(?)
297 1
298 deallocate prepare stmt1;
299 connection slave;
300 connection master;
301 create table t12 select * from t1;
302 drop table t1;
303 create table t1 (a int, b varchar(100), key(a));
304 select f("try_45_");
305 f("try_45_")
306 1
307 create table t13 select * from t1;
308 drop table t1;
309 create table t1 (a int primary key auto_increment, b varchar(100));
310 drop function f;
311 create table t14 (unique (a)) select * from t2;
312 truncate table t2;
313 create function f1 (x varchar(100)) returns int deterministic
314 begin
315 insert into t1 values(null,x);
316 return 1;
317 end|
318 create function f2 (x varchar(100)) returns int deterministic
319 begin
320 insert into t2 values(null,x);
321 return 1;
322 end|
323 select f1("try_46_"),f2("try_47_");
324 f1("try_46_") f2("try_47_")
325 1 1
326 connection slave;
327 insert into t2 values(2,null),(3,null),(4,null);
328 delete from t2 where a>=2;
329 connection master;
330 select f1("try_48_"),f2("try_49_");
331 f1("try_48_") f2("try_49_")
332 1 1
333 insert into t3 values(concat("try_50_",f1("try_51_"),f2("try_52_")));
334 connection slave;
335 connection master;
336 drop function f2;
337 create function f2 (x varchar(100)) returns int deterministic
338 begin
339 declare y int;
340 insert into t1 values(null,x);
341 set y = (select count(*) from t2);
342 return y;
343 end|
344 select f1("try_53_"),f2("try_54_");
345 f1("try_53_") f2("try_54_")
346 1 3
347 connection slave;
348 connection master;
349 drop function f2;
350 create trigger t1_bi before insert on t1 for each row
351 begin
352 insert into t2 values(null,"try_55_");
353 end|
354 insert into t1 values(null,"try_56_");
355 alter table t1 modify a int, drop primary key;
356 insert into t1 values(null,"try_57_");
357 connection slave;
358 connection master;
359 CREATE TEMPORARY TABLE t15 SELECT SYS_GUID();
360 create table t16 like t15;
361 INSERT INTO t16 SELECT * FROM t15;
362 insert into t16 values("try_65_");
363 drop table t15;
364 insert into t16 values("try_66_");
365 connection slave;
366 connection master;
367 select count(*) from t1;
368 count(*)
369 7
370 select count(*) from t2;
371 count(*)
372 5
373 select count(*) from t3;
374 count(*)
375 1
376 select count(*) from t4;
377 count(*)
378 29
379 select count(*) from t5;
380 count(*)
381 58
382 select count(*) from t11;
383 count(*)
384 8
385 select count(*) from t20;
386 count(*)
387 66
388 select count(*) from t21;
389 count(*)
390 19
391 select count(*) from t22;
392 count(*)
393 2
394 select count(*) from t12;
395 count(*)
396 4
397 select count(*) from t13;
398 count(*)
399 1
400 select count(*) from t14;
401 count(*)
402 4
403 select count(*) from t16;
404 count(*)
405 3
406 connection slave;
407 connection master;
408 DROP TABLE IF EXISTS t11;
409 SET SESSION BINLOG_FORMAT=STATEMENT;
410 CREATE TABLE t11 (song VARCHAR(255));
411 LOCK TABLES t11 WRITE;
412 SET SESSION BINLOG_FORMAT=ROW;
413 INSERT INTO t11 VALUES('Several Species of Small Furry Animals Gathered Together in a Cave and Grooving With a Pict');
414 SET SESSION BINLOG_FORMAT=STATEMENT;
415 INSERT INTO t11 VALUES('Careful With That Axe, Eugene');
416 UNLOCK TABLES;
417 SELECT * FROM t11;
418 song Several Species of Small Furry Animals Gathered Together in a Cave and Grooving With a Pict
419 song Careful With That Axe, Eugene
420 connection slave;
421 USE mysqltest1;
422 SELECT * FROM t11;
423 song Several Species of Small Furry Animals Gathered Together in a Cave and Grooving With a Pict
424 song Careful With That Axe, Eugene
425 connection master;
426 DROP TABLE IF EXISTS t12;
427 SET SESSION BINLOG_FORMAT=MIXED;
428 CREATE TABLE t12 (data LONG);
429 LOCK TABLES t12 WRITE;
430 INSERT INTO t12 VALUES(SYS_GUID());
431 UNLOCK TABLES;
432 connection slave;
433 connection master;
434 CREATE FUNCTION my_user()
435 RETURNS CHAR(64)
436 BEGIN
437 DECLARE user CHAR(64);
438 SELECT USER() INTO user;
439 RETURN user;
440 END $$
441 CREATE FUNCTION my_current_user()
442 RETURNS CHAR(64)
443 BEGIN
444 DECLARE user CHAR(64);
445 SELECT CURRENT_USER() INTO user;
446 RETURN user;
447 END $$
448 DROP TABLE IF EXISTS t13;
449 CREATE TABLE t13 (data CHAR(64));
450 INSERT INTO t13 VALUES (USER());
451 INSERT INTO t13 VALUES (my_user());
452 INSERT INTO t13 VALUES (CURRENT_USER());
453 INSERT INTO t13 VALUES (my_current_user());
454 connection slave;
455 connection master;
456 drop database mysqltest1;
457 connection slave;
458 connection master;
459 set global binlog_format =@my_binlog_format;
460 include/rpl_end.inc