Debian Codegraph @

mysql-test/suite/rpl_gtid/t/rpl_delayed_slave.test

raw
1 # ==== Purpose ====
2 #
3 # Test the time-delayed replication feature, i.e.,
4 # CHANGE MASTER TO MASTER_DELAY=X:
5 #
6 # - Verify that slave has executed the events after but not before the
7 # delay timeout.
8 #
9 # - Verify that delay is correct when slave is already lagging
10 # due to slow queries.
11 #
12 # - Verify that STOP SLAVE works instantly even during a delay, and
13 # that it does not cause the waited-for event to be executed too
14 # early on slave.
15 #
16 # - Verify that changing back to no delay works.
17 #
18 # - Verify that RESET SLAVE sets the delay to 0.
19 #
20 # - Verify that setting a bad value for the delay gives an error.
21 #
22 # ==== Implementation ====
23 #
24 # We run the slave with 10 seconds lag.
25 #
26 # To simulate that the slave lags due to slow queries, we invoke a
27 # stored function that executes SLEEP if @@server_id==2. This requires
28 # that we run with binlog_format=STATEMENT.
29 #
30 # ==== Related Bugs and Worklogs ====
31 #
32 # WL#344: Time-delayed replication
33 # BUG#28760: Simulating a replication lag
34 # [duplicate] BUG#22072: configurable delayed replication
35 # [duplicate] BUG#21639: Add Replication Delay parameter
36 # BUG#56442: Slave executes delayed statements when STOP SLAVE is issued
37 #
38
39 # Needed so that sleeps get executed in the slave SQL thread.
40 --source include/have_binlog_format_statement.inc
41
42 --source include/set_privilege_checks_user_as_system_user.inc
43 --source include/master-slave.inc
44
45
46 call mtr.add_suppression("Unsafe statement written to the binary log using statement format");
47 --connection slave
48 call mtr.add_suppression("Unsafe statement written to the binary log using statement format");
49 --connection master
50
51
52 --let $delay= 10
53 --let $slave_sleep= 2
54
55
56 --echo [on master]
57 CREATE TABLE t1 (a VARCHAR(100), b INT);
58 INSERT INTO t1 VALUES ("zero", 0);
59
60
61 --echo ==== Normal setup ====
62
63 --echo [on slave]
64 --source include/sync_slave_sql_with_master.inc
65
66 --source include/stop_slave.inc
67
68 --disable_query_log
69 eval CHANGE REPLICATION SOURCE TO SOURCE_DELAY = $delay;
70 --enable_query_log
71
72 --source include/start_slave.inc
73
74 --let $assert_text= SHOW SLAVE STATUS should return the same delay that we set with CHANGE MASTER
75 --let $assert_cond= [SHOW SLAVE STATUS, SQL_Delay, 1] = $delay
76 --source include/assert.inc
77
78 --echo [on master]
79 --connection master
80 INSERT INTO t1 VALUES ('normal setup', 1);
81
82 --let $trx_num= 4
83 --source extra/rpl_tests/check_slave_delay.inc
84
85
86 --echo ==== Slave lags "naturally" after master ====
87
88 --echo [on master]
89
90 --disable_query_log
91 --echo # CREATE FUNCTION delay_on_slave(time_units INT) RETURNS INT BEGIN IF @@server_id = 2 THEN RETURN SLEEP(time_units * T); ELSE RETURN 0; END IF; END
92 --eval CREATE FUNCTION delay_on_slave(time_units INT) RETURNS INT BEGIN IF @@server_id = 2 THEN RETURN SLEEP(time_units * $slave_sleep); ELSE RETURN 0; END IF; END
93 --enable_query_log
94
95 INSERT INTO t1 SELECT delay_on_slave(3), 2;
96
97 --let $trx_num= 6
98 --source extra/rpl_tests/check_slave_delay.inc
99
100 --save_master_pos
101 INSERT INTO t1 VALUES ('slave is already lagging: this statement should execute immediately', 3);
102
103 --let $trx_num= 7
104 --source extra/rpl_tests/check_slave_delay.inc
105
106 INSERT INTO t1 SELECT delay_on_slave(2), 4;
107
108 --let $trx_num= 8
109 --source extra/rpl_tests/check_slave_delay.inc
110
111
112 --echo ==== STOP SLAVE / START SLAVE + DML ====
113
114 --source include/rpl_connection_slave.inc
115 --source include/stop_slave.inc
116
117 --disable_query_log
118 eval CHANGE REPLICATION SOURCE TO SOURCE_DELAY = $delay;
119 --enable_query_log
120
121 --source include/start_slave.inc
122
123 --echo [on master]
124 --connection master
125 INSERT INTO t1 VALUES ('stop slave and start slave: DML', 5);
126
127 --echo [on slave]
128 --connection slave
129 --sleep $slave_sleep
130 --let $timestamp_before_stop= `SELECT UNIX_TIMESTAMP()`
131 --let $relay_log_pos_before_stop= query_get_value(SHOW SLAVE STATUS, Relay_Log_Pos, 1)
132 --source include/stop_slave.inc
133
134 --let $assert_text= SQL thread position should not increase after STOP SLAVE
135 --let $assert_cond= [SHOW SLAVE STATUS, Relay_Log_Pos, 1] = $relay_log_pos_before_stop
136 --source include/assert.inc
137
138 --let $assert_text= Query should not be executed after STOP SLAVE
139 --let $assert_cond= MAX(b) = 4 FROM t1
140 --source include/assert.inc
141
142 --let $assert_text= Status should be '' after STOP SLAVE
143 --let $assert_cond= "[SHOW SLAVE STATUS, Slave_SQL_Running_State, 1]" = ""
144 --source include/assert.inc
145
146 --source include/start_slave.inc
147
148 --source include/rpl_connection_master.inc
149
150 --let $trx_num= 9
151 --source extra/rpl_tests/check_slave_delay.inc
152
153
154 --echo ==== STOP SLAVE / START SLAVE + DDL ====
155
156 --echo This verifies BUG#56442
157
158 --echo [on master]
159 CREATE TABLE t_check_dml_not_executed_prematurely (a INT);
160 --source include/save_master_pos.inc
161
162 --echo [on slave]
163 --connection slave
164 --sleep $slave_sleep
165
166 --let $timestamp_before_stop= `SELECT UNIX_TIMESTAMP()`
167 --let $relay_log_pos_before_stop= query_get_value(SHOW SLAVE STATUS, Relay_Log_Pos, 1)
168 --source include/stop_slave.inc
169
170 --let $assert_text= SQL thread position should not increase after STOP SLAVE
171 --let $assert_cond= [SHOW SLAVE STATUS, Relay_Log_Pos, 1] = $relay_log_pos_before_stop
172 --source include/assert.inc
173
174 --let $assert_text= Query should not be executed after STOP SLAVE
175 --let $assert_cond= COUNT(*) = 0 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = "t_check_dml_not_executed_prematurely"
176 --source include/assert.inc
177
178 --let $assert_text= Status should be '' after STOP SLAVE
179 --let $assert_cond= "[SHOW SLAVE STATUS, Slave_SQL_Running_State, 1]" = ""
180 --source include/assert.inc
181
182 --source include/start_slave.inc
183
184 --source include/rpl_connection_master.inc
185
186 --let $trx_num= 10
187 --source extra/rpl_tests/check_slave_delay.inc
188
189 --source include/rpl_connection_slave.inc
190
191 --let $assert_text= DDL Query should be executed
192 --let $assert_cond= COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = "t_check_dml_not_executed_prematurely"
193 --source include/assert.inc
194
195 --source include/check_slave_is_running.inc
196
197
198 --echo ==== Change back to no delay ====
199
200 --echo [on slave]
201 --source include/stop_slave.inc
202 CHANGE REPLICATION SOURCE TO SOURCE_DELAY = 0;
203
204 --let $assert_text= Delay should be 0 when we set it to 0
205 --let $assert_cond= [SHOW SLAVE STATUS, SQL_Delay, 1] = 0
206 --source include/assert.inc
207
208
209 --echo ==== Reset delay with RESET SLAVE ====
210
211 CHANGE REPLICATION SOURCE TO SOURCE_DELAY = 71;
212 --source include/start_slave.inc
213
214 --let $assert_text= Delay should be 71 when we set it to 71
215 --let $assert_cond= [SHOW SLAVE STATUS, SQL_Delay, 1] = 71
216 --source include/assert.inc
217
218 --source include/stop_slave.inc
219 RESET SLAVE;
220 --source include/start_slave.inc
221
222 --let $assert_text= Delay should be 0 after RESET SLAVE
223 --let $assert_cond= [SHOW SLAVE STATUS, SQL_Delay, 1] = 0
224 --source include/assert.inc
225
226
227 --echo ==== Set an invalid value for the delay ====
228
229 --source include/stop_slave.inc
230
231 --echo # Expect error for setting negative delay
232 --error ER_PARSE_ERROR
233 CHANGE REPLICATION SOURCE TO SOURCE_DELAY = -1;
234
235 --echo # Expect that it's ok to set delay of 2^31-1
236 CHANGE REPLICATION SOURCE TO SOURCE_DELAY = 2147483647;
237 --echo # Expect error for setting delay between 2^31 and 2^32-1
238 --error ER_SOURCE_DELAY_VALUE_OUT_OF_RANGE
239 CHANGE REPLICATION SOURCE TO SOURCE_DELAY = 2147483648;
240
241 --echo # Expect error for setting delay to nonsense
242 --error ER_PARSE_ERROR
243 CHANGE REPLICATION SOURCE TO SOURCE_DELAY = blah;
244
245 # todo: CHANGE MASTER TO MASTER_DELAY = 999999999999999999999999999
246 # should give error
247
248 CHANGE REPLICATION SOURCE TO SOURCE_DELAY = 0;
249 --source include/start_slave.inc
250
251
252 --echo ==== Clean up ====
253
254 --echo [on master]
255 --connection master
256 DROP TABLE t1, t_check_dml_not_executed_prematurely;
257 DROP FUNCTION delay_on_slave;
258
259 --echo [on slave]
260 --source include/sync_slave_sql_with_master.inc
261
262 --source include/rpl_end.inc