1SELECT @@SESSION.binlog_format;
2@@SESSION.binlog_format
3MIXED
4CREATE TABLE t1 (a VARCHAR(100));
5CREATE TEMPORARY TABLE t2 (a VARCHAR(100));
6# Test allow switching @@SESSION.binlog_format from MIXED to STATEMENT
7# when there are open temp tables and we are logging in statement based format.
8SET SESSION binlog_format = STATEMENT;
9SELECT @@SESSION.binlog_format;
10@@SESSION.binlog_format
11STATEMENT
12# Test allow switching @@SESSION.binlog_format from STATEMENT to
13# STATEMENT when there are open temp tables.
14SET SESSION binlog_format = STATEMENT;
15SELECT @@SESSION.binlog_format;
16@@SESSION.binlog_format
17STATEMENT
18INSERT INTO t1 VALUES ('statement based');
19SELECT @@SESSION.binlog_format;
20@@SESSION.binlog_format
21STATEMENT
22# Test allow switching @@SESSION.binlog_format from STATEMENT to
23# MIXED when there are open temp tables.
24SET SESSION binlog_format = MIXED;
25SELECT @@SESSION.binlog_format;
26@@SESSION.binlog_format
27MIXED
28# Test allow switching @@SESSION.binlog_format from MIXED to MIXED
29# when there are open temp tables.
30SET SESSION binlog_format = MIXED;
31SELECT @@SESSION.binlog_format;
32@@SESSION.binlog_format
33MIXED
34INSERT INTO t2 VALUES (UUID());
35SELECT @@SESSION.binlog_format;
36@@SESSION.binlog_format
37MIXED
38# Test forbit switching @@SESSION.binlog_format from MIXED to STATEMENT
39# when there are open temp tables and we are logging in row based format.
40SET SESSION binlog_format = STATEMENT;
41ERROR HY000: Cannot switch out of the row-based binary log format when the session has open temporary tables
42SELECT @@SESSION.binlog_format;
43@@SESSION.binlog_format
44MIXED
45SET SESSION binlog_format = ROW;
46SELECT @@SESSION.binlog_format;
47@@SESSION.binlog_format
48ROW
49INSERT INTO t1 VALUES ('row based');
50# Test allow switching @@SESSION.binlog_format from ROW to MIXED
51# when there are open temp tables.
52SET SESSION binlog_format = MIXED;
53SELECT @@SESSION.binlog_format;
54@@SESSION.binlog_format
55MIXED
56INSERT INTO t1 VALUES ('row based');
57# Test allow switching @@SESSION.binlog_format from MIXED to ROW
58# when there are open temp tables.
59SET SESSION binlog_format = ROW;
60SELECT @@SESSION.binlog_format;
61@@SESSION.binlog_format
62ROW
63# Test allow switching @@SESSION.binlog_format from ROW to ROW
64# when there are open temp tables.
65SET SESSION binlog_format = ROW;
66SELECT @@SESSION.binlog_format;
67@@SESSION.binlog_format
68ROW
69INSERT INTO t1 VALUES ('row based');
70# Test forbit switching @@SESSION.binlog_format from ROW to STATEMENT
71# when there are open temp tables.
72SET SESSION binlog_format = STATEMENT;
73ERROR HY000: Cannot switch out of the row-based binary log format when the session has open temporary tables
74SELECT @@SESSION.binlog_format;
75@@SESSION.binlog_format
76ROW
77DROP TEMPORARY TABLE t2;
78DROP TABLE t1;
79