Debian Codegraph @

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

raw
1 Install clone plugin on recipient server
2 INSTALL PLUGIN clone SONAME 'CLONE_PLUGIN';
3 CREATE TABLE t_recipient(col1 INT PRIMARY KEY, col2 int);
4 INSERT INTO t_recipient VALUES(1, 10);
5 CREATE UNDO TABLESPACE undo_005 ADD DATAFILE 'undo_005.ibu';
6 CREATE TABLESPACE tbs1_recipient ADD DATAFILE 'tbs1_data1.ibd';
7 CREATE UNDO TABLESPACE undo_003 ADD DATAFILE 'undo_003.ibu';
8 CREATE UNDO TABLESPACE undo_004 ADD DATAFILE 'undo_004.ibu';
9 SELECT NAME, SPACE_TYPE, STATE
10 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
11 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
12 NAME SPACE_TYPE STATE
13 innodb_undo_001 Undo active
14 innodb_undo_002 Undo active
15 undo_003 Undo active
16 undo_004 Undo active
17 SELECT TABLESPACE_NAME, FILE_NAME, FILE_TYPE, ENGINE
18 FROM INFORMATION_SCHEMA.FILES
19 WHERE FILE_NAME LIKE '%undo_00%'
20 ORDER BY TABLESPACE_NAME;
21 TABLESPACE_NAME FILE_NAME FILE_TYPE ENGINE
22 innodb_undo_001 ./undo_001 UNDO LOG InnoDB
23 innodb_undo_002 ./undo_002 UNDO LOG InnoDB
24 undo_003 ./undo_003.ibu UNDO LOG InnoDB
25 undo_004 ./undo_004.ibu UNDO LOG InnoDB
26 INSTALL PLUGIN clone SONAME 'CLONE_PLUGIN';
27 SHOW VARIABLES LIKE "clone_buffer_size";
28 Variable_name Value
29 clone_buffer_size 4194304
30 SET GLOBAL clone_buffer_size = 2097152;
31 SHOW VARIABLES LIKE "clone_buffer_size";
32 Variable_name Value
33 clone_buffer_size 2097152
34 CREATE TABLE t1(col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB);
35 CREATE TABLE t2(col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB)
36 PARTITION BY KEY(col1) PARTITIONS 5;
37 CREATE PROCEDURE execute_dml(
38 p_dml_type INT,
39 p_key_min INT,
40 p_key_range INT,
41 p_loop_count INT,
42 p_frequency INT,
43 p_is_rand INT)
44 BEGIN
45 DECLARE v_idx INT DEFAULT 0;
46 DECLARE v_commit INT DEFAULT 0;
47 DECLARE v_key INT DEFAULT 0;
48 /* Loop and INSERT data at random position */
49 WHILE(v_idx < p_loop_count) DO
50 /* Generate key between 1 to p_loop_count */
51 IF p_is_rand = 1 THEN
52 SET v_key = p_key_min + FLOOR(RAND() * p_key_range);
53 ELSE
54 SET v_key = p_key_min + (v_idx % p_key_range);
55 END IF;
56 CASE p_dml_type
57 WHEN 0 THEN
58 SET @clol3_text = CONCAT('Clone Test Row - ', v_key);
59 INSERT INTO t1 (col1, col2, col3, col4) VALUES (
60 v_key, v_key * 10,
61 @clol3_text, REPEAT('Large Column Data ', 2048))
62 ON DUPLICATE KEY UPDATE col2 = col2 + 1;
63 INSERT INTO t2 (col1, col2, col3, col4) VALUES (
64 v_key, v_key * 10,
65 @clol3_text, REPEAT('Large Column Data ', 2048))
66 ON DUPLICATE KEY UPDATE col2 = col2 + 1;
67 WHEN 1 THEN
68 UPDATE t1 SET col2 = v_idx + 1 WHERE col1 = v_key;
69 UPDATE t2 SET col2 = v_idx + 1 WHERE col1 = v_key;
70 WHEN 2 THEN
71 DELETE FROM t1 WHERE col1 = v_key;
72 DELETE FROM t2 WHERE col1 = v_key;
73 ELSE
74 DELETE FROM t1;
75 DELETE FROM t2;
76 END CASE;
77 SET v_idx = v_idx + 1;
78 /* Commit or rollback work at specified frequency. */
79 IF v_idx % p_frequency = 0 THEN
80 SET v_commit = FLOOR(RAND() * 2);
81 IF v_commit = 0 AND p_is_rand = 1 THEN
82 ROLLBACK;
83 START TRANSACTION;
84 ELSE
85 COMMIT;
86 START TRANSACTION;
87 END IF;
88 END IF;
89 END WHILE;
90 COMMIT;
91 END|
92 call execute_dml(0, 0, 100, 100, 10, 0);
93 SHOW CREATE TABLE t1;
94 Table Create Table
95 t1 CREATE TABLE `t1` (
96 `col1` int NOT NULL,
97 `col2` int DEFAULT NULL,
98 `col3` varchar(64) DEFAULT NULL,
99 `col4` blob,
100 PRIMARY KEY (`col1`)
101 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
102 SELECT count(*) from t1;
103 count(*)
104 100
105 SELECT col1, col2, col3 FROM t1 ORDER BY col1 LIMIT 10;
106 col1 col2 col3
107 0 0 Clone Test Row - 0
108 1 10 Clone Test Row - 1
109 2 20 Clone Test Row - 2
110 3 30 Clone Test Row - 3
111 4 40 Clone Test Row - 4
112 5 50 Clone Test Row - 5
113 6 60 Clone Test Row - 6
114 7 70 Clone Test Row - 7
115 8 80 Clone Test Row - 8
116 9 90 Clone Test Row - 9
117 SELECT col1, col2, col3 FROM t1 ORDER BY col1 DESC LIMIT 10;
118 col1 col2 col3
119 99 990 Clone Test Row - 99
120 98 980 Clone Test Row - 98
121 97 970 Clone Test Row - 97
122 96 960 Clone Test Row - 96
123 95 950 Clone Test Row - 95
124 94 940 Clone Test Row - 94
125 93 930 Clone Test Row - 93
126 92 920 Clone Test Row - 92
127 91 910 Clone Test Row - 91
128 90 900 Clone Test Row - 90
129 SHOW CREATE TABLE t2;
130 Table Create Table
131 t2 CREATE TABLE `t2` (
132 `col1` int NOT NULL,
133 `col2` int DEFAULT NULL,
134 `col3` varchar(64) DEFAULT NULL,
135 `col4` blob,
136 PRIMARY KEY (`col1`)
137 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
138 /*!50100 PARTITION BY KEY (col1)
139 PARTITIONS 5 */
140 SELECT count(*) from t2;
141 count(*)
142 100
143 SELECT col1, col2, col3 FROM t2 ORDER BY col1 LIMIT 10;
144 col1 col2 col3
145 0 0 Clone Test Row - 0
146 1 10 Clone Test Row - 1
147 2 20 Clone Test Row - 2
148 3 30 Clone Test Row - 3
149 4 40 Clone Test Row - 4
150 5 50 Clone Test Row - 5
151 6 60 Clone Test Row - 6
152 7 70 Clone Test Row - 7
153 8 80 Clone Test Row - 8
154 9 90 Clone Test Row - 9
155 SELECT col1, col2, col3 FROM t2 ORDER BY col1 DESC LIMIT 10;
156 col1 col2 col3
157 99 990 Clone Test Row - 99
158 98 980 Clone Test Row - 98
159 97 970 Clone Test Row - 97
160 96 960 Clone Test Row - 96
161 95 950 Clone Test Row - 95
162 94 940 Clone Test Row - 94
163 93 930 Clone Test Row - 93
164 92 920 Clone Test Row - 92
165 91 910 Clone Test Row - 91
166 90 900 Clone Test Row - 90
167 # In connection con1 - Running Insert Random [100 - 200 Key range]
168 call execute_dml(0, 100, 100, 100, 20, 1);
169 # In connection con2 - Running Update Random [0 - 25 Key Range]
170 call execute_dml(1, 0, 25, 100, 20, 1);
171 # In connection con3 - Running Delete Random [26 - 50 Key Range]
172 call execute_dml(2, 26, 25, 100, 20, 1);
173 # In connection con4 - Running Update Random uncommited [51 - 75 Key Range]
174 begin;
175 call execute_dml(1, 51, 25, 100, 200, 1);
176 # In connection default - Cloning database
177 SET GLOBAL clone_autotune_concurrency = OFF;
178 SET GLOBAL clone_max_concurrency = 8;
179 SET GLOBAL clone_valid_donor_list = 'HOST:PORT';
180 CLONE INSTANCE FROM USER@HOST:PORT IDENTIFIED BY '' ;
181 select ID, STATE, ERROR_NO from performance_schema.clone_status;
182 ID STATE ERROR_NO
183 1 Completed 0
184 select ID, STAGE, STATE from performance_schema.clone_progress;
185 ID STAGE STATE
186 1 DROP DATA Completed
187 1 FILE COPY Completed
188 1 PAGE COPY Completed
189 1 REDO COPY Completed
190 1 FILE SYNC Completed
191 1 RESTART Completed
192 1 RECOVERY Completed
193 # In connection default - Finished Cloning
194 show variables like 'clone_autotune_concurrency';
195 Variable_name Value
196 clone_autotune_concurrency ON
197 # In connection con1 - Waiting
198 # In connection con2 - Waiting
199 # In connection con3 - Waiting
200 # In connection con4 - Waiting
201 commit;
202 # In connection default
203 select STATE, ERROR_NO, length(BINLOG_FILE) > 0, length(GTID_EXECUTED) > 0,
204 (BINLOG_POSITION > 0) from performance_schema.clone_status;
205 STATE ERROR_NO length(BINLOG_FILE) > 0 length(GTID_EXECUTED) > 0 (BINLOG_POSITION > 0)
206 Completed 0 1 0 1
207 call execute_dml(3, 0, 1, 1, 1, 0);
208 call execute_dml(0, 0, 100, 100, 10, 0);
209 SHOW CREATE TABLE t1;
210 Table Create Table
211 t1 CREATE TABLE `t1` (
212 `col1` int NOT NULL,
213 `col2` int DEFAULT NULL,
214 `col3` varchar(64) DEFAULT NULL,
215 `col4` blob,
216 PRIMARY KEY (`col1`)
217 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
218 SELECT count(*) from t1;
219 count(*)
220 100
221 SELECT col1, col2, col3 FROM t1 ORDER BY col1 LIMIT 10;
222 col1 col2 col3
223 0 0 Clone Test Row - 0
224 1 10 Clone Test Row - 1
225 2 20 Clone Test Row - 2
226 3 30 Clone Test Row - 3
227 4 40 Clone Test Row - 4
228 5 50 Clone Test Row - 5
229 6 60 Clone Test Row - 6
230 7 70 Clone Test Row - 7
231 8 80 Clone Test Row - 8
232 9 90 Clone Test Row - 9
233 SELECT col1, col2, col3 FROM t1 ORDER BY col1 DESC LIMIT 10;
234 col1 col2 col3
235 99 990 Clone Test Row - 99
236 98 980 Clone Test Row - 98
237 97 970 Clone Test Row - 97
238 96 960 Clone Test Row - 96
239 95 950 Clone Test Row - 95
240 94 940 Clone Test Row - 94
241 93 930 Clone Test Row - 93
242 92 920 Clone Test Row - 92
243 91 910 Clone Test Row - 91
244 90 900 Clone Test Row - 90
245 SHOW CREATE TABLE t2;
246 Table Create Table
247 t2 CREATE TABLE `t2` (
248 `col1` int NOT NULL,
249 `col2` int DEFAULT NULL,
250 `col3` varchar(64) DEFAULT NULL,
251 `col4` blob,
252 PRIMARY KEY (`col1`)
253 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
254 /*!50100 PARTITION BY KEY (col1)
255 PARTITIONS 5 */
256 SELECT count(*) from t2;
257 count(*)
258 100
259 SELECT col1, col2, col3 FROM t2 ORDER BY col1 LIMIT 10;
260 col1 col2 col3
261 0 0 Clone Test Row - 0
262 1 10 Clone Test Row - 1
263 2 20 Clone Test Row - 2
264 3 30 Clone Test Row - 3
265 4 40 Clone Test Row - 4
266 5 50 Clone Test Row - 5
267 6 60 Clone Test Row - 6
268 7 70 Clone Test Row - 7
269 8 80 Clone Test Row - 8
270 9 90 Clone Test Row - 9
271 SELECT col1, col2, col3 FROM t2 ORDER BY col1 DESC LIMIT 10;
272 col1 col2 col3
273 99 990 Clone Test Row - 99
274 98 980 Clone Test Row - 98
275 97 970 Clone Test Row - 97
276 96 960 Clone Test Row - 96
277 95 950 Clone Test Row - 95
278 94 940 Clone Test Row - 94
279 93 930 Clone Test Row - 93
280 92 920 Clone Test Row - 92
281 91 910 Clone Test Row - 91
282 90 900 Clone Test Row - 90
283 SELECT NAME, SPACE_TYPE, STATE
284 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
285 WHERE SPACE_TYPE = 'Undo';
286 NAME SPACE_TYPE STATE
287 innodb_undo_001 Undo active
288 innodb_undo_002 Undo active
289 undo_003 Undo active
290 undo_004 Undo active
291 SELECT TABLESPACE_NAME, FILE_NAME, FILE_TYPE, ENGINE
292 FROM INFORMATION_SCHEMA.FILES
293 WHERE FILE_NAME LIKE '%undo_00%';
294 TABLESPACE_NAME FILE_NAME FILE_TYPE ENGINE
295 innodb_undo_001 ./undo_001 UNDO LOG InnoDB
296 innodb_undo_002 ./undo_002 UNDO LOG InnoDB
297 undo_003 ./undo_003.ibu UNDO LOG InnoDB
298 undo_004 ./undo_004.ibu UNDO LOG InnoDB
299 SET GLOBAL clone_autotune_concurrency = OFF;
300 SET GLOBAL clone_max_concurrency = 8;
301 SET GLOBAL clone_valid_donor_list = 'HOST:PORT';
302 CLONE INSTANCE FROM USER@HOST:PORT IDENTIFIED BY '' ;
303 select ID, STATE, ERROR_NO from performance_schema.clone_status;
304 ID STATE ERROR_NO
305 1 Completed 0
306 select ID, STAGE, STATE from performance_schema.clone_progress;
307 ID STAGE STATE
308 1 DROP DATA Completed
309 1 FILE COPY Completed
310 1 PAGE COPY Completed
311 1 REDO COPY Completed
312 1 FILE SYNC Completed
313 1 RESTART Completed
314 1 RECOVERY Completed
315 select STATE, ERROR_NO, length(BINLOG_FILE) > 0, length(GTID_EXECUTED) > 0,
316 (BINLOG_POSITION > 0) from performance_schema.clone_status;
317 STATE ERROR_NO length(BINLOG_FILE) > 0 length(GTID_EXECUTED) > 0 (BINLOG_POSITION > 0)
318 Completed 0 1 0 1
319 call execute_dml(3, 0, 1, 1, 1, 0);
320 call execute_dml(0, 0, 100, 100, 10, 0);
321 SHOW CREATE TABLE t1;
322 Table Create Table
323 t1 CREATE TABLE `t1` (
324 `col1` int NOT NULL,
325 `col2` int DEFAULT NULL,
326 `col3` varchar(64) DEFAULT NULL,
327 `col4` blob,
328 PRIMARY KEY (`col1`)
329 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
330 SELECT count(*) from t1;
331 count(*)
332 100
333 SELECT col1, col2, col3 FROM t1 ORDER BY col1 LIMIT 10;
334 col1 col2 col3
335 0 0 Clone Test Row - 0
336 1 10 Clone Test Row - 1
337 2 20 Clone Test Row - 2
338 3 30 Clone Test Row - 3
339 4 40 Clone Test Row - 4
340 5 50 Clone Test Row - 5
341 6 60 Clone Test Row - 6
342 7 70 Clone Test Row - 7
343 8 80 Clone Test Row - 8
344 9 90 Clone Test Row - 9
345 SELECT col1, col2, col3 FROM t1 ORDER BY col1 DESC LIMIT 10;
346 col1 col2 col3
347 99 990 Clone Test Row - 99
348 98 980 Clone Test Row - 98
349 97 970 Clone Test Row - 97
350 96 960 Clone Test Row - 96
351 95 950 Clone Test Row - 95
352 94 940 Clone Test Row - 94
353 93 930 Clone Test Row - 93
354 92 920 Clone Test Row - 92
355 91 910 Clone Test Row - 91
356 90 900 Clone Test Row - 90
357 SHOW CREATE TABLE t2;
358 Table Create Table
359 t2 CREATE TABLE `t2` (
360 `col1` int NOT NULL,
361 `col2` int DEFAULT NULL,
362 `col3` varchar(64) DEFAULT NULL,
363 `col4` blob,
364 PRIMARY KEY (`col1`)
365 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
366 /*!50100 PARTITION BY KEY (col1)
367 PARTITIONS 5 */
368 SELECT count(*) from t2;
369 count(*)
370 100
371 SELECT col1, col2, col3 FROM t2 ORDER BY col1 LIMIT 10;
372 col1 col2 col3
373 0 0 Clone Test Row - 0
374 1 10 Clone Test Row - 1
375 2 20 Clone Test Row - 2
376 3 30 Clone Test Row - 3
377 4 40 Clone Test Row - 4
378 5 50 Clone Test Row - 5
379 6 60 Clone Test Row - 6
380 7 70 Clone Test Row - 7
381 8 80 Clone Test Row - 8
382 9 90 Clone Test Row - 9
383 SELECT col1, col2, col3 FROM t2 ORDER BY col1 DESC LIMIT 10;
384 col1 col2 col3
385 99 990 Clone Test Row - 99
386 98 980 Clone Test Row - 98
387 97 970 Clone Test Row - 97
388 96 960 Clone Test Row - 96
389 95 950 Clone Test Row - 95
390 94 940 Clone Test Row - 94
391 93 930 Clone Test Row - 93
392 92 920 Clone Test Row - 92
393 91 910 Clone Test Row - 91
394 90 900 Clone Test Row - 90
395 ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
396 DROP UNDO TABLESPACE undo_003;
397 ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
398 DROP UNDO TABLESPACE undo_004;
399 SELECT NAME, SPACE_TYPE, STATE
400 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
401 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
402 NAME SPACE_TYPE STATE
403 innodb_undo_001 Undo active
404 innodb_undo_002 Undo active
405 SELECT TABLESPACE_NAME, FILE_NAME, FILE_TYPE, ENGINE
406 FROM INFORMATION_SCHEMA.FILES
407 WHERE FILE_NAME LIKE '%undo_00%'
408 ORDER BY TABLESPACE_NAME;
409 TABLESPACE_NAME FILE_NAME FILE_TYPE ENGINE
410 innodb_undo_001 ./undo_001 UNDO LOG InnoDB
411 innodb_undo_002 ./undo_002 UNDO LOG InnoDB
412 DROP TABLE t1;
413 DROP TABLE t2;
414 DROP PROCEDURE execute_dml;
415 SET GLOBAL clone_autotune_concurrency = OFF;
416 SET GLOBAL clone_max_concurrency = 8;
417 SET GLOBAL clone_valid_donor_list = 'HOST:PORT';
418 CLONE INSTANCE FROM USER@HOST:PORT IDENTIFIED BY '' ;
419 select ID, STATE, ERROR_NO from performance_schema.clone_status;
420 ID STATE ERROR_NO
421 1 Completed 0
422 select ID, STAGE, STATE from performance_schema.clone_progress;
423 ID STAGE STATE
424 1 DROP DATA Completed
425 1 FILE COPY Completed
426 1 PAGE COPY Completed
427 1 REDO COPY Completed
428 1 FILE SYNC Completed
429 1 RESTART Completed
430 1 RECOVERY Completed
431 Uninstall clone plugin on recipient server
432 UNINSTALL PLUGIN clone;
433 UNINSTALL PLUGIN clone;