path: root/mysql-test/suite/rpl/t/rpl_switch_stm_row_mixed.test

#
# This include file is used by more than one test suite
# (currently rpl and binlog_encryption).
# Please check all dependent tests after modifying it
#

#
# rpl_switch_stm_row_mixed tests covers
#
#   - Master is switching explicitly between STATEMENT, ROW, and MIXED
#     binlog format showing when it is possible and when not. 
#   - Master switching from MIXED to RBR implicitly listing all use
#     cases, e.g a query invokes SYS_GUID(), thereafter to serve as the
#     definition of MIXED binlog format
#   - correctness of execution


-- source include/have_binlog_format_mixed_or_row.inc
-- source include/master-slave.inc

# Since this test generates row-based events in the binary log, the
# slave SQL thread cannot be in STATEMENT mode to execute this test,
# so we only execute it for MIXED and ROW as default value of
# BINLOG_FORMAT.

connection slave;

connection master;
--disable_warnings
drop database if exists mysqltest1;
create database mysqltest1;
--enable_warnings
use mysqltest1;

# Save binlog format
set @my_binlog_format= @@global.binlog_format;

#  play with switching
set session binlog_format=mixed;
show session variables like "binlog_format%";
set session binlog_format=statement;
show session variables like "binlog_format%";
set session binlog_format=row;
show session variables like "binlog_format%";

set global binlog_format=DEFAULT;
show global variables like "binlog_format%";
set global binlog_format=MIXED;
show global variables like "binlog_format%";
set global binlog_format=STATEMENT;
show global variables like "binlog_format%";
set global binlog_format=ROW;
show global variables like "binlog_format%";
show session variables like "binlog_format%";
select @@global.binlog_format, @@session.binlog_format;

CREATE TABLE t1 (a varchar(100));

prepare stmt1 from 'insert into t1 select concat(SYS_GUID(),?)';
set @string="emergency_1_";
insert into t1 values("work_2_");
execute stmt1 using @string;
deallocate prepare stmt1;

prepare stmt1 from 'insert into t1 select ?';
insert into t1 values(concat(SYS_GUID(),"work_3_"));
execute stmt1 using @string;
deallocate prepare stmt1;

insert into t1 values(concat("for_4_",SYS_GUID()));
insert into t1 select "yesterday_5_";

# verify that temp tables prevent a switch to SBR
create temporary table tmp(a char(100));
insert into tmp values("see_6_");
--error ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR
set binlog_format=statement;
insert into t1 select * from tmp;
drop temporary table tmp;

# Now we go to SBR
set binlog_format=statement;
show global variables like "binlog_format%";
show session variables like "binlog_format%";
select @@global.binlog_format, @@session.binlog_format;
set global binlog_format=statement;
show global variables like "binlog_format%";
show session variables like "binlog_format%";
select @@global.binlog_format, @@session.binlog_format;

prepare stmt1 from 'insert into t1 select ?';
set @string="emergency_7_";
insert into t1 values("work_8_");
execute stmt1 using @string;
deallocate prepare stmt1;

prepare stmt1 from 'insert into t1 select ?';
insert into t1 values("work_9_");
execute stmt1 using @string;
deallocate prepare stmt1;

insert into t1 values("for_10_");
insert into t1 select "yesterday_11_";

# test statement (is not default after wl#3368)
set binlog_format=statement;
select @@global.binlog_format, @@session.binlog_format;
set global binlog_format=statement;
select @@global.binlog_format, @@session.binlog_format;

prepare stmt1 from 'insert into t1 select ?';
set @string="emergency_12_";
insert into t1 values("work_13_");
execute stmt1 using @string;
deallocate prepare stmt1;

prepare stmt1 from 'insert into t1 select ?';
insert into t1 values("work_14_");
execute stmt1 using @string;
deallocate prepare stmt1;

insert into t1 values("for_15_");
insert into t1 select "yesterday_16_";

# and now the mixed mode

