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
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
|
include/master-slave.inc
Warnings:
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
[connection master]
[connection slave]
include/stop_slave.inc
SET @saved_replica_preserve_commit_order = @@GLOBAL.replica_preserve_commit_order;
SET GLOBAL replica_preserve_commit_order = OFF;
include/start_slave.inc
[connection master]
#
# Initialization the test
#
CREATE DATABASE db1;
CREATE DATABASE db2;
CREATE TABLE db1.t1(c1 INT PRIMARY KEY, c2 VARCHAR(4096)) ENGINE=InnoDB;
CREATE TABLE db1.t2(c1 INT PRIMARY KEY, c2 VARCHAR(4096)) ENGINE=InnoDB;
CREATE TABLE db2.t1(c1 INT PRIMARY KEY, c2 VARCHAR(4096)) ENGINE=InnoDB;
INSERT INTO db1.t1 VALUES(1, NULL), (10, NULL), (100, NULL);
INSERT INTO db1.t2 VALUES(1, NULL), (10, NULL), (100, NULL);
INSERT INTO db2.t1 VALUES(1, NULL), (10, NULL), (100, NULL);
include/sync_slave_sql_with_master.inc
CALL mtr.add_suppression("Replica SQL for channel '':.*Lock wait timeout exceeded.*");
CALL mtr.add_suppression("Replica SQL for channel '': Worker . failed executing transaction.*");
CALL mtr.add_suppression("Replica SQL for channel '': .*Error_code: MY-001756");
CALL mtr.add_suppression("Replica SQL for channel '': worker thread retried transaction.*");
SET @saved_innodb_lock_wait_timeout = @@GLOBAL.innodb_lock_wait_timeout;
SET @saved_replica_parallel_workers = @@GLOBAL.replica_parallel_workers;
SET @saved_replica_parallel_type = @@GLOBAL.replica_parallel_type;
Warnings:
Warning 1287 '@@replica_parallel_type' is deprecated and will be removed in a future release.
SET @saved_max_relay_log_size = @@GLOBAL.max_relay_log_size;
SET @saved_replica_transaction_retries = @@GLOBAL.replica_transaction_retries;
SET GLOBAL innodb_lock_wait_timeout = 1;
SET GLOBAL replica_parallel_workers = 2;
SET GLOBAL max_relay_log_size = 4096;
include/stop_slave_sql.inc
SET GLOBAL replica_parallel_type = "DATABASE";
Warnings:
Warning 1287 '@@replica_parallel_type' is deprecated and will be removed in a future release.
include/start_slave_sql.inc
#
# Case 1: slave will stop if replica_transaction_retries is 0
#
SET GLOBAL replica_transaction_retries = 0;
BEGIN;
SELECT c1 FROM db1.t1 FOR UPDATE;
c1
1
10
100
[connection master]
INSERT INTO db1.t1 VALUES(2, NULL);
INSERT INTO db2.t1 VALUES(2, NULL);
include/save_master_pos.inc
[connection slave]
include/wait_for_slave_sql_error.inc [errno=1205]
include/assert.inc [Value 2 is not in db1.t1.]
ROLLBACK;
#
# Case 2: Slave will stop after retrying a transaction
# replica_transaction_retries times.
#
SET GLOBAL replica_transaction_retries = 2;
include/start_slave_sql.inc
include/sync_slave_sql.inc
BEGIN;
SELECT c1 FROM db1.t2 FOR UPDATE;
c1
1
10
100
[connection master]
BEGIN;
INSERT INTO db1.t1 VALUES(20, NULL);
INSERT INTO db1.t2 VALUES(20, NULL);
COMMIT;
INSERT INTO db2.t1 VALUES(20, NULL);
include/save_master_pos.inc
[connection slave]
include/wait_for_slave_sql_error.inc [errno=1205]
include/assert.inc [Value 20 is not in db1.t1.]
ROLLBACK;
#
# Case 3: Slave worker will not stop after retring a transaction
# sucessfully.
#
SET GLOBAL replica_transaction_retries = 10;
include/start_slave_sql.inc
include/sync_slave_sql.inc
include/check_slave_no_error.inc
BEGIN;
SELECT c1 FROM db1.t2 FOR UPDATE;
c1
1
10
20
100
[connection master]
BEGIN;
# INSERT INTO db1.t1 VALUES(30, 4096'a')
# INSERT INTO db1.t1 VALUES(31, 4096'a')
SET @str= 'abc';
INSERT INTO db1.t2 VALUES(30, @str);
COMMIT;
INSERT INTO db2.t1 VALUES(30, NULL);
include/save_master_pos.inc
[connection slave]
include/check_slave_no_error.inc
ROLLBACK;
include/sync_slave_sql.inc
include/check_slave_no_error.inc
include/rpl_diff.inc
include/rpl_diff.inc
include/rpl_diff.inc
[connection master]
INSERT INTO db1.t1 VALUES(40, NULL);
INSERT INTO db2.t1 VALUES(40, NULL);
include/sync_slave_sql_with_master.inc
#
# Case 4: Non-temporary error will stop slave workers without retrying
#
INSERT INTO db1.t1 VALUES(50, NULL);
[connection master]
INSERT INTO db1.t1 VALUES(50, NULL);
include/save_master_pos.inc
[connection slave]
include/wait_for_slave_sql_error.inc [errno=1062]
include/assert.inc [count_transactions_retries should not increase]
DELETE FROM db1.t1 WHERE c1 = 50;
include/start_slave_sql.inc
include/sync_slave_sql.inc
#
# Cleanup
#
SET GLOBAL innodb_lock_wait_timeout = @saved_innodb_lock_wait_timeout;
SET GLOBAL replica_parallel_workers = @saved_replica_parallel_workers;
SET GLOBAL max_relay_log_size = @saved_max_relay_log_size;
SET GLOBAL replica_transaction_retries = @saved_replica_transaction_retries;
include/stop_slave_sql.inc
SET GLOBAL replica_parallel_type= @saved_replica_parallel_type;
Warnings:
Warning 1287 '@@replica_parallel_type' is deprecated and will be removed in a future release.
include/start_slave_sql.inc
[connection master]
DROP DATABASE db1;
DROP DATABASE db2;
[connection slave]
include/stop_slave.inc
SET GLOBAL replica_preserve_commit_order = @saved_replica_preserve_commit_order;
include/start_slave.inc
include/rpl_end.inc
|
