1# ==== Purpose ====
2#
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.
5# The test step is:
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.
10#
11
12source include/have_binlog_format_mixed.inc;
13source include/have_innodb.inc;
14source include/master-slave.inc;
15
16--echo ==== Initialize ====
17
18--connection master
19
20CREATE TABLE t1 (a CHAR(48));
21CREATE TEMPORARY TABLE t1_tmp1(a INT);
22INSERT INTO t1 VALUES (UUID());
23
24sync_slave_with_master;
25
26--echo ==== Verify results on slave ====
27SHOW STATUS LIKE "Slave_open_temp_tables";
28
29--connection master
30
31disconnect master;
32--connection master1
33
34# waiting DROP TEMPORARY TABLE event to be written into binlog
35let $wait_binlog_event= DROP;
36source include/wait_for_binlog_event.inc;
37
38sync_slave_with_master;
39
40--echo ==== Verify results on slave ====
41SHOW STATUS LIKE "Slave_open_temp_tables";
42
43--echo ==== Clean up ====
44
45--let $rpl_connection_name= master
46--let $rpl_server_number= 1
47--source include/rpl_connect.inc
48--connection master
49DROP TABLE t1;
50
51sync_slave_with_master;
52
53#
54# BUG#43046: mixed mode switch to row format with temp table lead to wrong
55# result
56#
57# NOTES
58# =====
59#
60#  1. Temporary tables cannot be logged using the row-based
61#     format. Thus, once row-based logging is used, all subsequent
62#     statements using that table are unsafe, and we approximate this
63#     condition by treating all statements made by that client as
64#     unsafe until the client no longer holds any temporary tables.
65#
66#  2. Two different connections can use the same temporary table
67#     name without conflicting with each other or with an
68#     existing non-TEMPORARY table of the same name.
69#
70# DESCRIPTION
71# ===========
72#
73#   The test is implemented as follows:
74#     1. create regular tables
75#     2. create a temporary table t1_tmp: should be logged as statement
76#     3. issue an alter table: should be logged as statement
77#     4. issue statement that forces switch to RBR
78#     5. create another temporary table t2_tmp: should not be logged
79#     6. issue alter table on t1_tmp: should not be logged
80#     7. drop t1_tmp and regular table on same statement: should log both in
81#        statement format (but different statements)
82#     8. issue deterministic insert: logged as row (because t2_tmp still
83#        exists).
84#     9. drop t2_tmp and issue deterministic statement: should log drop and
85#        query in statement format (show switch back to STATEMENT format)
86#    10. in the end the slave should not have open temp tables.
87#
88
89--source include/rpl_reset.inc
90-- connection master
91
92# action: setup environment
93CREATE TABLE t1 (a int) engine=innodb;
94CREATE TABLE t2 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) );
95CREATE TABLE t3 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) );
96CREATE TRIGGER tr1 AFTER DELETE ON t2 FOR EACH ROW INSERT INTO t3 () VALUES ();
97
98# assertion: assert that CREATE is logged as STATEMENT
99CREATE TEMPORARY TABLE t1_tmp (i1 int);
100
101# assertion: assert that ALTER TABLE is logged as STATEMENT
102ALTER TABLE t1_tmp ADD COLUMN b INT;
103
104# action: force switch to RBR
105INSERT INTO t1 values(1);
106INSERT INTO t2 (i1) select * from t1;
107
108# assertion: assert that t2_tmp will not make into the binlog (RBR logging atm)
109CREATE TEMPORARY TABLE t2_tmp (a int);
110
111# assertion: assert that ALTER TABLE on t1_tmp will not make into the binlog
112ALTER TABLE t1_tmp ADD COLUMN c INT;
113
114-- echo ### assertion: assert that there is one open temp table on slave
115-- sync_slave_with_master
116SHOW STATUS LIKE 'Slave_open_temp_tables';
117
118-- connection master
119
120# assertion: assert that both drops are logged
121DROP TABLE t1_tmp, t2;
122
123# assertion: assert that statement is logged as row (master still has one
124#            opened temporary table - t2_tmp.
125INSERT INTO t1 VALUES (1);
126
127# assertion: assert that DROP TABLE *is* logged despite CREATE is not.
128DROP TEMPORARY TABLE t2_tmp;
129
130# assertion: assert that statement is now logged as STMT (mixed mode switches
131#            back to STATEMENT).
132INSERT INTO t1 VALUES (2);
133
134-- sync_slave_with_master
135
136-- echo ### assertion: assert that slave has no temporary tables opened
137SHOW STATUS LIKE 'Slave_open_temp_tables';
138
139-- connection master
140
141# action: drop remaining tables
142DROP TABLE t3, t1;
143
144-- sync_slave_with_master
145
146-- source include/show_binlog_events.inc
147
148--echo
149--echo # Bug#55478 Row events wrongly apply on the temporary table of the same name
150--echo # ==========================================================================
151connection master;
152
153let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1);
154let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
155
156--echo # The statement should be binlogged
157CREATE TEMPORARY TABLE t1(c1 INT) ENGINE=InnoDB;
158
159--echo
160--echo # Case 1: CREATE TABLE t1 ... SELECT
161--echo # ----------------------------------
162let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1);
163let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
164
165--echo
166--echo # The statement generates row events on t1. And the rows events should
167--echo # be inserted into the base table on slave.
168CREATE TABLE t1 ENGINE=MyISAM SELECT rand();
169
170source include/show_binlog_events.inc;
171let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1);
172let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
173
174--echo
175--echo # Case 2: DROP TEMPORARY TABLE in a transacation
176--echo # ----------------------------------------------
177--echo
178
179BEGIN;
180DROP TEMPORARY TABLE t1;
181
182# The patch for BUG#55478 fixed the problem only on RBR. The problem on SBR
183# will be fixed by the patch for bug#55709. So This statement cannot be
184# executed until Bug#55709 is fixed
185#
186# INSERT INTO t1 VALUES(1);
187
188--echo # The rows event will binlogged after 'INSERT INTO t1 VALUES(1)'
189--disable_warnings
190INSERT IGNORE INTO t1 VALUES(uuid()+0);
191--enable_warnings
192COMMIT;
193
194source include/show_binlog_events.inc;
195
196--sync_slave_with_master
197
198--echo # Compare the base table.
199--let $diff_tables= master:t1, slave:t1
200--source include/diff_tables.inc
201
202--echo
203connection master;
204DROP TABLE t1;
205--source include/rpl_end.inc
206