Skip to content

Commit 27ed5bb

Browse files
author
Venkatesh Duggirala
committed
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. Analysis: In ROW based replication format, even though CREATE TEMPORARY TABLE query is not replicated, DROP TEMPORARY TABLE queries are replicated to achieve proper clean up on Slave end (CREATE TEMPORARY TABLE query would have executed and replicated when the replication format is STATEMENT) by adding 'IF EXISTS' clause. When DROP TEMPORARY TABLE query is in a stored function along with some DML statements, the binlog equivalent query for that function execution will look like BEGIN DROP TEMP TABLE ... ROW EVENT FOR DML 1 ROW EVENT FOR DML 2 END But when GTIDs are enabled, it is documented that CREATE/DROP TEMPORARY TABLE queries are not allowed in Multi Statement Transactions because half executed gtid transactions (rolled back of these transactions) can leave these temporary tables in a bad state. In the old code, one DROP TEMPORARY TABLE in a function is working fine because the 'DROP TEMP TABLE' is going into STMT_CACHE (which does not be wrapped with BEGIN/COMMIT). //STMT_CACHE GTID_EVENT DROP TEMP TABLE ... //TRANS_CACHE GTID_EVENT BEGIN ROW EVENT FOR DML 1 ROW EVENT FOR DML 2 END But if the function contains two 'DROP TEMP TABLE's, both of them are going into 'STMT_CACHE' (which does not be wrapped with BEGIN/COMMIT) and STMT_CACHE with one gtid_event cannot accommodate two separate DROP TEMP TABLE queries. And with above Multi Statement Transactions + GTID restriction, we cannot add 'BEGIN/COMMIT'. Fix: Stored functions and Triggers are also considered as another form of Multi Statement Transactions across the server. To maintain gtid 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 just like how they were restricted in Multi Statement Transactions. Now function execution that has CREATE/DROP TEMP TABLES will throw ER_GTID_UNSAFE_CREATE_DROP_TEMPORARY_TABLE_IN_TRANSACTION. ("When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, the statements CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can be executed in a non-transactional context only, and require that AUTOCOMMIT = 1. These statements are not allowed in Functions or Triggers also as they are also considered as Multi Statement transaction.)
1 parent c7fb24a commit 27ed5bb

6 files changed

Lines changed: 1441 additions & 6 deletions

File tree

‎mysql-test/extra/rpl_tests/rpl_gtid_temp_table_in_func_or_trigger.inc‎

