Debian Codegraph @

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