Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8181

Incorrect binlog order for concurrent CREATE TRIGGER and DROP TRIGGER

    Details

      Description

      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))
      

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

              There are no comments yet on this issue.

                People

                • Assignee:
                  Unassigned
                  Reporter:
                  knielsen Kristian Nielsen
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  1 Start watching this issue

                  Dates

                  • Created:
                    Updated: