-- source include/have_row_based.inc -- source include/master-slave.inc connection master; --disable_warnings drop database if exists mysqltest1; create database mysqltest1; --enable_warnings use mysqltest1; set session binlog_format=row; 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(UUID(),?)'; set @string="emergency"; insert into t1 values("work"); execute stmt1 using @string; deallocate prepare stmt1; prepare stmt1 from 'insert into t1 select ?'; insert into t1 values(concat(UUID(),"work")); execute stmt1 using @string; deallocate prepare stmt1; insert into t1 values(concat("for",UUID())); insert into t1 select "yesterday"; # verify that temp tables prevent a switch to SBR create temporary table tmp(a char(3)); insert into tmp values("see"); --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"; insert into t1 values("work"); execute stmt1 using @string; deallocate prepare stmt1; prepare stmt1 from 'insert into t1 select ?'; insert into t1 values("work"); execute stmt1 using @string; deallocate prepare stmt1; insert into t1 values("for"); insert into t1 select "yesterday"; # test SET DEFAULT (=statement at this point of test) set binlog_format=default; select @@global.binlog_format, @@session.binlog_format; # due to cluster it's hard to set back to default --error ER_NO_DEFAULT set global binlog_format=default; select @@global.binlog_format, @@session.binlog_format; prepare stmt1 from 'insert into t1 select ?'; set @string="emergency"; insert into t1 values("work"); execute stmt1 using @string; deallocate prepare stmt1; prepare stmt1 from 'insert into t1 select ?'; insert into t1 values("work"); execute stmt1 using @string; deallocate prepare stmt1; insert into t1 values("for"); insert into t1 select "yesterday"; # and now the mixed mode set binlog_format=mixed; select @@global.binlog_format, @@session.binlog_format; set global binlog_format=mixed; select @@global.binlog_format, @@session.binlog_format; prepare stmt1 from 'insert into t1 select concat(UUID(),?)'; set @string="emergency"; insert into t1 values("work"); execute stmt1 using @string; deallocate prepare stmt1; prepare stmt1 from 'insert into t1 select ?'; insert into t1 values(concat(UUID(),"work")); execute stmt1 using @string; deallocate prepare stmt1; insert into t1 values(concat("for",UUID())); insert into t1 select "yesterday"; prepare stmt1 from 'insert into t1 select ?'; insert into t1 values(concat(UUID(),"work")); execute stmt1 using @string; deallocate prepare stmt1; insert into t1 values(concat("for",UUID())); insert into t1 select "yesterday"; # Test of CREATE TABLE SELECT create table t2 select UUID(); create table t3 select 1 union select UUID(); create table t4 select * from t1 where 3 in (select 1 union select 2 union select UUID() union select 3); create table t5 select * from t1 where 3 in (select 1 union select 2 union select curdate() union select 3); # what if UUID() is first: insert into t5 select UUID() from t1 where 3 in (select 1 union select 2 union select 3 union select * from t4); # inside a stored procedure (inside a function or trigger won't # work) delimiter |; create procedure foo() begin insert into t1 values("work"); insert into t1 values(concat("for",UUID())); insert into t1 select "yesterday"; end| create procedure foo2() begin insert into t1 values(concat("emergency",UUID())); insert into t1 values("work"); insert into t1 values(concat("for",UUID())); set session binlog_format=row; # accepted for stored procs insert into t1 values("more work"); 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| delimiter ;| call foo(); call foo2(); # 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"; # 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"; insert into t1 values("work"); execute stmt1 using @string; deallocate prepare stmt1; prepare stmt1 from 'insert into t1 select ?'; insert into t1 values(metaphon("work")); execute stmt1 using @string; deallocate prepare stmt1; insert into t1 values(metaphon("for")); insert into t1 select "yesterday"; create table t6 select metaphon("for"); create table t7 select 1 union select metaphon("for"); create table t8 select * from t1 where 3 in (select 1 union select 2 union select metaphon("for") union select 3); create table t9 select * from t1 where 3 in (select 1 union select 2 union select curdate() union select 3); } # and now compare: # 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; if ($you_want_to_test_UDF) { select count(*) from t6; select count(*) from t7; select count(*) from t8; select count(*) from t9; } # # Bug#20863 If binlog format is changed between update and unlock of # tables, wrong binlog # connection master; 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; SET SESSION BINLOG_FORMAT=MIXED; CREATE TABLE t12 (data LONG); LOCK TABLES t12 WRITE; INSERT INTO t12 VALUES(UUID()); UNLOCK TABLES; --replace_column 2 # 5 # --replace_regex /table_id: [0-9]+/table_id: #/ show binlog events from 102; sync_slave_with_master; # as we're using UUID 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 connection master; drop database mysqltest1; sync_slave_with_master; # 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 --exec diff $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_slave.sql;