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

DROP TRIGGER IF NOT EXIST binlogged on master but not on slave

    XMLWordPrintable

    Details

      Description

      Consider this statement, when the trigger does not exist:

      DROP TRIGGER IF EXISTS mytrig

      This statement is binlogged on the master, but when replicated to a slave it
      is not binlogged on the slave.

      This causes binlogs to get out of sync and potentially breaking
      replication. For example, when using GTID, if a slave stops after replicating
      such a statement, it may not be able to connect to another server which is
      missing that statement in its binlog.

      Here is a test case that reproduces the problem:

      --source include/have_innodb.inc
      --let $rpl_topology=1->2,1->3
      --source include/rpl_init.inc
       
      connection server_1;
       
      SET @old_strict= @@GLOBAL.gtid_strict_mode;
      SET GLOBAL gtid_strict_mode=1;
      CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(10)) ENGINE=InnoDB;
      INSERT INTO t1 VALUES (1, "s1");
       
      save_master_pos;
       
      connection server_2;
      sync_with_master;
      --source include/stop_slave.inc
      SET @old_strict= @@GLOBAL.gtid_strict_mode;
      SET GLOBAL gtid_strict_mode=1;
      CHANGE MASTER TO MASTER_USE_GTID=CURRENT_POS;
      --source include/start_slave.inc
       
      connection server_3;
      sync_with_master;
      --source include/stop_slave.inc
      SET @old_strict= @@GLOBAL.gtid_strict_mode;
      SET GLOBAL gtid_strict_mode=1;
      CHANGE MASTER TO MASTER_USE_GTID=CURRENT_POS;
      --source include/start_slave.inc
       
       
      --connection server_1
      # The bug is that this statement is binlogged on the master, but not on the
      # slave. This means that the binlogs get out of sync, breaking GTID replication.
      DROP TRIGGER IF EXISTS no_exist_trig;
       
      SHOW BINLOG EVENTS;
      SELECT @@GLOBAL.gtid_slave_pos;
      SELECT @@GLOBAL.gtid_binlog_pos;
      SELECT @@GLOBAL.gtid_current_pos;
      --save_master_pos
       
      --connection server_2
      --sync_with_master
       
      --source include/stop_slave.inc
      SHOW BINLOG EVENTS;
      SELECT @@GLOBAL.gtid_slave_pos;
      SELECT @@GLOBAL.gtid_binlog_pos;
      SELECT @@GLOBAL.gtid_current_pos;
       
      --connection server_1
      INSERT INTO t1 VALUES (2, "s1");
      SELECT * FROM t1 ORDER BY a;
      --save_master_pos
       
      --connection server_3
      --sync_with_master
      SHOW BINLOG EVENTS;
      SELECT @@GLOBAL.gtid_slave_pos;
      SELECT @@GLOBAL.gtid_binlog_pos;
      SELECT @@GLOBAL.gtid_current_pos;
      --source include/save_master_gtid.inc
       
      --connection server_2
      # Switch S2 to use S3 as the master.
      # Here we get the bug: S2's position is the GTID of DROP IF EXISTS, but this
      # does not exist on S3, so we get an error (as we should) in strict mode.
       
      --replace_result $SERVER_MYPORT_3 SERVER_MYPORT_3
      eval CHANGE MASTER TO master_host = '127.0.0.1', master_port = $SERVER_MYPORT_3,
           MASTER_USE_GTID=CURRENT_POS;
      --source include/start_slave.inc
      --source include/sync_with_master_gtid.inc
      SELECT * FROM t1 ORDER BY a;
       
       
      # Clean up.
      --connection server_2
      --source include/stop_slave.inc
      SET GLOBAL gtid_strict_mode= @old_strict;
      --replace_result $SERVER_MYPORT_1 SERVER_MYPORT_1
      eval CHANGE MASTER TO master_host = '127.0.0.1', master_port = $SERVER_MYPORT_1,
           MASTER_USE_GTID=CURRENT_POS;
      --source include/start_slave.inc
       
      --connection server_3
      --source include/stop_slave.inc
      SET GLOBAL gtid_strict_mode= @old_strict;
      --source include/start_slave.inc
       
      --connection server_1
      DROP TABLE t1;
      SET GLOBAL gtid_strict_mode= @old_strict;
       
      --source include/rpl_end.inc

      Requires the following to be put in <testname>.cnf:

      !include ../my.cnf
       
      [mysqld.1]
      log-slave-updates
      loose-innodb
       
      [mysqld.2]
      log-slave-updates
      loose-innodb
       
      [mysqld.3]
      log-slave-updates
      loose-innodb
       
      [ENV]
      SERVER_MYPORT_3=		@mysqld.3.port
      SERVER_MYSOCK_3=		@mysqld.3.socket

        Attachments

          Activity

            People

            Assignee:
            serg Sergei Golubchik
            Reporter:
            knielsen Kristian Nielsen
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: