2
# rpl_switch_stm_row_mixed tests covers
4
# - switching explicitly between STATEMENT, ROW, and MIXED binlog format
5
# showing when it is possible and when not.
6
# - switching from MIXED to RBR implicitly listing all use cases,
7
# e.g a query invokes UUID(), thereafter to serve as the definition
8
# of MIXED binlog format
9
# - correctness of execution
12
-- source include/not_ndb_default.inc
13
-- source include/master-slave.inc
17
drop database if exists mysqltest1;
18
create database mysqltest1;
23
set @my_binlog_format= @@global.binlog_format;
26
set session binlog_format=mixed;
27
show session variables like "binlog_format%";
28
set session binlog_format=statement;
29
show session variables like "binlog_format%";
30
set session binlog_format=row;
31
show session variables like "binlog_format%";
33
set global binlog_format=DEFAULT;
34
show global variables like "binlog_format%";
35
set global binlog_format=MIXED;
36
show global variables like "binlog_format%";
37
set global binlog_format=STATEMENT;
38
show global variables like "binlog_format%";
39
set global binlog_format=ROW;
40
show global variables like "binlog_format%";
41
show session variables like "binlog_format%";
42
select @@global.binlog_format, @@session.binlog_format;
44
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;
52
prepare stmt1 from 'insert into t1 select ?';
53
insert into t1 values(concat(UUID(),"work_3_"));
54
execute stmt1 using @string;
55
deallocate prepare stmt1;
57
insert into t1 values(concat("for_4_",UUID()));
58
insert into t1 select "yesterday_5_";
60
# verify that temp tables prevent a switch to SBR
61
create temporary table tmp(a char(100));
62
insert into tmp values("see_6_");
63
--error ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR
64
set binlog_format=statement;
65
insert into t1 select * from tmp;
66
drop temporary table tmp;
69
set binlog_format=statement;
70
show global variables like "binlog_format%";
71
show session variables like "binlog_format%";
72
select @@global.binlog_format, @@session.binlog_format;
73
set global binlog_format=statement;
74
show global variables like "binlog_format%";
75
show session variables like "binlog_format%";
76
select @@global.binlog_format, @@session.binlog_format;
78
prepare stmt1 from 'insert into t1 select ?';
79
set @string="emergency_7_";
80
insert into t1 values("work_8_");
81
execute stmt1 using @string;
82
deallocate prepare stmt1;
84
prepare stmt1 from 'insert into t1 select ?';
85
insert into t1 values("work_9_");
86
execute stmt1 using @string;
87
deallocate prepare stmt1;
89
insert into t1 values("for_10_");
90
insert into t1 select "yesterday_11_";
92
# test statement (is not default after wl#3368)
93
set binlog_format=statement;
94
select @@global.binlog_format, @@session.binlog_format;
95
set global binlog_format=statement;
96
select @@global.binlog_format, @@session.binlog_format;
98
prepare stmt1 from 'insert into t1 select ?';
99
set @string="emergency_12_";
100
insert into t1 values("work_13_");
101
execute stmt1 using @string;
102
deallocate prepare stmt1;
104
prepare stmt1 from 'insert into t1 select ?';
105
insert into t1 values("work_14_");
106
execute stmt1 using @string;
107
deallocate prepare stmt1;
109
insert into t1 values("for_15_");
110
insert into t1 select "yesterday_16_";
112
# and now the mixed mode
114
set global binlog_format=mixed;
115
select @@global.binlog_format, @@session.binlog_format;
116
set binlog_format=default;
117
select @@global.binlog_format, @@session.binlog_format;
119
prepare stmt1 from 'insert into t1 select concat(UUID(),?)';
120
set @string="emergency_17_";
121
insert into t1 values("work_18_");
122
execute stmt1 using @string;
123
deallocate prepare stmt1;
125
prepare stmt1 from 'insert into t1 select ?';
126
insert into t1 values(concat(UUID(),"work_19_"));
127
execute stmt1 using @string;
128
deallocate prepare stmt1;
130
insert into t1 values(concat("for_20_",UUID()));
131
insert into t1 select "yesterday_21_";
133
prepare stmt1 from 'insert into t1 select ?';
134
insert into t1 values(concat(UUID(),"work_22_"));
135
execute stmt1 using @string;
136
deallocate prepare stmt1;
138
insert into t1 values(concat("for_23_",UUID()));
139
insert into t1 select "yesterday_24_";
141
# Test of CREATE TABLE SELECT
143
create table t2 select rpad(UUID(),100,' ');
144
create table t3 select 1 union select UUID();
145
create table t4 select * from t1 where 3 in (select 1 union select 2 union select UUID() union select 3);
146
create table t5 select * from t1 where 3 in (select 1 union select 2 union select curdate() union select 3);
147
# what if UUID() is first:
148
insert into t5 select UUID() from t1 where 3 in (select 1 union select 2 union select 3 union select * from t4);
150
# inside a stored procedure
153
create procedure foo()
155
insert into t1 values("work_25_");
156
insert into t1 values(concat("for_26_",UUID()));
157
insert into t1 select "yesterday_27_";
159
create procedure foo2()
161
insert into t1 values(concat("emergency_28_",UUID()));
162
insert into t1 values("work_29_");
163
insert into t1 values(concat("for_30_",UUID()));
164
set session binlog_format=row; # accepted for stored procs
165
insert into t1 values("more work_31_");
166
set session binlog_format=mixed;
168
create function foo3() returns bigint unsigned
170
set session binlog_format=row; # rejected for stored funcs
171
insert into t1 values("alarm");
174
create procedure foo4(x varchar(100))
176
insert into t1 values(concat("work_250_",x));
177
insert into t1 select "yesterday_270_";
186
# test that can't SET in a stored function
187
--error ER_STORED_FUNCTION_PREVENTS_SWITCH_BINLOG_FORMAT
189
select * from t1 where a="alarm";
191
# Tests of stored functions/triggers/views for BUG#20930 "Mixed
192
# binlogging mode does not work with stored functions, triggers,
195
# Function which calls procedure
198
create function foo3() returns bigint unsigned
200
insert into t1 values("foo3_32_");
205
insert into t2 select foo3();
207
prepare stmt1 from 'insert into t2 select foo3()';
210
deallocate prepare stmt1;
212
# Test if stored function calls stored function which calls procedure
213
# which requires row-based.
216
create function foo4() returns bigint unsigned
218
insert into t2 select foo3();
224
prepare stmt1 from 'select foo4()';
227
deallocate prepare stmt1;
229
# A simple stored function
231
create function foo5() returns bigint unsigned
233
insert into t2 select UUID();
239
prepare stmt1 from 'select foo5()';
242
deallocate prepare stmt1;
244
# A simple stored function where UUID() is in the argument
246
create function foo6(x varchar(100)) returns bigint unsigned
248
insert into t2 select x;
252
select foo6("foo6_1_");
253
select foo6(concat("foo6_2_",UUID()));
255
prepare stmt1 from 'select foo6(concat("foo6_3_",UUID()))';
258
deallocate prepare stmt1;
261
# Test of views using UUID()
263
create view v1 as select uuid();
264
create table t11 (data varchar(255));
265
insert into t11 select * from v1;
266
# Test of querying INFORMATION_SCHEMA which parses the view's body,
267
# to verify that it binlogs statement-based (is not polluted by
268
# the parsing of the view's body).
269
insert into t11 select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysqltest1' and TABLE_NAME IN ('v1','t11');
270
prepare stmt1 from "insert into t11 select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysqltest1' and TABLE_NAME IN ('v1','t11')";
273
deallocate prepare stmt1;
275
# Test of triggers with UUID()
277
create trigger t11_bi before insert on t11 for each row
279
set NEW.data = concat(NEW.data,UUID());
282
insert into t11 values("try_560_");
284
# Test that INSERT DELAYED works in mixed mode (BUG#20649)
285
insert delayed into t2 values("delay_1_");
286
insert delayed into t2 values(concat("delay_2_",UUID()));
287
insert delayed into t2 values("delay_6_");
289
# Test for BUG#20633 (INSERT DELAYED RAND()/user_variable does not
290
# replicate fine in statement-based ; we test that in mixed mode it
292
insert delayed into t2 values(rand());
294
insert delayed into t2 values(@a);
296
let $wait_condition= SELECT COUNT(*) = 19 FROM t2;
297
--source include/wait_condition.inc
299
# If you want to do manual testing of the mixed mode regarding UDFs (not
300
# testable automatically as quite platform- and compiler-dependent),
301
# you just need to set the variable below to 1, and to
302
# "make udf_example.so" in sql/, and to copy sql/udf_example.so to
303
# MYSQL_TEST_DIR/lib/mysql.
304
let $you_want_to_test_UDF=0;
305
if ($you_want_to_test_UDF)
307
CREATE FUNCTION metaphon RETURNS STRING SONAME 'udf_example.so';
308
prepare stmt1 from 'insert into t1 select metaphon(?)';
309
set @string="emergency_133_";
310
insert into t1 values("work_134_");
311
execute stmt1 using @string;
312
deallocate prepare stmt1;
313
prepare stmt1 from 'insert into t1 select ?';
314
insert into t1 values(metaphon("work_135_"));
315
execute stmt1 using @string;
316
deallocate prepare stmt1;
317
insert into t1 values(metaphon("for_136_"));
318
insert into t1 select "yesterday_137_";
319
create table t6 select metaphon("for_138_");
320
create table t7 select 1 union select metaphon("for_139_");
321
create table t8 select * from t1 where 3 in (select 1 union select 2 union select metaphon("for_140_") union select 3);
322
create table t9 select * from t1 where 3 in (select 1 union select 2 union select curdate() union select 3);
325
create table t20 select * from t1; # save for comparing later
326
create table t21 select * from t2;
327
create table t22 select * from t3;
330
# This tests the fix to
331
# BUG#19630 stored function inserting into two auto_increment breaks statement-based binlog
332
# We verify that under the mixed binlog mode, a stored function
333
# modifying at least two tables having an auto_increment column,
334
# is binlogged row-based. Indeed in statement-based binlogging,
335
# only the auto_increment value generated for the first table
336
# is recorded in the binlog, the value generated for the 2nd table
339
create table t1 (a int primary key auto_increment, b varchar(100));
340
create table t2 (a int primary key auto_increment, b varchar(100));
341
create table t3 (b varchar(100));
343
create function f (x varchar(100)) returns int deterministic
345
insert into t1 values(null,x);
346
insert into t2 values(null,x);
351
# Two operations which compensate each other except that their net
352
# effect is that they advance the auto_increment counter of t2 on slave:
353
sync_slave_with_master;
355
insert into t2 values(2,null),(3,null),(4,null);
356
delete from t2 where a>=2;
359
# this is the call which didn't replicate well
361
sync_slave_with_master;
363
# now use prepared statement and test again, just to see that the RBB
364
# mode isn't set at PREPARE but at EXECUTE.
366
insert into t2 values(3,null),(4,null);
367
delete from t2 where a>=3;
370
prepare stmt1 from 'select f(?)';
371
set @string="try_43_";
372
insert into t1 values(null,"try_44_"); # should be SBB
373
execute stmt1 using @string; # should be RBB
374
deallocate prepare stmt1;
375
sync_slave_with_master;
377
# verify that if only one table has auto_inc, it does not trigger RBB
378
# (we'll check in binlog further below)
381
create table t12 select * from t1; # save for comparing later
383
create table t1 (a int, b varchar(100), key(a));
386
# restore table's key
387
create table t13 select * from t1;
389
create table t1 (a int primary key auto_increment, b varchar(100));
391
# now test if it's two functions, each of them inserts in one table
394
# we need a unique key to have sorting of rows by mysqldump
395
create table t14 (unique (a)) select * from t2;
398
create function f1 (x varchar(100)) returns int deterministic
400
insert into t1 values(null,x);
403
create function f2 (x varchar(100)) returns int deterministic
405
insert into t2 values(null,x);
409
select f1("try_46_"),f2("try_47_");
411
sync_slave_with_master;
412
insert into t2 values(2,null),(3,null),(4,null);
413
delete from t2 where a>=2;
416
# Test with SELECT and INSERT
417
select f1("try_48_"),f2("try_49_");
418
insert into t3 values(concat("try_50_",f1("try_51_"),f2("try_52_")));
419
sync_slave_with_master;
421
# verify that if f2 does only read on an auto_inc table, this does not
426
create function f2 (x varchar(100)) returns int deterministic
429
insert into t1 values(null,x);
430
set y = (select count(*) from t2);
434
select f1("try_53_"),f2("try_54_");
435
sync_slave_with_master;
437
# And now, a normal statement with a trigger (no stored functions)
442
create trigger t1_bi before insert on t1 for each row
444
insert into t2 values(null,"try_55_");
447
insert into t1 values(null,"try_56_");
448
# and now remove one auto_increment and verify SBB
449
alter table t1 modify a int, drop primary key;
450
insert into t1 values(null,"try_57_");
451
sync_slave_with_master;
453
# Test for BUG#20499 "mixed mode with temporary table breaks binlog"
454
# Slave used to have only 2 rows instead of 3.
456
CREATE TEMPORARY TABLE t15 SELECT UUID();
457
create table t16 like t15;
458
INSERT INTO t16 SELECT * FROM t15;
459
# we'll verify that this one is done RBB
460
insert into t16 values("try_65_");
462
# we'll verify that this one is done SBB
463
insert into t16 values("try_66_");
464
sync_slave_with_master;
470
# first check that data on master is sensible
471
select count(*) from t1;
472
select count(*) from t2;
473
select count(*) from t3;
474
select count(*) from t4;
475
select count(*) from t5;
476
select count(*) from t11;
477
select count(*) from t20;
478
select count(*) from t21;
479
select count(*) from t22;
480
select count(*) from t12;
481
select count(*) from t13;
482
select count(*) from t14;
483
select count(*) from t16;
484
if ($you_want_to_test_UDF)
486
select count(*) from t6;
487
select count(*) from t7;
488
select count(*) from t8;
489
select count(*) from t9;
492
sync_slave_with_master;
495
# Bug#20863 If binlog format is changed between update and unlock of
496
# tables, wrong binlog
500
DROP TABLE IF EXISTS t11;
501
SET SESSION BINLOG_FORMAT=STATEMENT;
502
CREATE TABLE t11 (song VARCHAR(255));
503
LOCK TABLES t11 WRITE;
504
SET SESSION BINLOG_FORMAT=ROW;
505
INSERT INTO t11 VALUES('Several Species of Small Furry Animals Gathered Together in a Cave and Grooving With a Pict');
506
SET SESSION BINLOG_FORMAT=STATEMENT;
507
INSERT INTO t11 VALUES('Careful With That Axe, Eugene');
510
--query_vertical SELECT * FROM t11
511
sync_slave_with_master;
513
--query_vertical SELECT * FROM t11
516
DROP TABLE IF EXISTS t12;
517
SET SESSION BINLOG_FORMAT=MIXED;
518
CREATE TABLE t12 (data LONG);
519
LOCK TABLES t12 WRITE;
520
INSERT INTO t12 VALUES(UUID());
522
sync_slave_with_master;
525
# BUG#28086: SBR of USER() becomes corrupted on slave
530
# Just to get something that is non-trivial, albeit still simple, we
531
# stuff the result of USER() and CURRENT_USER() into a variable.
533
CREATE FUNCTION my_user()
536
DECLARE user CHAR(64);
537
SELECT USER() INTO user;
543
CREATE FUNCTION my_current_user()
546
DECLARE user CHAR(64);
547
SELECT CURRENT_USER() INTO user;
552
DROP TABLE IF EXISTS t13;
553
CREATE TABLE t13 (data CHAR(64));
554
INSERT INTO t13 VALUES (USER());
555
INSERT INTO t13 VALUES (my_user());
556
INSERT INTO t13 VALUES (CURRENT_USER());
557
INSERT INTO t13 VALUES (my_current_user());
559
sync_slave_with_master;
561
# as we're using UUID we don't SELECT but use "diff" like in rpl_row_UUID
562
--exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info mysqltest1 > $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql
563
--exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info mysqltest1 > $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_slave.sql
565
# Let's compare. Note: If they match test will pass, if they do not match
566
# the test will show that the diff statement failed and not reject file
567
# will be created. You will need to go to the mysql-test dir and diff
568
# the files your self to see what is not matching
570
diff_files $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_slave.sql;
574
# Now test that mysqlbinlog works fine on a binlog generated by the
577
# BUG#11312 "DELIMITER is not written to the binary log that causes
578
# syntax error" makes that mysqlbinlog will fail if we pass it the
579
# text of queries; this forces us to use --base64-output here.
581
# BUG#20929 "BINLOG command causes invalid free plus assertion
582
# failure" makes mysqld segfault when receiving --base64-output
584
# So I can't enable this piece of test
587
if ($enable_when_11312_or_20929_fixed)
589
--exec $MYSQL_BINLOG --base64-output $MYSQLTEST_VARDIR/log/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_mixed.sql
590
drop database mysqltest1;
591
--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/mysqlbinlog_mixed.sql
592
--exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info mysqltest1 > $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql
593
# the old mysqldump output on slave is the same as what it was on
594
# master before restoring on master.
595
diff_files $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_slave.sql;
598
drop database mysqltest1;
599
sync_slave_with_master;
602
# Restore binlog format setting
603
set global binlog_format =@my_binlog_format;