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