Debian Codegraph @

mysql-test/suite/ndb/t/ndb_alter_table_copy_check.test

raw
1 -- source include/have_multi_ndb.inc
2 -- source include/have_debug_sync.inc
3
4 --echo Create an extra connection to server1
5 connect (server1con2,127.0.0.1,root,,test,$MASTER_MYPORT,);
6
7 --connection server1
8
9 --echo Show that copying alter has some ability to detect when concurrent
10 --echo changes are occuring to a source table as part of an alter.
11 --echo This causes the alter to fail, avoiding silent data loss/corruption.
12 --echo Concurrent modifications are made on a separate MySQLD to avoid
13 --echo MDL effects.
14 --echo Also show that harmless read-only references to the base table
15 --echo succeed and do not cause the ALTER to fail
16
17 use test;
18 create table cambridge (a int primary key, b int, c int, d int) engine=ndb;
19 insert into cambridge values (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (5,5,5,5);
20
21 create table oxford(a int primary key, b int, c text, d int) engine=ndb;
22 insert into oxford values
23 (1,1,repeat('B', 1000), 1),
24 (2,2,repeat('B', 1000), 2),
25 (3,3,repeat('B', 1000), 3),
26 (4,4,repeat('B', 1000), 4),
27 (5,5,repeat('B', 1000), 5);
28
29
30 --echo -------------------------------------------------------
31 --echo Test various concurrent modifications that should cause
32 --echo copy failure for a copying alter drop column.
33 --echo -------------------------------------------------------
34
35
36 let alter_statement=ALTER TABLE test.cambridge drop column d;
37 let other_statement=UPDATE test.cambridge set c=c+1; # scanning
38 let expected_alter_error=ER_TABLE_DEF_CHANGED;
39 let expected_other_error=0;
40 -- source suite/ndb/t/ndb_alter_table_copy_check.inc
41
42 let alter_statement=ALTER TABLE test.cambridge drop column d;
43 let other_statement=UPDATE test.cambridge set c=20 where a=2; # by pk
44 let expected_alter_error=ER_TABLE_DEF_CHANGED;
45 let expected_other_error=0;
46 -- source suite/ndb/t/ndb_alter_table_copy_check.inc
47
48 let alter_statement=ALTER TABLE test.cambridge drop column d;
49 let other_statement=INSERT into test.cambridge values (6,6,6,6);
50 let expected_alter_error=ER_TABLE_DEF_CHANGED;
51 let expected_other_error=0;
52 -- source suite/ndb/t/ndb_alter_table_copy_check.inc
53
54 let alter_statement=ALTER TABLE test.cambridge drop column d;
55 let other_statement=DELETE from test.cambridge where a=1;
56 let expected_alter_error=ER_TABLE_DEF_CHANGED;
57 let expected_other_error=0;
58 -- source suite/ndb/t/ndb_alter_table_copy_check.inc
59
60 --echo -------------------------------------------------------
61 --echo Test various concurrent modifications that should cause
62 --echo copy failure for a copying pk change
63 --echo -------------------------------------------------------
64
65 let alter_statement=ALTER TABLE test.cambridge drop primary key;
66 let other_statement=UPDATE test.cambridge set c=c+1; # scanning
67 let expected_alter_error=ER_TABLE_DEF_CHANGED;
68 let expected_other_error=0;
69 -- source suite/ndb/t/ndb_alter_table_copy_check.inc
70
71
72 --echo --------------------------
73 --echo FK cascading modifications
74 --echo --------------------------
75 --echo Ndb maintains inter-table FKs in the kernel, so that
76 --echo changes in a different table can affect a table
77 --echo undergoing a copying alter
78
79 update cambridge set b=a;
80 alter table cambridge add unique(b);
81
82 # Setup FK via secondary keys allowing cascading update + delete...
83 create table bristol
84 (a int primary key, b int, c int,
85 foreign key (b) references cambridge (b) on update cascade on delete cascade)
86 engine=ndb;
87
88 insert into bristol values (2,2,2), (3,3,3), (4,4,4), (5,5,5);
89
90 # Check that cascading FK update op causes child alter to fail
91
92 let alter_statement=ALTER TABLE test.bristol drop column c;
93 let other_statement=UPDATE test.cambridge set b=50 where a=5;
94 let expected_alter_error=ER_TABLE_DEF_CHANGED;
95 let expected_other_error=0;
96 -- source suite/ndb/t/ndb_alter_table_copy_check.inc
97
98 # Check that cascading FK delete op causes child alter to fail
99
100 let alter_statement=ALTER TABLE test.bristol drop column c;
101 let other_statement=DELETE from test.cambridge where a=5;
102 let expected_alter_error=ER_TABLE_DEF_CHANGED;
103 let expected_other_error=0;
104 -- source suite/ndb/t/ndb_alter_table_copy_check.inc
105
106
107 drop table bristol;
108
109
110 # TODO : More copying alters...
111 # Partitioning,
112
113 --echo -----
114 --echo Blobs
115 --echo -----
116
117 # A more interesting test would use NdbApi to update only the
118 # parts rows of a blob, and check that this is treated as
119 # a main table update.
120 # MySQLD currently always overwrites the whole value
121
122 let alter_statement=ALTER TABLE test.oxford drop column d;
123 let other_statement=UPDATE test.oxford SET c=REPEAT('E',1000);
124 let expected_alter_error=ER_TABLE_DEF_CHANGED;
125 let expected_other_error=0;
126 -- source suite/ndb/t/ndb_alter_table_copy_check.inc
127
128
129 --echo -------------------------------------------------------
130 --echo Test various concurrent operations that should not cause
131 --echo copy failure for a copying alter drop column.
132 --echo -------------------------------------------------------
133
134 --echo Scanning SELECTs
135
136 let alter_statement=ALTER TABLE test.cambridge drop column d;
137 let other_statement=SELECT a,b from test.cambridge order by a;
138 let expected_alter_error=0;
139 let expected_other_error=0;
140 -- source suite/ndb/t/ndb_alter_table_copy_check.inc
141 alter table test.cambridge add column d int;
142
143 let alter_statement=ALTER TABLE test.cambridge drop column d;
144 let other_statement=SELECT a,b from test.cambridge order by a lock in share mode;
145 let expected_alter_error=0;
146 let expected_other_error=0;
147 -- source suite/ndb/t/ndb_alter_table_copy_check.inc
148 alter table test.cambridge add column d int;
149
150 let alter_statement=ALTER TABLE test.cambridge drop column d;
151 let other_statement=SELECT a,b from test.cambridge order by a for update;
152 let expected_alter_error=0;
153 let expected_other_error=0;
154 -- source suite/ndb/t/ndb_alter_table_copy_check.inc
155 alter table test.cambridge add column d int;
156
157 let alter_statement=ALTER TABLE test.oxford drop column d;
158 let other_statement=SELECT a,length(c) from test.oxford order by a;
159 let expected_alter_error=0;
160 let expected_other_error=0;
161 -- source suite/ndb/t/ndb_alter_table_copy_check.inc
162 alter table test.oxford add column d int;
163
164 let alter_statement=ALTER TABLE test.oxford drop column d;
165 let other_statement=SELECT a,length(c) from test.oxford order by a lock in share mode;
166 let expected_alter_error=0;
167 let expected_other_error=0;
168 -- source suite/ndb/t/ndb_alter_table_copy_check.inc
169 alter table test.oxford add column d int;
170
171 let alter_statement=ALTER TABLE test.oxford drop column d;
172 let other_statement=SELECT a,length(c) from test.oxford order by a for update;
173 let expected_alter_error=0;
174 let expected_other_error=0;
175 -- source suite/ndb/t/ndb_alter_table_copy_check.inc
176 alter table test.oxford add column d int;
177
178 --echo SELECT by PK
179
180 let alter_statement=ALTER TABLE test.cambridge drop column d;
181 let other_statement=SELECT a,b from test.cambridge where a=3;
182 let expected_alter_error=0;
183 let expected_other_error=0;
184 -- source suite/ndb/t/ndb_alter_table_copy_check.inc
185 alter table test.cambridge add column d int;
186
187 let alter_statement=ALTER TABLE test.cambridge drop column d;
188 let other_statement=SELECT a,b from test.cambridge where a=3 lock in share mode;
189 let expected_alter_error=0;
190 let expected_other_error=0;
191 -- source suite/ndb/t/ndb_alter_table_copy_check.inc
192 alter table test.cambridge add column d int;
193
194 let alter_statement=ALTER TABLE test.cambridge drop column d;
195 let other_statement=SELECT a,b from test.cambridge where a=3 for update;
196 let expected_alter_error=0;
197 let expected_other_error=0;
198 -- source suite/ndb/t/ndb_alter_table_copy_check.inc
199 alter table test.cambridge add column d int;
200
201 let alter_statement=ALTER TABLE test.oxford drop column d;
202 let other_statement=SELECT a,length(c) from test.oxford where a=3;
203 let expected_alter_error=0;
204 let expected_other_error=0;
205 -- source suite/ndb/t/ndb_alter_table_copy_check.inc
206 alter table test.oxford add column d int;
207
208 let alter_statement=ALTER TABLE test.oxford drop column d;
209 let other_statement=SELECT a,length(c) from test.oxford where a=3 lock in share mode;
210 let expected_alter_error=0;
211 let expected_other_error=0;
212 -- source suite/ndb/t/ndb_alter_table_copy_check.inc
213 alter table test.oxford add column d int;
214
215 let alter_statement=ALTER TABLE test.oxford drop column d;
216 let other_statement=SELECT a,length(c) from test.oxford where a=3 for update;
217 let expected_alter_error=0;
218 let expected_other_error=0;
219 -- source suite/ndb/t/ndb_alter_table_copy_check.inc
220 alter table test.oxford add column d int;
221
222
223 --echo NULL effect modifications
224
225 let alter_statement=ALTER TABLE test.cambridge drop column d;
226 let other_statement=INSERT into test.cambridge values (2,2,2,2); # PK clash
227 let expected_alter_error=0;
228 let expected_other_error=ER_DUP_ENTRY;
229 -- source suite/ndb/t/ndb_alter_table_copy_check.inc
230 alter table test.cambridge add column d int;
231
232 let alter_statement=ALTER TABLE test.cambridge drop column d;
233 let other_statement=UPDATE test.cambridge set b=888 where a=888; # Null change
234 let expected_alter_error=0;
235 let expected_other_error=0;
236 -- source suite/ndb/t/ndb_alter_table_copy_check.inc
237 alter table test.cambridge add column d int;
238
239 let alter_statement=ALTER TABLE test.cambridge drop column d;
240 let other_statement=UPDATE test.cambridge set b=b; # Null change
241 let expected_alter_error=0;
242 let expected_other_error=0;
243 -- source suite/ndb/t/ndb_alter_table_copy_check.inc
244 alter table test.cambridge add column d int;
245
246 let alter_statement=ALTER TABLE test.cambridge drop column d;
247 let other_statement=DELETE from test.cambridge where a=888; # No such row
248 let expected_alter_error=0;
249 let expected_other_error=0;
250 -- source suite/ndb/t/ndb_alter_table_copy_check.inc
251 alter table test.cambridge add column d int;
252
253 let alter_statement=ALTER TABLE test.oxford drop column d;
254 let other_statement=INSERT into test.oxford values (2,2,repeat('A', 1000),2); # PK clash
255 let expected_alter_error=0;
256 let expected_other_error=ER_DUP_ENTRY;
257 -- source suite/ndb/t/ndb_alter_table_copy_check.inc
258 alter table test.oxford add column d int;
259
260 let alter_statement=ALTER TABLE test.oxford drop column d;
261 let other_statement=UPDATE test.oxford set b=888 where a=888; # Null change
262 let expected_alter_error=0;
263 let expected_other_error=0;
264 -- source suite/ndb/t/ndb_alter_table_copy_check.inc
265 alter table test.oxford add column d int;
266
267 let alter_statement=ALTER TABLE test.oxford drop column d;
268 let other_statement=UPDATE test.oxford set c=c; # Null change
269 let expected_alter_error=0;
270 let expected_other_error=0;
271 -- source suite/ndb/t/ndb_alter_table_copy_check.inc
272 alter table test.oxford add column d int;
273
274 let alter_statement=ALTER TABLE test.oxford drop column d;
275 let other_statement=DELETE from test.oxford where a=888; # No such row
276 let expected_alter_error=0;
277 let expected_other_error=0;
278 -- source suite/ndb/t/ndb_alter_table_copy_check.inc
279 alter table test.oxford add column d int;
280
281 --echo ------------------------------------------------
282 --echo FK checks are unaffected by parent copying alter
283 --echo ------------------------------------------------
284
285 update cambridge set b=a;
286 # Setup FK via secondary keys allowing cascading update + delete...
287 create table bristol
288 (a int primary key, b int, c int,
289 foreign key (b) references cambridge (b) on update cascade on delete cascade)
290 engine=ndb;
291
292 select a from cambridge order by a;
293
294 insert into bristol select a,a,a from cambridge;
295
296 # Add new child row
297 let alter_statement=ALTER TABLE test.cambridge drop column d;
298 let other_statement=INSERT INTO test.bristol values (40,4,40);
299 let expected_alter_error=0;
300 let expected_other_error=0;
301 -- source suite/ndb/t/ndb_alter_table_copy_check.inc
302 alter table test.cambridge add column d int;
303
304 # Update child to refer to different parent row
305 let alter_statement=ALTER TABLE test.cambridge drop column d;
306 let other_statement=UPDATE test.bristol set b=4 where b=2;
307 let expected_alter_error=0;
308 let expected_other_error=0;
309 -- source suite/ndb/t/ndb_alter_table_copy_check.inc
310 alter table test.cambridge add column d int;
311
312 # Delete child row(s)
313 let alter_statement=ALTER TABLE test.cambridge drop column d;
314 let other_statement=DELETE FROM test.bristol;
315 let expected_alter_error=0;
316 let expected_other_error=0;
317 -- source suite/ndb/t/ndb_alter_table_copy_check.inc
318 alter table test.cambridge add column d int;
319
320 drop table bristol;
321
322 --echo ---------------------------------------------------------
323 --echo Show post-check exposure in copying alter check mechanism
324 --echo ---------------------------------------------------------
325 --echo Existing rows :
326 update cambridge set c=a;
327 select a,c from cambridge order by a;
328
329 set DEBUG_SYNC= 'ndb.after_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
330
331 --send ALTER TABLE cambridge drop column d;
332
333 --connection server1con2
334 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
335
336 --connection server2
337 # Perform insert, update, delete
338 insert into test.cambridge values
339 (20,20,20,20),
340 (21,21,21,21),
341 (22,22,22,22),
342 (23,23,23,23);
343
344 update test.cambridge set c=1000;
345 delete from test.cambridge where a=6;
346
347 --echo Committed table content :
348 select a,c from test.cambridge order by a;
349
350 --connection server1con2
351 set DEBUG_SYNC= 'now SIGNAL alter_go';
352
353 --connection server1
354 # Expect error-free ALTER table
355 --reap
356
357 --echo Committed table content after ALTER
358 --echo Changes have been lost
359 select a,c from test.cambridge order by a;
360
361 set DEBUG_SYNC='RESET';
362
363 # TODO NdbAPI
364
365 drop table oxford;
366 drop table cambridge;
367
368
369 # Test behaviour where different fragment replicas have
370 # different commit counts
371
372 --echo Create RP, RB and FR tables
373 create table mab_rp (a int primary key, b int, c int, d int) engine=ndb COMMENT='NDB_TABLE=READ_BACKUP=0';
374 create table mab_rb (a int primary key, b int, c int, d int) engine=ndb COMMENT='NDB_TABLE=READ_BACKUP=1';
375 create table mab_fr (a int primary key, b int, c int, d int) engine=ndb COMMENT='NDB_TABLE=FULLY_REPLICATED=1';
376
377 --echo Create some data in all table types
378 insert into mab_rp values (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (5,5,5,5);
379 insert into mab_rp select a+5, b+5, c+5, d+5 from mab_rp;
380 insert into mab_rp select a+10, b+10, c+10, d+10 from mab_rp;
381 insert into mab_rp select a+20, b+20, c+20, d+20 from mab_rp;
382
383 insert into mab_rb select * from mab_rp;
384 insert into mab_fr select * from mab_rp;
385
386 --echo Perform some updates, so commit count != row count
387 update mab_rp set b=b+1;
388 update mab_rb set b=b+1;
389 update mab_fr set b=b+1;
390
391 --echo Check stable commit counts
392 --echo 4 node cluster with 2 replicas ((1,2), (3,4))
393 --echo Split into two logical sub clusters with (at least) all fragments A (1,3), B (2,4)
394 --echo Show how (sum of) commit counts in each sub cluster diverge over node restart
395
396
397 select sum(tot_commits) into @rp_commits_a from ndbinfo.operations_per_fragment where fq_name='test/def/mab_rp' and (node_id=1 or node_id=3);
398 select sum(tot_commits) = @rp_commits_a as rp_commit_counts_same from ndbinfo.operations_per_fragment where fq_name='test/def/mab_rp' and (node_id=2 or node_id=4);
399
400 select sum(tot_commits) into @rb_commits_a from ndbinfo.operations_per_fragment where fq_name='test/def/mab_rb' and (node_id=1 or node_id=3);
401 select sum(tot_commits) = @rb_commits_a as rb_commits_counts_same from ndbinfo.operations_per_fragment where fq_name='test/def/mab_rb' and (node_id=2 or node_id=4);
402
403 select sum(tot_commits) into @fr_commits_a from ndbinfo.operations_per_fragment where fq_name='test/def/mab_fr' and (node_id=1 or node_id=3);
404 select sum(tot_commits) = @fr_commits_a as fr_commit_counts_same from ndbinfo.operations_per_fragment where fq_name='test/def/mab_fr' and (node_id=2 or node_id=4);
405
406 --echo Restart one data node to cause different replicas to
407 --echo have different commit counts
408
409 --exec $NDB_MGM -e "1 RESTART -n -I"
410 --exec $NDB_WAITER --nowait-nodes=2,3,4 --not-started > /dev/null
411 --exec $NDB_MGM -e "1 START"
412 --exec $NDB_WAITER > /dev/null
413
414 --echo Node restarted
415
416 --echo Show commit counts on A changed
417 select sum(tot_commits) = @rp_commits_a as rp_commits_a_same from ndbinfo.operations_per_fragment where fq_name='test/def/mab_rp' and (node_id=1 or node_id=3);
418 select sum(tot_commits) = @rb_commits_a as rb_commits_a_same from ndbinfo.operations_per_fragment where fq_name='test/def/mab_rb' and (node_id=1 or node_id=3);
419 select sum(tot_commits) = @fr_commits_a as fr_commits_a_same from ndbinfo.operations_per_fragment where fq_name='test/def/mab_fr' and (node_id=1 or node_id=3);
420
421 --echo Show commit counts no longer the same A vs B
422 select sum(tot_commits) into @rp_commits_a from ndbinfo.operations_per_fragment where fq_name='test/def/mab_rp' and (node_id=1 or node_id=3);
423 select sum(tot_commits) = @rp_commits_a as rp_commit_counts_same from ndbinfo.operations_per_fragment where fq_name='test/def/mab_rp' and (node_id=2 or node_id=4);
424
425 select sum(tot_commits) into @rb_commits_a from ndbinfo.operations_per_fragment where fq_name='test/def/mab_rb' and (node_id=1 or node_id=3);
426 select sum(tot_commits) = @rb_commits_a as rb_commit_counts_same from ndbinfo.operations_per_fragment where fq_name='test/def/mab_rb' and (node_id=2 or node_id=4);
427
428 select sum(tot_commits) into @fr_commits_a from ndbinfo.operations_per_fragment where fq_name='test/def/mab_fr' and (node_id=1 or node_id=3);
429 select sum(tot_commits) = @fr_commits_a as fr_commits_counts_same from ndbinfo.operations_per_fragment where fq_name='test/def/mab_fr' and (node_id=2 or node_id=4);
430
431 --echo Check that ALTER TABLE can handle this situation
432
433 ALTER TABLE mab_rp DROP COLUMN d;
434 ALTER TABLE mab_rb DROP COLUMN d;
435 ALTER TABLE mab_fr DROP COLUMN d;
436
437 --echo Check that there are no resources left in use
438 select count(1) from ndbinfo.cluster_transactions;
439 select count(1) from ndbinfo.cluster_operations;
440
441 drop table mab_rp;
442 drop table mab_rb;
443 drop table mab_fr;