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

SET STATEMENT skip_replication = .. makes the binary log non-replayable

    XMLWordPrintable

    Details

      Description

      The variable skip_replication has two important properties:

      • it's written into the binary log;
      • it cannot be changed inside a transaction.

      SET STATEMENT recognizes the latter and does not allow to change it inside the transaction.
      However, when it's applied outside a transaction, it works and is written into the binary log. But look how it looks in there:

      MariaDB [test]> set statement skip_replication=1 for insert into t1 values (1);
      Query OK, 1 row affected (0.11 sec)
       
      MariaDB [test]> set statement skip_replication=1 for insert into t1 values (2);
      Query OK, 1 row affected (0.03 sec)

      # at 2252
      #141028  0:54:26 server id 1  end_log_pos 2290  GTID 1-1-10
      /*!100001 SET @@session.gtid_seq_no=10*//*!*/;
      BEGIN
      /*!*/;
      # at 2290
      /*!50521 SET skip_replication=1*//*!*/;
      #141028  0:54:26 server id 1  end_log_pos 2415  Query   thread_id=37    exec_time=0     error_code=0
      SET TIMESTAMP=1414443266/*!*/;
      set statement skip_replication=1 for insert into t1 values (1)
      /*!*/;
      # at 2415
      /*!50521 SET skip_replication=0*//*!*/;
      #141028  0:54:26 server id 1  end_log_pos 2442  Xid = 921
      COMMIT/*!*/;
      # at 2442
      #141028  0:54:28 server id 1  end_log_pos 2480  GTID 1-1-11
      /*!100001 SET @@session.gtid_seq_no=11*//*!*/;
      BEGIN
      /*!*/;
      # at 2480
      /*!50521 SET skip_replication=1*//*!*/;
      #141028  0:54:28 server id 1  end_log_pos 2605  Query   thread_id=37    exec_time=0     error_code=0
      SET TIMESTAMP=1414443268/*!*/;
      set statement skip_replication=1 for insert into t1 values (2)
      /*!*/;
      # at 2605
      /*!50521 SET skip_replication=0*//*!*/;
      #141028  0:54:28 server id 1  end_log_pos 2632  Xid = 922
      COMMIT/*!*/;

      That is, it is written after BEGIN and before COMMIT, hence inside a transaction. So, when the binlog is replayed, the replaying server chokes on it.

      ERROR 1929 (HY000) at line 66: Cannot modify @@session.skip_replication inside a transaction

      It does not seem to affect replication – somehow it magically works. But when the binlog is used as a backup, it's a problem.

      Compare with how it's written in case of a "normal" SET:

      MariaDB [test]> set skip_replication=1;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]>  insert into t1 values (3);
      Query OK, 1 row affected (0.15 sec)
       
      MariaDB [test]>  insert into t1 values (4);
      Query OK, 1 row affected (0.25 sec)
       
      MariaDB [test]> set skip_replication=0;
      Query OK, 0 rows affected (0.01 sec)
       
      MariaDB [test]>  insert into t1 values (5);
      Query OK, 1 row affected (0.16 sec)

      # at 2632
      /*!50521 SET skip_replication=1*//*!*/;
      #141028  0:58:37 server id 1  end_log_pos 2670  GTID 1-1-12
      /*!100001 SET @@session.gtid_seq_no=12*//*!*/;
      BEGIN
      /*!*/;
      # at 2670
      #141028  0:58:37 server id 1  end_log_pos 2758  Query   thread_id=37    exec_time=0     error_code=0
      SET TIMESTAMP=1414443517/*!*/;
      insert into t1 values (3)
      /*!*/;
      # at 2758
      #141028  0:58:37 server id 1  end_log_pos 2785  Xid = 924
      COMMIT/*!*/;
      # at 2785
      #141028  0:58:39 server id 1  end_log_pos 2823  GTID 1-1-13
      /*!100001 SET @@session.gtid_seq_no=13*//*!*/;
      BEGIN
      /*!*/;
      # at 2823
      #141028  0:58:39 server id 1  end_log_pos 2911  Query   thread_id=37    exec_time=0     error_code=0
      SET TIMESTAMP=1414443519/*!*/;
      insert into t1 values (4)
      /*!*/;
      # at 2911
      #141028  0:58:39 server id 1  end_log_pos 2938  Xid = 925
      COMMIT/*!*/;
      # at 2938
      /*!50521 SET skip_replication=0*//*!*/;
      #141028  0:59:29 server id 1  end_log_pos 2976  GTID 1-1-14
      /*!100001 SET @@session.gtid_seq_no=14*//*!*/;
      BEGIN
      /*!*/;
      # at 2976
      #141028  0:59:29 server id 1  end_log_pos 3064  Query   thread_id=37    exec_time=0     error_code=0
      SET TIMESTAMP=1414443569/*!*/;
      insert into t1 values (5)
      /*!*/;
      # at 3064
      #141028  0:59:29 server id 1  end_log_pos 3091  Xid = 927
      COMMIT/*!*/;

      commit e64f5d8f758bcc1a8856ba9fba01780533f80747
      Author: Oleksandr Byelkin <sanja@mariadb.com>
      Date:   Sun Oct 26 16:27:54 2014 +0100
       
          Fixed test suite global variable saving

      Percona server does not have the variable, so nothing to compare with.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: