2
# Bug #45855 row events in binlog after switch from binlog_fmt=mix to stmt with open tmp tbl
3
# Bug #45856 can't switch from binlog_format=row to mix with open tmp tbl
4
# This test verfies if the program will generate ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR
5
# error and forbid switching @@SESSION.binlog_format from MIXED or ROW to
6
# STATEMENT when there are open temp tables and we are logging in row format.
7
# There is no error in any other case.
10
source include/have_binlog_format_mixed.inc;
12
SELECT @@SESSION.binlog_format;
13
CREATE TABLE t1 (a VARCHAR(100));
14
CREATE TEMPORARY TABLE t2 (a VARCHAR(100));
16
--echo # Test allow switching @@SESSION.binlog_format from MIXED to STATEMENT
17
--echo # when there are open temp tables and we are logging in statement based format.
18
SET SESSION binlog_format = STATEMENT;
19
SELECT @@SESSION.binlog_format;
21
--echo # Test allow switching @@SESSION.binlog_format from STATEMENT to
22
--echo # STATEMENT when there are open temp tables.
23
SET SESSION binlog_format = STATEMENT;
24
SELECT @@SESSION.binlog_format;
26
INSERT INTO t1 VALUES ('statement based');
27
SELECT @@SESSION.binlog_format;
28
--echo # Test allow switching @@SESSION.binlog_format from STATEMENT to
29
--echo # MIXED when there are open temp tables.
30
SET SESSION binlog_format = MIXED;
31
SELECT @@SESSION.binlog_format;
33
--echo # Test allow switching @@SESSION.binlog_format from MIXED to MIXED
34
--echo # when there are open temp tables.
35
SET SESSION binlog_format = MIXED;
36
SELECT @@SESSION.binlog_format;
38
INSERT INTO t2 VALUES (UUID());
39
SELECT @@SESSION.binlog_format;
41
--echo # Test forbit switching @@SESSION.binlog_format from MIXED to STATEMENT
42
--echo # when there are open temp tables and we are logging in row based format.
43
--ERROR ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR
44
SET SESSION binlog_format = STATEMENT;
45
SELECT @@SESSION.binlog_format;
47
SET SESSION binlog_format = ROW;
48
SELECT @@SESSION.binlog_format;
50
INSERT INTO t1 VALUES ('row based');
51
--echo # Test allow switching @@SESSION.binlog_format from ROW to MIXED
52
--echo # when there are open temp tables.
53
SET SESSION binlog_format = MIXED;
54
SELECT @@SESSION.binlog_format;
56
INSERT INTO t1 VALUES ('row based');
57
--echo # Test allow switching @@SESSION.binlog_format from MIXED to ROW
58
--echo # when there are open temp tables.
59
SET SESSION binlog_format = ROW;
60
SELECT @@SESSION.binlog_format;
62
--echo # Test allow switching @@SESSION.binlog_format from ROW to ROW
63
--echo # when there are open temp tables.
64
SET SESSION binlog_format = ROW;
65
SELECT @@SESSION.binlog_format;
67
INSERT INTO t1 VALUES ('row based');
68
--echo # Test forbit switching @@SESSION.binlog_format from ROW to STATEMENT
69
--echo # when there are open temp tables.
70
--ERROR ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR
71
SET SESSION binlog_format = STATEMENT;
72
SELECT @@SESSION.binlog_format;
74
DROP TEMPORARY TABLE t2;