Debian Codegraph @

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

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