Debian Codegraph @

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

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