Debian Codegraph @

mysql-test/suite/clone/r/remote_ddl_alter_inplace.result

raw
1 INSTALL PLUGIN clone SONAME 'CLONE_PLUGIN';
2 SET GLOBAL innodb_ddl_threads = 1;
3 CREATE TABLE t1(col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB);
4 CREATE TABLE t2(col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB)
5 PARTITION BY KEY(col1) PARTITIONS 5;
6 CREATE PROCEDURE execute_dml(
7 p_dml_type INT,
8 p_key_min INT,
9 p_key_range INT,
10 p_loop_count INT,
11 p_frequency INT,
12 p_is_rand INT)
13 BEGIN
14 DECLARE v_idx INT DEFAULT 0;
15 DECLARE v_commit INT DEFAULT 0;
16 DECLARE v_key INT DEFAULT 0;
17 /* Loop and INSERT data at random position */
18 WHILE(v_idx < p_loop_count) DO
19 /* Generate key between 1 to p_loop_count */
20 IF p_is_rand = 1 THEN
21 SET v_key = p_key_min + FLOOR(RAND() * p_key_range);
22 ELSE
23 SET v_key = p_key_min + (v_idx % p_key_range);
24 END IF;
25 CASE p_dml_type
26 WHEN 0 THEN
27 SET @clol3_text = CONCAT('Clone Test Row - ', v_key);
28 INSERT INTO t1 (col1, col2, col3, col4) VALUES (
29 v_key, v_key * 10,
30 @clol3_text, REPEAT('Large Column Data ', 2048))
31 ON DUPLICATE KEY UPDATE col2 = col2 + 1;
32 INSERT INTO t2 (col1, col2, col3, col4) VALUES (
33 v_key, v_key * 10,
34 @clol3_text, REPEAT('Large Column Data ', 2048))
35 ON DUPLICATE KEY UPDATE col2 = col2 + 1;
36 WHEN 1 THEN
37 UPDATE t1 SET col2 = v_idx + 1 WHERE col1 = v_key;
38 UPDATE t2 SET col2 = v_idx + 1 WHERE col1 = v_key;
39 WHEN 2 THEN
40 DELETE FROM t1 WHERE col1 = v_key;
41 DELETE FROM t2 WHERE col1 = v_key;
42 ELSE
43 DELETE FROM t1;
44 DELETE FROM t2;
45 END CASE;
46 SET v_idx = v_idx + 1;
47 /* Commit or rollback work at specified frequency. */
48 IF v_idx % p_frequency = 0 THEN
49 SET v_commit = FLOOR(RAND() * 2);
50 IF v_commit = 0 AND p_is_rand = 1 THEN
51 ROLLBACK;
52 START TRANSACTION;
53 ELSE
54 COMMIT;
55 START TRANSACTION;
56 END IF;
57 END IF;
58 END WHILE;
59 COMMIT;
60 END|
61 call execute_dml(0, 0, 100, 100, 10, 0);
62 CREATE TABLE t SELECT * FROM t1;
63 CREATE TABLE t0 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB);
64 CREATE TABLE t01 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB);
65 CREATE TABLE t3 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB);
66 CREATE TABLE t11 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB);
67 CREATE TABLE t21 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB);
68 INSERT INTO t11 SELECT * FROM t;
69 INSERT INTO t21 SELECT * FROM t;
70 SHOW VARIABLES LIKE 'clone_ddl_timeout';
71 Variable_name Value
72 clone_ddl_timeout 300
73 SHOW VARIABLES LIKE 'clone_block_ddl';
74 Variable_name Value
75 clone_block_ddl OFF
76 # 1. ALTER TABLE ADD COLUMN while clone in progress
77 RESET MASTER;
78 SET GLOBAL DEBUG = '+d,remote_release_clone_file_pin';
79 ALTER TABLE t0 ADD COLUMN col5 int, ALGORITHM=INPLACE ;
80 SET DEBUG_SYNC = 'clone_before_init_meta SIGNAL start_ddl_file_init WAIT_FOR resume_file_init';
81 SET DEBUG_SYNC = 'clone_file_copy SIGNAL start_ddl_file WAIT_FOR resume_file';
82 SET DEBUG_SYNC = 'clone_file_copy_end_before_ack SIGNAL start_ddl_file_ack WAIT_FOR resume_file_ack';
83 SET DEBUG_SYNC = 'clone_before_file_ddl_meta SIGNAL start_ddl_file_meta WAIT_FOR resume_file_meta';
84 SET DEBUG_SYNC = 'clone_page_copy SIGNAL start_ddl_page WAIT_FOR resume_page';
85 SET DEBUG_SYNC = 'clone_before_page_ddl_meta SIGNAL start_ddl_page_meta WAIT_FOR resume_page_meta';
86 SET DEBUG_SYNC = 'clone_redo_copy SIGNAL start_ddl_redo WAIT_FOR resume_redo';
87 SET GLOBAL clone_autotune_concurrency = OFF;
88 SET GLOBAL clone_max_concurrency = 8;
89 SET GLOBAL clone_valid_donor_list = 'HOST:PORT';
90 CLONE INSTANCE FROM USER@HOST:PORT IDENTIFIED BY '' DATA DIRECTORY = 'CLONE_DATADIR';
91 # In connection CON1
92 ALTER TABLE t01 ADD COLUMN col5 int, ALGORITHM=INPLACE ;
93 SET DEBUG_SYNC = 'now SIGNAL resume_file_init';
94 # Waiting for clone to reach 'file copy'
95 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_file';
96 # Wait finished
97 # For remote clone, let the donor progress concurrently
98 # otherwise file pins may not be released causing deadlock
99 SET DEBUG_SYNC = 'clone_notify_ddl SIGNAL resume_file';
100 ALTER TABLE t1 ADD COLUMN col5 int, ALGORITHM=INPLACE ;
101 CALL execute_dml(0, 0, 100, 100, 10, 0);
102 # Flush all dirty pages to track
103 SET GLOBAL innodb_buf_flush_list_now = 1;
104 SET DEBUG_SYNC = 'now SIGNAL resume_file';
105 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_file_ack';
106 SET DEBUG_SYNC = 'now SIGNAL resume_file_ack';
107 SET DEBUG_SYNC = 'now SIGNAL resume_file_meta';
108 # Waiting for clone to reach 'page copy'
109 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_page';
110 # Wait finished
111 SET DEBUG_SYNC = 'clone_notify_ddl SIGNAL resume_page';
112 ALTER TABLE t2 ADD COLUMN col5 int, ALGORITHM=INPLACE ;
113 CALL execute_dml(0, 0, 100, 100, 10, 0);
114 SET DEBUG_SYNC = 'now SIGNAL resume_page';
115 # Waiting for clone to reach 'redo copy'
116 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_redo';
117 # Wait finished
118 ALTER TABLE t21 ADD COLUMN col5 int, ALGORITHM=INPLACE ;
119 INSERT INTO t21 SELECT col1, col2, col3, col4, col2 + 100 FROM t ON DUPLICATE KEY UPDATE col2 = t21.col2 + 1;;
120 ALTER TABLE t3 ADD COLUMN col5 int, ALGORITHM=INPLACE ;
121 SET DEBUG_SYNC = 'now SIGNAL resume_redo';
122 # In connection DEFAULT
123 # Waiting for clone to complete
124 # Wait finished
125 SET GLOBAL DEBUG = '-d,remote_release_clone_file_pin';
126 # Restart server on cloned data directory
127 # restart: --datadir=CLONE_DATADIR
128 # Check cloned data
129 SHOW TABLES;
130 Tables_in_test
131 t
132 t0
133 t01
134 t1
135 t11
136 t2
137 t21
138 t3
139 SHOW CREATE TABLE t1;
140 Table Create Table
141 t1 CREATE TABLE `t1` (
142 `col1` int NOT NULL,
143 `col2` int DEFAULT NULL,
144 `col3` varchar(64) DEFAULT NULL,
145 `col4` blob,
146 `col5` int DEFAULT NULL,
147 PRIMARY KEY (`col1`)
148 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
149 CHECK TABLE t1 EXTENDED;
150 Table Op Msg_type Msg_text
151 test.t1 check status OK
152 SELECT count(*) from t1;
153 count(*)
154 100
155 SELECT col1, col2, col3 FROM t1 ORDER BY col1 LIMIT 10;;
156 col1 col2 col3
157 0 1 Clone Test Row - 0
158 1 11 Clone Test Row - 1
159 2 21 Clone Test Row - 2
160 3 31 Clone Test Row - 3
161 4 41 Clone Test Row - 4
162 5 51 Clone Test Row - 5
163 6 61 Clone Test Row - 6
164 7 71 Clone Test Row - 7
165 8 81 Clone Test Row - 8
166 9 91 Clone Test Row - 9
167 SELECT col1, col2, col3 FROM t1 ORDER BY col1 DESC LIMIT 10;;
168 col1 col2 col3
169 99 991 Clone Test Row - 99
170 98 981 Clone Test Row - 98
171 97 971 Clone Test Row - 97
172 96 961 Clone Test Row - 96
173 95 951 Clone Test Row - 95
174 94 941 Clone Test Row - 94
175 93 931 Clone Test Row - 93
176 92 921 Clone Test Row - 92
177 91 911 Clone Test Row - 91
178 90 901 Clone Test Row - 90
179 SHOW CREATE TABLE t11;
180 Table Create Table
181 t11 CREATE TABLE `t11` (
182 `col1` int NOT NULL,
183 `col2` int DEFAULT NULL,
184 `col3` varchar(64) DEFAULT NULL,
185 `col4` blob,
186 PRIMARY KEY (`col1`)
187 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
188 CHECK TABLE t11 EXTENDED;
189 Table Op Msg_type Msg_text
190 test.t11 check status OK
191 SELECT count(*) from t11;
192 count(*)
193 100
194 SELECT col1, col2, col3 FROM t11 ORDER BY col1 LIMIT 10;;
195 col1 col2 col3
196 0 0 Clone Test Row - 0
197 1 10 Clone Test Row - 1
198 2 20 Clone Test Row - 2
199 3 30 Clone Test Row - 3
200 4 40 Clone Test Row - 4
201 5 50 Clone Test Row - 5
202 6 60 Clone Test Row - 6
203 7 70 Clone Test Row - 7
204 8 80 Clone Test Row - 8
205 9 90 Clone Test Row - 9
206 SELECT col1, col2, col3 FROM t11 ORDER BY col1 DESC LIMIT 10;;
207 col1 col2 col3
208 99 990 Clone Test Row - 99
209 98 980 Clone Test Row - 98
210 97 970 Clone Test Row - 97
211 96 960 Clone Test Row - 96
212 95 950 Clone Test Row - 95
213 94 940 Clone Test Row - 94
214 93 930 Clone Test Row - 93
215 92 920 Clone Test Row - 92
216 91 910 Clone Test Row - 91
217 90 900 Clone Test Row - 90
218 SHOW CREATE TABLE t2;
219 Table Create Table
220 t2 CREATE TABLE `t2` (
221 `col1` int NOT NULL,
222 `col2` int DEFAULT NULL,
223 `col3` varchar(64) DEFAULT NULL,
224 `col4` blob,
225 PRIMARY KEY (`col1`)
226 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
227 /*!50100 PARTITION BY KEY (col1)
228 PARTITIONS 5 */
229 CHECK TABLE t2 EXTENDED;
230 Table Op Msg_type Msg_text
231 test.t2 check status OK
232 SELECT count(*) from t2;
233 count(*)
234 100
235 SELECT col1, col2, col3 FROM t2 ORDER BY col1 LIMIT 10;;
236 col1 col2 col3
237 0 1 Clone Test Row - 0
238 1 11 Clone Test Row - 1
239 2 21 Clone Test Row - 2
240 3 31 Clone Test Row - 3
241 4 41 Clone Test Row - 4
242 5 51 Clone Test Row - 5
243 6 61 Clone Test Row - 6
244 7 71 Clone Test Row - 7
245 8 81 Clone Test Row - 8
246 9 91 Clone Test Row - 9
247 SELECT col1, col2, col3 FROM t2 ORDER BY col1 DESC LIMIT 10;;
248 col1 col2 col3
249 99 991 Clone Test Row - 99
250 98 981 Clone Test Row - 98
251 97 971 Clone Test Row - 97
252 96 961 Clone Test Row - 96
253 95 951 Clone Test Row - 95
254 94 941 Clone Test Row - 94
255 93 931 Clone Test Row - 93
256 92 921 Clone Test Row - 92
257 91 911 Clone Test Row - 91
258 90 901 Clone Test Row - 90
259 call execute_dml(1, 0, 100, 100, 10, 0);
260 # Restart server back on base data directory
261 # restart:
262 SET DEBUG_SYNC = 'RESET';
263 # 2. ALTER TABLE DROP COLUMN while clone in progress
264 RESET MASTER;
265 SET GLOBAL DEBUG = '+d,remote_release_clone_file_pin';
266 ALTER TABLE t0 DROP COLUMN col5, ALGORITHM=INPLACE ;
267 SET DEBUG_SYNC = 'clone_before_init_meta SIGNAL start_ddl_file_init WAIT_FOR resume_file_init';
268 SET DEBUG_SYNC = 'clone_file_copy SIGNAL start_ddl_file WAIT_FOR resume_file';
269 SET DEBUG_SYNC = 'clone_file_copy_end_before_ack SIGNAL start_ddl_file_ack WAIT_FOR resume_file_ack';
270 SET DEBUG_SYNC = 'clone_before_file_ddl_meta SIGNAL start_ddl_file_meta WAIT_FOR resume_file_meta';
271 SET DEBUG_SYNC = 'clone_page_copy SIGNAL start_ddl_page WAIT_FOR resume_page';
272 SET DEBUG_SYNC = 'clone_before_page_ddl_meta SIGNAL start_ddl_page_meta WAIT_FOR resume_page_meta';
273 SET DEBUG_SYNC = 'clone_redo_copy SIGNAL start_ddl_redo WAIT_FOR resume_redo';
274 SET GLOBAL clone_autotune_concurrency = OFF;
275 SET GLOBAL clone_max_concurrency = 8;
276 SET GLOBAL clone_valid_donor_list = 'HOST:PORT';
277 CLONE INSTANCE FROM USER@HOST:PORT IDENTIFIED BY '' DATA DIRECTORY = 'CLONE_DATADIR';
278 # In connection CON1
279 ALTER TABLE t01 DROP COLUMN col5, ALGORITHM=INPLACE ;
280 SET DEBUG_SYNC = 'now SIGNAL resume_file_init';
281 # Waiting for clone to reach 'file copy'
282 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_file';
283 # Wait finished
284 # For remote clone, let the donor progress concurrently
285 # otherwise file pins may not be released causing deadlock
286 SET DEBUG_SYNC = 'clone_notify_ddl SIGNAL resume_file';
287 ALTER TABLE t1 DROP COLUMN col5, ALGORITHM=INPLACE ;
288 call execute_dml(1, 0, 100, 100, 10, 0);
289 # Flush all dirty pages to track
290 SET GLOBAL innodb_buf_flush_list_now = 1;
291 SET DEBUG_SYNC = 'now SIGNAL resume_file';
292 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_file_ack';
293 SET DEBUG_SYNC = 'now SIGNAL resume_file_ack';
294 SET DEBUG_SYNC = 'now SIGNAL resume_file_meta';
295 # Waiting for clone to reach 'page copy'
296 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_page';
297 # Wait finished
298 SET DEBUG_SYNC = 'clone_notify_ddl SIGNAL resume_page';
299 ALTER TABLE t2 DROP COLUMN col5, ALGORITHM=INPLACE ;
300 call execute_dml(1, 0, 100, 100, 10, 0);
301 SET DEBUG_SYNC = 'now SIGNAL resume_page';
302 # Waiting for clone to reach 'redo copy'
303 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_redo';
304 # Wait finished
305 ALTER TABLE t21 DROP COLUMN col5, ALGORITHM=INPLACE ;
306 INSERT INTO t21 SELECT * FROM t ON DUPLICATE KEY UPDATE col2 = t21.col2 + 1;;
307 ALTER TABLE t3 DROP COLUMN col5, ALGORITHM=INPLACE ;
308 SET DEBUG_SYNC = 'now SIGNAL resume_redo';
309 # In connection DEFAULT
310 # Waiting for clone to complete
311 # Wait finished
312 SET GLOBAL DEBUG = '-d,remote_release_clone_file_pin';
313 # Restart server on cloned data directory
314 # restart: --datadir=CLONE_DATADIR
315 # Check cloned data
316 SHOW TABLES;
317 Tables_in_test
318 t
319 t0
320 t01
321 t1
322 t11
323 t2
324 t21
325 t3
326 SHOW CREATE TABLE t1;
327 Table Create Table
328 t1 CREATE TABLE `t1` (
329 `col1` int NOT NULL,
330 `col2` int DEFAULT NULL,
331 `col3` varchar(64) DEFAULT NULL,
332 `col4` blob,
333 PRIMARY KEY (`col1`)
334 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
335 CHECK TABLE t1 EXTENDED;
336 Table Op Msg_type Msg_text
337 test.t1 check status OK
338 SELECT count(*) from t1;
339 count(*)
340 100
341 SELECT col1, col2, col3 FROM t1 ORDER BY col1 LIMIT 10;;
342 col1 col2 col3
343 0 1 Clone Test Row - 0
344 1 2 Clone Test Row - 1
345 2 3 Clone Test Row - 2
346 3 4 Clone Test Row - 3
347 4 5 Clone Test Row - 4
348 5 6 Clone Test Row - 5
349 6 7 Clone Test Row - 6
350 7 8 Clone Test Row - 7
351 8 9 Clone Test Row - 8
352 9 10 Clone Test Row - 9
353 SELECT col1, col2, col3 FROM t1 ORDER BY col1 DESC LIMIT 10;;
354 col1 col2 col3
355 99 100 Clone Test Row - 99
356 98 99 Clone Test Row - 98
357 97 98 Clone Test Row - 97
358 96 97 Clone Test Row - 96
359 95 96 Clone Test Row - 95
360 94 95 Clone Test Row - 94
361 93 94 Clone Test Row - 93
362 92 93 Clone Test Row - 92
363 91 92 Clone Test Row - 91
364 90 91 Clone Test Row - 90
365 SHOW CREATE TABLE t11;
366 Table Create Table
367 t11 CREATE TABLE `t11` (
368 `col1` int NOT NULL,
369 `col2` int DEFAULT NULL,
370 `col3` varchar(64) DEFAULT NULL,
371 `col4` blob,
372 PRIMARY KEY (`col1`)
373 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
374 CHECK TABLE t11 EXTENDED;
375 Table Op Msg_type Msg_text
376 test.t11 check status OK
377 SELECT count(*) from t11;
378 count(*)
379 100
380 SELECT col1, col2, col3 FROM t11 ORDER BY col1 LIMIT 10;;
381 col1 col2 col3
382 0 0 Clone Test Row - 0
383 1 10 Clone Test Row - 1
384 2 20 Clone Test Row - 2
385 3 30 Clone Test Row - 3
386 4 40 Clone Test Row - 4
387 5 50 Clone Test Row - 5
388 6 60 Clone Test Row - 6
389 7 70 Clone Test Row - 7
390 8 80 Clone Test Row - 8
391 9 90 Clone Test Row - 9
392 SELECT col1, col2, col3 FROM t11 ORDER BY col1 DESC LIMIT 10;;
393 col1 col2 col3
394 99 990 Clone Test Row - 99
395 98 980 Clone Test Row - 98
396 97 970 Clone Test Row - 97
397 96 960 Clone Test Row - 96
398 95 950 Clone Test Row - 95
399 94 940 Clone Test Row - 94
400 93 930 Clone Test Row - 93
401 92 920 Clone Test Row - 92
402 91 910 Clone Test Row - 91
403 90 900 Clone Test Row - 90
404 SHOW CREATE TABLE t2;
405 Table Create Table
406 t2 CREATE TABLE `t2` (
407 `col1` int NOT NULL,
408 `col2` int DEFAULT NULL,
409 `col3` varchar(64) DEFAULT NULL,
410 `col4` blob,
411 `col5` int DEFAULT NULL,
412 PRIMARY KEY (`col1`)
413 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
414 /*!50100 PARTITION BY KEY (col1)
415 PARTITIONS 5 */
416 CHECK TABLE t2 EXTENDED;
417 Table Op Msg_type Msg_text
418 test.t2 check status OK
419 SELECT count(*) from t2;
420 count(*)
421 100
422 SELECT col1, col2, col3 FROM t2 ORDER BY col1 LIMIT 10;;
423 col1 col2 col3
424 0 1 Clone Test Row - 0
425 1 2 Clone Test Row - 1
426 2 3 Clone Test Row - 2
427 3 4 Clone Test Row - 3
428 4 5 Clone Test Row - 4
429 5 6 Clone Test Row - 5
430 6 7 Clone Test Row - 6
431 7 8 Clone Test Row - 7
432 8 9 Clone Test Row - 8
433 9 10 Clone Test Row - 9
434 SELECT col1, col2, col3 FROM t2 ORDER BY col1 DESC LIMIT 10;;
435 col1 col2 col3
436 99 100 Clone Test Row - 99
437 98 99 Clone Test Row - 98
438 97 98 Clone Test Row - 97
439 96 97 Clone Test Row - 96
440 95 96 Clone Test Row - 95
441 94 95 Clone Test Row - 94
442 93 94 Clone Test Row - 93
443 92 93 Clone Test Row - 92
444 91 92 Clone Test Row - 91
445 90 91 Clone Test Row - 90
446 call execute_dml(1, 0, 100, 100, 10, 0);
447 # Restart server back on base data directory
448 # restart:
449 SET DEBUG_SYNC = 'RESET';
450 # 3. ADD INDEX while clone in progress
451 SET GLOBAL innodb_ddl_threads = 1;
452 RESET MASTER;
453 SET GLOBAL DEBUG = '+d,remote_release_clone_file_pin';
454 ALTER TABLE t0 ADD INDEX col2_idx(col2), ALGORITHM=INPLACE ;
455 SET DEBUG_SYNC = 'clone_before_init_meta SIGNAL start_ddl_file_init WAIT_FOR resume_file_init';
456 SET DEBUG_SYNC = 'clone_file_copy SIGNAL start_ddl_file WAIT_FOR resume_file';
457 SET DEBUG_SYNC = 'clone_file_copy_end_before_ack SIGNAL start_ddl_file_ack WAIT_FOR resume_file_ack';
458 SET DEBUG_SYNC = 'clone_before_file_ddl_meta SIGNAL start_ddl_file_meta WAIT_FOR resume_file_meta';
459 SET DEBUG_SYNC = 'clone_page_copy SIGNAL start_ddl_page WAIT_FOR resume_page';
460 SET DEBUG_SYNC = 'clone_before_page_ddl_meta SIGNAL start_ddl_page_meta WAIT_FOR resume_page_meta';
461 SET DEBUG_SYNC = 'clone_redo_copy SIGNAL start_ddl_redo WAIT_FOR resume_redo';
462 SET GLOBAL clone_autotune_concurrency = OFF;
463 SET GLOBAL clone_max_concurrency = 8;
464 SET GLOBAL clone_valid_donor_list = 'HOST:PORT';
465 CLONE INSTANCE FROM USER@HOST:PORT IDENTIFIED BY '' DATA DIRECTORY = 'CLONE_DATADIR';
466 # In connection CON1
467 ALTER TABLE t01 ADD INDEX col2_idx(col2), ALGORITHM=INPLACE ;
468 SET DEBUG_SYNC = 'now SIGNAL resume_file_init';
469 # Waiting for clone to reach 'file copy'
470 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_file';
471 # Wait finished
472 # For remote clone, let the donor progress concurrently
473 # otherwise file pins may not be released causing deadlock
474 SET DEBUG_SYNC = 'clone_notify_ddl SIGNAL resume_file';
475 ALTER TABLE t1 ADD INDEX col2_idx(col2), ALGORITHM=INPLACE ;
476 CALL execute_dml(0, 0, 100, 100, 10, 0);
477 # Flush all dirty pages to track
478 SET GLOBAL innodb_buf_flush_list_now = 1;
479 SET DEBUG_SYNC = 'now SIGNAL resume_file';
480 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_file_ack';
481 SET DEBUG_SYNC = 'now SIGNAL resume_file_ack';
482 SET DEBUG_SYNC = 'now SIGNAL resume_file_meta';
483 # Waiting for clone to reach 'page copy'
484 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_page';
485 # Wait finished
486 SET DEBUG_SYNC = 'clone_notify_ddl SIGNAL resume_page';
487 ALTER TABLE t2 ADD INDEX col2_idx(col2), ALGORITHM=INPLACE ;
488 CALL execute_dml(0, 0, 100, 100, 10, 0);
489 SET DEBUG_SYNC = 'now SIGNAL resume_page';
490 # Waiting for clone to reach 'redo copy'
491 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_redo';
492 # Wait finished
493 ALTER TABLE t21 ADD INDEX col2_idx(col2), ALGORITHM=INPLACE ;
494 INSERT INTO t21 SELECT * FROM t ON DUPLICATE KEY UPDATE col2 = t21.col2 + 1;;
495 ALTER TABLE t3 ADD INDEX col2_idx(col2), ALGORITHM=INPLACE ;
496 SET DEBUG_SYNC = 'now SIGNAL resume_redo';
497 # In connection DEFAULT
498 # Waiting for clone to complete
499 # Wait finished
500 SET GLOBAL DEBUG = '-d,remote_release_clone_file_pin';
501 # Restart server on cloned data directory
502 # restart: --datadir=CLONE_DATADIR
503 # Check cloned data
504 SHOW TABLES;
505 Tables_in_test
506 t
507 t0
508 t01
509 t1
510 t11
511 t2
512 t21
513 t3
514 SHOW CREATE TABLE t1;
515 Table Create Table
516 t1 CREATE TABLE `t1` (
517 `col1` int NOT NULL,
518 `col2` int DEFAULT NULL,
519 `col3` varchar(64) DEFAULT NULL,
520 `col4` blob,
521 PRIMARY KEY (`col1`),
522 KEY `col2_idx` (`col2`)
523 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
524 CHECK TABLE t1 EXTENDED;
525 Table Op Msg_type Msg_text
526 test.t1 check status OK
527 SELECT count(*) from t1;
528 count(*)
529 100
530 SELECT col1, col2, col3 FROM t1 ORDER BY col1 LIMIT 10;;
531 col1 col2 col3
532 0 2 Clone Test Row - 0
533 1 3 Clone Test Row - 1
534 2 4 Clone Test Row - 2
535 3 5 Clone Test Row - 3
536 4 6 Clone Test Row - 4
537 5 7 Clone Test Row - 5
538 6 8 Clone Test Row - 6
539 7 9 Clone Test Row - 7
540 8 10 Clone Test Row - 8
541 9 11 Clone Test Row - 9
542 SELECT col1, col2, col3 FROM t1 ORDER BY col1 DESC LIMIT 10;;
543 col1 col2 col3
544 99 101 Clone Test Row - 99
545 98 100 Clone Test Row - 98
546 97 99 Clone Test Row - 97
547 96 98 Clone Test Row - 96
548 95 97 Clone Test Row - 95
549 94 96 Clone Test Row - 94
550 93 95 Clone Test Row - 93
551 92 94 Clone Test Row - 92
552 91 93 Clone Test Row - 91
553 90 92 Clone Test Row - 90
554 SHOW CREATE TABLE t11;
555 Table Create Table
556 t11 CREATE TABLE `t11` (
557 `col1` int NOT NULL,
558 `col2` int DEFAULT NULL,
559 `col3` varchar(64) DEFAULT NULL,
560 `col4` blob,
561 PRIMARY KEY (`col1`)
562 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
563 CHECK TABLE t11 EXTENDED;
564 Table Op Msg_type Msg_text
565 test.t11 check status OK
566 SELECT count(*) from t11;
567 count(*)
568 100
569 SELECT col1, col2, col3 FROM t11 ORDER BY col1 LIMIT 10;;
570 col1 col2 col3
571 0 0 Clone Test Row - 0
572 1 10 Clone Test Row - 1
573 2 20 Clone Test Row - 2
574 3 30 Clone Test Row - 3
575 4 40 Clone Test Row - 4
576 5 50 Clone Test Row - 5
577 6 60 Clone Test Row - 6
578 7 70 Clone Test Row - 7
579 8 80 Clone Test Row - 8
580 9 90 Clone Test Row - 9
581 SELECT col1, col2, col3 FROM t11 ORDER BY col1 DESC LIMIT 10;;
582 col1 col2 col3
583 99 990 Clone Test Row - 99
584 98 980 Clone Test Row - 98
585 97 970 Clone Test Row - 97
586 96 960 Clone Test Row - 96
587 95 950 Clone Test Row - 95
588 94 940 Clone Test Row - 94
589 93 930 Clone Test Row - 93
590 92 920 Clone Test Row - 92
591 91 910 Clone Test Row - 91
592 90 900 Clone Test Row - 90
593 SHOW CREATE TABLE t2;
594 Table Create Table
595 t2 CREATE TABLE `t2` (
596 `col1` int NOT NULL,
597 `col2` int DEFAULT NULL,
598 `col3` varchar(64) DEFAULT NULL,
599 `col4` blob,
600 PRIMARY KEY (`col1`)
601 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
602 /*!50100 PARTITION BY KEY (col1)
603 PARTITIONS 5 */
604 CHECK TABLE t2 EXTENDED;
605 Table Op Msg_type Msg_text
606 test.t2 check status OK
607 SELECT count(*) from t2;
608 count(*)
609 100
610 SELECT col1, col2, col3 FROM t2 ORDER BY col1 LIMIT 10;;
611 col1 col2 col3
612 0 2 Clone Test Row - 0
613 1 3 Clone Test Row - 1
614 2 4 Clone Test Row - 2
615 3 5 Clone Test Row - 3
616 4 6 Clone Test Row - 4
617 5 7 Clone Test Row - 5
618 6 8 Clone Test Row - 6
619 7 9 Clone Test Row - 7
620 8 10 Clone Test Row - 8
621 9 11 Clone Test Row - 9
622 SELECT col1, col2, col3 FROM t2 ORDER BY col1 DESC LIMIT 10;;
623 col1 col2 col3
624 99 101 Clone Test Row - 99
625 98 100 Clone Test Row - 98
626 97 99 Clone Test Row - 97
627 96 98 Clone Test Row - 96
628 95 97 Clone Test Row - 95
629 94 96 Clone Test Row - 94
630 93 95 Clone Test Row - 93
631 92 94 Clone Test Row - 92
632 91 93 Clone Test Row - 91
633 90 92 Clone Test Row - 90
634 call execute_dml(1, 0, 100, 100, 10, 0);
635 # Restart server back on base data directory
636 # restart:
637 SET DEBUG_SYNC = 'RESET';
638 # 4. DROP INDEX while clone in progress
639 RESET MASTER;
640 SET GLOBAL DEBUG = '+d,remote_release_clone_file_pin';
641 ALTER TABLE t0 DROP INDEX col2_idx, ALGORITHM=INPLACE ;
642 SET DEBUG_SYNC = 'clone_before_init_meta SIGNAL start_ddl_file_init WAIT_FOR resume_file_init';
643 SET DEBUG_SYNC = 'clone_file_copy SIGNAL start_ddl_file WAIT_FOR resume_file';
644 SET DEBUG_SYNC = 'clone_file_copy_end_before_ack SIGNAL start_ddl_file_ack WAIT_FOR resume_file_ack';
645 SET DEBUG_SYNC = 'clone_before_file_ddl_meta SIGNAL start_ddl_file_meta WAIT_FOR resume_file_meta';
646 SET DEBUG_SYNC = 'clone_page_copy SIGNAL start_ddl_page WAIT_FOR resume_page';
647 SET DEBUG_SYNC = 'clone_before_page_ddl_meta SIGNAL start_ddl_page_meta WAIT_FOR resume_page_meta';
648 SET DEBUG_SYNC = 'clone_redo_copy SIGNAL start_ddl_redo WAIT_FOR resume_redo';
649 SET GLOBAL clone_autotune_concurrency = OFF;
650 SET GLOBAL clone_max_concurrency = 8;
651 SET GLOBAL clone_valid_donor_list = 'HOST:PORT';
652 CLONE INSTANCE FROM USER@HOST:PORT IDENTIFIED BY '' DATA DIRECTORY = 'CLONE_DATADIR';
653 # In connection CON1
654 ALTER TABLE t01 DROP INDEX col2_idx, ALGORITHM=INPLACE ;
655 SET DEBUG_SYNC = 'now SIGNAL resume_file_init';
656 # Waiting for clone to reach 'file copy'
657 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_file';
658 # Wait finished
659 # For remote clone, let the donor progress concurrently
660 # otherwise file pins may not be released causing deadlock
661 SET DEBUG_SYNC = 'clone_notify_ddl SIGNAL resume_file';
662 ALTER TABLE t1 DROP INDEX col2_idx, ALGORITHM=INPLACE ;
663 CALL execute_dml(0, 0, 100, 100, 10, 0);
664 # Flush all dirty pages to track
665 SET GLOBAL innodb_buf_flush_list_now = 1;
666 SET DEBUG_SYNC = 'now SIGNAL resume_file';
667 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_file_ack';
668 SET DEBUG_SYNC = 'now SIGNAL resume_file_ack';
669 SET DEBUG_SYNC = 'now SIGNAL resume_file_meta';
670 # Waiting for clone to reach 'page copy'
671 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_page';
672 # Wait finished
673 SET DEBUG_SYNC = 'clone_notify_ddl SIGNAL resume_page';
674 ALTER TABLE t2 DROP INDEX col2_idx, ALGORITHM=INPLACE ;
675 CALL execute_dml(0, 0, 100, 100, 10, 0);
676 SET DEBUG_SYNC = 'now SIGNAL resume_page';
677 # Waiting for clone to reach 'redo copy'
678 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_redo';
679 # Wait finished
680 ALTER TABLE t21 DROP INDEX col2_idx, ALGORITHM=INPLACE ;
681 INSERT INTO t21 SELECT * FROM t ON DUPLICATE KEY UPDATE col2 = t21.col2 + 1;;
682 ALTER TABLE t3 DROP INDEX col2_idx, ALGORITHM=INPLACE ;
683 SET DEBUG_SYNC = 'now SIGNAL resume_redo';
684 # In connection DEFAULT
685 # Waiting for clone to complete
686 # Wait finished
687 SET GLOBAL DEBUG = '-d,remote_release_clone_file_pin';
688 # Restart server on cloned data directory
689 # restart: --datadir=CLONE_DATADIR
690 # Check cloned data
691 SHOW TABLES;
692 Tables_in_test
693 t
694 t0
695 t01
696 t1
697 t11
698 t2
699 t21
700 t3
701 SHOW CREATE TABLE t1;
702 Table Create Table
703 t1 CREATE TABLE `t1` (
704 `col1` int NOT NULL,
705 `col2` int DEFAULT NULL,
706 `col3` varchar(64) DEFAULT NULL,
707 `col4` blob,
708 PRIMARY KEY (`col1`)
709 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
710 CHECK TABLE t1 EXTENDED;
711 Table Op Msg_type Msg_text
712 test.t1 check status OK
713 SELECT count(*) from t1;
714 count(*)
715 100
716 SELECT col1, col2, col3 FROM t1 ORDER BY col1 LIMIT 10;;
717 col1 col2 col3
718 0 5 Clone Test Row - 0
719 1 6 Clone Test Row - 1
720 2 7 Clone Test Row - 2
721 3 8 Clone Test Row - 3
722 4 9 Clone Test Row - 4
723 5 10 Clone Test Row - 5
724 6 11 Clone Test Row - 6
725 7 12 Clone Test Row - 7
726 8 13 Clone Test Row - 8
727 9 14 Clone Test Row - 9
728 SELECT col1, col2, col3 FROM t1 ORDER BY col1 DESC LIMIT 10;;
729 col1 col2 col3
730 99 104 Clone Test Row - 99
731 98 103 Clone Test Row - 98
732 97 102 Clone Test Row - 97
733 96 101 Clone Test Row - 96
734 95 100 Clone Test Row - 95
735 94 99 Clone Test Row - 94
736 93 98 Clone Test Row - 93
737 92 97 Clone Test Row - 92
738 91 96 Clone Test Row - 91
739 90 95 Clone Test Row - 90
740 SHOW CREATE TABLE t11;
741 Table Create Table
742 t11 CREATE TABLE `t11` (
743 `col1` int NOT NULL,
744 `col2` int DEFAULT NULL,
745 `col3` varchar(64) DEFAULT NULL,
746 `col4` blob,
747 PRIMARY KEY (`col1`)
748 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
749 CHECK TABLE t11 EXTENDED;
750 Table Op Msg_type Msg_text
751 test.t11 check status OK
752 SELECT count(*) from t11;
753 count(*)
754 100
755 SELECT col1, col2, col3 FROM t11 ORDER BY col1 LIMIT 10;;
756 col1 col2 col3
757 0 0 Clone Test Row - 0
758 1 10 Clone Test Row - 1
759 2 20 Clone Test Row - 2
760 3 30 Clone Test Row - 3
761 4 40 Clone Test Row - 4
762 5 50 Clone Test Row - 5
763 6 60 Clone Test Row - 6
764 7 70 Clone Test Row - 7
765 8 80 Clone Test Row - 8
766 9 90 Clone Test Row - 9
767 SELECT col1, col2, col3 FROM t11 ORDER BY col1 DESC LIMIT 10;;
768 col1 col2 col3
769 99 990 Clone Test Row - 99
770 98 980 Clone Test Row - 98
771 97 970 Clone Test Row - 97
772 96 960 Clone Test Row - 96
773 95 950 Clone Test Row - 95
774 94 940 Clone Test Row - 94
775 93 930 Clone Test Row - 93
776 92 920 Clone Test Row - 92
777 91 910 Clone Test Row - 91
778 90 900 Clone Test Row - 90
779 SHOW CREATE TABLE t2;
780 Table Create Table
781 t2 CREATE TABLE `t2` (
782 `col1` int NOT NULL,
783 `col2` int DEFAULT NULL,
784 `col3` varchar(64) DEFAULT NULL,
785 `col4` blob,
786 PRIMARY KEY (`col1`)
787 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
788 /*!50100 PARTITION BY KEY (col1)
789 PARTITIONS 5 */
790 CHECK TABLE t2 EXTENDED;
791 Table Op Msg_type Msg_text
792 test.t2 check status OK
793 SELECT count(*) from t2;
794 count(*)
795 100
796 SELECT col1, col2, col3 FROM t2 ORDER BY col1 LIMIT 10;;
797 col1 col2 col3
798 0 5 Clone Test Row - 0
799 1 6 Clone Test Row - 1
800 2 7 Clone Test Row - 2
801 3 8 Clone Test Row - 3
802 4 9 Clone Test Row - 4
803 5 10 Clone Test Row - 5
804 6 11 Clone Test Row - 6
805 7 12 Clone Test Row - 7
806 8 13 Clone Test Row - 8
807 9 14 Clone Test Row - 9
808 SELECT col1, col2, col3 FROM t2 ORDER BY col1 DESC LIMIT 10;;
809 col1 col2 col3
810 99 104 Clone Test Row - 99
811 98 103 Clone Test Row - 98
812 97 102 Clone Test Row - 97
813 96 101 Clone Test Row - 96
814 95 100 Clone Test Row - 95
815 94 99 Clone Test Row - 94
816 93 98 Clone Test Row - 93
817 92 97 Clone Test Row - 92
818 91 96 Clone Test Row - 91
819 90 95 Clone Test Row - 90
820 call execute_dml(1, 0, 100, 100, 10, 0);
821 # Restart server back on base data directory
822 # restart:
823 SET DEBUG_SYNC = 'RESET';
824 # Cleanup
825 DROP TABLE t;
826 DROP TABLE t1;
827 DROP TABLE t2;
828 DROP PROCEDURE execute_dml;
829 DROP TABLE t0;
830 DROP TABLE t01;
831 DROP TABLE t3;
832 DROP TABLE t11;
833 DROP TABLE t21;
834 SET GLOBAL innodb_ddl_threads = default;
835 # Uninstall clone
836 UNINSTALL PLUGIN clone;