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