Debian Codegraph @

mysql-test/suite/clone/r/remote_dml_compressed_row.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 DROP TABLE t1;
87 DROP TABLE t2;
88 CREATE TABLE t1(col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB)
89 ROW_FORMAT = COMPRESSED;
90 CREATE TABLE t2(col1 INT PRIMARY KEY, col2 int, col3 varchar(64), col4 BLOB)
91 ROW_FORMAT = COMPRESSED;
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 ROW_FORMAT=COMPRESSED
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 ROW_FORMAT=COMPRESSED
138 SELECT count(*) from t2;
139 count(*)
140 100
141 SELECT col1, col2, col3 FROM t2 ORDER BY col1 LIMIT 10;
142 col1 col2 col3
143 0 0 Clone Test Row - 0
144 1 10 Clone Test Row - 1
145 2 20 Clone Test Row - 2
146 3 30 Clone Test Row - 3
147 4 40 Clone Test Row - 4
148 5 50 Clone Test Row - 5
149 6 60 Clone Test Row - 6
150 7 70 Clone Test Row - 7
151 8 80 Clone Test Row - 8
152 9 90 Clone Test Row - 9
153 SELECT col1, col2, col3 FROM t2 ORDER BY col1 DESC LIMIT 10;
154 col1 col2 col3
155 99 990 Clone Test Row - 99
156 98 980 Clone Test Row - 98
157 97 970 Clone Test Row - 97
158 96 960 Clone Test Row - 96
159 95 950 Clone Test Row - 95
160 94 940 Clone Test Row - 94
161 93 930 Clone Test Row - 93
162 92 920 Clone Test Row - 92
163 91 910 Clone Test Row - 91
164 90 900 Clone Test Row - 90
165 # In connection con1 - Running Insert Random [100 - 200 Key range]
166 call execute_dml(0, 100, 100, 100, 20, 1);
167 # In connection con2 - Running Update Random [0 - 25 Key Range]
168 call execute_dml(1, 0, 25, 100, 20, 1);
169 # In connection con3 - Running Delete Random [26 - 50 Key Range]
170 call execute_dml(2, 26, 25, 100, 20, 1);
171 # In connection con4 - Running Update Random uncommited [51 - 75 Key Range]
172 begin;
173 call execute_dml(1, 51, 25, 100, 200, 1);
174 # In connection default - Cloning database
175 SET GLOBAL clone_autotune_concurrency = OFF;
176 SET GLOBAL clone_max_concurrency = 8;
177 SET GLOBAL clone_valid_donor_list = 'HOST:PORT';
178 CLONE INSTANCE FROM USER@HOST:PORT IDENTIFIED BY '' DATA DIRECTORY = 'CLONE_DATADIR';
179 select ID, STATE, ERROR_NO from performance_schema.clone_status;
180 ID STATE ERROR_NO
181 1 Completed 0
182 select ID, STAGE, STATE from performance_schema.clone_progress;
183 ID STAGE STATE
184 1 DROP DATA Completed
185 1 FILE COPY Completed
186 1 PAGE COPY Completed
187 1 REDO COPY Completed
188 1 FILE SYNC Completed
189 1 RESTART Not Started
190 1 RECOVERY Not Started
191 # In connection default - Finished Cloning
192 show variables like 'clone_autotune_concurrency';
193 Variable_name Value
194 clone_autotune_concurrency OFF
195 # In connection con1 - Waiting
196 # In connection con2 - Waiting
197 # In connection con3 - Waiting
198 # In connection con4 - Waiting
199 commit;
200 # In connection default
201 # Restart cloned database
202 # restart: --datadir=CLONE_DATADIR
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 ROW_FORMAT=COMPRESSED
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 ROW_FORMAT=COMPRESSED
254 SELECT count(*) from t2;
255 count(*)
256 100
257 SELECT col1, col2, col3 FROM t2 ORDER BY col1 LIMIT 10;
258 col1 col2 col3
259 0 0 Clone Test Row - 0
260 1 10 Clone Test Row - 1
261 2 20 Clone Test Row - 2
262 3 30 Clone Test Row - 3
263 4 40 Clone Test Row - 4
264 5 50 Clone Test Row - 5
265 6 60 Clone Test Row - 6
266 7 70 Clone Test Row - 7
267 8 80 Clone Test Row - 8
268 9 90 Clone Test Row - 9
269 SELECT col1, col2, col3 FROM t2 ORDER BY col1 DESC LIMIT 10;
270 col1 col2 col3
271 99 990 Clone Test Row - 99
272 98 980 Clone Test Row - 98
273 97 970 Clone Test Row - 97
274 96 960 Clone Test Row - 96
275 95 950 Clone Test Row - 95
276 94 940 Clone Test Row - 94
277 93 930 Clone Test Row - 93
278 92 920 Clone Test Row - 92
279 91 910 Clone Test Row - 91
280 90 900 Clone Test Row - 90
281 SELECT NAME, SPACE_TYPE, STATE
282 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
283 WHERE SPACE_TYPE = 'Undo';
284 NAME SPACE_TYPE STATE
285 innodb_undo_001 Undo active
286 innodb_undo_002 Undo active
287 undo_003 Undo active
288 undo_004 Undo active
289 SELECT TABLESPACE_NAME, FILE_NAME, FILE_TYPE, ENGINE
290 FROM INFORMATION_SCHEMA.FILES
291 WHERE FILE_NAME LIKE '%undo_00%';
292 TABLESPACE_NAME FILE_NAME FILE_TYPE ENGINE
293 innodb_undo_001 ./undo_001 UNDO LOG InnoDB
294 innodb_undo_002 ./undo_002 UNDO LOG InnoDB
295 undo_003 ./undo_003.ibu UNDO LOG InnoDB
296 undo_004 ./undo_004.ibu UNDO LOG InnoDB
297 SET GLOBAL clone_autotune_concurrency = OFF;
298 SET GLOBAL clone_max_concurrency = 8;
299 SET GLOBAL clone_valid_donor_list = 'HOST:PORT';
300 CLONE INSTANCE FROM USER@HOST:PORT IDENTIFIED BY '' DATA DIRECTORY = 'CLONE_DATADIR';
301 select ID, STATE, ERROR_NO from performance_schema.clone_status;
302 ID STATE ERROR_NO
303 1 Completed 0
304 select ID, STAGE, STATE from performance_schema.clone_progress;
305 ID STAGE STATE
306 1 DROP DATA Completed
307 1 FILE COPY Completed
308 1 PAGE COPY Completed
309 1 REDO COPY Completed
310 1 FILE SYNC Completed
311 1 RESTART Not Started
312 1 RECOVERY Not Started
313 # Restart cloned database
314 # restart: --datadir=CLONE_DATADIR1
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 ROW_FORMAT=COMPRESSED
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 ROW_FORMAT=COMPRESSED
366 SELECT count(*) from t2;
367 count(*)
368 100
369 SELECT col1, col2, col3 FROM t2 ORDER BY col1 LIMIT 10;
370 col1 col2 col3
371 0 0 Clone Test Row - 0
372 1 10 Clone Test Row - 1
373 2 20 Clone Test Row - 2
374 3 30 Clone Test Row - 3
375 4 40 Clone Test Row - 4
376 5 50 Clone Test Row - 5
377 6 60 Clone Test Row - 6
378 7 70 Clone Test Row - 7
379 8 80 Clone Test Row - 8
380 9 90 Clone Test Row - 9
381 SELECT col1, col2, col3 FROM t2 ORDER BY col1 DESC LIMIT 10;
382 col1 col2 col3
383 99 990 Clone Test Row - 99
384 98 980 Clone Test Row - 98
385 97 970 Clone Test Row - 97
386 96 960 Clone Test Row - 96
387 95 950 Clone Test Row - 95
388 94 940 Clone Test Row - 94
389 93 930 Clone Test Row - 93
390 92 920 Clone Test Row - 92
391 91 910 Clone Test Row - 91
392 90 900 Clone Test Row - 90
393 # restart:
394 ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
395 DROP UNDO TABLESPACE undo_003;
396 ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
397 DROP UNDO TABLESPACE undo_004;
398 SELECT NAME, SPACE_TYPE, STATE
399 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
400 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
401 NAME SPACE_TYPE STATE
402 innodb_undo_001 Undo active
403 innodb_undo_002 Undo active
404 SELECT TABLESPACE_NAME, FILE_NAME, FILE_TYPE, ENGINE
405 FROM INFORMATION_SCHEMA.FILES
406 WHERE FILE_NAME LIKE '%undo_00%'
407 ORDER BY TABLESPACE_NAME;
408 TABLESPACE_NAME FILE_NAME FILE_TYPE ENGINE
409 innodb_undo_001 ./undo_001 UNDO LOG InnoDB
410 innodb_undo_002 ./undo_002 UNDO LOG InnoDB
411 DROP TABLE t1;
412 DROP TABLE t2;
413 DROP PROCEDURE execute_dml;
414 UNINSTALL PLUGIN clone;