binlog_switch_inside_trans.test 6.16 KB
Newer Older
1 2
#
# BUG#47863
3 4 5
# 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.
6 7 8 9 10 11
#

source include/have_innodb.inc;
source include/have_binlog_format_row.inc;

set @save_binlog_format= @@global.binlog_format;
12
set @save_binlog_dirct= @@global.binlog_direct_non_transactional_updates;
13 14 15 16
create table t1 (a int) engine= myisam;
create table t2 (a int) engine= innodb;

SELECT @@session.binlog_format;
17
SELECT @@session.binlog_direct_non_transactional_updates;
18
SET AUTOCOMMIT=1;
19 20 21 22
--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.
23
set @@session.binlog_format= statement;
24
set @@session.binlog_direct_non_transactional_updates= TRUE;
25
SELECT @@session.binlog_format;
26
SELECT @@session.binlog_direct_non_transactional_updates;
27 28

begin;
29 30 31 32
--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.
33 34
--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_FORMAT
  set @@session.binlog_format= mixed;
35 36
--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_DIRECT
  set @@session.binlog_direct_non_transactional_updates= FALSE;
37 38

  insert into t2 values (1);
39 40 41 42
--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.
43 44
--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_FORMAT
  set @@session.binlog_format= row;
45 46
--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_DIRECT
  set @@session.binlog_direct_non_transactional_updates= FALSE;
47 48 49 50
commit;

begin;
  insert into t1 values (2);
51 52 53 54
--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.
55
--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_FORMAT
56
  set @@session.binlog_format= mixed;
57 58
--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_DIRECT
  set @@session.binlog_direct_non_transactional_updates= FALSE;
59 60
commit;

61 62 63 64
--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.
65 66
set AUTOCOMMIT=0;
set @@session.binlog_format= row;
67
set @@session.binlog_direct_non_transactional_updates= FALSE;
68
SELECT @@session.binlog_format;
69
SELECT @@session.binlog_direct_non_transactional_updates;
70

71 72 73 74 75 76
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.
77 78
--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_FORMAT
set @@session.binlog_format= statement;
79 80
--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_DIRECT
set @@session.binlog_direct_non_transactional_updates= TRUE;
81
SELECT @@session.binlog_format;
82
SELECT @@session.binlog_direct_non_transactional_updates;
83 84
commit;

85 86 87 88 89 90
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.
91
--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_FORMAT
92
set @@session.binlog_format= statement;
93 94
--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_DIRECT
set @@session.binlog_direct_non_transactional_updates= TRUE;
95
SELECT @@session.binlog_format;
96
SELECT @@session.binlog_direct_non_transactional_updates;
97 98 99
commit;

begin;
100 101 102 103 104
  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.
105 106
  SELECT @@global.binlog_format;
  set @@global.binlog_format= statement;
107
  set @@global.binlog_direct_non_transactional_updates= TRUE;
108
  SELECT @@global.binlog_format;
109
  SELECT @@global.binlog_direct_non_transactional_updates;
110 111 112
commit;

set @@global.binlog_format= @save_binlog_format;
113
set @@global.binlog_direct_non_transactional_updates= @save_binlog_dirct;
114 115 116 117 118

create table t3(a int, b int) engine= innodb;
create table t4(a int) engine= innodb;
create table t5(a int) engine= innodb;
delimiter |;
119
eval create trigger tr1 after insert on t3 for each row begin
120
    insert into t4(a) values(1);
121
    set @@session.binlog_format= statement;
122 123 124 125 126 127 128
    insert into t4(a) values(2);
    insert into t5(a) values(3);
end |
delimiter ;|

--echo # Test that the session variable 'binlog_format' is read-only
--echo # in sub-statements.
129
--echo # Current session value is ROW.
130 131 132 133
--error ER_STORED_FUNCTION_PREVENTS_SWITCH_BINLOG_FORMAT
insert into t3(a,b) values(1,1);
SELECT @@session.binlog_format;

134 135 136 137 138 139
create table t6(a int, b int) engine= innodb;
create table t7(a int) engine= innodb;
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);
140
    set @@session.binlog_direct_non_transactional_updates= TRUE;
141 142 143 144 145
    insert into t7(a) values(2);
    insert into t8(a) values(3);
end |
delimiter ;|

146 147 148 149
--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.
150 151 152 153
--error ER_STORED_FUNCTION_PREVENTS_SWITCH_BINLOG_DIRECT
insert into t6(a,b) values(1,1);
SELECT @@session.binlog_direct_non_transactional_updates;

154 155 156 157 158
drop table t1;
drop table t2;
drop table t3;
drop table t4;
drop table t5;
159 160 161
drop table t6;
drop table t7;
drop table t8;