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

Concurrent CREATE TRIGGER statements mix up in binlog and break replication

    XMLWordPrintable

    Details

      Description

      The test case is non-deterministic and for reproducing purposes only, not suitable for the regression suite.

      --source include/master-slave.inc
       
      CREATE TABLE t1 (a INT);
      CREATE TABLE t2 (b INT);
       
      --let $run= 100
      --disable_abort_on_error
      while ($run)
      {
        --send
          CREATE TRIGGER tr AFTER INSERT ON t1 FOR EACH ROW SET @b = 1;
        --connection master1
        CREATE TRIGGER tr AFTER INSERT ON t2 FOR EACH ROW SET @b = 1;
        --connection master
        --reap  
        DROP TRIGGER IF EXISTS tr;
        --dec $run
      }
       
      --sync_slave_with_master
       
      # Cleanup
      --connection master
      DROP TABLE t1, t2;
      --source include/rpl_end.inc
      

      It very quickly ends up with something like this in the binary log:

      bb-10.6-monty 387d673ed

      master-bin.000001	653	Query	1	836	use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER tr AFTER INSERT ON t1 FOR EACH ROW SET @b = 1
      master-bin.000001	836	Gtid	1	878	GTID 0-1-4
      master-bin.000001	878	Query	1	979	use `test`; DROP TRIGGER IF EXISTS tr
      master-bin.000001	979	Gtid	1	1021	GTID 0-1-5
      master-bin.000001	1021	Query	1	1204	use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER tr AFTER INSERT ON t2 FOR EACH ROW SET @b = 1
      master-bin.000001	1204	Gtid	1	1246	GTID 0-1-6
      master-bin.000001	1246	Query	1	1429	use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER tr AFTER INSERT ON t1 FOR EACH ROW SET @b = 1
      master-bin.000001	1429	Gtid	1	1471	GTID 0-1-7
      master-bin.000001	1471	Query	1	1572	use `test`; DROP TRIGGER IF EXISTS tr
      

      which naturally makes the replication fail with

      2021-05-06  0:44:33 8 [ERROR] Slave SQL: Error 'Trigger 'test.tr' already exists' on query. Default database: 'test'. Query: 'CREATE DEFINER=`root`@`localhost` TRIGGER tr AFTER INSERT ON t1 FOR EACH ROW SET @b = 1', Gtid 0-1-6, Internal MariaDB error code: 1359
      2021-05-06  0:44:33 8 [Warning] Slave: Trigger 'test.tr' already exists Error_code: 1359
      

      There was some problem with this on 10.2, but nothing bad happens on 10.3-10.6. However, on bb-10.6-monty starting from the patch below it fails very reliably.

      commit d9fe53cfe71443c645862f62b389b4e7b56a5953
      Author: Monty
      Date:   Mon Mar 22 16:05:08 2021 +0200
       
          Check if we can rename triggers before doing an ALTER TABLE ... RENAME
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              monty Michael Widenius
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: