3
# Test that temporary tables are correctly replicated after switching to ROW format in MIX mode.
4
# This test case will test the condition of the bug#40013.
6
# 1: create temp table on connection 'master';
7
# 2: switch to ROW format using 'INSERT INTO t1 VALUES (UUID());'
8
# 3: disconnect 'master' and connect to a new connection 'master1';
9
# 4: sync to slave and check the number of temp tables on slave.
12
source include/master-slave.inc;
13
source include/have_binlog_format_mixed.inc;
14
source include/have_innodb.inc;
15
source include/not_gtid_enabled.inc;
17
--echo ==== Initialize ====
22
CREATE TABLE t1 (a CHAR(48));
23
CREATE TEMPORARY TABLE t1_tmp1(a INT);
24
INSERT INTO t1 VALUES (UUID());
27
sync_slave_with_master;
29
--echo ==== Verify results on slave ====
30
SHOW STATUS LIKE "Slave_open_temp_tables";
39
# waiting DROP TEMPORARY TABLE event to be written into binlog
40
let $wait_binlog_event= DROP;
41
source include/wait_for_binlog_event.inc;
44
sync_slave_with_master;
46
--echo ==== Verify results on slave ====
47
SHOW STATUS LIKE "Slave_open_temp_tables";
49
--echo ==== Clean up ====
52
--let $rpl_connection_name= master
53
--let $rpl_server_number= 1
54
--source include/rpl_connect.inc
59
sync_slave_with_master;
62
# BUG#43046: mixed mode switch to row format with temp table lead to wrong
68
# 1. Temporary tables cannot be logged using the row-based
69
# format. Thus, once row-based logging is used, all subsequent
70
# statements using that table are unsafe, and we approximate this
71
# condition by treating all statements made by that client as
72
# unsafe until the client no longer holds any temporary tables.
74
# 2. Two different connections can use the same temporary table
75
# name without conflicting with each other or with an
76
# existing non-TEMPORARY table of the same name.
81
# The test is implemented as follows:
82
# 1. create regular tables
83
# 2. create a temporary table t1_tmp: should be logged as statement
84
# 3. issue an alter table: should be logged as statement
85
# 4. issue statement that forces switch to RBR
86
# 5. create another temporary table t2_tmp: should not be logged
87
# 6. issue alter table on t1_tmp: should not be logged
88
# 7. drop t1_tmp and regular table on same statement: should log both in
89
# statement format (but different statements)
90
# 8. issue deterministic insert: logged as row (because t2_tmp still
92
# 9. drop t2_tmp and issue deterministic statement: should log drop and
93
# query in statement format (show switch back to STATEMENT format)
94
# 10. in the end the slave should not have open temp tables.
97
--source include/rpl_reset.inc
100
# action: setup environment
101
CREATE TABLE t1 (a int);
102
CREATE TABLE t2 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) );
103
CREATE TABLE t3 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) );
104
CREATE TRIGGER tr1 AFTER DELETE ON t2 FOR EACH ROW INSERT INTO t3 () VALUES ();
106
# assertion: assert that CREATE is logged as STATEMENT
107
CREATE TEMPORARY TABLE t1_tmp (i1 int);
109
# assertion: assert that ALTER TABLE is logged as STATEMENT
110
ALTER TABLE t1_tmp ADD COLUMN b INT;
112
# action: force switch to RBR
115
# assertion: assert that t2_tmp will not make into the binlog (RBR logging atm)
116
CREATE TEMPORARY TABLE t2_tmp (a int);
118
# assertion: assert that ALTER TABLE on t1_tmp will not make into the binlog
119
ALTER TABLE t1_tmp ADD COLUMN c INT;
121
-- echo ### assertion: assert that there is one open temp table on slave
122
-- sync_slave_with_master
123
SHOW STATUS LIKE 'Slave_open_temp_tables';
127
# assertion: assert that both drops are logged
128
DROP TABLE t1_tmp, t2;
130
# assertion: assert that statement is logged as row (master still has one
131
# opened temporary table - t2_tmp.
132
INSERT INTO t1 VALUES (1);
134
# assertion: assert that DROP TABLE *is* logged despite CREATE is not.
135
DROP TEMPORARY TABLE t2_tmp;
137
# assertion: assert that statement is now logged as STMT (mixed mode switches
138
# back to STATEMENT).
139
INSERT INTO t1 VALUES (2);
141
-- sync_slave_with_master
143
-- echo ### assertion: assert that slave has no temporary tables opened
144
SHOW STATUS LIKE 'Slave_open_temp_tables';
148
# action: drop remaining tables
151
-- sync_slave_with_master
153
-- source include/show_binlog_events.inc
156
--echo # Bug#55478 Row events wrongly apply on the temporary table of the same name
157
--echo # ==========================================================================
160
let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1);
161
let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
163
--echo # The statement should be binlogged
164
CREATE TEMPORARY TABLE t1(c1 INT) ENGINE=InnoDB;
167
--echo # Case 1: CREATE TABLE t1 ... SELECT
168
--echo # ----------------------------------
169
let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1);
170
let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
173
--echo # The statement generates row events on t1. And the rows events should
174
--echo # be inserted into the base table on slave.
175
CREATE TABLE t1 ENGINE=MyISAM SELECT rand();
177
source include/show_binlog_events.inc;
178
let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1);
179
let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
182
--echo # Case 2: DROP TEMPORARY TABLE in a transacation
183
--echo # ----------------------------------------------
187
DROP TEMPORARY TABLE t1;
189
# The patch for BUG#55478 fixed the problem only on RBR. The problem on SBR
190
# will be fixed by the patch for bug#55709. So This statement cannot be
191
# executed until Bug#55709 is fixed
193
# INSERT INTO t1 VALUES(1);
195
--echo # The rows event will binlogged before 'DROP TEMPORARY TABLE t1',
196
--echo # as t1 is non-transactional table
197
INSERT INTO t1 VALUES(Rand());
200
source include/show_binlog_events.inc;
202
--sync_slave_with_master
204
--echo # Compare the base table.
205
--let $diff_tables= master:t1, slave:t1
206
--source include/diff_tables.inc
212
sync_slave_with_master;
214
--source include/rpl_end.inc