1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
|
# ==== Purpose ====
#
# This test will generate two XA transactions on the master in a way that
# they will block each other on the slave if the transaction isolation level
# used by the slave applier is more restrictive than the READ COMMITTED one.
#
# Consider:
# E=execute, P=prepare, C=commit;
# 1=first transaction, 2=second transaction;
#
# Master does: E1, E2, P2, P1, C1, C2
# Slave does: E2, P2, E1, P1, C1, C2
#
# The transactions are designed so that, if the applier transaction isolation
# level is more restrictive than the READ COMMITTED, E1 will be blocked on
# the slave waiting for gap locks to be released.
#
# Step 1
#
# The test will verify that the transactions don't block each other because
# the applier thread automatically changed the isolation level.
#
# Step 2
#
# The test will verify that applying master's binary log dump in slave doesn't
# block because mysqlbinlog is informing the isolation level to be used.
#
# ==== Related Bugs and Worklogs ====
#
# BUG#25040331: INTERLEAVED XA TRANSACTIONS MAY DEADLOCK SLAVE APPLIER WITH
# REPEATABLE READ
#
--source include/have_debug.inc
--source include/have_innodb.inc
# The test case only make sense for RBR
--source include/have_binlog_format_row.inc
--source include/master-slave.inc
--connection slave
# To hit the issue, we need to split the data in two pages.
# This global variable will help us.
SET @saved_innodb_limit_optimistic_insert_debug = @@GLOBAL.innodb_limit_optimistic_insert_debug;
SET @@GLOBAL.innodb_limit_optimistic_insert_debug = 2;
#
# Step 1 - Using async replication
#
# Let's generate the workload on the master
--connection master
CREATE TABLE t1 (
c1 INT NOT NULL,
KEY(c1)
) ENGINE=InnoDB;
CREATE TABLE t2 (
c1 INT NOT NULL,
FOREIGN KEY(c1) REFERENCES t1(c1)
) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1), (3), (4);
--connection master1
XA START 'XA1';
INSERT INTO t1 values(2);
XA END 'XA1';
# This transaction will reference the gap where XA1
# was inserted, and will be prepared and committed
# before XA1, so the slave will prepare it (but will
# not commit it) before preparing XA1.
--connection master
XA START 'XA2';
INSERT INTO t2 values(3);
XA END 'XA2';
# The XA2 prepare should be binary logged first
XA PREPARE 'XA2';
# The XA1 prepare should be binary logged
# after XA2 prepare and before XA2 commit.
--connection master1
XA PREPARE 'XA1';
# The commit order doesn't matter much for the issue being tested.
XA COMMIT 'XA1';
--connection master
XA COMMIT 'XA2';
# Everything is fine if the slave can sync with the master.
--source include/sync_slave_sql_with_master.inc
#
# Step 2 - Using mysqlbinlog dump to restore the salve
#
--source include/stop_slave.inc
DROP TABLE t2, t1;
RESET SLAVE;
RESET MASTER;
--connection master
--let $master_data_dir= `SELECT @@datadir`
--let $master_log_file= query_get_value(SHOW MASTER STATUS, File, 1)
--let $mysql_server= $MYSQL --defaults-group-suffix=.2
--echo Restore binary log from the master into the slave
--exec $MYSQL_BINLOG --force-if-open $master_data_dir/$master_log_file | $mysql_server
--let $diff_tables= master:test.t1, slave:test.t1
--source include/diff_tables.inc
--let $diff_tables= master:test.t2, slave:test.t2
--source include/diff_tables.inc
#
# Cleanup
#
--let $master_file= query_get_value(SHOW MASTER STATUS, File, 1)
--let $master_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
DROP TABLE t2, t1;
## When GTID_MODE=OFF, we need to skip already applied transactions
--connection slave
#--let $gtid_mode= `SELECT @@GTID_MODE`
#if ($gtid_mode == OFF)
#{
# --disable_query_log
# --disable_result_log
# --eval CHANGE MASTER TO MASTER_LOG_FILE='$master_file', MASTER_LOG_POS=$master_pos
# --enable_result_log
# --enable_query_log
#}
--replace_result $master_file LOG_FILE $master_pos LOG_POS
--eval CHANGE MASTER TO MASTER_LOG_FILE='$master_file', MASTER_LOG_POS=$master_pos, MASTER_USE_GTID=NO
SET @@GLOBAL.innodb_limit_optimistic_insert_debug = @saved_innodb_limit_optimistic_insert_debug;
--source include/start_slave.inc
--source include/rpl_end.inc
|
