Debian Codegraph @

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