Debian Codegraph @

mysql-test/suite/clone/r/ddl_mix.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 CREATE TABLE t0 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB);
63 CREATE TABLE t01 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB);
64 CREATE TABLE t3 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB);
65 CREATE TABLE t11 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB);
66 CREATE TABLE t21 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB);
67 INSERT INTO t11 SELECT * FROM t;
68 INSERT INTO t21 SELECT * FROM t;
69 SHOW VARIABLES LIKE 'clone_ddl_timeout';
70 Variable_name Value
71 clone_ddl_timeout 300
72 SHOW VARIABLES LIKE 'clone_block_ddl';
73 Variable_name Value
74 clone_block_ddl OFF
75 # 1. RENAME and CREATE a TABLE with same name while clone in progress
76 RESET MASTER;
77 SET GLOBAL DEBUG = '+d,local_release_clone_file_pin';
78 ALTER TABLE t0 RENAME t0_new;
79 CREATE TABLE t0 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB) ;
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 RENAME t01_new;
95 CREATE TABLE t01 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB) ;
96 SET DEBUG_SYNC = 'now SIGNAL resume_file_init';
97 # Waiting for clone to reach 'file copy'
98 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_file';
99 # Wait finished
100 ALTER TABLE t1 RENAME t1_new;
101 CREATE TABLE t1 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB) ;
102 CALL execute_dml(0, 0, 100, 100, 10, 0);
103 # Flush all dirty pages to track
104 SET GLOBAL innodb_buf_flush_list_now = 1;
105 SET DEBUG_SYNC = 'now SIGNAL resume_file';
106 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_file_ack';
107 SET DEBUG_SYNC = 'now SIGNAL resume_file_ack';
108 # Waiting for clone to reach DDL metadata for 'file copy'
109 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_file_meta';
110 # Wait finished
111 ALTER TABLE t11 RENAME t11_new;
112 CREATE TABLE t11 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB) ;
113 INSERT INTO t11 SELECT * FROM t;
114 SET DEBUG_SYNC = 'now SIGNAL resume_file_meta';
115 # Waiting for clone to reach 'page copy'
116 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_page';
117 # Wait finished
118 ALTER TABLE t2 RENAME t2_new;
119 CREATE TABLE t2 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB) ;
120 CALL execute_dml(0, 0, 100, 100, 10, 0);
121 SET DEBUG_SYNC = 'now SIGNAL resume_page';
122 # Waiting for clone to reach DDL metadata for 'page copy'
123 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_page_meta';
124 # Wait finished
125 ALTER TABLE t21 RENAME t21_new;
126 CREATE TABLE t21 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB) ;
127 INSERT INTO t21 SELECT * FROM t;
128 SET DEBUG_SYNC = 'now SIGNAL resume_page_meta';
129 # Waiting for clone to reach 'redo copy'
130 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_redo';
131 # Wait finished
132 ALTER TABLE t3 RENAME t3_new;
133 CREATE TABLE t3 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB) ;
134 SET DEBUG_SYNC = 'now SIGNAL resume_redo';
135 # In connection DEFAULT
136 # Waiting for clone to complete
137 # Wait finished
138 SET GLOBAL DEBUG = '-d,local_release_clone_file_pin';
139 # Restart server on cloned data directory
140 # restart: --datadir=CLONE_DATADIR
141 # Check cloned data
142 SHOW TABLES;
143 Tables_in_test
144 t
145 t0
146 t01
147 t01_new
148 t0_new
149 t1
150 t11
151 t11_new
152 t1_new
153 t2
154 t21
155 t2_new
156 t3
157 SHOW CREATE TABLE t1_new;
158 Table Create Table
159 t1_new CREATE TABLE `t1_new` (
160 `col1` int NOT NULL,
161 `col2` int DEFAULT NULL,
162 `col3` varchar(64) DEFAULT NULL,
163 `col4` blob,
164 PRIMARY KEY (`col1`)
165 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
166 CHECK TABLE t1_new EXTENDED;
167 Table Op Msg_type Msg_text
168 test.t1_new check status OK
169 SELECT count(*) from t1_new;
170 count(*)
171 100
172 SELECT col1, col2, col3 FROM t1_new ORDER BY col1 LIMIT 10;;
173 col1 col2 col3
174 0 0 Clone Test Row - 0
175 1 10 Clone Test Row - 1
176 2 20 Clone Test Row - 2
177 3 30 Clone Test Row - 3
178 4 40 Clone Test Row - 4
179 5 50 Clone Test Row - 5
180 6 60 Clone Test Row - 6
181 7 70 Clone Test Row - 7
182 8 80 Clone Test Row - 8
183 9 90 Clone Test Row - 9
184 SELECT col1, col2, col3 FROM t1_new ORDER BY col1 DESC LIMIT 10;;
185 col1 col2 col3
186 99 990 Clone Test Row - 99
187 98 980 Clone Test Row - 98
188 97 970 Clone Test Row - 97
189 96 960 Clone Test Row - 96
190 95 950 Clone Test Row - 95
191 94 940 Clone Test Row - 94
192 93 930 Clone Test Row - 93
193 92 920 Clone Test Row - 92
194 91 910 Clone Test Row - 91
195 90 900 Clone Test Row - 90
196 SHOW CREATE TABLE t11_new;
197 Table Create Table
198 t11_new CREATE TABLE `t11_new` (
199 `col1` int NOT NULL,
200 `col2` int DEFAULT NULL,
201 `col3` varchar(64) DEFAULT NULL,
202 `col4` blob,
203 PRIMARY KEY (`col1`)
204 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
205 CHECK TABLE t11_new EXTENDED;
206 Table Op Msg_type Msg_text
207 test.t11_new check status OK
208 SELECT count(*) from t11_new;
209 count(*)
210 100
211 SELECT col1, col2, col3 FROM t11_new ORDER BY col1 LIMIT 10;;
212 col1 col2 col3
213 0 0 Clone Test Row - 0
214 1 10 Clone Test Row - 1
215 2 20 Clone Test Row - 2
216 3 30 Clone Test Row - 3
217 4 40 Clone Test Row - 4
218 5 50 Clone Test Row - 5
219 6 60 Clone Test Row - 6
220 7 70 Clone Test Row - 7
221 8 80 Clone Test Row - 8
222 9 90 Clone Test Row - 9
223 SELECT col1, col2, col3 FROM t11_new ORDER BY col1 DESC LIMIT 10;;
224 col1 col2 col3
225 99 990 Clone Test Row - 99
226 98 980 Clone Test Row - 98
227 97 970 Clone Test Row - 97
228 96 960 Clone Test Row - 96
229 95 950 Clone Test Row - 95
230 94 940 Clone Test Row - 94
231 93 930 Clone Test Row - 93
232 92 920 Clone Test Row - 92
233 91 910 Clone Test Row - 91
234 90 900 Clone Test Row - 90
235 SHOW CREATE TABLE t2_new;
236 Table Create Table
237 t2_new CREATE TABLE `t2_new` (
238 `col1` int NOT NULL,
239 `col2` int DEFAULT NULL,
240 `col3` varchar(64) DEFAULT NULL,
241 `col4` blob,
242 PRIMARY KEY (`col1`)
243 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
244 /*!50100 PARTITION BY KEY (col1)
245 PARTITIONS 5 */
246 CHECK TABLE t2_new EXTENDED;
247 Table Op Msg_type Msg_text
248 test.t2_new check status OK
249 SELECT count(*) from t2_new;
250 count(*)
251 100
252 SELECT col1, col2, col3 FROM t2_new ORDER BY col1 LIMIT 10;;
253 col1 col2 col3
254 0 1 Clone Test Row - 0
255 1 11 Clone Test Row - 1
256 2 21 Clone Test Row - 2
257 3 31 Clone Test Row - 3
258 4 41 Clone Test Row - 4
259 5 51 Clone Test Row - 5
260 6 61 Clone Test Row - 6
261 7 71 Clone Test Row - 7
262 8 81 Clone Test Row - 8
263 9 91 Clone Test Row - 9
264 SELECT col1, col2, col3 FROM t2_new ORDER BY col1 DESC LIMIT 10;;
265 col1 col2 col3
266 99 991 Clone Test Row - 99
267 98 981 Clone Test Row - 98
268 97 971 Clone Test Row - 97
269 96 961 Clone Test Row - 96
270 95 951 Clone Test Row - 95
271 94 941 Clone Test Row - 94
272 93 931 Clone Test Row - 93
273 92 921 Clone Test Row - 92
274 91 911 Clone Test Row - 91
275 90 901 Clone Test Row - 90
276 CALL execute_dml(1, 0, 100, 100, 10, 0);
277 # Restart server back on base data directory
278 # restart:
279 SET DEBUG_SYNC = 'RESET';
280 # Cleanup
281 DROP TABLE t0;
282 DROP TABLE t01;
283 DROP TABLE t1;
284 DROP TABLE t2;
285 DROP TABLE t3;
286 DROP TABLE t11;
287 DROP TABLE t21;
288 DROP TABLE t0_new;
289 DROP TABLE t01_new;
290 DROP TABLE t1_new;
291 DROP TABLE t2_new;
292 DROP TABLE t3_new;
293 DROP TABLE t11_new;
294 DROP TABLE t21_new;
295 # 2. CREATE & DROP TABLE while clone in progress
296 RESET MASTER;
297 SET GLOBAL DEBUG = '+d,local_release_clone_file_pin';
298 CREATE TABLE t0 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB) PARTITION BY KEY(col1) PARTITIONS 5;
299 DROP TABLE t0 ;
300 SET DEBUG_SYNC = 'clone_before_init_meta SIGNAL start_ddl_file_init WAIT_FOR resume_file_init';
301 SET DEBUG_SYNC = 'clone_file_copy SIGNAL start_ddl_file WAIT_FOR resume_file';
302 SET DEBUG_SYNC = 'clone_file_copy_end_before_ack SIGNAL start_ddl_file_ack WAIT_FOR resume_file_ack';
303 SET DEBUG_SYNC = 'clone_before_file_ddl_meta SIGNAL start_ddl_file_meta WAIT_FOR resume_file_meta';
304 SET DEBUG_SYNC = 'clone_page_copy SIGNAL start_ddl_page WAIT_FOR resume_page';
305 SET DEBUG_SYNC = 'clone_before_page_ddl_meta SIGNAL start_ddl_page_meta WAIT_FOR resume_page_meta';
306 SET DEBUG_SYNC = 'clone_redo_copy SIGNAL start_ddl_redo WAIT_FOR resume_redo';
307 SET GLOBAL clone_autotune_concurrency = OFF;
308 SET GLOBAL clone_max_concurrency = 8;
309 CLONE LOCAL DATA DIRECTORY = 'CLONE_DATADIR';
310 # In connection CON1
311 # Waiting for clone to reach 'Initial metadata transfer'
312 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_file_init';
313 # Wait finished
314 CREATE TABLE t01 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB) PARTITION BY KEY(col1) PARTITIONS 5;
315 DROP TABLE t01 ;
316 SET DEBUG_SYNC = 'now SIGNAL resume_file_init';
317 # Waiting for clone to reach 'file copy'
318 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_file';
319 # Wait finished
320 CREATE TABLE t1 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB) PARTITION BY KEY(col1) PARTITIONS 5;
321 INSERT INTO t1 SELECT * FROM t;
322 # Flush all dirty pages to track
323 SET GLOBAL innodb_buf_flush_list_now = 1;
324 DROP TABLE t1 ;
325 SET DEBUG_SYNC = 'now SIGNAL resume_file';
326 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_file_ack';
327 SET DEBUG_SYNC = 'now SIGNAL resume_file_ack';
328 # Waiting for clone to reach DDL metadata for 'file copy'
329 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_file_meta';
330 # Wait finished
331 CREATE TABLE t11 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB) PARTITION BY KEY(col1) PARTITIONS 5;
332 INSERT INTO t11 SELECT * FROM t;
333 DROP TABLE t11 ;
334 SET DEBUG_SYNC = 'now SIGNAL resume_file_meta';
335 # Waiting for clone to reach 'page copy'
336 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_page';
337 # Wait finished
338 CREATE TABLE t2 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB) PARTITION BY KEY(col1) PARTITIONS 5;
339 INSERT INTO t2 SELECT * FROM t;
340 DROP TABLE t2 ;
341 SET DEBUG_SYNC = 'now SIGNAL resume_page';
342 # Waiting for clone to reach DDL metadata for 'page copy'
343 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_page_meta';
344 # Wait finished
345 CREATE TABLE t21 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB) PARTITION BY KEY(col1) PARTITIONS 5;
346 INSERT INTO t21 SELECT * FROM t;
347 DROP TABLE t21 ;
348 SET DEBUG_SYNC = 'now SIGNAL resume_page_meta';
349 # Waiting for clone to reach 'redo copy'
350 SET DEBUG_SYNC = 'now WAIT_FOR start_ddl_redo';
351 # Wait finished
352 CREATE TABLE t3 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB) PARTITION BY KEY(col1) PARTITIONS 5;
353 DROP TABLE t3 ;
354 SET DEBUG_SYNC = 'now SIGNAL resume_redo';
355 # In connection DEFAULT
356 # Waiting for clone to complete
357 # Wait finished
358 SET GLOBAL DEBUG = '-d,local_release_clone_file_pin';
359 # Restart server on cloned data directory
360 # restart: --datadir=CLONE_DATADIR
361 # Check cloned data
362 SHOW TABLES;
363 Tables_in_test
364 t
365 # Restart server back on base data directory
366 # restart:
367 SET DEBUG_SYNC = 'RESET';
368 # Cleanup
369 DROP TABLE t;
370 CREATE TABLE t1 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB);
371 CREATE TABLE t2 (col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB);
372 DROP TABLE t1;
373 DROP TABLE t2;
374 DROP PROCEDURE execute_dml;
375 # Uninstall clone
376 UNINSTALL PLUGIN clone;