^^&&^^

Edit File: rpl_gtid_temp_table.test

###############################################################################
# Bug #21253415 MULTIPLE DROP TEMP TABLE STATEMENTS IN SF CAUSE REPLICATION
# FAILS USING 5.6 GTID
# Problem: When there are more than one drop temp table in stored function or
#          trigger, replication is failing when GTIDs are enabled.
# Fix: When GTIDs are enabled, it is documented that CREATE/DROP temp tables
#      are not allowed in Multi Statement Transactions. Stored functions
#      and Triggers are also considered as another form of Multi Statement
#      Transactions. To maintain consistency and to avoid the problems that
#      are mentioned in this bug scenario,  CREATE/DROP temp tables are
#      disallowed from stored functions and triggers also.
# Step to reproduce:
#  1) Create different combinations of functions/triggers with create/drop
#      temp tables queries
#  2) Test SELECT, DMLs with those restricted functions and triggers
#  3) Server should throw error in all the cases.
###############################################################################

# This test cannot be run in STATEMENT/MIXED mode until
# "Bug #22134026: ON BINLOGLESS SERVER ENFORCE_GTID_CONSISTENCY DOESN'T CHECK
# FOR ER1785,1786,1787" is fixed.
--source include/have_binlog_format_row.inc
--source include/have_gtid.inc
--source include/master-slave.inc
# Initial Setup
CREATE TABLE trans_table1 (i INT) ENGINE=INNODB;
INSERT INTO trans_table1 VALUES (1);

CREATE TABLE trans_table2 (i INT) ENGINE=INNODB;
INSERT INTO trans_table2 VALUES (1);

CREATE TABLE non_trans_table1 (i INT) ENGINE=MYISAM;
INSERT INTO non_trans_table1 VALUES (1);

CREATE TABLE non_trans_table2 (i INT) ENGINE=MYISAM;
INSERT INTO non_trans_table2 VALUES (1);

# Case 1: Function/Trigger with create and drop non trans temp table
--let $func_or_trig_body=CREATE TEMPORARY TABLE tt1(i INT) ENGINE=MyISAM; DROP TEMPORARY TABLE IF EXISTS tt1;
--source extra/rpl_tests/rpl_gtid_temp_table_in_func_or_trigger.inc

# Case 2: Function/Trigger with create and drop trans temp table
--let $func_or_trig_body=CREATE TEMPORARY TABLE tt1(i INT) ENGINE=INNODB; DROP TEMPORARY TABLE IF EXISTS tt1;
--source extra/rpl_tests/rpl_gtid_temp_table_in_func_or_trigger.inc

# Case 3: Function/Trigger with just create non trans temp table
--let $func_or_trig_body=CREATE TEMPORARY TABLE tt1(i INT) ENGINE=MyISAM;
--source extra/rpl_tests/rpl_gtid_temp_table_in_func_or_trigger.inc

# Case 4: Function/Trigger with just create trans temp table
--let $func_or_trig_body=CREATE TEMPORARY TABLE tt1(i INT) ENGINE=INNODB;
--source extra/rpl_tests/rpl_gtid_temp_table_in_func_or_trigger.inc

# Case 5: Function/Trigger with just drop temp table
--let $func_or_trig_body=DROP TEMPORARY TABLE IF EXISTS tt1;
--source extra/rpl_tests/rpl_gtid_temp_table_in_func_or_trigger.inc

# Case 6: Function/Trigger with two create/drop temp tables
--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;
--source extra/rpl_tests/rpl_gtid_temp_table_in_func_or_trigger.inc

# Case 7: Function/Trigger with DML operation on trans table followed by create/drop temp table
--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;
--source extra/rpl_tests/rpl_gtid_temp_table_in_func_or_trigger.inc

# Case 8: Function/Trigger with DML operation on non trans table followed by create/drop temp table
--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;
--source extra/rpl_tests/rpl_gtid_temp_table_in_func_or_trigger.inc

# Case 9: Function/Trigger with DML operation on trans table after create/drop temp table queries
--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);
--source extra/rpl_tests/rpl_gtid_temp_table_in_func_or_trigger.inc

# Case 10: Function/Trigger with DML operation on non trans table after create/drop temp table queries
--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);
--source extra/rpl_tests/rpl_gtid_temp_table_in_func_or_trigger.inc

# Sync slave with master before checking diff tables
--source include/sync_slave_sql_with_master.inc
--source include/rpl_connection_master.inc

# Test that all Slave tables are in sync with Master tables
--let $diff_tables=master:trans_table1, slave:trans_table1
--source include/diff_tables.inc

--let $diff_tables=master:trans_table2, slave:trans_table2
--source include/diff_tables.inc

--let $diff_tables=master:non_trans_table1, slave:non_trans_table1
--source include/diff_tables.inc

--let $diff_tables=master:non_trans_table2, slave:non_trans_table2
--source include/diff_tables.inc

# Cleanup
DROP TABLE trans_table1, trans_table2;
DROP TABLE non_trans_table1, non_trans_table2;
--source include/rpl_end.inc