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

Incorrect binlog order for concurrent CREATE TRIGGER and DROP TRIGGER

    XMLWordPrintable

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

      Attachments

        Activity

          People

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

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.