2
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
5
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
7
drop database if exists mysqltest1;
8
create database mysqltest1;
10
set @my_binlog_format= @@global.binlog_format;
11
set session binlog_format=mixed;
12
show session variables like "binlog_format%";
15
set session binlog_format=statement;
16
show session variables like "binlog_format%";
18
binlog_format STATEMENT
19
set session binlog_format=row;
20
show session variables like "binlog_format%";
23
set global binlog_format=DEFAULT;
24
show global variables like "binlog_format%";
27
set global binlog_format=MIXED;
28
show global variables like "binlog_format%";
31
set global binlog_format=STATEMENT;
32
show global variables like "binlog_format%";
34
binlog_format STATEMENT
35
set global binlog_format=ROW;
36
show global variables like "binlog_format%";
39
show session variables like "binlog_format%";
42
select @@global.binlog_format, @@session.binlog_format;
43
@@global.binlog_format @@session.binlog_format
45
CREATE TABLE t1 (a varchar(100));
46
prepare stmt1 from 'insert into t1 select concat(UUID(),?)';
47
set @string="emergency_1_";
48
insert into t1 values("work_2_");
49
execute stmt1 using @string;
50
deallocate prepare stmt1;
51
prepare stmt1 from 'insert into t1 select ?';
52
insert into t1 values(concat(UUID(),"work_3_"));
53
execute stmt1 using @string;
54
deallocate prepare stmt1;
55
insert into t1 values(concat("for_4_",UUID()));
56
insert into t1 select "yesterday_5_";
57
create temporary table tmp(a char(100));
58
insert into tmp values("see_6_");
59
set binlog_format=statement;
60
ERROR HY000: Cannot switch out of the row-based binary log format when the session has open temporary tables
61
insert into t1 select * from tmp;
62
drop temporary table tmp;
63
set binlog_format=statement;
64
show global variables like "binlog_format%";
67
show session variables like "binlog_format%";
69
binlog_format STATEMENT
70
select @@global.binlog_format, @@session.binlog_format;
71
@@global.binlog_format @@session.binlog_format
73
set global binlog_format=statement;
74
show global variables like "binlog_format%";
76
binlog_format STATEMENT
77
show session variables like "binlog_format%";
79
binlog_format STATEMENT
80
select @@global.binlog_format, @@session.binlog_format;
81
@@global.binlog_format @@session.binlog_format
83
prepare stmt1 from 'insert into t1 select ?';
84
set @string="emergency_7_";
85
insert into t1 values("work_8_");
86
execute stmt1 using @string;
87
deallocate prepare stmt1;
88
prepare stmt1 from 'insert into t1 select ?';
89
insert into t1 values("work_9_");
90
execute stmt1 using @string;
91
deallocate prepare stmt1;
92
insert into t1 values("for_10_");
93
insert into t1 select "yesterday_11_";
94
set binlog_format=statement;
95
select @@global.binlog_format, @@session.binlog_format;
96
@@global.binlog_format @@session.binlog_format
98
set global binlog_format=statement;
99
select @@global.binlog_format, @@session.binlog_format;
100
@@global.binlog_format @@session.binlog_format
102
prepare stmt1 from 'insert into t1 select ?';
103
set @string="emergency_12_";
104
insert into t1 values("work_13_");
105
execute stmt1 using @string;
106
deallocate prepare stmt1;
107
prepare stmt1 from 'insert into t1 select ?';
108
insert into t1 values("work_14_");
109
execute stmt1 using @string;
110
deallocate prepare stmt1;
111
insert into t1 values("for_15_");
112
insert into t1 select "yesterday_16_";
113
set global binlog_format=mixed;
114
select @@global.binlog_format, @@session.binlog_format;
115
@@global.binlog_format @@session.binlog_format
117
set binlog_format=default;
118
select @@global.binlog_format, @@session.binlog_format;
119
@@global.binlog_format @@session.binlog_format
121
prepare stmt1 from 'insert into t1 select concat(UUID(),?)';
122
set @string="emergency_17_";
123
insert into t1 values("work_18_");
124
execute stmt1 using @string;
125
deallocate prepare stmt1;
126
prepare stmt1 from 'insert into t1 select ?';
127
insert into t1 values(concat(UUID(),"work_19_"));
128
execute stmt1 using @string;
129
deallocate prepare stmt1;
130
insert into t1 values(concat("for_20_",UUID()));
131
insert into t1 select "yesterday_21_";
132
prepare stmt1 from 'insert into t1 select ?';
133
insert into t1 values(concat(UUID(),"work_22_"));
134
execute stmt1 using @string;
135
deallocate prepare stmt1;
136
insert into t1 values(concat("for_23_",UUID()));
137
insert into t1 select "yesterday_24_";
138
create table t2 select rpad(UUID(),100,' ');
139
create table t3 select 1 union select UUID();
140
create table t4 select * from t1 where 3 in (select 1 union select 2 union select UUID() union select 3);
141
create table t5 select * from t1 where 3 in (select 1 union select 2 union select curdate() union select 3);
142
insert into t5 select UUID() from t1 where 3 in (select 1 union select 2 union select 3 union select * from t4);
143
create procedure foo()
145
insert into t1 values("work_25_");
146
insert into t1 values(concat("for_26_",UUID()));
147
insert into t1 select "yesterday_27_";
149
create procedure foo2()
151
insert into t1 values(concat("emergency_28_",UUID()));
152
insert into t1 values("work_29_");
153
insert into t1 values(concat("for_30_",UUID()));
154
set session binlog_format=row; # accepted for stored procs
155
insert into t1 values("more work_31_");
156
set session binlog_format=mixed;
158
create function foo3() returns bigint unsigned
160
set session binlog_format=row; # rejected for stored funcs
161
insert into t1 values("alarm");
164
create procedure foo4(x varchar(100))
166
insert into t1 values(concat("work_250_",x));
167
insert into t1 select "yesterday_270_";
175
ERROR HY000: Cannot change the binary logging format inside a stored function or trigger
176
select * from t1 where a="alarm";
179
create function foo3() returns bigint unsigned
181
insert into t1 values("foo3_32_");
185
insert into t2 select foo3();
186
prepare stmt1 from 'insert into t2 select foo3()';
189
deallocate prepare stmt1;
190
create function foo4() returns bigint unsigned
192
insert into t2 select foo3();
198
prepare stmt1 from 'select foo4()';
205
deallocate prepare stmt1;
206
create function foo5() returns bigint unsigned
208
insert into t2 select UUID();
214
prepare stmt1 from 'select foo5()';
221
deallocate prepare stmt1;
222
create function foo6(x varchar(100)) returns bigint unsigned
224
insert into t2 select x;
227
select foo6("foo6_1_");
230
select foo6(concat("foo6_2_",UUID()));
231
foo6(concat("foo6_2_",UUID()))
233
prepare stmt1 from 'select foo6(concat("foo6_3_",UUID()))';
235
foo6(concat("foo6_3_",UUID()))
238
foo6(concat("foo6_3_",UUID()))
240
deallocate prepare stmt1;
241
create view v1 as select uuid();
242
create table t11 (data varchar(255));
243
insert into t11 select * from v1;
244
insert into t11 select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysqltest1' and TABLE_NAME IN ('v1','t11');
245
prepare stmt1 from "insert into t11 select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysqltest1' and TABLE_NAME IN ('v1','t11')";
248
deallocate prepare stmt1;
249
create trigger t11_bi before insert on t11 for each row
251
set NEW.data = concat(NEW.data,UUID());
253
insert into t11 values("try_560_");
254
insert delayed into t2 values("delay_1_");
255
insert delayed into t2 values(concat("delay_2_",UUID()));
256
insert delayed into t2 values("delay_6_");
257
insert delayed into t2 values(rand());
259
insert delayed into t2 values(@a);
260
create table t20 select * from t1;
261
create table t21 select * from t2;
262
create table t22 select * from t3;
264
create table t1 (a int primary key auto_increment, b varchar(100));
265
create table t2 (a int primary key auto_increment, b varchar(100));
266
create table t3 (b varchar(100));
267
create function f (x varchar(100)) returns int deterministic
269
insert into t1 values(null,x);
270
insert into t2 values(null,x);
277
insert into t2 values(2,null),(3,null),(4,null);
278
delete from t2 where a>=2;
282
insert into t2 values(3,null),(4,null);
283
delete from t2 where a>=3;
284
prepare stmt1 from 'select f(?)';
285
set @string="try_43_";
286
insert into t1 values(null,"try_44_");
287
execute stmt1 using @string;
290
deallocate prepare stmt1;
291
create table t12 select * from t1;
293
create table t1 (a int, b varchar(100), key(a));
297
create table t13 select * from t1;
299
create table t1 (a int primary key auto_increment, b varchar(100));
301
create table t14 (unique (a)) select * from t2;
303
create function f1 (x varchar(100)) returns int deterministic
305
insert into t1 values(null,x);
308
create function f2 (x varchar(100)) returns int deterministic
310
insert into t2 values(null,x);
313
select f1("try_46_"),f2("try_47_");
314
f1("try_46_") f2("try_47_")
316
insert into t2 values(2,null),(3,null),(4,null);
317
delete from t2 where a>=2;
318
select f1("try_48_"),f2("try_49_");
319
f1("try_48_") f2("try_49_")
321
insert into t3 values(concat("try_50_",f1("try_51_"),f2("try_52_")));
323
create function f2 (x varchar(100)) returns int deterministic
326
insert into t1 values(null,x);
327
set y = (select count(*) from t2);
330
select f1("try_53_"),f2("try_54_");
331
f1("try_53_") f2("try_54_")
334
create trigger t1_bi before insert on t1 for each row
336
insert into t2 values(null,"try_55_");
338
insert into t1 values(null,"try_56_");
339
alter table t1 modify a int, drop primary key;
340
insert into t1 values(null,"try_57_");
341
CREATE TEMPORARY TABLE t15 SELECT UUID();
342
create table t16 like t15;
343
INSERT INTO t16 SELECT * FROM t15;
344
insert into t16 values("try_65_");
346
insert into t16 values("try_66_");
347
select count(*) from t1;
350
select count(*) from t2;
353
select count(*) from t3;
356
select count(*) from t4;
359
select count(*) from t5;
362
select count(*) from t11;
365
select count(*) from t20;
368
select count(*) from t21;
371
select count(*) from t22;
374
select count(*) from t12;
377
select count(*) from t13;
380
select count(*) from t14;
383
select count(*) from t16;
386
DROP TABLE IF EXISTS t11;
387
SET SESSION BINLOG_FORMAT=STATEMENT;
388
CREATE TABLE t11 (song VARCHAR(255));
389
LOCK TABLES t11 WRITE;
390
SET SESSION BINLOG_FORMAT=ROW;
391
INSERT INTO t11 VALUES('Several Species of Small Furry Animals Gathered Together in a Cave and Grooving With a Pict');
392
SET SESSION BINLOG_FORMAT=STATEMENT;
393
INSERT INTO t11 VALUES('Careful With That Axe, Eugene');
396
song Several Species of Small Furry Animals Gathered Together in a Cave and Grooving With a Pict
397
song Careful With That Axe, Eugene
400
song Several Species of Small Furry Animals Gathered Together in a Cave and Grooving With a Pict
401
song Careful With That Axe, Eugene
402
DROP TABLE IF EXISTS t12;
403
SET SESSION BINLOG_FORMAT=MIXED;
404
CREATE TABLE t12 (data LONG);
405
LOCK TABLES t12 WRITE;
406
INSERT INTO t12 VALUES(UUID());
408
CREATE FUNCTION my_user()
411
DECLARE user CHAR(64);
412
SELECT USER() INTO user;
415
CREATE FUNCTION my_current_user()
418
DECLARE user CHAR(64);
419
SELECT CURRENT_USER() INTO user;
422
DROP TABLE IF EXISTS t13;
423
CREATE TABLE t13 (data CHAR(64));
424
INSERT INTO t13 VALUES (USER());
425
INSERT INTO t13 VALUES (my_user());
426
INSERT INTO t13 VALUES (CURRENT_USER());
427
INSERT INTO t13 VALUES (my_current_user());
428
drop database mysqltest1;
429
set global binlog_format =@my_binlog_format;