Debian Codegraph @

mysql-test/suite/rpl/include/rpl_secondary_engine_load.inc

raw
1 #####################################################################
2 # #
3 # The aim of this test is to set up replication between a master #
4 # and slave and check that the new ALTER TABLE syntax that has #
5 # been introduced in WL#11581 and WL#11581 to support secondary #
6 # engines work as expected. A mock engine has been written to test #
7 # the statements. #
8 # #
9 # Creation Date: 2018-06-21 #
10 # Author: Deepa Dixit #
11 # #
12 #####################################################################
13
14 --source include/master-slave.inc
15 CALL mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* \\[Repl\\] Replica SQL for channel '':.*Error 'Unknown storage engine 'MOCK'' on query");
16 CALL mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* \\[Repl\\] Replica SQL for channel '':.*Error 'Secondary engine operation failed. No secondary engine defined");
17 CALL mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* \\[Repl\\] Replica: Unknown storage engine 'MOCK'");
18 CALL mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* \\[Repl\\] Replica: Secondary engine operation failed. No secondary engine defined");
19 CALL mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* \\[Server\\] Unsafe statement written to the binary log using statement format");
20 CALL mtr.add_suppression("The replica coordinator and worker threads are stopped");
21
22 --connection master
23 --echo [ Connection Master ]
24
25 # Install plugin on master.
26 --replace_regex /\.dll/.so/
27 --eval INSTALL PLUGIN mock SONAME '$MOCK_PLUGIN'
28 SET @@secondary_engine_cost_threshold = 0;
29
30 --connection slave
31 --echo [ Connection Slave ]
32
33 # Install plugin on slave.
34 --replace_regex /\.dll/.so/
35 --eval INSTALL PLUGIN mock SONAME '$MOCK_PLUGIN'
36 SET @@secondary_engine_cost_threshold = 0;
37
38 --connection master
39 --echo [ Connection Master ]
40
41 # Create table with SECONDARY_ENGINE MOCK
42 CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, vc VARCHAR(10), j JSON, b BLOB, vb VARBINARY(32)) SECONDARY_ENGINE=MOCK;
43
44 --source include/sync_slave_sql_with_master.inc
45 --echo [ Connection Slave ]
46
47 # Check if the table is created with the secondary engine.
48 SHOW CREATE TABLE t1;
49
50 # Since the table is not loaded into the MOCK engine, the query is not
51 # offloaded.
52 FLUSH STATUS;
53 SELECT * FROM t1;
54 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
55
56 --connection master
57 --echo [ Connection Master ]
58
59 # Insert rows and offload the table to mock engine.
60 INSERT INTO t1 VALUES(NULL, 'Poodle', '{"key":"value"}', 'Pug', x'1234');
61 INSERT INTO t1 VALUES(NULL, 'Labrador', '[{"key":"value"},{"key":"value2"}]', 'Boxer', x'abcd');
62 INSERT INTO t1 VALUES(NULL, 'Beagle', '[1,2,3]', 'Pitbull', x'abcd1234');
63 INSERT INTO t1 VALUES(NULL, 'Collie', '["ab","bc","cd"]', 'Huskey', x'1ab2c3d4');
64 INSERT INTO t1 VALUES(NULL, 'Shiba', '{"1":"one"}', 'Mastiff', x'ffff');
65
66 ALTER TABLE t1 SECONDARY_LOAD;
67
68 --source include/sync_slave_sql_with_master.inc
69 --echo [ Connection Slave ]
70
71
72 # Check that the query is offloaded.
73 FLUSH STATUS;
74 SELECT * FROM t1;
75 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
76
77 SET @save_use_secondary_engine = @@use_secondary_engine;
78
79 # The query should get executed on the primary engine.
80 SET use_secondary_engine = OFF;
81
82 FLUSH STATUS;
83 SELECT * FROM t1;
84 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
85
86 # The query should get executed on the secondary engine.
87 SET use_secondary_engine = FORCED;
88
89 FLUSH STATUS;
90 SELECT * FROM t1;
91 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
92
93 SET use_secondary_engine = @save_use_secondary_engine;
94
95 # The query should get executed on secondary engine.
96 FLUSH STATUS;
97 SELECT /*+SET_VAR(use_secondary_engine=ON)*/ * FROM t1;
98 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
99
100 # The query should get executed on primary engine.
101 FLUSH STATUS;
102 SELECT /*+SET_VAR(use_secondary_engine=OFF)*/ * FROM t1;
103 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
104
105 # The query should get executed on secondary engine.
106 FLUSH STATUS;
107 SELECT /*+SET_VAR(use_secondary_engine=FORCED)*/ * FROM t1;
108 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
109
110 --connection master
111 --echo [ Connection Master ]
112
113 # Create a table like t1.
114 CREATE TABLE t2 LIKE t1;
115
116 ANALYZE TABLE t1;
117 EXPLAIN INSERT INTO t2 SELECT * FROM t1;
118 EXPLAIN INSERT INTO t2 SELECT * FROM t1 UNION SELECT * FROM t1;
119 EXPLAIN INSERT INTO t2 SELECT * FROM t1 UNION ALL SELECT * FROM t1;
120
121 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
122 CREATE TABLE t3 SELECT * from t1;
123 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
124
125 --disable_warnings
126 INSERT INTO t2 VALUES(NULL, 'Poodle', '{"key":"value"}', 'Pug', x'1234');
127 INSERT INTO t2 VALUES(NULL, 'Labrador', '[{"key":"value"},{"key":"value2"}]',
128 'Boxer', x'abcd');
129 INSERT INTO t2 VALUES(NULL, 'Beagle', '[1,2,3]', 'Pitbull', x'abcd1234');
130 INSERT INTO t2 VALUES(NULL, 'Collie', '["ab","bc","cd"]', 'Huskey',
131 x'1ab2c3d4');
132 INSERT INTO t2 VALUES(NULL, 'Shiba', '{"1":"one"}', 'Mastiff', x'ffff');
133 --enable_warnings
134
135 # Offload the table to MOCK engine.
136 ALTER TABLE t2 SECONDARY_LOAD;
137
138 --source include/sync_slave_sql_with_master.inc
139 --echo [ Connection Slave ]
140
141 # Check that t2 is created with the secondary engine.
142 SHOW CREATE TABLE t2;
143 SHOW CREATE TABLE t3;
144
145
146 FLUSH STATUS;
147 SELECT * FROM t2;
148 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
149
150 --connection master
151 --echo [ Connection Master ]
152
153 # Query the table on master.
154 FLUSH STATUS;
155 SELECT * FROM t2;
156 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
157
158 # Unload the table from the secondary engine.
159 ALTER TABLE t1 SECONDARY_UNLOAD;
160 DROP TABLE t3;
161
162 --source include/sync_slave_sql_with_master.inc
163 --echo [ Connection Slave ]
164
165
166 # Check that the table is unloaded.
167 FLUSH STATUS;
168 SELECT * FROM t1;
169 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
170
171 # Setting use_secondary_engine to FORCED should throw an error since the table is unloaded.
172 SET use_secondary_engine = FORCED;
173
174 FLUSH STATUS;
175 --error ER_SECONDARY_ENGINE,ER_SECONDARY_ENGINE_PLUGIN
176 SELECT * FROM t1;
177 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
178 # ps-protocol and regular runs give different error codes
179 --error ER_SECONDARY_ENGINE,ER_SECONDARY_ENGINE_PLUGIN
180 CREATE TABLE t3 SELECT * from t1;
181
182 SET use_secondary_engine = @save_use_secondary_engine;
183
184 # Should execute throw an error.
185 FLUSH STATUS;
186 --error ER_SECONDARY_ENGINE,ER_SECONDARY_ENGINE_PLUGIN
187 SELECT /*+SET_VAR(use_secondary_engine=FORCED)*/ * FROM t1;
188 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
189
190 # Set secondary_engine to NULL only on slave.
191 ALTER TABLE t2 SECONDARY_ENGINE=NULL;
192
193 # Stop slave SQL thread.
194 --source include/stop_slave_sql.inc
195
196 --connection master
197 --echo [ Connection Master ]
198
199 # Load the table on master.
200 ALTER TABLE t2 SECONDARY_LOAD;
201
202 --connection slave
203 --echo [ Connection Slave ]
204
205 # Change secondary engine to MOCK
206 ALTER TABLE t2 SECONDARY_ENGINE=MOCK;
207
208 --source include/start_slave_sql.inc
209 --let $slave_param= Slave_SQL_Running_State
210 --let $slave_param_value= Replica has read all relay log; waiting for more updates
211 --source include/wait_for_slave_param.inc
212
213 # This should execute on MOCK engine.
214 FLUSH STATUS;
215 SELECT * FROM t2;
216 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
217
218 --connection master
219 --echo [ Connection Master ]
220
221 # Change the secondary engine to NULL.
222 ALTER TABLE t1 SECONDARY_ENGINE=NULL;
223
224 --source include/sync_slave_sql_with_master.inc
225 --echo [ Connection Slave ]
226
227 # Check whether the secondary engine has been changed.
228 SHOW CREATE TABLE t1;
229 FLUSH STATUS;
230 SELECT * FROM t1;
231 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
232
233 --connection master
234 --echo [ Connection Master ]
235
236 # Change the secondary engine to something non existent.
237 ALTER TABLE t1 SECONDARY_ENGINE=HELLO;
238
239 --source include/sync_slave_sql_with_master.inc
240 --echo [ Connection Slave ]
241
242 # Check the secondary engine of the table.
243 SHOW CREATE TABLE t1;
244
245 # Check whether the ALTER statements work only on the slave.
246 ALTER TABLE t1 SECONDARY_ENGINE=NULL;
247 ALTER TABLE t1 SECONDARY_ENGINE=NADA;
248 SHOW CREATE TABLE t1;
249
250 ALTER TABLE t1 SECONDARY_ENGINE=NULL;
251 ALTER TABLE t1 SECONDARY_ENGINE=MOCK;
252 SHOW CREATE TABLE t1;
253 ALTER TABLE t1 SECONDARY_LOAD;
254 ALTER TABLE t2 SECONDARY_LOAD;
255 FLUSH STATUS;
256 SELECT * FROM t1;
257 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
258 ALTER TABLE t1 SECONDARY_UNLOAD;
259 FLUSH STATUS;
260 SELECT * FROM t1;
261 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
262
263 ALTER TABLE t2 SECONDARY_UNLOAD;
264
265 --connection master
266 --echo [ Connection Master ]
267
268 ALTER TABLE t1 SECONDARY_ENGINE=NULL;
269 ALTER TABLE t1 SECONDARY_ENGINE=MOCK;
270
271 --source include/sync_slave_sql_with_master.inc
272 --echo [ Connection Slave ]
273
274 # Uninstall the plugin on slave.
275 UNINSTALL PLUGIN mock;
276
277 # Stop slave SQL thread.
278 --source include/stop_slave_sql.inc
279
280 --connection master
281 --echo [ Connection Master ]
282
283 # Check if table gets loaded on master even if there is no plugin installed on
284 # slave.
285 ALTER TABLE t1 SECONDARY_LOAD;
286
287 --connection slave
288 --echo [ Connection Slave ]
289
290 # Install the plugin on slave again
291 --replace_regex /\.dll/.so/
292 --eval INSTALL PLUGIN mock SONAME '$MOCK_PLUGIN'
293
294 --sleep 3
295 --source include/start_slave_sql.inc
296 --let $slave_param= Slave_SQL_Running_State
297 --let $slave_param_value= Replica has read all relay log; waiting for more updates
298 --source include/wait_for_slave_param.inc
299
300 FLUSH STATUS;
301 SELECT * FROM t1;
302 SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
303
304 --connection master
305 --echo [ Connection Master ]
306
307 # Clean up
308 DROP TABLE t1;
309 DROP TABLE t2;
310 UNINSTALL PLUGIN mock;
311 SET @@secondary_engine_cost_threshold = DEFAULT;
312
313 --source include/sync_slave_sql_with_master.inc
314
315 # Uninstall plugin on slave too.
316 --sleep 3
317 UNINSTALL PLUGIN mock;
318 SET @@secondary_engine_cost_threshold = DEFAULT;
319
320 --source include/rpl_end.inc