Debian Codegraph @

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

raw
1 INSTALL PLUGIN clone SONAME 'CLONE_PLUGIN';
2 CREATE TABLE t1(col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB);
3 CREATE TABLE t2(col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB)
4 PARTITION BY KEY(col1) PARTITIONS 5;
5 CREATE PROCEDURE execute_dml(
6 p_dml_type INT,
7 p_key_min INT,
8 p_key_range INT,
9 p_loop_count INT,
10 p_frequency INT,
11 p_is_rand INT)
12 BEGIN
13 DECLARE v_idx INT DEFAULT 0;
14 DECLARE v_commit INT DEFAULT 0;
15 DECLARE v_key INT DEFAULT 0;
16 /* Loop and INSERT data at random position */
17 WHILE(v_idx < p_loop_count) DO
18 /* Generate key between 1 to p_loop_count */
19 IF p_is_rand = 1 THEN
20 SET v_key = p_key_min + FLOOR(RAND() * p_key_range);
21 ELSE
22 SET v_key = p_key_min + (v_idx % p_key_range);
23 END IF;
24 CASE p_dml_type
25 WHEN 0 THEN
26 SET @clol3_text = CONCAT('Clone Test Row - ', v_key);
27 INSERT INTO t1 (col1, col2, col3, col4) VALUES (
28 v_key, v_key * 10,
29 @clol3_text, REPEAT('Large Column Data ', 2048))
30 ON DUPLICATE KEY UPDATE col2 = col2 + 1;
31 INSERT INTO t2 (col1, col2, col3, col4) VALUES (
32 v_key, v_key * 10,
33 @clol3_text, REPEAT('Large Column Data ', 2048))
34 ON DUPLICATE KEY UPDATE col2 = col2 + 1;
35 WHEN 1 THEN
36 UPDATE t1 SET col2 = v_idx + 1 WHERE col1 = v_key;
37 UPDATE t2 SET col2 = v_idx + 1 WHERE col1 = v_key;
38 WHEN 2 THEN
39 DELETE FROM t1 WHERE col1 = v_key;
40 DELETE FROM t2 WHERE col1 = v_key;
41 ELSE
42 DELETE FROM t1;
43 DELETE FROM t2;
44 END CASE;
45 SET v_idx = v_idx + 1;
46 /* Commit or rollback work at specified frequency. */
47 IF v_idx % p_frequency = 0 THEN
48 SET v_commit = FLOOR(RAND() * 2);
49 IF v_commit = 0 AND p_is_rand = 1 THEN
50 ROLLBACK;
51 START TRANSACTION;
52 ELSE
53 COMMIT;
54 START TRANSACTION;
55 END IF;
56 END IF;
57 END WHILE;
58 COMMIT;
59 END|
60 call execute_dml(0, 0, 100, 100, 10, 0);
61 CREATE TABLE t SELECT * FROM t1;
62 DROP table t1;
63 DROP table t2;
64 SHOW VARIABLES LIKE 'clone_ddl_timeout';
65 Variable_name Value
66 clone_ddl_timeout 300
67 SHOW VARIABLES LIKE 'clone_block_ddl';
68 Variable_name Value
69 clone_block_ddl OFF
70 # 1. CREATE TABLE while clone in progress
71 RESET MASTER;
72 SET GLOBAL DEBUG = '+d,local_release_clone_file_pin';
73 CREATE TABLE t0 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB) PARTITION BY KEY(col1) PARTITIONS 5;
74 SET DEBUG_SYNC = 'clone_before_init_meta SIGNAL start_ddl_file_init WAIT_FOR resume_file_init';
75 SET DEBUG_SYNC = 'clone_file_copy SIGNAL start_ddl_file WAIT_FOR resume_file';
76 SET DEBUG_SYNC = 'clone_file_copy_end_before_ack SIGNAL start_ddl_file_ack WAIT_FOR resume_file_ack';
77 SET DEBUG_SYNC = 'clone_before_file_ddl_meta SIGNAL start_ddl_file_meta WAIT_FOR resume_file_meta';
78 SET DEBUG_SYNC = 'clone_page_copy SIGNAL start_ddl_page WAIT_FOR resume_page';
79 SET DEBUG_SYNC = 'clone_before_page_ddl_meta SIGNAL start_ddl_page_meta WAIT_FOR resume_page_meta';
80 SET DEBUG_SYNC = 'clone_redo_copy SIGNAL start_ddl_redo WAIT_FOR resume_redo';
81 SET GLOBAL clone_autotune_concurrency = OFF;
82 SET GLOBAL clone_max_concurrency = 8;
83 CLONE LOCAL DATA DIRECTORY = 'CLONE_DATADIR';
84 # In connection CON1
85 # Waiting for clone to reach 'Initial metadata transfer'
86 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_file_init';
87 # Wait finished
88 CREATE TABLE t01 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB) PARTITION BY KEY(col1) PARTITIONS 5;
89 SET DEBUG_SYNC = 'now SIGNAL resume_file_init';
90 # Waiting for clone to reach 'file copy'
91 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_file';
92 # Wait finished
93 CREATE TABLE t1 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB) PARTITION BY KEY(col1) PARTITIONS 5;
94 INSERT INTO t1 SELECT * FROM t;
95 # Flush all dirty pages to track
96 SET GLOBAL innodb_buf_flush_list_now = 1;
97 SET DEBUG_SYNC = 'now SIGNAL resume_file';
98 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_file_ack';
99 SET DEBUG_SYNC = 'now SIGNAL resume_file_ack';
100 # Waiting for clone to reach DDL metadata for 'file copy'
101 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_file_meta';
102 # Wait finished
103 CREATE TABLE t11 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB) PARTITION BY KEY(col1) PARTITIONS 5;
104 INSERT INTO t11 SELECT * FROM t;
105 SET DEBUG_SYNC = 'now SIGNAL resume_file_meta';
106 # Waiting for clone to reach 'page copy'
107 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_page';
108 # Wait finished
109 CREATE TABLE t2 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB) PARTITION BY KEY(col1) PARTITIONS 5;
110 CALL execute_dml(0, 0, 100, 100, 10, 0);
111 SET DEBUG_SYNC = 'now SIGNAL resume_page';
112 # Waiting for clone to reach DDL metadata for 'page copy'
113 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_page_meta';
114 # Wait finished
115 CREATE TABLE t21 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB) PARTITION BY KEY(col1) PARTITIONS 5;
116 INSERT INTO t21 SELECT * FROM t;
117 SET DEBUG_SYNC = 'now SIGNAL resume_page_meta';
118 # Waiting for clone to reach 'redo copy'
119 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_redo';
120 # Wait finished
121 CREATE TABLE t3 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB) PARTITION BY KEY(col1) PARTITIONS 5;
122 SET DEBUG_SYNC = 'now SIGNAL resume_redo';
123 # In connection DEFAULT
124 # Waiting for clone to complete
125 # Wait finished
126 SET GLOBAL DEBUG = '-d,local_release_clone_file_pin';
127 # Restart server on cloned data directory
128 # restart: --datadir=CLONE_DATADIR
129 # Check cloned data
130 SHOW TABLES;
131 Tables_in_test
132 t
133 t0
134 t01
135 t1
136 t11
137 t2
138 SHOW CREATE TABLE t1;
139 Table Create Table
140 t1 CREATE TABLE `t1` (
141 `col1` int NOT NULL,
142 `col2` int DEFAULT NULL,
143 `col3` varchar(64) DEFAULT NULL,
144 `col4` blob,
145 PRIMARY KEY (`col1`)
146 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
147 /*!50100 PARTITION BY KEY (col1)
148 PARTITIONS 5 */
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 /*!50100 PARTITION BY KEY (col1)
189 PARTITIONS 5 */
190 CHECK TABLE t11 EXTENDED;
191 Table Op Msg_type Msg_text
192 test.t11 check status OK
193 SELECT count(*) from t11;
194 count(*)
195 100
196 SELECT col1, col2, col3 FROM t11 ORDER BY col1 LIMIT 10;;
197 col1 col2 col3
198 0 0 Clone Test Row - 0
199 1 10 Clone Test Row - 1
200 2 20 Clone Test Row - 2
201 3 30 Clone Test Row - 3
202 4 40 Clone Test Row - 4
203 5 50 Clone Test Row - 5
204 6 60 Clone Test Row - 6
205 7 70 Clone Test Row - 7
206 8 80 Clone Test Row - 8
207 9 90 Clone Test Row - 9
208 SELECT col1, col2, col3 FROM t11 ORDER BY col1 DESC LIMIT 10;;
209 col1 col2 col3
210 99 990 Clone Test Row - 99
211 98 980 Clone Test Row - 98
212 97 970 Clone Test Row - 97
213 96 960 Clone Test Row - 96
214 95 950 Clone Test Row - 95
215 94 940 Clone Test Row - 94
216 93 930 Clone Test Row - 93
217 92 920 Clone Test Row - 92
218 91 910 Clone Test Row - 91
219 90 900 Clone Test Row - 90
220 SHOW CREATE TABLE t2;
221 Table Create Table
222 t2 CREATE TABLE `t2` (
223 `col1` int NOT NULL,
224 `col2` int DEFAULT NULL,
225 `col3` varchar(64) DEFAULT NULL,
226 `col4` blob,
227 PRIMARY KEY (`col1`)
228 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
229 /*!50100 PARTITION BY KEY (col1)
230 PARTITIONS 5 */
231 CHECK TABLE t2 EXTENDED;
232 Table Op Msg_type Msg_text
233 test.t2 check status OK
234 SELECT count(*) from t2;
235 count(*)
236 100
237 SELECT col1, col2, col3 FROM t2 ORDER BY col1 LIMIT 10;;
238 col1 col2 col3
239 0 0 Clone Test Row - 0
240 1 10 Clone Test Row - 1
241 2 20 Clone Test Row - 2
242 3 30 Clone Test Row - 3
243 4 40 Clone Test Row - 4
244 5 50 Clone Test Row - 5
245 6 60 Clone Test Row - 6
246 7 70 Clone Test Row - 7
247 8 80 Clone Test Row - 8
248 9 90 Clone Test Row - 9
249 SELECT col1, col2, col3 FROM t2 ORDER BY col1 DESC LIMIT 10;;
250 col1 col2 col3
251 99 990 Clone Test Row - 99
252 98 980 Clone Test Row - 98
253 97 970 Clone Test Row - 97
254 96 960 Clone Test Row - 96
255 95 950 Clone Test Row - 95
256 94 940 Clone Test Row - 94
257 93 930 Clone Test Row - 93
258 92 920 Clone Test Row - 92
259 91 910 Clone Test Row - 91
260 90 900 Clone Test Row - 90
261 CALL execute_dml(1, 0, 100, 100, 10, 0);
262 # Restart server back on base data directory
263 # restart:
264 SET DEBUG_SYNC = 'RESET';
265 # 2. DROP TABLE while clone in progress
266 RESET MASTER;
267 SET GLOBAL DEBUG = '+d,local_release_clone_file_pin';
268 DROP TABLE t0 ;
269 SET DEBUG_SYNC = 'clone_before_init_meta SIGNAL start_ddl_file_init WAIT_FOR resume_file_init';
270 SET DEBUG_SYNC = 'clone_file_copy SIGNAL start_ddl_file WAIT_FOR resume_file';
271 SET DEBUG_SYNC = 'clone_file_copy_end_before_ack SIGNAL start_ddl_file_ack WAIT_FOR resume_file_ack';
272 SET DEBUG_SYNC = 'clone_before_file_ddl_meta SIGNAL start_ddl_file_meta WAIT_FOR resume_file_meta';
273 SET DEBUG_SYNC = 'clone_page_copy SIGNAL start_ddl_page WAIT_FOR resume_page';
274 SET DEBUG_SYNC = 'clone_before_page_ddl_meta SIGNAL start_ddl_page_meta WAIT_FOR resume_page_meta';
275 SET DEBUG_SYNC = 'clone_redo_copy SIGNAL start_ddl_redo WAIT_FOR resume_redo';
276 SET GLOBAL clone_autotune_concurrency = OFF;
277 SET GLOBAL clone_max_concurrency = 8;
278 CLONE LOCAL DATA DIRECTORY = 'CLONE_DATADIR';
279 # In connection CON1
280 # Waiting for clone to reach 'Initial metadata transfer'
281 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_file_init';
282 # Wait finished
283 DROP TABLE t01 ;
284 SET DEBUG_SYNC = 'now SIGNAL resume_file_init';
285 # Waiting for clone to reach 'file copy'
286 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_file';
287 # Wait finished
288 DROP TABLE t1 ;
289 SHOW TABLES;
290 Tables_in_test
291 t
292 t11
293 t2
294 t21
295 t3
296 # Flush all dirty pages to track
297 SET GLOBAL innodb_buf_flush_list_now = 1;
298 SET DEBUG_SYNC = 'now SIGNAL resume_file';
299 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_file_ack';
300 SET DEBUG_SYNC = 'now SIGNAL resume_file_ack';
301 # Waiting for clone to reach DDL metadata for 'file copy'
302 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_file_meta';
303 # Wait finished
304 DROP TABLE t11 ;
305 SHOW TABLES;
306 Tables_in_test
307 t
308 t2
309 t21
310 t3
311 SET DEBUG_SYNC = 'now SIGNAL resume_file_meta';
312 # Waiting for clone to reach 'page copy'
313 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_page';
314 # Wait finished
315 DROP TABLE t2 ;
316 SHOW TABLES;
317 Tables_in_test
318 t
319 t21
320 t3
321 SET DEBUG_SYNC = 'now SIGNAL resume_page';
322 # Waiting for clone to reach DDL metadata for 'page copy'
323 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_page_meta';
324 # Wait finished
325 DROP TABLE t21 ;
326 SHOW TABLES;
327 Tables_in_test
328 t
329 t3
330 SET DEBUG_SYNC = 'now SIGNAL resume_page_meta';
331 # Waiting for clone to reach 'redo copy'
332 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_redo';
333 # Wait finished
334 DROP TABLE t3 ;
335 SET DEBUG_SYNC = 'now SIGNAL resume_redo';
336 # In connection DEFAULT
337 # Waiting for clone to complete
338 # Wait finished
339 SET GLOBAL DEBUG = '-d,local_release_clone_file_pin';
340 # Restart server on cloned data directory
341 # restart: --datadir=CLONE_DATADIR
342 # Check cloned data
343 SHOW TABLES;
344 Tables_in_test
345 t
346 t21
347 t3
348 # Restart server back on base data directory
349 # restart:
350 SET DEBUG_SYNC = 'RESET';
351 # Cleanup
352 DROP TABLE t;
353 CREATE TABLE t1 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB);
354 CREATE TABLE t2 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB);
355 DROP TABLE t1;
356 DROP TABLE t2;
357 DROP PROCEDURE execute_dml;
358 # Uninstall clone
359 UNINSTALL PLUGIN clone;