1###############################################################################
2# Bug #21253415 MULTIPLE DROP TEMP TABLE STATEMENTS IN SF CAUSE REPLICATION
3# FAILS USING 5.6 GTID
4# Problem: When there are more than one drop temp table in stored function or
5#          trigger, replication is failing when GTIDs are enabled.
6# Fix: When GTIDs are enabled, it is documented that CREATE/DROP temp tables
7#      are not allowed in Multi Statement Transactions. Stored functions
8#      and Triggers are also considered as another form of Multi Statement
9#      Transactions. To maintain consistency and to avoid the problems that
10#      are mentioned in this bug scenario,  CREATE/DROP temp tables are
11#      disallowed from stored functions and triggers also.
12# Step to reproduce:
13#  1) Create different combinations of functions/triggers with create/drop
14#      temp tables queries
15#  2) Test SELECT, DMLs with those restricted functions and triggers
16#  3) Server should throw error in all the cases.
17###############################################################################
18
19# This test cannot be run in STATEMENT/MIXED mode until
20# "Bug #22134026: ON BINLOGLESS SERVER ENFORCE_GTID_CONSISTENCY DOESN'T CHECK
21# FOR ER1785,1786,1787" is fixed.
22--source include/have_binlog_format_row.inc
23--source include/have_gtid.inc
24--source include/master-slave.inc
25# Initial Setup
26CREATE TABLE trans_table1 (i INT) ENGINE=INNODB;
27INSERT INTO trans_table1 VALUES (1);
28
29CREATE TABLE trans_table2 (i INT) ENGINE=INNODB;
30INSERT INTO trans_table2 VALUES (1);
31
32CREATE TABLE non_trans_table1 (i INT) ENGINE=MYISAM;
33INSERT INTO non_trans_table1 VALUES (1);
34
35CREATE TABLE non_trans_table2 (i INT) ENGINE=MYISAM;
36INSERT INTO non_trans_table2 VALUES (1);
37
38# Case 1: Function/Trigger with create and drop non trans temp table
39--let $func_or_trig_body=CREATE TEMPORARY TABLE tt1(i INT) ENGINE=MyISAM; DROP TEMPORARY TABLE IF EXISTS tt1;
40--source extra/rpl_tests/rpl_gtid_temp_table_in_func_or_trigger.inc
41
42# Case 2: Function/Trigger with create and drop trans temp table
43--let $func_or_trig_body=CREATE TEMPORARY TABLE tt1(i INT) ENGINE=INNODB; DROP TEMPORARY TABLE IF EXISTS tt1;
44--source extra/rpl_tests/rpl_gtid_temp_table_in_func_or_trigger.inc
45
46# Case 3: Function/Trigger with just create non trans temp table
47--let $func_or_trig_body=CREATE TEMPORARY TABLE tt1(i INT) ENGINE=MyISAM;
48--source extra/rpl_tests/rpl_gtid_temp_table_in_func_or_trigger.inc
49
50# Case 4: Function/Trigger with just create trans temp table
51--let $func_or_trig_body=CREATE TEMPORARY TABLE tt1(i INT) ENGINE=INNODB;
52--source extra/rpl_tests/rpl_gtid_temp_table_in_func_or_trigger.inc
53
54# Case 5: Function/Trigger with just drop temp table
55--let $func_or_trig_body=DROP TEMPORARY TABLE IF EXISTS tt1;
56--source extra/rpl_tests/rpl_gtid_temp_table_in_func_or_trigger.inc
57
58# Case 6: Function/Trigger with two create/drop temp tables
59--let $func_or_trig_body=CREATE TEMPORARY TABLE tt1(i INT) ENGINE=MyISAM; CREATE TEMPORARY TABLE tt2(i INT) ENGINE=Innodb; DROP TEMPORARY TABLE IF EXISTS tt1; DROP TEMPORARY TABLE IF EXISTS tt2;
60--source extra/rpl_tests/rpl_gtid_temp_table_in_func_or_trigger.inc
61
62# Case 7: Function/Trigger with DML operation on trans table followed by create/drop temp table
63--let $func_or_trig_body=INSERT INTO trans_table1 VALUES (12); CREATE TEMPORARY TABLE tt1(i INT) ENGINE=MyISAM; DROP TEMPORARY TABLE IF EXISTS tt1;
64--source extra/rpl_tests/rpl_gtid_temp_table_in_func_or_trigger.inc
65
66# Case 8: Function/Trigger with DML operation on non trans table followed by create/drop temp table
67--let $func_or_trig_body=INSERT INTO non_trans_table1 VALUES (12); CREATE TEMPORARY TABLE tt1(i INT) ENGINE=INNODB; DROP TEMPORARY TABLE IF EXISTS tt1;
68--source extra/rpl_tests/rpl_gtid_temp_table_in_func_or_trigger.inc
69
70# Case 9: Function/Trigger with DML operation on trans table after create/drop temp table queries
71--let $func_or_trig_body=CREATE TEMPORARY TABLE tt1(i INT) ENGINE=INNODB; DROP TEMPORARY TABLE IF EXISTS tt1; INSERT INTO non_trans_table1 VALUES (12);
72--source extra/rpl_tests/rpl_gtid_temp_table_in_func_or_trigger.inc
73
74# Case 10: Function/Trigger with DML operation on non trans table after create/drop temp table queries
75--let $func_or_trig_body=CREATE TEMPORARY TABLE tt1(i INT) ENGINE=MyISAM; DROP TEMPORARY TABLE IF EXISTS tt1; INSERT INTO trans_table1 VALUES (12);
76--source extra/rpl_tests/rpl_gtid_temp_table_in_func_or_trigger.inc
77
78# Sync slave with master before checking diff tables
79--source include/sync_slave_sql_with_master.inc
80--source include/rpl_connection_master.inc
81
82# Test that all Slave tables are in sync with Master tables
83--let $diff_tables=master:trans_table1, slave:trans_table1
84--source include/diff_tables.inc
85
86--let $diff_tables=master:trans_table2, slave:trans_table2
87--source include/diff_tables.inc
88
89--let $diff_tables=master:non_trans_table1, slave:non_trans_table1
90--source include/diff_tables.inc
91
92--let $diff_tables=master:non_trans_table2, slave:non_trans_table2
93--source include/diff_tables.inc
94
95# Cleanup
96DROP TABLE trans_table1, trans_table2;
97DROP TABLE non_trans_table1, non_trans_table2;
98--source include/rpl_end.inc
99