Debian Codegraph @

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