1###############################################################################
2#  In this test case, we check how changes to temporary tables are written
3#  to the binary log.
4#
5#
6# B - Begin
7# C - Commit
8# R - Rollback
9#
10#
11# T - Inserts a row into a T-table
12# N-Temp - Inserts a row into a temporary N-table.
13#
14#
15# T-SELECT-N-Temp - Selects from a temporary N-table and inserts
16#                   into a T-table.
17# N-SELECT-N-Temp - Selects from a temporary N-table and inserts
18#                   into a N-table.
19# T-SELECT-T-Temp - Selects from a temporary T-table and inserts
20#                   into a T-table.
21# N-SELECT-T-Temp - Selects from a temporary T-table and inserts
22#                   into a N-table.
23
24# Create-N-Temp - Creates a temporary N-table if a temporary N-table
25#                 was dropped before
26# Create-T-Temp - Creates a temporary T-table if a temporary T-table
27#                 was dropped before
28#
29#
30# Drop-Temp-T-Temp - Drops a temporary T-table if there is any
31# Drop-Temp-N-Temp - Drops a temporary N-table if there is any
32# Drop-Temp-TN-Temp - Drops both a temporary T-table and N-table if there
33#                     is any
34# Drop-Temp-TT-Temp - Drops two temporary T-tables if there is any
35# Drop-Temp-NN-Temp - Drops two temporary N-tables if there is any
36# Drop-Temp-Xe-Temp - Tries to drop a temporary table that does not exist
37# Drop-Temp-NXe-Temp - Drops a temporary N-table if there is any and
38#                      a temporary table that does not exist
39# Drop-Temp-TXe-Temp - Drops a temporary T-table if there is any and
40#                      a temporary table that does not exist
41#
42#
43# Drop-Temp-If-Xe-Temp - Tries to drop a temporary table that does not exist
44# Drop-Temp-If-TXe-Temp - Drops a temporary T-table if there is any and
45#                         a temporary table that does not exist
46#
47#
48# Drop-T - Drops a T-table if there is any
49# Drop-N - Drops a  N-table if there is any
50# Drop-Xe - Tries to drop a table that does not exist
51# Drop-TXe - Drops a T-table if there is any and a table that does
52#            not exist
53# Drop-NXe - Drops a N-table if there is any and a table that does
54#            not exist
55# Drop-TN - Drops both a T-table and N-table if there is any
56# Drop-TT - Drops two T-tables if there is any
57# Drop-NN - Drops two N-tables if there is any
58# Drop-N-TN-Temp - Drops a N-table and both a temporary T-table and
59#                  N-table if there is any
60#
61#
62# Drop-If-Xe - Tries to drop a table that does not exist
63# Drop-If-TXe - Drops a T-table if there is any and a table that does
64#               not exist
65#
66#
67# This test uses the commands available at:
68#   extra/rpl_tests/rpl_drop_create_temp_table.inc
69#
70###############################################################################
71
72
73--echo #########################################################################
74--echo #                            CONFIGURATION
75--echo #########################################################################
76
77call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.");
78
79--let $tot_table= 2
80SET @commands= 'configure';
81--source extra/rpl_tests/rpl_drop_create_temp_table.inc
82
83--echo #########################################################################
84--echo #           1 - Tables dropped by "DROP TEMPORARY TABLE"
85--echo #########################################################################
86connection master;
87
88--echo
89--echo #
90--echo #1) Generates in the binlog what follows:
91--echo #
92SET @commands= 'Drop-Temp-T-Temp';
93--source extra/rpl_tests/rpl_drop_create_temp_table.inc
94
95SET @commands= 'Drop-Temp-N-Temp';
96--source extra/rpl_tests/rpl_drop_create_temp_table.inc
97
98SET @commands= 'Drop-Temp-Xe-Temp';
99--source extra/rpl_tests/rpl_drop_create_temp_table.inc
100
101SET @commands= 'Drop-Temp-If-Xe-Temp';
102--source extra/rpl_tests/rpl_drop_create_temp_table.inc
103
104SET @commands= 'Drop-Temp-TXe-Temp';
105--source extra/rpl_tests/rpl_drop_create_temp_table.inc
106
107SET @commands= 'Drop-Temp-If-TXe-Temp';
108--source extra/rpl_tests/rpl_drop_create_temp_table.inc
109
110SET @commands= 'Drop-Temp-NXe-Temp';
111--source extra/rpl_tests/rpl_drop_create_temp_table.inc
112
113SET @commands= 'Drop-Temp-If-NXe-Temp';
114--source extra/rpl_tests/rpl_drop_create_temp_table.inc
115
116SET @commands= 'Drop-Temp-TN-Temp';
117--source extra/rpl_tests/rpl_drop_create_temp_table.inc
118
119SET @commands= 'Drop-Temp-TT-Temp';
120--source extra/rpl_tests/rpl_drop_create_temp_table.inc
121
122SET @commands= 'Drop-Temp-NN-Temp';
123--source extra/rpl_tests/rpl_drop_create_temp_table.inc
124
125--echo
126--echo #
127--echo #2) Generates in the binlog what follows:
128--echo #
129SET @commands= 'B T Drop-Temp-T-Temp C';
130--source extra/rpl_tests/rpl_drop_create_temp_table.inc
131
132SET @commands= 'B T Drop-Temp-T-Temp N Drop-Temp-T-Temp C';
133--source extra/rpl_tests/rpl_drop_create_temp_table.inc
134
135SET @commands= 'B T Drop-Temp-N-Temp C';
136--source extra/rpl_tests/rpl_drop_create_temp_table.inc
137
138SET @commands= 'B T Drop-Temp-N-Temp N Drop-Temp-N-Temp C';
139--source extra/rpl_tests/rpl_drop_create_temp_table.inc
140
141SET @commands= 'B T Drop-Temp-Xe-Temp C';
142--source extra/rpl_tests/rpl_drop_create_temp_table.inc
143
144SET @commands= 'B T Drop-Temp-Xe-Temp N Drop-Temp-Xe-Temp C';
145--source extra/rpl_tests/rpl_drop_create_temp_table.inc
146
147SET @commands= 'B T Drop-Temp-If-Xe-Temp C';
148--source extra/rpl_tests/rpl_drop_create_temp_table.inc
149
150SET @commands= 'B T Drop-Temp-If-Xe-Temp N Drop-Temp-If-Xe-Temp C';
151--source extra/rpl_tests/rpl_drop_create_temp_table.inc
152
153SET @commands= 'B T Drop-Temp-TXe-Temp C';
154--source extra/rpl_tests/rpl_drop_create_temp_table.inc
155
156SET @commands= 'B T Drop-Temp-TXe-Temp N Drop-Temp-TXe-Temp C';
157--source extra/rpl_tests/rpl_drop_create_temp_table.inc
158
159SET @commands= 'B T Drop-Temp-If-TXe-Temp C';
160--source extra/rpl_tests/rpl_drop_create_temp_table.inc
161
162SET @commands= 'B T Drop-Temp-If-TXe-Temp N Drop-Temp-If-TXe-Temp C';
163--source extra/rpl_tests/rpl_drop_create_temp_table.inc
164
165SET @commands= 'B T Drop-Temp-NXe-Temp C';
166--source extra/rpl_tests/rpl_drop_create_temp_table.inc
167
168SET @commands= 'B T Drop-Temp-NXe-Temp N Drop-Temp-NXe-Temp C';
169--source extra/rpl_tests/rpl_drop_create_temp_table.inc
170
171SET @commands= 'B T Drop-Temp-If-NXe-Temp C';
172--source extra/rpl_tests/rpl_drop_create_temp_table.inc
173
174SET @commands= 'B T Drop-Temp-If-NXe-Temp N Drop-Temp-If-NXe-Temp C';
175--source extra/rpl_tests/rpl_drop_create_temp_table.inc
176
177SET @commands= 'B T Drop-Temp-TN-Temp C';
178--source extra/rpl_tests/rpl_drop_create_temp_table.inc
179
180SET @commands= 'B T Drop-Temp-TN-Temp N Drop-Temp-TN-Temp C';
181--source extra/rpl_tests/rpl_drop_create_temp_table.inc
182
183SET @commands= 'B T Drop-Temp-TT-Temp C';
184--source extra/rpl_tests/rpl_drop_create_temp_table.inc
185
186SET @commands= 'B T Drop-Temp-TT-Temp N Drop-Temp-TT-Temp C';
187--source extra/rpl_tests/rpl_drop_create_temp_table.inc
188
189SET @commands= 'B T Drop-Temp-NN-Temp C';
190--source extra/rpl_tests/rpl_drop_create_temp_table.inc
191
192SET @commands= 'B T Drop-Temp-NN-Temp N Drop-Temp-NN-Temp C';
193--source extra/rpl_tests/rpl_drop_create_temp_table.inc
194
195SET @commands= 'B T Drop-Temp-NN-Temp N Drop-Temp-NN-Temp Ne C';
196--source extra/rpl_tests/rpl_drop_create_temp_table.inc
197
198SET @commands= 'B T Drop-Temp-NN-Temp N Drop-Temp-NN-Temp Te C';
199--source extra/rpl_tests/rpl_drop_create_temp_table.inc
200
201SET @commands= 'B T Drop-Temp-NN-Temp N Drop-Temp-NN-Temp NeT-trig C';
202--source extra/rpl_tests/rpl_drop_create_temp_table.inc
203
204--echo
205--echo #
206--echo #3) Generates in the binlog what follows:
207--echo #
208SET @commands= 'B T Drop-Temp-T-Temp R';
209--source extra/rpl_tests/rpl_drop_create_temp_table.inc
210
211SET @commands= 'B T Drop-Temp-T-Temp N Drop-Temp-T-Temp R';
212--source extra/rpl_tests/rpl_drop_create_temp_table.inc
213
214SET @commands= 'B T Drop-Temp-N-Temp R';
215--source extra/rpl_tests/rpl_drop_create_temp_table.inc
216
217SET @commands= 'B T Drop-Temp-N-Temp N Drop-Temp-N-Temp R';
218--source extra/rpl_tests/rpl_drop_create_temp_table.inc
219
220SET @commands= 'B T Drop-Temp-Xe-Temp R';
221--source extra/rpl_tests/rpl_drop_create_temp_table.inc
222
223SET @commands= 'B T Drop-Temp-Xe-Temp N Drop-Temp-Xe-Temp R';
224--source extra/rpl_tests/rpl_drop_create_temp_table.inc
225
226SET @commands= 'B T Drop-Temp-If-Xe-Temp R';
227--source extra/rpl_tests/rpl_drop_create_temp_table.inc
228
229SET @commands= 'B T Drop-Temp-If-Xe-Temp N Drop-Temp-If-Xe-Temp R';
230--source extra/rpl_tests/rpl_drop_create_temp_table.inc
231
232SET @commands= 'B T Drop-Temp-TXe-Temp R';
233--source extra/rpl_tests/rpl_drop_create_temp_table.inc
234
235SET @commands= 'B T Drop-Temp-TXe-Temp N Drop-Temp-TXe-Temp R';
236--source extra/rpl_tests/rpl_drop_create_temp_table.inc
237
238SET @commands= 'B T Drop-Temp-If-TXe-Temp R';
239--source extra/rpl_tests/rpl_drop_create_temp_table.inc
240
241SET @commands= 'B T Drop-Temp-If-TXe-Temp N Drop-Temp-If-TXe-Temp R';
242--source extra/rpl_tests/rpl_drop_create_temp_table.inc
243
244SET @commands= 'B T Drop-Temp-NXe-Temp R';
245--source extra/rpl_tests/rpl_drop_create_temp_table.inc
246
247SET @commands= 'B T Drop-Temp-NXe-Temp N Drop-Temp-NXe-Temp R';
248--source extra/rpl_tests/rpl_drop_create_temp_table.inc
249
250SET @commands= 'B T Drop-Temp-If-NXe-Temp R';
251--source extra/rpl_tests/rpl_drop_create_temp_table.inc
252
253SET @commands= 'B T Drop-Temp-If-NXe-Temp N Drop-Temp-If-NXe-Temp R';
254--source extra/rpl_tests/rpl_drop_create_temp_table.inc
255
256SET @commands= 'B T Drop-Temp-TN-Temp R';
257--source extra/rpl_tests/rpl_drop_create_temp_table.inc
258
259SET @commands= 'B T Drop-Temp-TN-Temp N Drop-Temp-TN-Temp R';
260--source extra/rpl_tests/rpl_drop_create_temp_table.inc
261
262SET @commands= 'B T Drop-Temp-TT-Temp R';
263--source extra/rpl_tests/rpl_drop_create_temp_table.inc
264
265SET @commands= 'B T Drop-Temp-TT-Temp N Drop-Temp-TT-Temp R';
266--source extra/rpl_tests/rpl_drop_create_temp_table.inc
267
268SET @commands= 'B T Drop-Temp-NN-Temp R';
269--source extra/rpl_tests/rpl_drop_create_temp_table.inc
270
271SET @commands= 'B T Drop-Temp-NN-Temp N Drop-Temp-NN-Temp R';
272--source extra/rpl_tests/rpl_drop_create_temp_table.inc
273
274SET @commands= 'B T Drop-Temp-NN-Temp N Drop-Temp-NN-Temp Ne R';
275--source extra/rpl_tests/rpl_drop_create_temp_table.inc
276
277SET @commands= 'B T Drop-Temp-NN-Temp N Drop-Temp-NN-Temp Te R';
278--source extra/rpl_tests/rpl_drop_create_temp_table.inc
279
280SET @commands= 'B T Drop-Temp-NN-Temp N Drop-Temp-NN-Temp NeT-trig R';
281--source extra/rpl_tests/rpl_drop_create_temp_table.inc
282
283--echo #########################################################################
284--echo #                   2 - Tables dropped by "DROP TABLE"
285--echo #########################################################################
286connection master;
287
288--echo
289--echo #
290--echo #1) Generates in the binlog what follows:
291--echo #
292SET @commands= 'Drop-T';
293--source extra/rpl_tests/rpl_drop_create_temp_table.inc
294
295SET @commands= 'Drop-N';
296--source extra/rpl_tests/rpl_drop_create_temp_table.inc
297
298SET @commands= 'Drop-Xe';
299--source extra/rpl_tests/rpl_drop_create_temp_table.inc
300
301SET @commands= 'Drop-If-Xe';
302--source extra/rpl_tests/rpl_drop_create_temp_table.inc
303
304SET @commands= 'Drop-TXe';
305--source extra/rpl_tests/rpl_drop_create_temp_table.inc
306
307SET @commands= 'Drop-If-TXe';
308--source extra/rpl_tests/rpl_drop_create_temp_table.inc
309
310SET @commands= 'Drop-NXe';
311--source extra/rpl_tests/rpl_drop_create_temp_table.inc
312
313SET @commands= 'Drop-If-NXe';
314--source extra/rpl_tests/rpl_drop_create_temp_table.inc
315
316SET @commands= 'Drop-TN';
317--source extra/rpl_tests/rpl_drop_create_temp_table.inc
318
319SET @commands= 'Drop-TT';
320--source extra/rpl_tests/rpl_drop_create_temp_table.inc
321
322SET @commands= 'Drop-NN';
323--source extra/rpl_tests/rpl_drop_create_temp_table.inc
324
325SET @commands= 'Drop-N-TN-Temp';
326--source extra/rpl_tests/rpl_drop_create_temp_table.inc
327
328SET @commands= 'Drop-TN-Temp';
329--source extra/rpl_tests/rpl_drop_create_temp_table.inc
330
331--echo
332--echo #
333--echo #2) Generates in the binlog what follows:
334--echo #
335SET @commands= 'B T Drop-T';
336--source extra/rpl_tests/rpl_drop_create_temp_table.inc
337
338SET @commands= 'B T Drop-N';
339--source extra/rpl_tests/rpl_drop_create_temp_table.inc
340
341SET @commands= 'B T Drop-Xe';
342--source extra/rpl_tests/rpl_drop_create_temp_table.inc
343
344SET @commands= 'B T Drop-If-Xe';
345--source extra/rpl_tests/rpl_drop_create_temp_table.inc
346
347SET @commands= 'B T Drop-TXe';
348--source extra/rpl_tests/rpl_drop_create_temp_table.inc
349
350SET @commands= 'B T Drop-If-TXe';
351--source extra/rpl_tests/rpl_drop_create_temp_table.inc
352
353SET @commands= 'B T Drop-NXe';
354--source extra/rpl_tests/rpl_drop_create_temp_table.inc
355
356SET @commands= 'B T Drop-If-NXe';
357--source extra/rpl_tests/rpl_drop_create_temp_table.inc
358
359SET @commands= 'B T Drop-TN';
360--source extra/rpl_tests/rpl_drop_create_temp_table.inc
361
362SET @commands= 'B T Drop-TT';
363--source extra/rpl_tests/rpl_drop_create_temp_table.inc
364
365SET @commands= 'B T Drop-NN';
366--source extra/rpl_tests/rpl_drop_create_temp_table.inc
367
368SET @commands= 'B T Drop-N-TN-Temp';
369--source extra/rpl_tests/rpl_drop_create_temp_table.inc
370
371SET @commands= 'B T Drop-TN-Temp';
372--source extra/rpl_tests/rpl_drop_create_temp_table.inc
373
374--echo #########################################################################
375--echo #                      3 - CREATE TEMPORARY TABLE
376--echo #########################################################################
377connection master;
378
379--echo
380--echo #
381--echo #1) Generates in the binlog what follows:
382--echo #
383SET @commands= 'Drop-Temp-N-Temp Create-N-Temp Drop-Temp-N-Temp Drop-Temp-N-Temp';
384--source extra/rpl_tests/rpl_drop_create_temp_table.inc
385
386SET @commands= 'Drop-Temp-T-Temp Create-T-Temp Drop-Temp-T-Temp Drop-Temp-T-Temp';
387--source extra/rpl_tests/rpl_drop_create_temp_table.inc
388
389--echo
390--echo #
391--echo #2) Generates in the binlog what follows:
392--echo #
393SET @commands= 'B T Drop-Temp-N-Temp Create-N-Temp Drop-Temp-N-Temp Drop-Temp-N-Temp C';
394--source extra/rpl_tests/rpl_drop_create_temp_table.inc
395
396SET @commands= 'B Drop-Temp-T-Temp Create-T-Temp Drop-Temp-T-Temp Drop-Temp-T-Temp C';
397--source extra/rpl_tests/rpl_drop_create_temp_table.inc
398
399SET @commands= 'B Drop-Temp-T-Temp Create-T-Temp Drop-Temp-T-Temp Drop-Temp-T-Temp Ne C';
400--source extra/rpl_tests/rpl_drop_create_temp_table.inc
401
402SET @commands= 'B Drop-Temp-T-Temp Create-T-Temp Drop-Temp-T-Temp Drop-Temp-T-Temp Te C';
403--source extra/rpl_tests/rpl_drop_create_temp_table.inc
404
405SET @commands= 'B Drop-Temp-T-Temp Create-T-Temp Drop-Temp-T-Temp Drop-Temp-T-Temp NeT-trig C';
406--source extra/rpl_tests/rpl_drop_create_temp_table.inc
407
408--echo
409--echo #
410--echo #3) Generates in the binlog what follows:
411--echo #
412SET @commands= 'B T Drop-Temp-N-Temp Create-N-Temp Drop-Temp-N-Temp Drop-Temp-N-Temp R';
413--source extra/rpl_tests/rpl_drop_create_temp_table.inc
414
415SET @commands= 'B Drop-Temp-T-Temp Create-T-Temp Drop-Temp-T-Temp Drop-Temp-T-Temp R';
416--source extra/rpl_tests/rpl_drop_create_temp_table.inc
417
418SET @commands= 'B Drop-Temp-T-Temp Create-T-Temp Drop-Temp-T-Temp Drop-Temp-T-Temp Ne R';
419--source extra/rpl_tests/rpl_drop_create_temp_table.inc
420
421SET @commands= 'B Drop-Temp-T-Temp Create-T-Temp Drop-Temp-T-Temp Drop-Temp-T-Temp Te R';
422--source extra/rpl_tests/rpl_drop_create_temp_table.inc
423
424SET @commands= 'B Drop-Temp-T-Temp Create-T-Temp Drop-Temp-T-Temp Drop-Temp-T-Temp NeT-trig R';
425--source extra/rpl_tests/rpl_drop_create_temp_table.inc
426
427--echo #########################################################################
428--echo #                     4 -  CHANGING TEMPORARY TABLES
429--echo #########################################################################
430connection master;
431
432--echo
433--echo #
434--echo #1) Generates in the binlog what follows:
435--echo #
436SET @commands= 'B N N-Temp N-SELECT-N-Temp N-Temp C';
437--source extra/rpl_tests/rpl_drop_create_temp_table.inc
438
439SET @commands= 'B N N-Temp T-SELECT-N-Temp N-Temp C';
440--source extra/rpl_tests/rpl_drop_create_temp_table.inc
441
442SET @commands= 'B N N-Temp N-SELECT-T-Temp N-Temp C';
443--source extra/rpl_tests/rpl_drop_create_temp_table.inc
444
445SET @commands= 'B N N-Temp T-SELECT-T-Temp N-Temp C';
446--source extra/rpl_tests/rpl_drop_create_temp_table.inc
447
448SET @commands= 'B N N-Temp T-SELECT-T-Temp N-Temp Ne C';
449--source extra/rpl_tests/rpl_drop_create_temp_table.inc
450
451SET @commands= 'B N N-Temp T-SELECT-T-Temp N-Temp Te C';
452--source extra/rpl_tests/rpl_drop_create_temp_table.inc
453
454SET @commands= 'B N N-Temp T-SELECT-T-Temp N-Temp NeT-trig C';
455--source extra/rpl_tests/rpl_drop_create_temp_table.inc
456
457--echo
458--echo
459--echo #
460--echo #2) Generates in the binlog what follows:
461--echo #
462SET @commands= 'B N N-Temp N-SELECT-N-Temp N-Temp R';
463--source extra/rpl_tests/rpl_drop_create_temp_table.inc
464
465SET @commands= 'B N N-Temp T-SELECT-N-Temp N-Temp R';
466--source extra/rpl_tests/rpl_drop_create_temp_table.inc
467
468SET @commands= 'B N N-Temp N-SELECT-T-Temp N-Temp R';
469--source extra/rpl_tests/rpl_drop_create_temp_table.inc
470
471SET @commands= 'B N N-Temp T-SELECT-T-Temp N-Temp R';
472--source extra/rpl_tests/rpl_drop_create_temp_table.inc
473
474SET @commands= 'B N N-Temp T-SELECT-T-Temp N-Temp Ne R';
475--source extra/rpl_tests/rpl_drop_create_temp_table.inc
476
477SET @commands= 'B N N-Temp T-SELECT-T-Temp N-Temp Te R';
478--source extra/rpl_tests/rpl_drop_create_temp_table.inc
479
480SET @commands= 'B N N-Temp T-SELECT-T-Temp N-Temp NeT-trig R';
481--source extra/rpl_tests/rpl_drop_create_temp_table.inc
482
483--echo ###################################################################################
484--echo #                               CHECK CONSISTENCY
485--echo ###################################################################################
486connection master;
487sync_slave_with_master;
488
489let $MYSQLD_DATADIR= `SELECT @@datadir`;
490
491if (`select @@session.binlog_format != 'STATEMENT'`)
492{
493  --exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLD_DATADIR/test-temporary-master.sql
494  --exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLD_DATADIR/test-temporary-slave.sql
495  --diff_files $MYSQLD_DATADIR/test-temporary-master.sql $MYSQLD_DATADIR/test-temporary-slave.sql
496}
497
498--echo #########################################################################
499--echo #                           CLEAN
500--echo #########################################################################
501SET @commands= 'clean';
502--source extra/rpl_tests/rpl_drop_create_temp_table.inc
503