|
Test case:
--source include/have_log_bin.inc
|
--source include/have_debug.inc
|
|
CREATE TABLE t1 (a INT PRIMARY KEY);
|
CREATE TABLE t2 (a INT PRIMARY KEY);
|
CREATE TABLE t3 (a INT PRIMARY KEY);
|
CREATE TABLE t4 (a INT PRIMARY KEY);
|
|
CREATE TRIGGER trigger_1 AFTER INSERT ON t1 FOR EACH ROW UPDATE t3 SET a=a+1;
|
connect(con1,localhost,root,,);
|
SET SESSION debug_dbug="+d,inject_drop_trigger_sleep_before_binlog";
|
send DROP TRIGGER trigger_1;
|
--connection default
|
--sleep 1
|
CREATE TRIGGER trigger_1 AFTER INSERT ON t2 FOR EACH ROW UPDATE t4 SET a=a+1;
|
--connection con1
|
reap;
|
--disconnect con1
|
--connection default
|
|
SHOW BINLOG EVENTS;
|
|
DROP TRIGGER trigger_1;
|
DROP TABLE t1,t2,t3,t4;
|
Test case requires this server patch:
diff --git a/sql/sql_trigger.cc b/sql/sql_trigger.cc
|
index 54a68da..51efb84 100644
|
--- a/sql/sql_trigger.cc
|
+++ b/sql/sql_trigger.cc
|
@@ -587,6 +587,10 @@ bool mysql_create_or_drop_trigger(THD *thd, TABLE_LIST *tables, bool create)
|
end:
|
if (!result)
|
{
|
+ DBUG_EXECUTE_IF("inject_drop_trigger_sleep_before_binlog", {
|
+ if (!create)
|
+ my_sleep(5000000);
|
+ });
|
result= write_bin_log(thd, TRUE, stmt_query.ptr(), stmt_query.length());
|
}
|
|
The output shows wrong binlog order:
master-bin.000001 857 Gtid 1 895 GTID 0-1-5
|
master-bin.000001 895 Query 1 1077 use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER trigger_1 AFTER INSERT ON t1 FOR EACH ROW UPDATE t3 SET a=a+1
|
master-bin.000001 1077 Gtid 1 1115 GTID 0-1-6
|
master-bin.000001 1115 Query 1 1297 use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER trigger_1 AFTER INSERT ON t2 FOR EACH ROW UPDATE t4 SET a=a+1
|
master-bin.000001 1297 Gtid 1 1335 GTID 0-1-7
|
master-bin.000001 1335 Query 1 1420 use `test`; DROP TRIGGER trigger_1
|
This will make the slave fail with "trigger already exists", as the DROP
TRIGGER is logged after the second CREATE TRIGGER, while it ran before (and
needs to run before) on the master.
Looking at the code in mysql_create_or_drop_trigger() in sql/sql_trigger.cc,
there seems to be no locking at all to attempt to prevent this from
happening. The trigger namespace seems only protected by filesystem checks,
which is surely insufficient:
/* Use the filesystem to enforce trigger namespace constraints. */
|
if (!access(trigname_buff, F_OK))
|
|