Debian Codegraph @

mysql-test/suite/ndb/r/ndb_alter_table_copy_check.result

raw
1 Create an extra connection to server1
2 Show that copying alter has some ability to detect when concurrent
3 changes are occuring to a source table as part of an alter.
4 This causes the alter to fail, avoiding silent data loss/corruption.
5 Concurrent modifications are made on a separate MySQLD to avoid
6 MDL effects.
7 Also show that harmless read-only references to the base table
8 succeed and do not cause the ALTER to fail
9 use test;
10 create table cambridge (a int primary key, b int, c int, d int) engine=ndb;
11 insert into cambridge values (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (5,5,5,5);
12 create table oxford(a int primary key, b int, c text, d int) engine=ndb;
13 insert into oxford values
14 (1,1,repeat('B', 1000), 1),
15 (2,2,repeat('B', 1000), 2),
16 (3,3,repeat('B', 1000), 3),
17 (4,4,repeat('B', 1000), 4),
18 (5,5,repeat('B', 1000), 5);
19 -------------------------------------------------------
20 Test various concurrent modifications that should cause
21 copy failure for a copying alter drop column.
22 -------------------------------------------------------
23 Checking copying alter ALTER TABLE test.cambridge drop column d
24 concurrent with UPDATE test.cambridge set c=c+1
25 has alter error ER_TABLE_DEF_CHANGED
26 and statement error 0
27 set DEBUG_SYNC= 'ndb.before_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
28 ALTER TABLE test.cambridge drop column d;
29 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
30 UPDATE test.cambridge set c=c+1;
31 set DEBUG_SYNC= 'now SIGNAL alter_go';
32 ERROR HY000: Detected change to data in source table during copying ALTER TABLE. Alter aborted to avoid inconsistency.
33 set DEBUG_SYNC='RESET';
34 Checking copying alter ALTER TABLE test.cambridge drop column d
35 concurrent with UPDATE test.cambridge set c=20 where a=2
36 has alter error ER_TABLE_DEF_CHANGED
37 and statement error 0
38 set DEBUG_SYNC= 'ndb.before_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
39 ALTER TABLE test.cambridge drop column d;
40 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
41 UPDATE test.cambridge set c=20 where a=2;
42 set DEBUG_SYNC= 'now SIGNAL alter_go';
43 ERROR HY000: Detected change to data in source table during copying ALTER TABLE. Alter aborted to avoid inconsistency.
44 set DEBUG_SYNC='RESET';
45 Checking copying alter ALTER TABLE test.cambridge drop column d
46 concurrent with INSERT into test.cambridge values (6,6,6,6)
47 has alter error ER_TABLE_DEF_CHANGED
48 and statement error 0
49 set DEBUG_SYNC= 'ndb.before_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
50 ALTER TABLE test.cambridge drop column d;
51 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
52 INSERT into test.cambridge values (6,6,6,6);
53 set DEBUG_SYNC= 'now SIGNAL alter_go';
54 ERROR HY000: Detected change to data in source table during copying ALTER TABLE. Alter aborted to avoid inconsistency.
55 set DEBUG_SYNC='RESET';
56 Checking copying alter ALTER TABLE test.cambridge drop column d
57 concurrent with DELETE from test.cambridge where a=1
58 has alter error ER_TABLE_DEF_CHANGED
59 and statement error 0
60 set DEBUG_SYNC= 'ndb.before_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
61 ALTER TABLE test.cambridge drop column d;
62 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
63 DELETE from test.cambridge where a=1;
64 set DEBUG_SYNC= 'now SIGNAL alter_go';
65 ERROR HY000: Detected change to data in source table during copying ALTER TABLE. Alter aborted to avoid inconsistency.
66 set DEBUG_SYNC='RESET';
67 -------------------------------------------------------
68 Test various concurrent modifications that should cause
69 copy failure for a copying pk change
70 -------------------------------------------------------
71 Checking copying alter ALTER TABLE test.cambridge drop primary key
72 concurrent with UPDATE test.cambridge set c=c+1
73 has alter error ER_TABLE_DEF_CHANGED
74 and statement error 0
75 set DEBUG_SYNC= 'ndb.before_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
76 ALTER TABLE test.cambridge drop primary key;
77 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
78 UPDATE test.cambridge set c=c+1;
79 set DEBUG_SYNC= 'now SIGNAL alter_go';
80 ERROR HY000: Detected change to data in source table during copying ALTER TABLE. Alter aborted to avoid inconsistency.
81 set DEBUG_SYNC='RESET';
82 --------------------------
83 FK cascading modifications
84 --------------------------
85 Ndb maintains inter-table FKs in the kernel, so that
86 changes in a different table can affect a table
87 undergoing a copying alter
88 update cambridge set b=a;
89 alter table cambridge add unique(b);
90 create table bristol
91 (a int primary key, b int, c int,
92 foreign key (b) references cambridge (b) on update cascade on delete cascade)
93 engine=ndb;
94 insert into bristol values (2,2,2), (3,3,3), (4,4,4), (5,5,5);
95 Checking copying alter ALTER TABLE test.bristol drop column c
96 concurrent with UPDATE test.cambridge set b=50 where a=5
97 has alter error ER_TABLE_DEF_CHANGED
98 and statement error 0
99 set DEBUG_SYNC= 'ndb.before_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
100 ALTER TABLE test.bristol drop column c;
101 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
102 UPDATE test.cambridge set b=50 where a=5;
103 set DEBUG_SYNC= 'now SIGNAL alter_go';
104 ERROR HY000: Detected change to data in source table during copying ALTER TABLE. Alter aborted to avoid inconsistency.
105 set DEBUG_SYNC='RESET';
106 Checking copying alter ALTER TABLE test.bristol drop column c
107 concurrent with DELETE from test.cambridge where a=5
108 has alter error ER_TABLE_DEF_CHANGED
109 and statement error 0
110 set DEBUG_SYNC= 'ndb.before_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
111 ALTER TABLE test.bristol drop column c;
112 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
113 DELETE from test.cambridge where a=5;
114 set DEBUG_SYNC= 'now SIGNAL alter_go';
115 ERROR HY000: Detected change to data in source table during copying ALTER TABLE. Alter aborted to avoid inconsistency.
116 set DEBUG_SYNC='RESET';
117 drop table bristol;
118 -----
119 Blobs
120 -----
121 Checking copying alter ALTER TABLE test.oxford drop column d
122 concurrent with UPDATE test.oxford SET c=REPEAT('E',1000)
123 has alter error ER_TABLE_DEF_CHANGED
124 and statement error 0
125 set DEBUG_SYNC= 'ndb.before_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
126 ALTER TABLE test.oxford drop column d;
127 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
128 UPDATE test.oxford SET c=REPEAT('E',1000);
129 set DEBUG_SYNC= 'now SIGNAL alter_go';
130 ERROR HY000: Detected change to data in source table during copying ALTER TABLE. Alter aborted to avoid inconsistency.
131 set DEBUG_SYNC='RESET';
132 -------------------------------------------------------
133 Test various concurrent operations that should not cause
134 copy failure for a copying alter drop column.
135 -------------------------------------------------------
136 Scanning SELECTs
137 Checking copying alter ALTER TABLE test.cambridge drop column d
138 concurrent with SELECT a,b from test.cambridge order by a
139 has alter error 0
140 and statement error 0
141 set DEBUG_SYNC= 'ndb.before_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
142 ALTER TABLE test.cambridge drop column d;
143 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
144 SELECT a,b from test.cambridge order by a;
145 a b
146 2 2
147 3 3
148 4 4
149 6 6
150 set DEBUG_SYNC= 'now SIGNAL alter_go';
151 set DEBUG_SYNC='RESET';
152 alter table test.cambridge add column d int;
153 Warnings:
154 Warning 1478 Converted FIXED field 'd' to DYNAMIC to enable online ADD COLUMN
155 Checking copying alter ALTER TABLE test.cambridge drop column d
156 concurrent with SELECT a,b from test.cambridge order by a lock in share mode
157 has alter error 0
158 and statement error 0
159 set DEBUG_SYNC= 'ndb.before_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
160 ALTER TABLE test.cambridge drop column d;
161 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
162 SELECT a,b from test.cambridge order by a lock in share mode;
163 a b
164 2 2
165 3 3
166 4 4
167 6 6
168 set DEBUG_SYNC= 'now SIGNAL alter_go';
169 set DEBUG_SYNC='RESET';
170 alter table test.cambridge add column d int;
171 Warnings:
172 Warning 1478 Converted FIXED field 'd' to DYNAMIC to enable online ADD COLUMN
173 Checking copying alter ALTER TABLE test.cambridge drop column d
174 concurrent with SELECT a,b from test.cambridge order by a for update
175 has alter error 0
176 and statement error 0
177 set DEBUG_SYNC= 'ndb.before_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
178 ALTER TABLE test.cambridge drop column d;
179 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
180 SELECT a,b from test.cambridge order by a for update;
181 a b
182 2 2
183 3 3
184 4 4
185 6 6
186 set DEBUG_SYNC= 'now SIGNAL alter_go';
187 set DEBUG_SYNC='RESET';
188 alter table test.cambridge add column d int;
189 Warnings:
190 Warning 1478 Converted FIXED field 'd' to DYNAMIC to enable online ADD COLUMN
191 Checking copying alter ALTER TABLE test.oxford drop column d
192 concurrent with SELECT a,length(c) from test.oxford order by a
193 has alter error 0
194 and statement error 0
195 set DEBUG_SYNC= 'ndb.before_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
196 ALTER TABLE test.oxford drop column d;
197 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
198 SELECT a,length(c) from test.oxford order by a;
199 a length(c)
200 1 1000
201 2 1000
202 3 1000
203 4 1000
204 5 1000
205 set DEBUG_SYNC= 'now SIGNAL alter_go';
206 set DEBUG_SYNC='RESET';
207 alter table test.oxford add column d int;
208 Warnings:
209 Warning 1478 Converted FIXED field 'd' to DYNAMIC to enable online ADD COLUMN
210 Checking copying alter ALTER TABLE test.oxford drop column d
211 concurrent with SELECT a,length(c) from test.oxford order by a lock in share mode
212 has alter error 0
213 and statement error 0
214 set DEBUG_SYNC= 'ndb.before_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
215 ALTER TABLE test.oxford drop column d;
216 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
217 SELECT a,length(c) from test.oxford order by a lock in share mode;
218 a length(c)
219 1 1000
220 2 1000
221 3 1000
222 4 1000
223 5 1000
224 set DEBUG_SYNC= 'now SIGNAL alter_go';
225 set DEBUG_SYNC='RESET';
226 alter table test.oxford add column d int;
227 Warnings:
228 Warning 1478 Converted FIXED field 'd' to DYNAMIC to enable online ADD COLUMN
229 Checking copying alter ALTER TABLE test.oxford drop column d
230 concurrent with SELECT a,length(c) from test.oxford order by a for update
231 has alter error 0
232 and statement error 0
233 set DEBUG_SYNC= 'ndb.before_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
234 ALTER TABLE test.oxford drop column d;
235 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
236 SELECT a,length(c) from test.oxford order by a for update;
237 a length(c)
238 1 1000
239 2 1000
240 3 1000
241 4 1000
242 5 1000
243 set DEBUG_SYNC= 'now SIGNAL alter_go';
244 set DEBUG_SYNC='RESET';
245 alter table test.oxford add column d int;
246 Warnings:
247 Warning 1478 Converted FIXED field 'd' to DYNAMIC to enable online ADD COLUMN
248 SELECT by PK
249 Checking copying alter ALTER TABLE test.cambridge drop column d
250 concurrent with SELECT a,b from test.cambridge where a=3
251 has alter error 0
252 and statement error 0
253 set DEBUG_SYNC= 'ndb.before_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
254 ALTER TABLE test.cambridge drop column d;
255 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
256 SELECT a,b from test.cambridge where a=3;
257 a b
258 3 3
259 set DEBUG_SYNC= 'now SIGNAL alter_go';
260 set DEBUG_SYNC='RESET';
261 alter table test.cambridge add column d int;
262 Warnings:
263 Warning 1478 Converted FIXED field 'd' to DYNAMIC to enable online ADD COLUMN
264 Checking copying alter ALTER TABLE test.cambridge drop column d
265 concurrent with SELECT a,b from test.cambridge where a=3 lock in share mode
266 has alter error 0
267 and statement error 0
268 set DEBUG_SYNC= 'ndb.before_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
269 ALTER TABLE test.cambridge drop column d;
270 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
271 SELECT a,b from test.cambridge where a=3 lock in share mode;
272 a b
273 3 3
274 set DEBUG_SYNC= 'now SIGNAL alter_go';
275 set DEBUG_SYNC='RESET';
276 alter table test.cambridge add column d int;
277 Warnings:
278 Warning 1478 Converted FIXED field 'd' to DYNAMIC to enable online ADD COLUMN
279 Checking copying alter ALTER TABLE test.cambridge drop column d
280 concurrent with SELECT a,b from test.cambridge where a=3 for update
281 has alter error 0
282 and statement error 0
283 set DEBUG_SYNC= 'ndb.before_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
284 ALTER TABLE test.cambridge drop column d;
285 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
286 SELECT a,b from test.cambridge where a=3 for update;
287 a b
288 3 3
289 set DEBUG_SYNC= 'now SIGNAL alter_go';
290 set DEBUG_SYNC='RESET';
291 alter table test.cambridge add column d int;
292 Warnings:
293 Warning 1478 Converted FIXED field 'd' to DYNAMIC to enable online ADD COLUMN
294 Checking copying alter ALTER TABLE test.oxford drop column d
295 concurrent with SELECT a,length(c) from test.oxford where a=3
296 has alter error 0
297 and statement error 0
298 set DEBUG_SYNC= 'ndb.before_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
299 ALTER TABLE test.oxford drop column d;
300 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
301 SELECT a,length(c) from test.oxford where a=3;
302 a length(c)
303 3 1000
304 set DEBUG_SYNC= 'now SIGNAL alter_go';
305 set DEBUG_SYNC='RESET';
306 alter table test.oxford add column d int;
307 Warnings:
308 Warning 1478 Converted FIXED field 'd' to DYNAMIC to enable online ADD COLUMN
309 Checking copying alter ALTER TABLE test.oxford drop column d
310 concurrent with SELECT a,length(c) from test.oxford where a=3 lock in share mode
311 has alter error 0
312 and statement error 0
313 set DEBUG_SYNC= 'ndb.before_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
314 ALTER TABLE test.oxford drop column d;
315 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
316 SELECT a,length(c) from test.oxford where a=3 lock in share mode;
317 a length(c)
318 3 1000
319 set DEBUG_SYNC= 'now SIGNAL alter_go';
320 set DEBUG_SYNC='RESET';
321 alter table test.oxford add column d int;
322 Warnings:
323 Warning 1478 Converted FIXED field 'd' to DYNAMIC to enable online ADD COLUMN
324 Checking copying alter ALTER TABLE test.oxford drop column d
325 concurrent with SELECT a,length(c) from test.oxford where a=3 for update
326 has alter error 0
327 and statement error 0
328 set DEBUG_SYNC= 'ndb.before_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
329 ALTER TABLE test.oxford drop column d;
330 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
331 SELECT a,length(c) from test.oxford where a=3 for update;
332 a length(c)
333 3 1000
334 set DEBUG_SYNC= 'now SIGNAL alter_go';
335 set DEBUG_SYNC='RESET';
336 alter table test.oxford add column d int;
337 Warnings:
338 Warning 1478 Converted FIXED field 'd' to DYNAMIC to enable online ADD COLUMN
339 NULL effect modifications
340 Checking copying alter ALTER TABLE test.cambridge drop column d
341 concurrent with INSERT into test.cambridge values (2,2,2,2)
342 has alter error 0
343 and statement error ER_DUP_ENTRY
344 set DEBUG_SYNC= 'ndb.before_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
345 ALTER TABLE test.cambridge drop column d;
346 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
347 INSERT into test.cambridge values (2,2,2,2);
348 ERROR 23000: Duplicate entry '2' for key 'cambridge.PRIMARY'
349 set DEBUG_SYNC= 'now SIGNAL alter_go';
350 set DEBUG_SYNC='RESET';
351 alter table test.cambridge add column d int;
352 Warnings:
353 Warning 1478 Converted FIXED field 'd' to DYNAMIC to enable online ADD COLUMN
354 Checking copying alter ALTER TABLE test.cambridge drop column d
355 concurrent with UPDATE test.cambridge set b=888 where a=888
356 has alter error 0
357 and statement error 0
358 set DEBUG_SYNC= 'ndb.before_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
359 ALTER TABLE test.cambridge drop column d;
360 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
361 UPDATE test.cambridge set b=888 where a=888;
362 set DEBUG_SYNC= 'now SIGNAL alter_go';
363 set DEBUG_SYNC='RESET';
364 alter table test.cambridge add column d int;
365 Warnings:
366 Warning 1478 Converted FIXED field 'd' to DYNAMIC to enable online ADD COLUMN
367 Checking copying alter ALTER TABLE test.cambridge drop column d
368 concurrent with UPDATE test.cambridge set b=b
369 has alter error 0
370 and statement error 0
371 set DEBUG_SYNC= 'ndb.before_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
372 ALTER TABLE test.cambridge drop column d;
373 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
374 UPDATE test.cambridge set b=b;
375 set DEBUG_SYNC= 'now SIGNAL alter_go';
376 set DEBUG_SYNC='RESET';
377 alter table test.cambridge add column d int;
378 Warnings:
379 Warning 1478 Converted FIXED field 'd' to DYNAMIC to enable online ADD COLUMN
380 Checking copying alter ALTER TABLE test.cambridge drop column d
381 concurrent with DELETE from test.cambridge where a=888
382 has alter error 0
383 and statement error 0
384 set DEBUG_SYNC= 'ndb.before_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
385 ALTER TABLE test.cambridge drop column d;
386 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
387 DELETE from test.cambridge where a=888;
388 set DEBUG_SYNC= 'now SIGNAL alter_go';
389 set DEBUG_SYNC='RESET';
390 alter table test.cambridge add column d int;
391 Warnings:
392 Warning 1478 Converted FIXED field 'd' to DYNAMIC to enable online ADD COLUMN
393 Checking copying alter ALTER TABLE test.oxford drop column d
394 concurrent with INSERT into test.oxford values (2,2,repeat('A', 1000),2)
395 has alter error 0
396 and statement error ER_DUP_ENTRY
397 set DEBUG_SYNC= 'ndb.before_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
398 ALTER TABLE test.oxford drop column d;
399 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
400 INSERT into test.oxford values (2,2,repeat('A', 1000),2);
401 ERROR 23000: Duplicate entry '2' for key 'oxford.PRIMARY'
402 set DEBUG_SYNC= 'now SIGNAL alter_go';
403 set DEBUG_SYNC='RESET';
404 alter table test.oxford add column d int;
405 Warnings:
406 Warning 1478 Converted FIXED field 'd' to DYNAMIC to enable online ADD COLUMN
407 Checking copying alter ALTER TABLE test.oxford drop column d
408 concurrent with UPDATE test.oxford set b=888 where a=888
409 has alter error 0
410 and statement error 0
411 set DEBUG_SYNC= 'ndb.before_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
412 ALTER TABLE test.oxford drop column d;
413 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
414 UPDATE test.oxford set b=888 where a=888;
415 set DEBUG_SYNC= 'now SIGNAL alter_go';
416 set DEBUG_SYNC='RESET';
417 alter table test.oxford add column d int;
418 Warnings:
419 Warning 1478 Converted FIXED field 'd' to DYNAMIC to enable online ADD COLUMN
420 Checking copying alter ALTER TABLE test.oxford drop column d
421 concurrent with UPDATE test.oxford set c=c
422 has alter error 0
423 and statement error 0
424 set DEBUG_SYNC= 'ndb.before_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
425 ALTER TABLE test.oxford drop column d;
426 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
427 UPDATE test.oxford set c=c;
428 set DEBUG_SYNC= 'now SIGNAL alter_go';
429 set DEBUG_SYNC='RESET';
430 alter table test.oxford add column d int;
431 Warnings:
432 Warning 1478 Converted FIXED field 'd' to DYNAMIC to enable online ADD COLUMN
433 Checking copying alter ALTER TABLE test.oxford drop column d
434 concurrent with DELETE from test.oxford where a=888
435 has alter error 0
436 and statement error 0
437 set DEBUG_SYNC= 'ndb.before_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
438 ALTER TABLE test.oxford drop column d;
439 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
440 DELETE from test.oxford where a=888;
441 set DEBUG_SYNC= 'now SIGNAL alter_go';
442 set DEBUG_SYNC='RESET';
443 alter table test.oxford add column d int;
444 Warnings:
445 Warning 1478 Converted FIXED field 'd' to DYNAMIC to enable online ADD COLUMN
446 ------------------------------------------------
447 FK checks are unaffected by parent copying alter
448 ------------------------------------------------
449 update cambridge set b=a;
450 create table bristol
451 (a int primary key, b int, c int,
452 foreign key (b) references cambridge (b) on update cascade on delete cascade)
453 engine=ndb;
454 select a from cambridge order by a;
455 a
456 2
457 3
458 4
459 6
460 insert into bristol select a,a,a from cambridge;
461 Checking copying alter ALTER TABLE test.cambridge drop column d
462 concurrent with INSERT INTO test.bristol values (40,4,40)
463 has alter error 0
464 and statement error 0
465 set DEBUG_SYNC= 'ndb.before_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
466 ALTER TABLE test.cambridge drop column d;
467 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
468 INSERT INTO test.bristol values (40,4,40);
469 set DEBUG_SYNC= 'now SIGNAL alter_go';
470 set DEBUG_SYNC='RESET';
471 alter table test.cambridge add column d int;
472 Warnings:
473 Warning 1478 Converted FIXED field 'd' to DYNAMIC to enable online ADD COLUMN
474 Checking copying alter ALTER TABLE test.cambridge drop column d
475 concurrent with UPDATE test.bristol set b=4 where b=2
476 has alter error 0
477 and statement error 0
478 set DEBUG_SYNC= 'ndb.before_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
479 ALTER TABLE test.cambridge drop column d;
480 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
481 UPDATE test.bristol set b=4 where b=2;
482 set DEBUG_SYNC= 'now SIGNAL alter_go';
483 set DEBUG_SYNC='RESET';
484 alter table test.cambridge add column d int;
485 Warnings:
486 Warning 1478 Converted FIXED field 'd' to DYNAMIC to enable online ADD COLUMN
487 Checking copying alter ALTER TABLE test.cambridge drop column d
488 concurrent with DELETE FROM test.bristol
489 has alter error 0
490 and statement error 0
491 set DEBUG_SYNC= 'ndb.before_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
492 ALTER TABLE test.cambridge drop column d;
493 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
494 DELETE FROM test.bristol;
495 set DEBUG_SYNC= 'now SIGNAL alter_go';
496 set DEBUG_SYNC='RESET';
497 alter table test.cambridge add column d int;
498 Warnings:
499 Warning 1478 Converted FIXED field 'd' to DYNAMIC to enable online ADD COLUMN
500 drop table bristol;
501 ---------------------------------------------------------
502 Show post-check exposure in copying alter check mechanism
503 ---------------------------------------------------------
504 Existing rows :
505 update cambridge set c=a;
506 select a,c from cambridge order by a;
507 a c
508 2 2
509 3 3
510 4 4
511 6 6
512 set DEBUG_SYNC= 'ndb.after_commit_count_check SIGNAL alter_waiting WAIT_FOR alter_go';
513 ALTER TABLE cambridge drop column d;;
514 set DEBUG_SYNC= 'now WAIT_FOR alter_waiting';
515 insert into test.cambridge values
516 (20,20,20,20),
517 (21,21,21,21),
518 (22,22,22,22),
519 (23,23,23,23);
520 update test.cambridge set c=1000;
521 delete from test.cambridge where a=6;
522 Committed table content :
523 select a,c from test.cambridge order by a;
524 a c
525 2 1000
526 3 1000
527 4 1000
528 20 1000
529 21 1000
530 22 1000
531 23 1000
532 set DEBUG_SYNC= 'now SIGNAL alter_go';
533 Committed table content after ALTER
534 Changes have been lost
535 select a,c from test.cambridge order by a;
536 a c
537 2 2
538 3 3
539 4 4
540 6 6
541 set DEBUG_SYNC='RESET';
542 drop table oxford;
543 drop table cambridge;
544 Create RP, RB and FR tables
545 create table mab_rp (a int primary key, b int, c int, d int) engine=ndb COMMENT='NDB_TABLE=READ_BACKUP=0';
546 create table mab_rb (a int primary key, b int, c int, d int) engine=ndb COMMENT='NDB_TABLE=READ_BACKUP=1';
547 create table mab_fr (a int primary key, b int, c int, d int) engine=ndb COMMENT='NDB_TABLE=FULLY_REPLICATED=1';
548 Create some data in all table types
549 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);
550 insert into mab_rp select a+5, b+5, c+5, d+5 from mab_rp;
551 insert into mab_rp select a+10, b+10, c+10, d+10 from mab_rp;
552 insert into mab_rp select a+20, b+20, c+20, d+20 from mab_rp;
553 insert into mab_rb select * from mab_rp;
554 insert into mab_fr select * from mab_rp;
555 Perform some updates, so commit count != row count
556 update mab_rp set b=b+1;
557 update mab_rb set b=b+1;
558 update mab_fr set b=b+1;
559 Check stable commit counts
560 4 node cluster with 2 replicas ((1,2), (3,4))
561 Split into two logical sub clusters with (at least) all fragments A (1,3), B (2,4)
562 Show how (sum of) commit counts in each sub cluster diverge over node restart
563 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);
564 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);
565 rp_commit_counts_same
566 1
567 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);
568 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);
569 rb_commits_counts_same
570 1
571 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);
572 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);
573 fr_commit_counts_same
574 1
575 Restart one data node to cause different replicas to
576 have different commit counts
577 Node 1 is being restarted
578
579 Database node 1 is being started.
580
581 Node restarted
582 Show commit counts on A changed
583 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);
584 rp_commits_a_same
585 0
586 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);
587 rb_commits_a_same
588 0
589 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);
590 fr_commits_a_same
591 0
592 Show commit counts no longer the same A vs B
593 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);
594 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);
595 rp_commit_counts_same
596 0
597 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);
598 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);
599 rb_commit_counts_same
600 0
601 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);
602 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);
603 fr_commits_counts_same
604 0
605 Check that ALTER TABLE can handle this situation
606 ALTER TABLE mab_rp DROP COLUMN d;
607 ALTER TABLE mab_rb DROP COLUMN d;
608 ALTER TABLE mab_fr DROP COLUMN d;
609 Check that there are no resources left in use
610 select count(1) from ndbinfo.cluster_transactions;
611 count(1)
612 0
613 select count(1) from ndbinfo.cluster_operations;
614 count(1)
615 0
616 drop table mab_rp;
617 drop table mab_rb;
618 drop table mab_fr;