Debian Codegraph @

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

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