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