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
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
|
#####################################################################
# #
# The aim of this test is to set up replication between a master #
# and slave and check that the new ALTER TABLE syntax that has #
# been introduced in WL#11581 and WL#11581 to support secondary #
# engines work as expected. A mock engine has been written to test #
# the statements. #
# #
# Creation Date: 2018-06-21 #
# Author: Deepa Dixit #
# #
#####################################################################
--source include/master-slave.inc
CALL mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* \\[Repl\\] Replica SQL for channel '':.*Error 'Unknown storage engine 'MOCK'' on query");
CALL mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* \\[Repl\\] Replica SQL for channel '':.*Error 'Secondary engine operation failed. No secondary engine defined");
CALL mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* \\[Repl\\] Replica: Unknown storage engine 'MOCK'");
CALL mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* \\[Repl\\] Replica: Secondary engine operation failed. No secondary engine defined");
CALL mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* \\[Server\\] Unsafe statement written to the binary log using statement format");
CALL mtr.add_suppression("The replica coordinator and worker threads are stopped");
--connection master
--echo [ Connection Master ]
# Install plugin on master.
--replace_regex /\.dll/.so/
--eval INSTALL PLUGIN mock SONAME '$MOCK_PLUGIN'
SET @@secondary_engine_cost_threshold = 0;
--connection slave
--echo [ Connection Slave ]
# Install plugin on slave.
--replace_regex /\.dll/.so/
--eval INSTALL PLUGIN mock SONAME '$MOCK_PLUGIN'
SET @@secondary_engine_cost_threshold = 0;
--connection master
--echo [ Connection Master ]
# Create table with SECONDARY_ENGINE MOCK
CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, vc VARCHAR(10), j JSON, b BLOB, vb VARBINARY(32)) SECONDARY_ENGINE=MOCK;
--source include/sync_slave_sql_with_master.inc
--echo [ Connection Slave ]
# Check if the table is created with the secondary engine.
SHOW CREATE TABLE t1;
# Since the table is not loaded into the MOCK engine, the query is not
# offloaded.
FLUSH STATUS;
SELECT * FROM t1;
SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
--connection master
--echo [ Connection Master ]
# Insert rows and offload the table to mock engine.
INSERT INTO t1 VALUES(NULL, 'Poodle', '{"key":"value"}', 'Pug', x'1234');
INSERT INTO t1 VALUES(NULL, 'Labrador', '[{"key":"value"},{"key":"value2"}]', 'Boxer', x'abcd');
INSERT INTO t1 VALUES(NULL, 'Beagle', '[1,2,3]', 'Pitbull', x'abcd1234');
INSERT INTO t1 VALUES(NULL, 'Collie', '["ab","bc","cd"]', 'Huskey', x'1ab2c3d4');
INSERT INTO t1 VALUES(NULL, 'Shiba', '{"1":"one"}', 'Mastiff', x'ffff');
ALTER TABLE t1 SECONDARY_LOAD;
--source include/sync_slave_sql_with_master.inc
--echo [ Connection Slave ]
# Check that the query is offloaded.
FLUSH STATUS;
SELECT * FROM t1;
SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
SET @save_use_secondary_engine = @@use_secondary_engine;
# The query should get executed on the primary engine.
SET use_secondary_engine = OFF;
FLUSH STATUS;
SELECT * FROM t1;
SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
# The query should get executed on the secondary engine.
SET use_secondary_engine = FORCED;
FLUSH STATUS;
SELECT * FROM t1;
SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
SET use_secondary_engine = @save_use_secondary_engine;
# The query should get executed on secondary engine.
FLUSH STATUS;
SELECT /*+SET_VAR(use_secondary_engine=ON)*/ * FROM t1;
SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
# The query should get executed on primary engine.
FLUSH STATUS;
SELECT /*+SET_VAR(use_secondary_engine=OFF)*/ * FROM t1;
SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
# The query should get executed on secondary engine.
FLUSH STATUS;
SELECT /*+SET_VAR(use_secondary_engine=FORCED)*/ * FROM t1;
SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
--connection master
--echo [ Connection Master ]
# Create a table like t1.
CREATE TABLE t2 LIKE t1;
ANALYZE TABLE t1;
EXPLAIN INSERT INTO t2 SELECT * FROM t1;
EXPLAIN INSERT INTO t2 SELECT * FROM t1 UNION SELECT * FROM t1;
EXPLAIN INSERT INTO t2 SELECT * FROM t1 UNION ALL SELECT * FROM t1;
SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
CREATE TABLE t3 SELECT * from t1;
SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
--disable_warnings
INSERT INTO t2 VALUES(NULL, 'Poodle', '{"key":"value"}', 'Pug', x'1234');
INSERT INTO t2 VALUES(NULL, 'Labrador', '[{"key":"value"},{"key":"value2"}]',
'Boxer', x'abcd');
INSERT INTO t2 VALUES(NULL, 'Beagle', '[1,2,3]', 'Pitbull', x'abcd1234');
INSERT INTO t2 VALUES(NULL, 'Collie', '["ab","bc","cd"]', 'Huskey',
x'1ab2c3d4');
INSERT INTO t2 VALUES(NULL, 'Shiba', '{"1":"one"}', 'Mastiff', x'ffff');
--enable_warnings
# Offload the table to MOCK engine.
ALTER TABLE t2 SECONDARY_LOAD;
--source include/sync_slave_sql_with_master.inc
--echo [ Connection Slave ]
# Check that t2 is created with the secondary engine.
SHOW CREATE TABLE t2;
SHOW CREATE TABLE t3;
FLUSH STATUS;
SELECT * FROM t2;
SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
--connection master
--echo [ Connection Master ]
# Query the table on master.
FLUSH STATUS;
SELECT * FROM t2;
SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
# Unload the table from the secondary engine.
ALTER TABLE t1 SECONDARY_UNLOAD;
DROP TABLE t3;
--source include/sync_slave_sql_with_master.inc
--echo [ Connection Slave ]
# Check that the table is unloaded.
FLUSH STATUS;
SELECT * FROM t1;
SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
# Setting use_secondary_engine to FORCED should throw an error since the table is unloaded.
SET use_secondary_engine = FORCED;
FLUSH STATUS;
--error ER_SECONDARY_ENGINE,ER_SECONDARY_ENGINE_PLUGIN
SELECT * FROM t1;
SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
# ps-protocol and regular runs give different error codes
--error ER_SECONDARY_ENGINE,ER_SECONDARY_ENGINE_PLUGIN
CREATE TABLE t3 SELECT * from t1;
SET use_secondary_engine = @save_use_secondary_engine;
# Should execute throw an error.
FLUSH STATUS;
--error ER_SECONDARY_ENGINE,ER_SECONDARY_ENGINE_PLUGIN
SELECT /*+SET_VAR(use_secondary_engine=FORCED)*/ * FROM t1;
SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
# Set secondary_engine to NULL only on slave.
ALTER TABLE t2 SECONDARY_ENGINE=NULL;
# Stop slave SQL thread.
--source include/stop_slave_sql.inc
--connection master
--echo [ Connection Master ]
# Load the table on master.
ALTER TABLE t2 SECONDARY_LOAD;
--connection slave
--echo [ Connection Slave ]
# Change secondary engine to MOCK
ALTER TABLE t2 SECONDARY_ENGINE=MOCK;
--source include/start_slave_sql.inc
--let $slave_param= Slave_SQL_Running_State
--let $slave_param_value= Replica has read all relay log; waiting for more updates
--source include/wait_for_slave_param.inc
# This should execute on MOCK engine.
FLUSH STATUS;
SELECT * FROM t2;
SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
--connection master
--echo [ Connection Master ]
# Change the secondary engine to NULL.
ALTER TABLE t1 SECONDARY_ENGINE=NULL;
--source include/sync_slave_sql_with_master.inc
--echo [ Connection Slave ]
# Check whether the secondary engine has been changed.
SHOW CREATE TABLE t1;
FLUSH STATUS;
SELECT * FROM t1;
SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
--connection master
--echo [ Connection Master ]
# Change the secondary engine to something non existent.
ALTER TABLE t1 SECONDARY_ENGINE=HELLO;
--source include/sync_slave_sql_with_master.inc
--echo [ Connection Slave ]
# Check the secondary engine of the table.
SHOW CREATE TABLE t1;
# Check whether the ALTER statements work only on the slave.
ALTER TABLE t1 SECONDARY_ENGINE=NULL;
ALTER TABLE t1 SECONDARY_ENGINE=NADA;
SHOW CREATE TABLE t1;
ALTER TABLE t1 SECONDARY_ENGINE=NULL;
ALTER TABLE t1 SECONDARY_ENGINE=MOCK;
SHOW CREATE TABLE t1;
ALTER TABLE t1 SECONDARY_LOAD;
ALTER TABLE t2 SECONDARY_LOAD;
FLUSH STATUS;
SELECT * FROM t1;
SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
ALTER TABLE t1 SECONDARY_UNLOAD;
FLUSH STATUS;
SELECT * FROM t1;
SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
ALTER TABLE t2 SECONDARY_UNLOAD;
--connection master
--echo [ Connection Master ]
ALTER TABLE t1 SECONDARY_ENGINE=NULL;
ALTER TABLE t1 SECONDARY_ENGINE=MOCK;
--source include/sync_slave_sql_with_master.inc
--echo [ Connection Slave ]
# Uninstall the plugin on slave.
UNINSTALL PLUGIN mock;
# Stop slave SQL thread.
--source include/stop_slave_sql.inc
--connection master
--echo [ Connection Master ]
# Check if table gets loaded on master even if there is no plugin installed on
# slave.
ALTER TABLE t1 SECONDARY_LOAD;
--connection slave
--echo [ Connection Slave ]
# Install the plugin on slave again
--replace_regex /\.dll/.so/
--eval INSTALL PLUGIN mock SONAME '$MOCK_PLUGIN'
--sleep 3
--source include/start_slave_sql.inc
--let $slave_param= Slave_SQL_Running_State
--let $slave_param_value= Replica has read all relay log; waiting for more updates
--source include/wait_for_slave_param.inc
FLUSH STATUS;
SELECT * FROM t1;
SHOW SESSION STATUS LIKE 'Secondary_engine_execution_count';
--connection master
--echo [ Connection Master ]
# Clean up
DROP TABLE t1;
DROP TABLE t2;
UNINSTALL PLUGIN mock;
SET @@secondary_engine_cost_threshold = DEFAULT;
--source include/sync_slave_sql_with_master.inc
# Uninstall plugin on slave too.
--sleep 3
UNINSTALL PLUGIN mock;
SET @@secondary_engine_cost_threshold = DEFAULT;
--source include/rpl_end.inc
|