set global binlog_format=mixed;
select @@global.binlog_format, @@session.binlog_format;
set binlog_format=default;
select @@global.binlog_format, @@session.binlog_format;

prepare stmt1 from 'insert into t1 select concat(SYS_GUID(),?)';
set @string="emergency_17_";
insert into t1 values("work_18_");
execute stmt1 using @string;
deallocate prepare stmt1;

prepare stmt1 from 'insert into t1 select ?';
insert into t1 values(concat(SYS_GUID(),"work_19_"));
execute stmt1 using @string;
deallocate prepare stmt1;

insert into t1 values(concat("for_20_",SYS_GUID()));
insert into t1 select "yesterday_21_";

prepare stmt1 from 'insert into t1 select ?';
insert into t1 values(concat(SYS_GUID(),"work_22_"));
execute stmt1 using @string;
deallocate prepare stmt1;

insert into t1 values(concat("for_23_",SYS_GUID()));
insert into t1 select "yesterday_24_";

# Test of CREATE TABLE SELECT

create table t2 ENGINE=MyISAM select rpad(SYS_GUID(),100,' ');
create table t3 select 1 union select SYS_GUID();
--disable_warnings
SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
create table t4 select * from t1 where 3 in (select 1 union select 2 union select SYS_GUID() union select 3);
--enable_warnings
SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
create table t5 select * from t1 where 3 in (select 1 union select 2 union select curdate() union select 3);
# what if SYS_GUID() is first:
--disable_warnings
insert ignore into t5 select SYS_GUID() from t1 where 3 in (select 1 union select 2 union select 3 union select * from t4);
--enable_warnings

# inside a stored procedure

delimiter |;
create procedure foo()
begin
insert into t1 values("work_25_");
insert into t1 values(concat("for_26_",SYS_GUID()));
insert into t1 select "yesterday_27_";
end|
create procedure foo2()
begin
insert into t1 values(concat("emergency_28_",SYS_GUID()));
insert into t1 values("work_29_");
insert into t1 values(concat("for_30_",SYS_GUID()));
set session binlog_format=row; # accepted for stored procs
insert into t1 values("more work_31_");
set session binlog_format=mixed;
end|
create function foo3() returns bigint unsigned
begin
  set session binlog_format=row; # rejected for stored funcs
  insert into t1 values("alarm");
  return 100;
end|
create procedure foo4(x varchar(100))
begin
insert into t1 values(concat("work_250_",x));
insert into t1 select "yesterday_270_";
end|
delimiter ;|
call foo();
call foo2();
call foo4("hello");
call foo4(SYS_GUID());
call foo4("world");

# test that can't SET in a stored function
--error ER_STORED_FUNCTION_PREVENTS_SWITCH_BINLOG_FORMAT
select foo3();
select * from t1 where a="alarm";

# Tests of stored functions/triggers/views for BUG#20930 "Mixed
# binlogging mode does not work with stored functions, triggers,
# views"

# Function which calls procedure
drop function foo3;
delimiter |;
create function foo3() returns bigint unsigned
begin
  insert into t1 values("foo3_32_");
  call foo();
  return 100;
end|
delimiter ;|
insert into t2 select foo3();

prepare stmt1 from 'insert into t2 select foo3()';
execute stmt1;
execute stmt1;
deallocate prepare stmt1;

# Test if stored function calls stored function which calls procedure
# which requires row-based.

delimiter |;
create function foo4() returns bigint unsigned
begin
  insert into t2 select foo3();
  return 100;
end|
delimiter ;|
select foo4();

prepare stmt1 from 'select foo4()';
execute stmt1;
execute stmt1;
deallocate prepare stmt1;

# A simple stored function
delimiter |;
create function foo5() returns bigint unsigned
begin
  insert into t2 select SYS_GUID();
  return 100;
end|
delimiter ;|
select foo5();

prepare stmt1 from 'select foo5()';
execute stmt1;
execute stmt1;
deallocate prepare stmt1;

# A simple stored function where SYS_GUID() is in the argument
delimiter |;
create function foo6(x varchar(100)) returns bigint unsigned
begin
  insert into t2 select x;
  return 100;
