1#
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.
8#
9
10source include/have_binlog_format_mixed.inc;
11
12SELECT @@SESSION.binlog_format;
13CREATE TABLE t1 (a VARCHAR(100));
14CREATE TEMPORARY TABLE t2 (a VARCHAR(100));
15
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.
18SET SESSION binlog_format = STATEMENT;
19SELECT @@SESSION.binlog_format;
20
21--echo # Test allow switching @@SESSION.binlog_format from STATEMENT to
22--echo # STATEMENT when there are open temp tables.
23SET SESSION binlog_format = STATEMENT;
24SELECT @@SESSION.binlog_format;
25
26INSERT INTO t1 VALUES ('statement based');
27SELECT @@SESSION.binlog_format;
28--echo # Test allow switching @@SESSION.binlog_format from STATEMENT to
29--echo # MIXED when there are open temp tables.
30SET SESSION binlog_format = MIXED;
31SELECT @@SESSION.binlog_format;
32
33--echo # Test allow switching @@SESSION.binlog_format from MIXED to MIXED
34--echo # when there are open temp tables.
35SET SESSION binlog_format = MIXED;
36SELECT @@SESSION.binlog_format;
37
38INSERT INTO t2 VALUES (UUID());
39SELECT @@SESSION.binlog_format;
40
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
44SET SESSION binlog_format = STATEMENT;
45SELECT @@SESSION.binlog_format;
46
47SET SESSION binlog_format = ROW;
48SELECT @@SESSION.binlog_format;
49
50INSERT 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.
53SET SESSION binlog_format = MIXED;
54SELECT @@SESSION.binlog_format;
55
56INSERT 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.
59SET SESSION binlog_format = ROW;
60SELECT @@SESSION.binlog_format;
61
62--echo # Test allow switching @@SESSION.binlog_format from ROW to ROW
63--echo # when there are open temp tables.
64SET SESSION binlog_format = ROW;
65SELECT @@SESSION.binlog_format;
66
67INSERT 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
71SET SESSION binlog_format = STATEMENT;
72SELECT @@SESSION.binlog_format;
73
74DROP TEMPORARY TABLE t2;
75DROP TABLE t1;
76
77