Lines changed: 120 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,120 @@
1+
# CREATE FUNCTION USING func_or_trig_body PROVIDED BY MAIN TEST SCRIPT
2+
--let $header=CREATE FUNCTION func1 () RETURNS varchar(30) CHARSET utf8
3+
--let $begin=BEGIN
4+
--let $return=RETURN 0;
5+
--let $end=END
6+
--let $cmd=`select CONCAT("$header", "\n","$begin","\n", "$func_or_trig_body","\n", "$return", "\n","$end")`
7+
--eval $cmd
8+
9+
# USE THAT FUNCTION IN SELECT QUERY
10+
--error ER_GTID_UNSAFE_CREATE_DROP_TEMPORARY_TABLE_IN_TRANSACTION
11+
SELECT func1();
12+
13+
# USE THAT FUNCTION IN SELECT QUERY IN MULTI TRANSACTION STATEMENT
14+
START TRANSACTION;
15+
--error ER_GTID_UNSAFE_CREATE_DROP_TEMPORARY_TABLE_IN_TRANSACTION
16+
SELECT func1();
17+
COMMIT;
18+
19+
# iter 1 : INSERT/UPDATE/DELETE QUERY ON transaction table USING THAT FUNCTION
20+
# iter 2 : INSERT/UPDATE/DELETE QUERY ON non transaction table USING THAT FUNCTION
21+
--let $iter=1
22+
while ($iter <=2)
23+
{
24+
if ($iter == 1)
25+
{
26+
--let $table=trans_table2
27+
}
28+
if ($iter == 2)
29+
{
30+
--let $table=non_trans_table2
31+
}
32+
--let $insert_cmd= INSERT INTO $table VALUES (func1())
33+
--error ER_GTID_UNSAFE_CREATE_DROP_TEMPORARY_TABLE_IN_TRANSACTION, ER_GTID_UNSAFE_NON_TRANSACTIONAL_TABLE
34+
--eval $insert_cmd
35+
36+
--let $update_cmd= UPDATE $table SET i=func1()
37+
--error ER_GTID_UNSAFE_CREATE_DROP_TEMPORARY_TABLE_IN_TRANSACTION, ER_GTID_UNSAFE_NON_TRANSACTIONAL_TABLE
38+
--eval $update_cmd
39+
40+
--let $delete_cmd= DELETE FROM $table WHERE i=func1()
41+
--error ER_GTID_UNSAFE_CREATE_DROP_TEMPORARY_TABLE_IN_TRANSACTION, ER_GTID_UNSAFE_NON_TRANSACTIONAL_TABLE
42+
--eval $delete_cmd
43+
44+
--inc $iter
45+
}
46+
47+
# DELETE THAT FUNCTION
48+
DROP FUNCTION func1;
49+
50+
# CREATE A TRIGGER USING func_or_trig_body PROVIDED BY MAIN TEST SCRIPT
51+
# iter1: BEFORE INSERT ON transaction table
52+
# iter2: AFTER INSERT ON transaction table
53+
# iter3: BEFORE UPDATE ON transaction table
54+
# iter4: AFTER UPDATE ON transaction table
55+
# iter5: BEFORE DELETE ON transaction table
56+
# iter6: AFTER DELETE ON transaction table
57+
# iter7: BEFORE INSERT ON non transaction table
58+
# iter8: AFTER INSERT ON non transaction table
59+
# iter9: BEFORE UPDATE ON non transaction table
60+
# iter10: AFTER UPDATE ON non transaction table
61+
# iter11: BEFORE DELETE ON non transaction table
62+
# iter12: AFTER DELETE ON non transaction table
63+
64+
# And all 12 iterations should throw an error
65+
--let $iter=1
66+
while ($iter <= 12)
67+
{
68+
--let $header=CREATE TRIGGER trigger1
69+
--let $for_each_row= FOR EACH ROW
70+
--let $begin=BEGIN
71+
--let $end=END
72+
73+
--let $table=trans_table2
74+
if ($iter >= 7)
75+
{
76+
--let $table=non_trans_table2
77+
}
78+
79+
--let $before_or_after=BEFORE
80+
if (`SELECT $iter IN (2, 4, 6, 8, 10, 12)`)
81+
{
82+
--let $before_or_after=AFTER
83+
}
84+
85+
--let $insert_or_update_or_delete=INSERT
86+
if (`SELECT $iter IN (3, 4, 9, 10)`)
87+
{
88+
--let $insert_or_update_or_delete=UPDATE
89+
}
90+
if (`SELECT $iter IN (5, 6, 11, 12)`)
91+
{
92+
--let $insert_or_update_or_delete=DELETE
93+
}
94+
95+
--let $cmd=`select CONCAT("$header", "\n", "$before_or_after", " ", "$insert_or_update_or_delete", " ON ", "$table", " ", "$for_each_row", "\n", "$begin","\n", "$func_or_trig_body","\n", "$end")`
96+
--eval $cmd
97+
98+
if (`SELECT $iter IN (1, 2, 7, 8)`)
99+
{
100+
--let $insert_cmd= INSERT INTO $table VALUES (10)
101+
--error ER_GTID_UNSAFE_CREATE_DROP_TEMPORARY_TABLE_IN_TRANSACTION, ER_GTID_UNSAFE_NON_TRANSACTIONAL_TABLE
102+
--eval $insert_cmd
103+
}
104+
105+
if (`SELECT $iter IN (3, 4, 9, 10)`)
106+
{
107+
--let $update_cmd= UPDATE $table SET i=12
108+
--error ER_GTID_UNSAFE_CREATE_DROP_TEMPORARY_TABLE_IN_TRANSACTION, ER_GTID_UNSAFE_NON_TRANSACTIONAL_TABLE
109+
--eval $update_cmd
110+
}
111+
112+
if (`SELECT $iter IN (5, 6, 11, 12)`)
113+
{
114+
--let $delete_cmd= DELETE FROM $table
115+
--error ER_GTID_UNSAFE_CREATE_DROP_TEMPORARY_TABLE_IN_TRANSACTION, ER_GTID_UNSAFE_NON_TRANSACTIONAL_TABLE
116+
--eval $delete_cmd
117+
}
118+
DROP TRIGGER trigger1;
119+
--inc $iter
120+
}

‎mysql-test/suite/binlog/r/binlog_enforce_gtid_consistency.result‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -51,11 +51,11 @@ CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB;
5151
DROP TEMPORARY TABLE t1;
5252
BEGIN;
5353
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB;
54-
ERROR HY000: When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, the statements CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can be executed in a non-transactional context only, and require that AUTOCOMMIT = 1.
54+
ERROR HY000: When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, the statements CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can be executed in a non-transactional context only, and require that AUTOCOMMIT = 1. These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions.
5555
COMMIT;
5656
SET AUTOCOMMIT = 0;
5757
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB;
58-
ERROR HY000: When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, the statements CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can be executed in a non-transactional context only, and require that AUTOCOMMIT = 1.
58+
ERROR HY000: When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, the statements CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can be executed in a non-transactional context only, and require that AUTOCOMMIT = 1. These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions.
5959
SET AUTOCOMMIT = 1;
6060
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB;
6161
BEGIN;
@@ -67,11 +67,11 @@ SET AUTOCOMMIT = 1;
6767
ALTER TABLE t1 ADD COLUMN (d INT);
6868
BEGIN;
6969
DROP TEMPORARY TABLE t1;
70-
ERROR HY000: When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, the statements CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can be executed in a non-transactional context only, and require that AUTOCOMMIT = 1.
70+
ERROR HY000: When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, the statements CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can be executed in a non-transactional context only, and require that AUTOCOMMIT = 1. These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions.
7171
COMMIT;
7272
SET AUTOCOMMIT = 0;
7373
DROP TEMPORARY TABLE t1;
74-
ERROR HY000: When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, the statements CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can be executed in a non-transactional context only, and require that AUTOCOMMIT = 1.
74+
ERROR HY000: When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, the statements CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can be executed in a non-transactional context only, and require that AUTOCOMMIT = 1. These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions.
7575
COMMIT;
7676
SET AUTOCOMMIT = 1;
7777
DROP TEMPORARY TABLE t1;

0 commit comments

Comments
 (0)