end|
delimiter ;|
select foo6("foo6_1_");
select foo6(concat("foo6_2_",SYS_GUID()));

prepare stmt1 from 'select foo6(concat("foo6_3_",SYS_GUID()))';
execute stmt1;
execute stmt1;
deallocate prepare stmt1;


# Test of views using SYS_GUID()

create view v1 as select SYS_GUID();
create table t11 (data varchar(255));
insert into t11 select * from v1;
# Test of querying INFORMATION_SCHEMA which parses the view's body,
# to verify that it binlogs statement-based (is not polluted by
# the parsing of the view's body).
insert into t11 select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysqltest1' and TABLE_NAME IN ('v1','t11');
prepare stmt1 from "insert into t11 select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysqltest1' and TABLE_NAME IN ('v1','t11')";
execute stmt1;
execute stmt1;
deallocate prepare stmt1;

# Test of triggers with SYS_GUID()
delimiter |;
create trigger t11_bi before insert on t11 for each row
begin
  set NEW.data = concat(NEW.data,SYS_GUID());
end|
delimiter ;|
insert into t11 values("try_560_");

# Test that INSERT DELAYED works in mixed mode (BUG#20649)
insert delayed into t2 values("delay_1_");
insert delayed into t2 values(concat("delay_2_",SYS_GUID()));
insert delayed into t2 values("delay_6_");

# Test for BUG#20633 (INSERT DELAYED RAND()/user_variable does not
# replicate fine in statement-based ; we test that in mixed mode it
# works).
insert delayed into t2 values(rand());
set @a=2.345;
insert delayed into t2 values(@a);

# With INSERT DELAYED, rows are written to the binlog after they are
# written to the table. Therefore, it is not enough to wait until the
# rows make it to t2 on the master (the rows may not be in the binlog
# at that time, and may still not be in the binlog when
# sync_slave_with_master is later called). Instead, we wait until the
# rows make it to t2 on the slave. We first call
# sync_slave_with_master, so that we are sure that t2 has been created
# on the slave.
sync_slave_with_master;
let $wait_condition= SELECT COUNT(*) = 19 FROM mysqltest1.t2;
--source include/wait_condition.inc
connection master;

# If you want to do manual testing of the mixed mode regarding UDFs (not
# testable automatically as quite platform- and compiler-dependent),
# you just need to set the variable below to 1, and to
# "make udf_example.so" in sql/, and to copy sql/udf_example.so to
# MYSQL_TEST_DIR/lib/mysql.
let $you_want_to_test_UDF=0;
if ($you_want_to_test_UDF)
{
  CREATE FUNCTION metaphon RETURNS STRING SONAME 'udf_example.so';
  prepare stmt1 from 'insert into t1 select metaphon(?)';
  set @string="emergency_133_";
  insert into t1 values("work_134_");
  execute stmt1 using @string;
  deallocate prepare stmt1;
  prepare stmt1 from 'insert into t1 select ?';
  insert into t1 values(metaphon("work_135_"));
  execute stmt1 using @string;
  deallocate prepare stmt1;
  insert into t1 values(metaphon("for_136_"));
  insert into t1 select "yesterday_137_";
  create table t6 select metaphon("for_138_");
  create table t7 select 1 union select metaphon("for_139_");
  create table t8 select * from t1 where 3 in (select 1 union select 2 union select metaphon("for_140_") union select 3);
  create table t9 select * from t1 where 3 in (select 1 union select 2 union select curdate() union select 3);
}

create table t20 select * from t1; # save for comparing later
create table t21 select * from t2;
create table t22 select * from t3;
drop table t1,t2,t3;

# This tests the fix to
# BUG#19630 stored function inserting into two auto_increment breaks statement-based binlog
# We verify that under the mixed binlog mode, a stored function
# modifying at least two tables having an auto_increment column,
# is binlogged row-based. Indeed in statement-based binlogging,
# only the auto_increment value generated for the first table
# is recorded in the binlog, the value generated for the 2nd table
# lacking.

