[MDEV-6957] SET STATEMENT skip_replication = .. makes the binary log non-replayable - JIRA

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Fixed
Affects Version/s: N/A
Fix Version/s: 10.1.2
Component/s: Admin statements
Labels:
None

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.

Gliffy Diagrams

Attachments

Issue Links

relates to

MDEV-5231 Per query variables from Percona Server [patch]

Closed

MDEV-6923 Testing for SET STATEMENT .. FOR (MDEV-5231, per-query variables)

Closed

Activity

Hide

Permalink

Oleksandr Byelkin added a comment - 2014-10-29 11:29

The variable is prohibited in SET STATEMENT for now.

Show

Oleksandr Byelkin added a comment - 2014-10-29 11:29 The variable is prohibited in SET STATEMENT for now.

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

Details

Description

Gliffy Diagrams

Attachments

Issue Links

Activity

People

Dates

Agile