Debian Codegraph @

mysql-test/suite/ndb_opt/spj_dict.test

raw
1 ##############################################################
2 # Purpose: To test DICTionary changes while executing
3 # SPJ (Pushed join) operations.
4 # SPJ block should take part in the dictionary update
5 # loop controlled by DICT. Alter /dropping a table
6 # while there are ongoing SPJ operations should cause
7 # these operations to be terminated.
8 #
9 ##############################################################
10 ##############################################################
11 # NOTE ABOUT TESTING BELOW:
12 #
13 # Test cases are expected to terminate query execution on SPJ
14 # when a (SPJ-) dictionary change are detected. The error codes
15 # 20019-20021 are returned from SPJ, which are mapped to either
16 # ER_NO_SUCH_TABLE, or ER_TABLE_DEF_CHANGED.
17 #
18 # To allow for possible race between DML and DDL queries,
19 # (Didn't start, or completed before the other), 'error 0'
20 # is also allowed as an outcome from these tests.
21 #
22 # '--replace_column 10 ### 11 ###' is used for explains throughout
23 # the test as experience has shown that 'rows' estimates
24 # may vary depending on OS, compiler and .. phase of moon.
25 #
26 # Note that the 'Table ... does not exist' error message
27 # will incorrectly refer 'parent' instead of 'child'.
28 # This is an artifact of NDB to mysqld error translation
29 # as the pushed join is sent for execution when accessing the
30 # 'root' table. Thus any failure of finding any tables when
31 # executing the pushed join, will be reported as the 'parent'
32 # table not found.
33 #
34 ##############################################################
35 --source include/have_multi_ndb.inc
36
37 connect (ddl,localhost,root,,test);
38
39 connection ddl;
40
41 use test;
42
43 ## Initially create as MyISAM to avoid 'Out of operation records'
44 ## ... Later alter to 'engine=ndb'
45
46 create table parent(a int primary key, b int not null, key(b)) engine=myisam;
47 create table child(a int, b int, primary key(a,b), key(b)) engine=myisam;
48
49 insert into parent values (1,1), (2,2), (3,3), (4,4);
50 insert into parent select a+4, b+4 from parent;
51 insert into parent select a+8, b+8 from parent;
52 insert into parent select a+16, b+16 from parent;
53 insert into parent select a+32, b+32 from parent;
54 insert into parent select a+64, b+64 from parent;
55 insert into parent select a+128, b+128 from parent;
56 insert into parent select a+256, b+256 from parent;
57 insert into parent select a+512, b+512 from parent;
58 insert into parent select a+1024, b+1024 from parent;
59 insert into parent select a+(2*1024), b+(2*1024) from parent;
60 insert into parent select a+(4*1024), b+(4*1024) from parent;
61 insert into parent select a+(8*1024), b+(8*1024) from parent;
62 insert into parent select a+(16*1024), b+(16*1024) from parent;
63
64 insert into child select * from parent;
65
66 alter table parent engine=ndb;
67 alter table child engine=ndb;
68
69
70 connection server1;
71 show variables like 'server_id';
72
73 let $query1 =
74 select straight_join count(*)
75 from parent join child
76 on child.a = parent.a and child.b = parent.b
77 where parent.b > 5;
78
79 let $query2 =
80 select straight_join count(*)
81 from parent join child
82 on child.a = parent.a
83 where parent.b > 5;
84
85 let $query3 =
86 select straight_join count(*)
87 from parent join child
88 on child.b = parent.a
89 where parent.b > 5;
90
91 set ndb_join_pushdown = true;
92
93
94 # This test provokes failure to lock metadata
95 connection server1;
96 --disable_query_log
97 call mtr.add_suppression("Failed to acquire metadata lock");
98 call mtr.add_suppression(".*NDB_SHARE.*Moving away for safety, but possible memleak");
99 --enable_query_log
100
101 connection server2;
102 --disable_query_log
103 call mtr.add_suppression("Failed to acquire metadata lock");
104 call mtr.add_suppression(".*NDB_SHARE.*Moving away for safety, but possible memleak");
105 --enable_query_log
106
107
108 --echo ============================================
109 --echo Early 'online' drop of REF'ed child's KEY b.
110 --echo ============================================
111 connection server1;
112 --replace_column 10 ### 11 ###
113 eval explain $query3;
114 --disable_query_log
115 send_eval $query3;
116 --enable_query_log
117
118 connection server2;
119 alter table child algorithm=inplace, drop key b;
120 commit;
121
122 connection server1;
123 --disable_result_log
124 --error 0,ER_NO_SUCH_TABLE
125 reap;
126 --enable_result_log
127 alter table child add key(b);
128
129 --echo ============================================
130 --echo Late 'online' drop of REF'ed child's KEY b.
131 --echo ============================================
132 connection server1;
133 --replace_column 10 ### 11 ###
134 eval explain $query3;
135 --disable_query_log
136 send_eval $query3;
137 --enable_query_log
138
139 connection server2;
140 sleep 0.1;
141 alter table child algorithm=inplace, drop key b;
142 commit;
143
144 connection server1;
145 --disable_result_log
146 --error 0,ER_NO_SUCH_TABLE
147 reap;
148 --enable_result_log
149 alter table child add key(b);
150
151
152 --echo ============================================
153 --echo Early drop of EQ_REF'ed child's PRIMARY KEY.
154 --echo ============================================
155 connection server1;
156 --replace_column 10 ### 11 ###
157 eval explain $query1;
158 --disable_query_log
159 send_eval $query1;
160 --enable_query_log
161
162 connection server2;
163 alter table child drop primary key;
164 commit;
165
166 connection server1;
167 --disable_result_log
168 --error 0,ER_NO_SUCH_TABLE
169 reap;
170 --enable_result_log
171 alter table child add primary key(a,b);
172
173 --echo =============================================================
174 --echo Drop of EQ_REF'ed child's PRIMARY KEY after executed a while.
175 --echo =============================================================
176 connection server1;
177 --replace_column 10 ### 11 ###
178 eval explain $query1;
179 --disable_query_log
180 send_eval $query1;
181 --enable_query_log
182
183 connection server2;
184 #Takes a short sleep to let query start executing
185 sleep 0.1;
186 alter table child drop primary key;
187 commit;
188
189 connection server1;
190 --disable_result_log
191 --error 0,ER_NO_SUCH_TABLE
192 reap;
193 --enable_result_log
194 alter table child add primary key(a,b);
195
196
197 --echo ===========================================================
198 --echo ONLINE ALTER: Rename table to make it go away - temporarily
199 --echo ===========================================================
200 connection server1;
201 --replace_column 10 ### 11 ###
202 eval explain $query1;
203 --disable_query_log
204 send_eval $query1;
205 --enable_query_log
206
207 connection server2;
208 sleep 0.1;
209 alter table child algorithm=inplace, rename to child_orig;
210 commit;
211
212 connection server1;
213 --disable_result_log
214 --error 0,ER_TABLE_DEF_CHANGED
215 reap;
216 --enable_result_log
217 alter table child_orig rename to child;
218
219 --echo ============================================================
220 --echo OFFLINE ALTER: Rename table to make it go away - temporarily
221 --echo ============================================================
222 connection server1;
223 --replace_column 10 ### 11 ###
224 eval explain $query1;
225 --disable_query_log
226 send_eval $query1;
227 --enable_query_log
228
229 connection server2;
230 sleep 0.1;
231 alter table child algorithm=copy, rename to child_orig;
232 commit;
233
234 connection server1;
235 --disable_result_log
236 --error 0,ER_TABLE_DEF_CHANGED,ER_NO_SUCH_TABLE
237 reap;
238 --enable_result_log
239 alter table child_orig rename to child;
240
241
242 --echo =================================================================
243 --echo ONLINE ALTER: drop + recreate table 'child' key b in use by query
244 --echo =================================================================
245 connection server1;
246 --replace_column 10 ### 11 ###
247 eval explain $query3;
248 --disable_query_log
249 send_eval $query3;
250 --enable_query_log
251
252 connection server2;
253 sleep 0.1;
254 alter table child algorithm=inplace, drop key b;
255
256 connection server1;
257 --disable_result_log
258 --error 0,ER_NO_SUCH_TABLE
259 reap;
260 --enable_result_log
261 alter table child add key(b);
262
263
264 --echo ==================================================================
265 --echo ONLINE ALTER: drop + recreate table 'parent' key b in use by query
266 --echo ==================================================================
267 connection server1;
268 --replace_column 10 ### 11 ###
269 eval explain $query1;
270 --disable_query_log
271 send_eval $query1;
272 --enable_query_log
273
274 connection server2;
275 sleep 0.1;
276 alter table parent algorithm=inplace, drop key b;
277
278 connection server1;
279 --disable_result_log
280 --error 0,ER_NO_SUCH_TABLE
281 reap;
282 --enable_result_log
283 alter table parent add key(b);
284
285
286 --echo ===============================================
287 --echo OFFLINE ALTER: add + drop column c from 'child'
288 --echo ===============================================
289 connection server1;
290 --replace_column 10 ### 11 ###
291 eval explain $query1;
292 --disable_query_log
293 send_eval $query1;
294 --enable_query_log
295
296 connection server2;
297 sleep 0.1;
298 alter table child algorithm=copy, add column c int;
299
300 connection server1;
301 --disable_result_log
302 --error 0,ER_NO_SUCH_TABLE
303 reap;
304 --enable_result_log
305 alter table child drop column c;
306
307
308 --echo ==========================================================
309 --echo OFFLINE ALTER: drop + recreate primary key(a,b) for 'child'
310 --echo ==========================================================
311 connection server1;
312 --replace_column 10 ### 11 ###
313 eval explain $query2;
314 --disable_query_log
315 send_eval $query2;
316 --enable_query_log
317
318 connection server2;
319 sleep 0.01;
320 alter table child algorithm=copy, drop primary key;
321
322 connection server1;
323 --disable_result_log
324 --error 0,ER_NO_SUCH_TABLE
325 reap;
326 --enable_result_log
327 alter table child add primary key(a,b);
328
329
330 --echo ==================
331 --echo DROP TABLE 'child'
332 --echo ==================
333 connection server1;
334 --replace_column 10 ### 11 ###
335 eval explain $query1;
336 --disable_query_log
337 send_eval $query1;
338 --enable_query_log
339
340 connection server2;
341 sleep 0.01;
342 drop table child;
343
344 connection server1;
345 --disable_result_log
346 --error 0,ER_NO_SUCH_TABLE
347 reap;
348 --enable_result_log
349
350 --echo ===========
351 --echo Cleaning up
352 --echo ===========
353 connection ddl;
354
355 drop table if exists parent;
356