create table t1 (a int primary key auto_increment, b varchar(100));
create table t2 (a int primary key auto_increment, b varchar(100));
create table t3 (b varchar(100));
delimiter |;
create function f (x varchar(100)) returns int deterministic
begin
 insert into t1 values(null,x);
 insert into t2 values(null,x);
 return 1;
end|
delimiter ;|
select f("try_41_");
# Two operations which compensate each other except that their net
# effect is that they advance the auto_increment counter of t2 on slave:
sync_slave_with_master;
use mysqltest1;
insert into t2 values(2,null),(3,null),(4,null);
delete from t2 where a>=2;

connection master;
# this is the call which didn't replicate well
select f("try_42_");
sync_slave_with_master;

# now use prepared statement and test again, just to see that the RBB
# mode isn't set at PREPARE but at EXECUTE.

insert into t2 values(3,null),(4,null);
delete from t2 where a>=3;

connection master;
prepare stmt1 from 'select f(?)';
set @string="try_43_";
insert into t1 values(null,"try_44_"); # should be SBB
execute stmt1 using @string; # should be RBB
deallocate prepare stmt1;
sync_slave_with_master;

# verify that if only one table has auto_inc, it does not trigger RBB
# (we'll check in binlog further below)

connection master;
create table t12 select * from t1; # save for comparing later
drop table t1;
create table t1 (a int, b varchar(100), key(a));
select f("try_45_");

# restore table's key
create table t13 select * from t1;
drop table t1;
create table t1 (a int primary key auto_increment, b varchar(100));

# now test if it's two functions, each of them inserts in one table

drop function f;
# we need a unique key to have sorting of rows by mysqldump
create table t14 (unique (a)) select * from t2;
truncate table t2;
delimiter |;
create function f1 (x varchar(100)) returns int deterministic
begin
 insert into t1 values(null,x);
 return 1;
end|
create function f2 (x varchar(100)) returns int deterministic
begin
 insert into t2 values(null,x);
 return 1;
end|
delimiter ;|
select f1("try_46_"),f2("try_47_");

sync_slave_with_master;
insert into t2 values(2,null),(3,null),(4,null);
delete from t2 where a>=2;

connection master;
# Test with SELECT and INSERT
select f1("try_48_"),f2("try_49_");
insert into t3 values(concat("try_50_",f1("try_51_"),f2("try_52_")));
sync_slave_with_master;

# verify that if f2 does only read on an auto_inc table, this does not
# switch to RBB
connection master;
drop function f2;
delimiter |;
create function f2 (x varchar(100)) returns int deterministic
begin
 declare y int;
 insert into t1 values(null,x);
 set y = (select count(*) from t2);
 return y;
end|
delimiter ;|
select f1("try_53_"),f2("try_54_");
sync_slave_with_master;

# And now, a normal statement with a trigger (no stored functions)

connection master;
drop function f2;
delimiter |;
create trigger t1_bi before insert on t1 for each row
begin
  insert into t2 values(null,"try_55_");
end|
delimiter ;|
insert into t1 values(null,"try_56_");
# and now remove one auto_increment and verify SBB
alter table t1 modify a int, drop primary key;
insert into t1 values(null,"try_57_");
sync_slave_with_master;

# Test for BUG#20499 "mixed mode with temporary table breaks binlog"
# Slave used to have only 2 rows instead of 3.
connection master;
CREATE TEMPORARY TABLE t15 SELECT SYS_GUID();
create table t16 like t15;
INSERT INTO t16 SELECT * FROM t15;
# we'll verify that this one is done RBB
insert into t16 values("try_65_");
drop table t15;
# we'll verify that this one is done SBB
insert into t16 values("try_66_");
sync_slave_with_master;

# and now compare:

connection master;

