diff options
Diffstat (limited to 'mysql-test/suite/binlog')
11 files changed, 134 insertions, 84 deletions
diff --git a/mysql-test/suite/binlog/r/binlog_database.result b/mysql-test/suite/binlog/r/binlog_database.result index bb12679f081..33ae6c05ed2 100644 --- a/mysql-test/suite/binlog/r/binlog_database.result +++ b/mysql-test/suite/binlog/r/binlog_database.result @@ -118,6 +118,7 @@ master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ +master-bin.000001 # Query # # use `test`; DROP TEMPORARY TABLE IF EXISTS `tt1` /* generated by server */ FLUSH STATUS; show databases; Database diff --git a/mysql-test/suite/binlog/r/binlog_innodb_row.result b/mysql-test/suite/binlog/r/binlog_innodb_row.result index 4e64660abf8..ab9a3c8cdec 100644 --- a/mysql-test/suite/binlog/r/binlog_innodb_row.result +++ b/mysql-test/suite/binlog/r/binlog_innodb_row.result @@ -57,4 +57,5 @@ DROP TEMPORARY TABLE t1; ############################################### show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; DROP TEMPORARY TABLE IF EXISTS `t1` /* generated by server */ ############################################### diff --git a/mysql-test/suite/binlog/r/binlog_multi_engine.result b/mysql-test/suite/binlog/r/binlog_multi_engine.result index 6668c3fa027..22e32d85c37 100644 --- a/mysql-test/suite/binlog/r/binlog_multi_engine.result +++ b/mysql-test/suite/binlog/r/binlog_multi_engine.result @@ -8,14 +8,14 @@ INSERT INTO t1b VALUES (1,1), (1,2), (2,1), (2,2); INSERT INTO t1m VALUES (1,1), (1,2), (2,1), (2,2); UPDATE t1m, t1b SET m = 2, b = 3 WHERE n = c; Warnings: -Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Non-transactional reads or writes are unsafe if they occur after transactional reads or writes inside a transaction. +Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Statements that read from both transactional (or a temporary table of any engine type) and non-transactional tables and write to any of them are unsafe. The last event before the COMMIT is use `test`; UPDATE t1m, t1b SET m = 2, b = 3 WHERE n = c *** Please look in binlog_multi_engine.test if you have a diff here **** START TRANSACTION; INSERT INTO t1n VALUES (1,1), (1,2), (2,1), (2,2); UPDATE t1m, t1n SET m = 2, e = 3 WHERE n = f; Warnings: -Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Non-transactional reads or writes are unsafe if they occur after transactional reads or writes inside a transaction. +Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Statements that read from both transactional (or a temporary table of any engine type) and non-transactional tables and write to any of them are unsafe. UPDATE t1n, t1b SET e = 2, b = 3 WHERE f = c; COMMIT; TRUNCATE t1m; diff --git a/mysql-test/suite/binlog/r/binlog_row_binlog.result b/mysql-test/suite/binlog/r/binlog_row_binlog.result index 7dc37d7c365..89b70b2c77e 100644 --- a/mysql-test/suite/binlog/r/binlog_row_binlog.result +++ b/mysql-test/suite/binlog/r/binlog_row_binlog.result @@ -1133,6 +1133,7 @@ COERCIBILITY(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) d2, COERCIBILITY(s1) d3; DROP TEMPORARY TABLE tmp1; END +master-bin.000001 # Query # # use `bug39182`; DROP TEMPORARY TABLE IF EXISTS `tmp1` /* generated by server */ DROP PROCEDURE p1; DROP TABLE t1; DROP DATABASE bug39182; @@ -1254,6 +1255,7 @@ master-bin.000001 # Table_map # # table_id: # (mysql.user) master-bin.000001 # Delete_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # use `test`; DROP TABLE `t1`,`t2`,`t3` /* generated by server */ +master-bin.000001 # Query # # use `test`; DROP TEMPORARY TABLE IF EXISTS `tt1` /* generated by server */ master-bin.000001 # Query # # use `test`; create table t1 (a int not null auto_increment, primary key (a)) engine=myisam master-bin.000001 # Query # # BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) diff --git a/mysql-test/suite/binlog/r/binlog_row_drop_tmp_tbl.result b/mysql-test/suite/binlog/r/binlog_row_drop_tmp_tbl.result index 75c088e595d..a43ed03b774 100644 --- a/mysql-test/suite/binlog/r/binlog_row_drop_tmp_tbl.result +++ b/mysql-test/suite/binlog/r/binlog_row_drop_tmp_tbl.result @@ -27,6 +27,10 @@ show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # CREATE DATABASE `drop-temp+table-test` master-bin.000001 # Query # # use `drop-temp+table-test`; CREATE TABLE t(c1 int) +master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TEMPORARY TABLE IF EXISTS `tmp` /* generated by server */ +master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TEMPORARY TABLE IF EXISTS `tmp1` /* generated by server */ +master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TEMPORARY TABLE IF EXISTS `tmp3` /* generated by server */ master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TABLE IF EXISTS `t` /* generated by server */ +master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TEMPORARY TABLE IF EXISTS `tmp2` /* generated by server */ master-bin.000001 # Query # # use `drop-temp+table-test`; DROP TABLE IF EXISTS tmp2, t DROP DATABASE `drop-temp+table-test`; diff --git a/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result b/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result index 1f4645e91e9..d760e6d2f22 100644 --- a/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result +++ b/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result @@ -420,6 +420,7 @@ master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ +master-bin.000001 # Query # # use `test`; DROP TEMPORARY TABLE IF EXISTS `t2` /* generated by server */ reset master; create table t1 (a int) engine=innodb; create table t2 (a int) engine=myisam; diff --git a/mysql-test/suite/binlog/r/binlog_stm_binlog.result b/mysql-test/suite/binlog/r/binlog_stm_binlog.result index baecff8bb85..961ba7217f4 100644 --- a/mysql-test/suite/binlog/r/binlog_stm_binlog.result +++ b/mysql-test/suite/binlog/r/binlog_stm_binlog.result @@ -639,9 +639,7 @@ COERCIBILITY(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) d2, COERCIBILITY(s1) d3; DROP TEMPORARY TABLE tmp1; END -master-bin.000001 # Query # # use `bug39182`; CREATE TEMPORARY TABLE tmp1 -SELECT * FROM t1 WHERE a LIKE CONCAT("%", NAME_CONST('s1',_utf8'test' COLLATE 'utf8_unicode_ci'), "%") -master-bin.000001 # Query # # use `bug39182`; DROP TEMPORARY TABLE tmp1 +master-bin.000001 # Query # # use `bug39182`; DROP TEMPORARY TABLE IF EXISTS `tmp1` /* generated by server */ DROP PROCEDURE p1; DROP TABLE t1; DROP DATABASE bug39182; diff --git a/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result b/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result index e1023db842d..0ae94fb0839 100644 --- a/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result +++ b/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result @@ -8,7 +8,7 @@ begin; insert into t1 values(1); insert into t2 select * from t1; Warnings: -Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Non-transactional reads or writes are unsafe if they occur after transactional reads or writes inside a transaction. +Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Statements that read from both transactional (or a temporary table of any engine type) and non-transactional tables and write to any of them are unsafe. commit; show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info @@ -23,7 +23,7 @@ begin; insert into t1 values(2); insert into t2 select * from t1; Warnings: -Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Non-transactional reads or writes are unsafe if they occur after transactional reads or writes inside a transaction. +Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Statements that read from both transactional (or a temporary table of any engine type) and non-transactional tables and write to any of them are unsafe. rollback; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back @@ -42,7 +42,7 @@ savepoint my_savepoint; insert into t1 values(4); insert into t2 select * from t1; Warnings: -Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Non-transactional reads or writes are unsafe if they occur after transactional reads or writes inside a transaction. +Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Statements that read from both transactional (or a temporary table of any engine type) and non-transactional tables and write to any of them are unsafe. rollback to savepoint my_savepoint; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back @@ -65,7 +65,7 @@ savepoint my_savepoint; insert into t1 values(6); insert into t2 select * from t1; Warnings: -Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Non-transactional reads or writes are unsafe if they occur after transactional reads or writes inside a transaction. +Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Statements that read from both transactional (or a temporary table of any engine type) and non-transactional tables and write to any of them are unsafe. rollback to savepoint my_savepoint; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back @@ -95,7 +95,7 @@ begin; insert into t1 values(8); insert into t2 select * from t1; Warnings: -Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Non-transactional reads or writes are unsafe if they occur after transactional reads or writes inside a transaction. +Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Statements that read from both transactional (or a temporary table of any engine type) and non-transactional tables and write to any of them are unsafe. select get_lock("a",10); get_lock("a",10) 1 @@ -111,7 +111,7 @@ reset master; insert into t1 values(9); insert into t2 select * from t1; Warnings: -Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Non-transactional reads or writes are unsafe if they occur after transactional reads or writes inside a transaction. +Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Statements that read from both transactional (or a temporary table of any engine type) and non-transactional tables and write to any of them are unsafe. show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN @@ -127,7 +127,7 @@ insert into t1 values(10); begin; insert into t2 select * from t1; Warnings: -Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Non-transactional reads or writes are unsafe if they occur after transactional reads or writes inside a transaction. +Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Statements that read from both transactional (or a temporary table of any engine type) and non-transactional tables and write to any of them are unsafe. show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN @@ -245,6 +245,8 @@ Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back create table t0 (n int); insert t0 select * from t1; +Warnings: +Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Statements that read from both transactional (or a temporary table of any engine type) and non-transactional tables and write to any of them are unsafe. set autocommit=1; insert into t0 select GET_LOCK("lock1",null); Warnings: @@ -277,7 +279,7 @@ master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # use `test`; drop table t1,t2 master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; create temporary table ti (a int) engine=innodb -master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # ROLLBACK master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert into ti values(1) master-bin.000001 # Query # # COMMIT @@ -286,7 +288,7 @@ master-bin.000001 # Query # # use `test`; create temporary table t1 (a int) engi master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert t1 values (1) -master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # ROLLBACK master-bin.000001 # Query # # use `test`; create table t0 (n int) master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert t0 select * from t1 @@ -332,6 +334,8 @@ DROP TABLE t2; INSERT INTO t1 values (6,6); CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)) engine=innodb ; INSERT INTO t1 values (7,7); +Warnings: +Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Non-transactional reads or writes are unsafe if they occur after transactional reads or writes inside a transaction. ROLLBACK; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back @@ -399,10 +403,8 @@ master-bin.000001 # Query # # use `test`; INSERT INTO t1 values (6,6) master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)) engine=innodb -master-bin.000001 # Query # # COMMIT -master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; INSERT INTO t1 values (7,7) -master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # ROLLBACK master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; INSERT INTO t1 values (8,8) master-bin.000001 # Query # # COMMIT @@ -427,7 +429,7 @@ begin; insert into t1 values(8); insert into t2 select * from t1; Warnings: -Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Non-transactional reads or writes are unsafe if they occur after transactional reads or writes inside a transaction. +Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Statements that read from both transactional (or a temporary table of any engine type) and non-transactional tables and write to any of them are unsafe. select get_lock("a",10); get_lock("a",10) 1 @@ -453,8 +455,6 @@ begin; insert into ti values (1); insert into ti values (2) ; insert into tt select * from ti; -Warnings: -Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Non-transactional reads or writes are unsafe if they occur after transactional reads or writes inside a transaction. rollback; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back @@ -472,8 +472,6 @@ select count(*) from ti /* zero */; count(*) 0 insert into ti select * from tt; -Warnings: -Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Non-transactional reads or writes are unsafe if they occur after transactional reads or writes inside a transaction. select * from ti /* that is what slave would miss - a bug */; a 1 @@ -500,8 +498,6 @@ select count(*) from ti /* zero */; count(*) 0 insert into ti select * from tt; -Warnings: -Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Non-transactional reads or writes are unsafe if they occur after transactional reads or writes inside a transaction. select * from tt /* that is what otherwise slave missed - the bug */; a 1 @@ -717,8 +713,6 @@ begin; insert into ti values (1); insert into ti values (2) ; insert into tt select * from ti; -Warnings: -Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Non-transactional reads or writes are unsafe if they occur after transactional reads or writes inside a transaction. rollback; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back @@ -736,8 +730,6 @@ select count(*) from ti /* zero */; count(*) 0 insert into ti select * from tt; -Warnings: -Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Non-transactional reads or writes are unsafe if they occur after transactional reads or writes inside a transaction. select * from ti /* that is what slave would miss - bug#28960 */; a 1 @@ -764,8 +756,6 @@ select count(*) from ti /* zero */; count(*) 0 insert into ti select * from tt; -Warnings: -Note 1592 Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Non-transactional reads or writes are unsafe if they occur after transactional reads or writes inside a transaction. select * from tt /* that is what otherwise slave missed - the bug */; a 1 diff --git a/mysql-test/suite/binlog/r/binlog_switch_inside_trans.result b/mysql-test/suite/binlog/r/binlog_switch_inside_trans.result index 57feb3f50f0..de224a190c2 100644 --- a/mysql-test/suite/binlog/r/binlog_switch_inside_trans.result +++ b/mysql-test/suite/binlog/r/binlog_switch_inside_trans.result @@ -5,9 +5,14 @@ create table t2 (a int) engine= innodb; SELECT @@session.binlog_format; @@session.binlog_format ROW +SELECT @@session.binlog_direct_non_transactional_updates; +@@session.binlog_direct_non_transactional_updates +1 SET AUTOCOMMIT=1; -# Test that the session variable 'binlog_format' -# is writable outside a transaction. +# Test that the session variable 'binlog_format' and +# 'binlog_direct_non_transactional_updates' are +# writable outside a transaction. +# Current session values are ROW and FALSE, respectively. set @@session.binlog_format= statement; set @@session.binlog_direct_non_transactional_updates= TRUE; SELECT @@session.binlog_format; @@ -17,15 +22,19 @@ SELECT @@session.binlog_direct_non_transactional_updates; @@session.binlog_direct_non_transactional_updates 1 begin; -# Test that the session variable 'binlog_format' is read-only -# inside a transaction with no preceding updates. +# Test that the session variable 'binlog_format' and +# 'binlog_direct_non_transactional_updates' are +# read-only inside a transaction with no preceding updates. +# Current session values are STATEMENT and TRUE, respectively. set @@session.binlog_format= mixed; ERROR HY000: Cannot modify @@session.binlog_format inside a transaction set @@session.binlog_direct_non_transactional_updates= FALSE; ERROR HY000: Cannot modify @@session.binlog_direct_non_transactional_updates inside a transaction insert into t2 values (1); -# Test that the session variable 'binlog_format' is read-only -# inside a transaction with preceding transactional updates. +# Test that the session variable 'binlog_format' and +# 'binlog_direct_non_transactional_updates' are +# read-only inside a transaction with preceding transactional updates. +# Current session values are STATEMENT and TRUE, respectively. set @@session.binlog_format= row; ERROR HY000: Cannot modify @@session.binlog_format inside a transaction set @@session.binlog_direct_non_transactional_updates= FALSE; @@ -33,15 +42,19 @@ ERROR HY000: Cannot modify @@session.binlog_direct_non_transactional_updates ins commit; begin; insert into t1 values (2); -# Test that the session variable 'binlog_format' is read-only -# inside a transaction with preceding non-transactional updates. -set @@session.binlog_format= statement; +# Test that the session variable 'binlog_format' and +# 'binlog_direct_non_transactional_updates' are +# read-only inside a transaction with preceding non-transactional updates. +# Current session values are STATEMENT and TRUE, respectively. +set @@session.binlog_format= mixed; ERROR HY000: Cannot modify @@session.binlog_format inside a transaction set @@session.binlog_direct_non_transactional_updates= FALSE; ERROR HY000: Cannot modify @@session.binlog_direct_non_transactional_updates inside a transaction commit; -# Test that the session variable 'binlog_format' is writable -# when AUTOCOMMIT=0, before a transaction has started. +# Test that the session variable 'binlog_format' and +# 'binlog_direct_non_transactional_updates' are +# writable when AUTOCOMMIT=0, before a transaction has started. +# Current session values are STATEMENT and TRUE, respectively. set AUTOCOMMIT=0; set @@session.binlog_format= row; set @@session.binlog_direct_non_transactional_updates= FALSE; @@ -51,9 +64,12 @@ ROW SELECT @@session.binlog_direct_non_transactional_updates; @@session.binlog_direct_non_transactional_updates 0 -insert into t1 values (4); -# Test that the session variable 'binlog_format' is read-only inside an -# AUTOCOMMIT=0 transaction with preceding non-transactional updates. +insert into t1 values (3); +# Test that the session variable 'binlog_format' and +# 'binlog_direct_non_transactional_updates' are +# read-only inside an AUTOCOMMIT=0 transaction +# with preceding non-transactional updates. +# Current session values are ROW and FALSE, respectively. set @@session.binlog_format= statement; ERROR HY000: Cannot modify @@session.binlog_format inside a transaction set @@session.binlog_direct_non_transactional_updates= TRUE; @@ -65,10 +81,13 @@ SELECT @@session.binlog_direct_non_transactional_updates; @@session.binlog_direct_non_transactional_updates 0 commit; -insert into t2 values (5); -# Test that the session variable 'binlog_format' is read-only inside an -# AUTOCOMMIT=0 transaction with preceding transactional updates. -set @@session.binlog_format= row; +insert into t2 values (4); +# Test that the session variable 'binlog_format' and +# 'binlog_direct_non_transactional_updates' are +# read-only inside an AUTOCOMMIT=0 transaction with +# preceding transactional updates. +# Current session values are ROW and FALSE, respectively. +set @@session.binlog_format= statement; ERROR HY000: Cannot modify @@session.binlog_format inside a transaction set @@session.binlog_direct_non_transactional_updates= TRUE; ERROR HY000: Cannot modify @@session.binlog_direct_non_transactional_updates inside a transaction @@ -80,9 +99,11 @@ SELECT @@session.binlog_direct_non_transactional_updates; 0 commit; begin; -insert into t2 values (6); -# Test that the global variable 'binlog_format' is writable -# inside a transaction. +insert into t2 values (5); +# Test that the global variable 'binlog_format' and +# 'binlog_direct_non_transactional_updates' are +# writable inside a transaction. +# Current session values are ROW and FALSE, respectively. SELECT @@global.binlog_format; @@global.binlog_format ROW @@ -96,18 +117,19 @@ SELECT @@global.binlog_direct_non_transactional_updates; 1 commit; set @@global.binlog_format= @save_binlog_format; -set @@global.binlog_direct_non_transactional_updates= @save_binlog_dirct; +set @@global.binlog_direct_non_transactional_updates= @save_binlog_dirct; create table t3(a int, b int) engine= innodb; create table t4(a int) engine= innodb; create table t5(a int) engine= innodb; create trigger tr1 after insert on t3 for each row begin insert into t4(a) values(1); -set @@session.binlog_format= statement; +set @@session.binlog_format= statement; insert into t4(a) values(2); insert into t5(a) values(3); end | # Test that the session variable 'binlog_format' is read-only # in sub-statements. +# Current session value is ROW. insert into t3(a,b) values(1,1); ERROR HY000: Cannot change the binary logging format inside a stored function or trigger SELECT @@session.binlog_format; @@ -118,12 +140,14 @@ create table t7(a int) engine= innodb; create table t8(a int) engine= innodb; create trigger tr2 after insert on t6 for each row begin insert into t7(a) values(1); -set @@global.binlog_direct_non_transactional_updates= FALSE; +set @@session.binlog_direct_non_transactional_updates= TRUE; insert into t7(a) values(2); insert into t8(a) values(3); end | -# Test that the session variable 'binlog_format' is read-only -# in sub-statements. +# Test that the session variable +# 'binlog_direct_non_transactional_updates' is +# read-only in sub-statements. +# Current session value is FALSE. insert into t6(a,b) values(1,1); ERROR HY000: Cannot change the binlog direct flag inside a stored function or trigger SELECT @@session.binlog_direct_non_transactional_updates; diff --git a/mysql-test/suite/binlog/t/binlog_switch_inside_trans.test b/mysql-test/suite/binlog/t/binlog_switch_inside_trans.test index 7b98b5cd0d5..cdc94198933 100644 --- a/mysql-test/suite/binlog/t/binlog_switch_inside_trans.test +++ b/mysql-test/suite/binlog/t/binlog_switch_inside_trans.test @@ -1,7 +1,8 @@ # # BUG#47863 -# This test verifies if the session variable 'binlog_format' -# is read-only inside a transaction and in sub-statements. +# This test verifies if the session variable 'binlog_format' and +# 'binlog_direct_non_transactional_updates' are read-only inside +# a transaction and in sub-statements. # source include/have_innodb.inc; @@ -13,25 +14,32 @@ create table t1 (a int) engine= myisam; create table t2 (a int) engine= innodb; SELECT @@session.binlog_format; +SELECT @@session.binlog_direct_non_transactional_updates; SET AUTOCOMMIT=1; ---echo # Test that the session variable 'binlog_format' ---echo # is writable outside a transaction. +--echo # Test that the session variable 'binlog_format' and +--echo # 'binlog_direct_non_transactional_updates' are +--echo # writable outside a transaction. +--echo # Current session values are ROW and FALSE, respectively. set @@session.binlog_format= statement; set @@session.binlog_direct_non_transactional_updates= TRUE; SELECT @@session.binlog_format; SELECT @@session.binlog_direct_non_transactional_updates; begin; ---echo # Test that the session variable 'binlog_format' is read-only ---echo # inside a transaction with no preceding updates. +--echo # Test that the session variable 'binlog_format' and +--echo # 'binlog_direct_non_transactional_updates' are +--echo # read-only inside a transaction with no preceding updates. +--echo # Current session values are STATEMENT and TRUE, respectively. --error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_FORMAT set @@session.binlog_format= mixed; --error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_DIRECT set @@session.binlog_direct_non_transactional_updates= FALSE; insert into t2 values (1); ---echo # Test that the session variable 'binlog_format' is read-only ---echo # inside a transaction with preceding transactional updates. +--echo # Test that the session variable 'binlog_format' and +--echo # 'binlog_direct_non_transactional_updates' are +--echo # read-only inside a transaction with preceding transactional updates. +--echo # Current session values are STATEMENT and TRUE, respectively. --error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_FORMAT set @@session.binlog_format= row; --error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_DIRECT @@ -40,25 +48,32 @@ commit; begin; insert into t1 values (2); ---echo # Test that the session variable 'binlog_format' is read-only ---echo # inside a transaction with preceding non-transactional updates. +--echo # Test that the session variable 'binlog_format' and +--echo # 'binlog_direct_non_transactional_updates' are +--echo # read-only inside a transaction with preceding non-transactional updates. +--echo # Current session values are STATEMENT and TRUE, respectively. --error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_FORMAT - set @@session.binlog_format= statement; + set @@session.binlog_format= mixed; --error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_DIRECT set @@session.binlog_direct_non_transactional_updates= FALSE; commit; ---echo # Test that the session variable 'binlog_format' is writable ---echo # when AUTOCOMMIT=0, before a transaction has started. +--echo # Test that the session variable 'binlog_format' and +--echo # 'binlog_direct_non_transactional_updates' are +--echo # writable when AUTOCOMMIT=0, before a transaction has started. +--echo # Current session values are STATEMENT and TRUE, respectively. set AUTOCOMMIT=0; set @@session.binlog_format= row; set @@session.binlog_direct_non_transactional_updates= FALSE; SELECT @@session.binlog_format; SELECT @@session.binlog_direct_non_transactional_updates; -insert into t1 values (4); ---echo # Test that the session variable 'binlog_format' is read-only inside an ---echo # AUTOCOMMIT=0 transaction with preceding non-transactional updates. +insert into t1 values (3); +--echo # Test that the session variable 'binlog_format' and +--echo # 'binlog_direct_non_transactional_updates' are +--echo # read-only inside an AUTOCOMMIT=0 transaction +--echo # with preceding non-transactional updates. +--echo # Current session values are ROW and FALSE, respectively. --error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_FORMAT set @@session.binlog_format= statement; --error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_DIRECT @@ -67,11 +82,14 @@ SELECT @@session.binlog_format; SELECT @@session.binlog_direct_non_transactional_updates; commit; -insert into t2 values (5); ---echo # Test that the session variable 'binlog_format' is read-only inside an ---echo # AUTOCOMMIT=0 transaction with preceding transactional updates. +insert into t2 values (4); +--echo # Test that the session variable 'binlog_format' and +--echo # 'binlog_direct_non_transactional_updates' are +--echo # read-only inside an AUTOCOMMIT=0 transaction with +--echo # preceding transactional updates. +--echo # Current session values are ROW and FALSE, respectively. --error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_FORMAT -set @@session.binlog_format= row; +set @@session.binlog_format= statement; --error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_DIRECT set @@session.binlog_direct_non_transactional_updates= TRUE; SELECT @@session.binlog_format; @@ -79,9 +97,11 @@ SELECT @@session.binlog_direct_non_transactional_updates; commit; begin; - insert into t2 values (6); ---echo # Test that the global variable 'binlog_format' is writable ---echo # inside a transaction. + insert into t2 values (5); +--echo # Test that the global variable 'binlog_format' and +--echo # 'binlog_direct_non_transactional_updates' are +--echo # writable inside a transaction. +--echo # Current session values are ROW and FALSE, respectively. SELECT @@global.binlog_format; set @@global.binlog_format= statement; set @@global.binlog_direct_non_transactional_updates= TRUE; @@ -90,7 +110,7 @@ begin; commit; set @@global.binlog_format= @save_binlog_format; -set @@global.binlog_direct_non_transactional_updates= @save_binlog_dirct; +set @@global.binlog_direct_non_transactional_updates= @save_binlog_dirct; create table t3(a int, b int) engine= innodb; create table t4(a int) engine= innodb; @@ -98,7 +118,7 @@ create table t5(a int) engine= innodb; delimiter |; eval create trigger tr1 after insert on t3 for each row begin insert into t4(a) values(1); - set @@session.binlog_format= statement; + set @@session.binlog_format= statement; insert into t4(a) values(2); insert into t5(a) values(3); end | @@ -106,6 +126,7 @@ delimiter ;| --echo # Test that the session variable 'binlog_format' is read-only --echo # in sub-statements. +--echo # Current session value is ROW. --error ER_STORED_FUNCTION_PREVENTS_SWITCH_BINLOG_FORMAT insert into t3(a,b) values(1,1); SELECT @@session.binlog_format; @@ -116,14 +137,16 @@ create table t8(a int) engine= innodb; delimiter |; eval create trigger tr2 after insert on t6 for each row begin insert into t7(a) values(1); - set @@global.binlog_direct_non_transactional_updates= FALSE; + set @@session.binlog_direct_non_transactional_updates= TRUE; insert into t7(a) values(2); insert into t8(a) values(3); end | delimiter ;| ---echo # Test that the session variable 'binlog_format' is read-only ---echo # in sub-statements. +--echo # Test that the session variable +--echo # 'binlog_direct_non_transactional_updates' is +--echo # read-only in sub-statements. +--echo # Current session value is FALSE. --error ER_STORED_FUNCTION_PREVENTS_SWITCH_BINLOG_DIRECT insert into t6(a,b) values(1,1); SELECT @@session.binlog_direct_non_transactional_updates; diff --git a/mysql-test/suite/binlog/t/binlog_tmp_table.test b/mysql-test/suite/binlog/t/binlog_tmp_table.test index 3a245fa01e4..982ba9eedac 100644 --- a/mysql-test/suite/binlog/t/binlog_tmp_table.test +++ b/mysql-test/suite/binlog/t/binlog_tmp_table.test @@ -28,6 +28,10 @@ source include/have_log_bin.inc; source include/have_binlog_format_mixed_or_statement.inc; +--disable_query_log +CALL mtr.add_suppression("Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT"); +--enable_query_log + connect (master,127.0.0.1,root,,test,$MASTER_MYPORT,); connect (master1,127.0.0.1,root,,test,$MASTER_MYPORT,); @@ -55,10 +59,12 @@ delete from tmp1_foo where a < 5; connection master1; delete from tmp2_foo where a < 5; +--disable_warnings connection master; insert into foo select * from tmp1_foo; connection master1; insert into foo select * from tmp2_foo; +--enable_warnings connection master; truncate table tmp1_foo; |