Debian Codegraph @

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