# first check that data on master is sensible
select count(*) from t1;
select count(*) from t2;
select count(*) from t3;
select count(*) from t4;
select count(*) from t5;
select count(*) from t11;
select count(*) from t20;
select count(*) from t21;
select count(*) from t22;
select count(*) from t12;
select count(*) from t13;
select count(*) from t14;
select count(*) from t16;
if ($you_want_to_test_UDF)
{
  select count(*) from t6;
  select count(*) from t7;
  select count(*) from t8;
  select count(*) from t9;
}

sync_slave_with_master;

#
# Bug#20863 If binlog format is changed between update and unlock of
#           tables, wrong binlog
#

connection master;
DROP TABLE IF EXISTS t11;
SET SESSION BINLOG_FORMAT=STATEMENT;
CREATE TABLE t11 (song VARCHAR(255));
LOCK TABLES t11 WRITE;
SET SESSION BINLOG_FORMAT=ROW;
INSERT INTO t11 VALUES('Several Species of Small Furry Animals Gathered Together in a Cave and Grooving With a Pict');
SET SESSION BINLOG_FORMAT=STATEMENT;
INSERT INTO t11 VALUES('Careful With That Axe, Eugene');
UNLOCK TABLES;

--query_vertical SELECT * FROM t11
sync_slave_with_master;
USE mysqltest1;
--query_vertical SELECT * FROM t11

connection master;
DROP TABLE IF EXISTS t12;
SET SESSION BINLOG_FORMAT=MIXED;
CREATE TABLE t12 (data LONG);
LOCK TABLES t12 WRITE;
INSERT INTO t12 VALUES(SYS_GUID());
UNLOCK TABLES;
sync_slave_with_master;

#
# BUG#28086: SBR of USER() becomes corrupted on slave
# 

connection master;

# Just to get something that is non-trivial, albeit still simple, we
# stuff the result of USER() and CURRENT_USER() into a variable.
--delimiter $$
CREATE FUNCTION my_user()
    RETURNS CHAR(64)
BEGIN
  DECLARE user CHAR(64);
  SELECT USER() INTO user;
  RETURN user;
END $$
--delimiter ;

--delimiter $$
CREATE FUNCTION my_current_user()
    RETURNS CHAR(64)
BEGIN
  DECLARE user CHAR(64);
  SELECT CURRENT_USER() INTO user;
  RETURN user;
END $$
--delimiter ;

DROP TABLE IF EXISTS t13;
CREATE TABLE t13 (data CHAR(64));
INSERT INTO t13 VALUES (USER());
INSERT INTO t13 VALUES (my_user());
INSERT INTO t13 VALUES (CURRENT_USER());
INSERT INTO t13 VALUES (my_current_user());

sync_slave_with_master;

# as we're using SYS_GUID we don't SELECT but use "diff" like in rpl_row_UUID
--exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info mysqltest1 > $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql
--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

# Let's compare. Note: If they match test will pass, if they do not match
# the test will show that the diff statement failed and not reject file
# will be created. You will need to go to the mysql-test dir and diff
# the files your self to see what is not matching

diff_files $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_slave.sql;

connection master;

# Now test that mysqlbinlog works fine on a binlog generated by the
# mixed mode

# BUG#11312 "DELIMITER is not written to the binary log that causes
# syntax error" makes that mysqlbinlog will fail if we pass it the
# text of queries; this forces us to use --base64-output here.

# BUG#20929 "BINLOG command causes invalid free plus assertion
# failure" makes mysqld segfault when receiving --base64-output

# So I can't enable this piece of test
# SIGH

if ($enable_when_11312_or_20929_fixed)
{
--exec $MYSQL_BINLOG --base64-output $MYSQLTEST_VARDIR/log/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_mixed.sql
drop database mysqltest1;
--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/mysqlbinlog_mixed.sql
--exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info mysqltest1 > $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql
# the old mysqldump output on slave is the same as what it was on
# master before restoring on master.
diff_files $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_slave.sql;
}

drop database mysqltest1;
sync_slave_with_master;

connection master;
# Restore binlog format setting
set global binlog_format =@my_binlog_format;
--source include/rpl_end.inc