Debian Codegraph @

mysql-test/suite/clone/r/ddl_dml.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 CREATE PROCEDURE execute_ddl(
87 p_loop_count INT)
88 BEGIN
89 DECLARE v_idx INT DEFAULT 0;
90 /* Loop and execute DDL. */
91 /* Concurrent DDL and DML creates MDL deadlock. Disabled till fixed. */
92 WHILE(v_idx < p_loop_count) DO
93 ALTER TABLE t1 ENGINE = InnoDB;
94 ALTER TABLE t2 ENGINE = InnoDB;
95 DO SLEEP(0.1);
96 SET v_idx = v_idx + 1;
97 END WHILE;
98 END|
99 call execute_dml(0, 0, 200, 200, 10, 0);
100 SHOW CREATE TABLE t1;
101 Table Create Table
102 t1 CREATE TABLE `t1` (
103 `col1` int NOT NULL,
104 `col2` int DEFAULT NULL,
105 `col3` varchar(64) DEFAULT NULL,
106 `col4` blob,
107 PRIMARY KEY (`col1`)
108 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
109 SELECT count(*) from t1;
110 count(*)
111 200
112 SELECT col1, col2, col3 FROM t1 ORDER BY col1 LIMIT 10;
113 col1 col2 col3
114 0 0 Clone Test Row - 0
115 1 10 Clone Test Row - 1
116 2 20 Clone Test Row - 2
117 3 30 Clone Test Row - 3
118 4 40 Clone Test Row - 4
119 5 50 Clone Test Row - 5
120 6 60 Clone Test Row - 6
121 7 70 Clone Test Row - 7
122 8 80 Clone Test Row - 8
123 9 90 Clone Test Row - 9
124 SELECT col1, col2, col3 FROM t1 ORDER BY col1 DESC LIMIT 10;
125 col1 col2 col3
126 199 1990 Clone Test Row - 199
127 198 1980 Clone Test Row - 198
128 197 1970 Clone Test Row - 197
129 196 1960 Clone Test Row - 196
130 195 1950 Clone Test Row - 195
131 194 1940 Clone Test Row - 194
132 193 1930 Clone Test Row - 193
133 192 1920 Clone Test Row - 192
134 191 1910 Clone Test Row - 191
135 190 1900 Clone Test Row - 190
136 SHOW CREATE TABLE t2;
137 Table Create Table
138 t2 CREATE TABLE `t2` (
139 `col1` int NOT NULL,
140 `col2` int DEFAULT NULL,
141 `col3` varchar(64) DEFAULT NULL,
142 `col4` blob,
143 PRIMARY KEY (`col1`)
144 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
145 /*!50100 PARTITION BY KEY (col1)
146 PARTITIONS 5 */
147 SELECT count(*) from t2;
148 count(*)
149 200
150 SELECT col1, col2, col3 FROM t2 ORDER BY col1 LIMIT 10;
151 col1 col2 col3
152 0 0 Clone Test Row - 0
153 1 10 Clone Test Row - 1
154 2 20 Clone Test Row - 2
155 3 30 Clone Test Row - 3
156 4 40 Clone Test Row - 4
157 5 50 Clone Test Row - 5
158 6 60 Clone Test Row - 6
159 7 70 Clone Test Row - 7
160 8 80 Clone Test Row - 8
161 9 90 Clone Test Row - 9
162 SELECT col1, col2, col3 FROM t2 ORDER BY col1 DESC LIMIT 10;
163 col1 col2 col3
164 199 1990 Clone Test Row - 199
165 198 1980 Clone Test Row - 198
166 197 1970 Clone Test Row - 197
167 196 1960 Clone Test Row - 196
168 195 1950 Clone Test Row - 195
169 194 1940 Clone Test Row - 194
170 193 1930 Clone Test Row - 193
171 192 1920 Clone Test Row - 192
172 191 1910 Clone Test Row - 191
173 190 1900 Clone Test Row - 190
174 # In connection con1 - Running Insert Random [100 - 200 Key range]
175 call execute_dml(0, 100, 100, 100, 20, 1);
176 # In connection con2 - Running Update Random [0 - 25 Key Range]
177 call execute_dml(1, 0, 25, 100, 20, 1);
178 # In connection con3 - Running Delete Random [26 - 50 Key Range]
179 call execute_dml(2, 26, 25, 100, 20, 1);
180 # In connection con4 - Running Update Random uncommited [51 - 75 Key Range]
181 call execute_ddl(10);
182 # In connection default - Cloning database
183 SET GLOBAL clone_autotune_concurrency = OFF;
184 SET GLOBAL clone_max_concurrency = 8;
185 SET GLOBAL clone_max_data_bandwidth = 5;
186 SET GLOBAL clone_max_network_bandwidth = 4;
187 CLONE LOCAL DATA DIRECTORY = 'CLONE_DATADIR';
188 select ID, STATE, ERROR_NO from performance_schema.clone_status;
189 ID STATE ERROR_NO
190 1 Completed 0
191 select ID, STAGE, STATE from performance_schema.clone_progress;
192 ID STAGE STATE
193 1 DROP DATA Completed
194 1 FILE COPY Completed
195 1 PAGE COPY Completed
196 1 REDO COPY Completed
197 1 FILE SYNC Completed
198 1 RESTART Not Started
199 1 RECOVERY Not Started
200 # In connection default - Finished Cloning
201 show variables like 'clone_autotune_concurrency';
202 Variable_name Value
203 clone_autotune_concurrency OFF
204 # In connection con1 - Waiting
205 # In connection con2 - Waiting
206 # In connection con3 - Waiting
207 # In connection con4 - Waiting
208 commit;
209 # In connection default
210 # Restart cloned database
211 # restart: --datadir=CLONE_DATADIR
212 select STATE, ERROR_NO, length(BINLOG_FILE) > 0, length(GTID_EXECUTED) > 0,
213 (BINLOG_POSITION > 0) from performance_schema.clone_status;
214 STATE ERROR_NO length(BINLOG_FILE) > 0 length(GTID_EXECUTED) > 0 (BINLOG_POSITION > 0)
215 Completed 0 1 0 1
216 call execute_dml(3, 0, 1, 1, 1, 0);
217 call execute_dml(0, 0, 200, 200, 10, 0);
218 SHOW CREATE TABLE t1;
219 Table Create Table
220 t1 CREATE TABLE `t1` (
221 `col1` int NOT NULL,
222 `col2` int DEFAULT NULL,
223 `col3` varchar(64) DEFAULT NULL,
224 `col4` blob,
225 PRIMARY KEY (`col1`)
226 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
227 SELECT count(*) from t1;
228 count(*)
229 200
230 SELECT col1, col2, col3 FROM t1 ORDER BY col1 LIMIT 10;
231 col1 col2 col3
232 0 0 Clone Test Row - 0
233 1 10 Clone Test Row - 1
234 2 20 Clone Test Row - 2
235 3 30 Clone Test Row - 3
236 4 40 Clone Test Row - 4
237 5 50 Clone Test Row - 5
238 6 60 Clone Test Row - 6
239 7 70 Clone Test Row - 7
240 8 80 Clone Test Row - 8
241 9 90 Clone Test Row - 9
242 SELECT col1, col2, col3 FROM t1 ORDER BY col1 DESC LIMIT 10;
243 col1 col2 col3
244 199 1990 Clone Test Row - 199
245 198 1980 Clone Test Row - 198
246 197 1970 Clone Test Row - 197
247 196 1960 Clone Test Row - 196
248 195 1950 Clone Test Row - 195
249 194 1940 Clone Test Row - 194
250 193 1930 Clone Test Row - 193
251 192 1920 Clone Test Row - 192
252 191 1910 Clone Test Row - 191
253 190 1900 Clone Test Row - 190
254 SHOW CREATE TABLE t2;
255 Table Create Table
256 t2 CREATE TABLE `t2` (
257 `col1` int NOT NULL,
258 `col2` int DEFAULT NULL,
259 `col3` varchar(64) DEFAULT NULL,
260 `col4` blob,
261 PRIMARY KEY (`col1`)
262 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
263 /*!50100 PARTITION BY KEY (col1)
264 PARTITIONS 5 */
265 SELECT count(*) from t2;
266 count(*)
267 200
268 SELECT col1, col2, col3 FROM t2 ORDER BY col1 LIMIT 10;
269 col1 col2 col3
270 0 0 Clone Test Row - 0
271 1 10 Clone Test Row - 1
272 2 20 Clone Test Row - 2
273 3 30 Clone Test Row - 3
274 4 40 Clone Test Row - 4
275 5 50 Clone Test Row - 5
276 6 60 Clone Test Row - 6
277 7 70 Clone Test Row - 7
278 8 80 Clone Test Row - 8
279 9 90 Clone Test Row - 9
280 SELECT col1, col2, col3 FROM t2 ORDER BY col1 DESC LIMIT 10;
281 col1 col2 col3
282 199 1990 Clone Test Row - 199
283 198 1980 Clone Test Row - 198
284 197 1970 Clone Test Row - 197
285 196 1960 Clone Test Row - 196
286 195 1950 Clone Test Row - 195
287 194 1940 Clone Test Row - 194
288 193 1930 Clone Test Row - 193
289 192 1920 Clone Test Row - 192
290 191 1910 Clone Test Row - 191
291 190 1900 Clone Test Row - 190
292 SELECT NAME, SPACE_TYPE, STATE
293 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
294 WHERE SPACE_TYPE = 'Undo';
295 NAME SPACE_TYPE STATE
296 innodb_undo_001 Undo active
297 innodb_undo_002 Undo active
298 undo_003 Undo active
299 undo_004 Undo active
300 SELECT TABLESPACE_NAME, FILE_NAME, FILE_TYPE, ENGINE
301 FROM INFORMATION_SCHEMA.FILES
302 WHERE FILE_NAME LIKE '%undo_00%';
303 TABLESPACE_NAME FILE_NAME FILE_TYPE ENGINE
304 innodb_undo_001 ./undo_001 UNDO LOG InnoDB
305 innodb_undo_002 ./undo_002 UNDO LOG InnoDB
306 undo_003 ./undo_003.ibu UNDO LOG InnoDB
307 undo_004 ./undo_004.ibu UNDO LOG InnoDB
308 SET GLOBAL clone_autotune_concurrency = OFF;
309 SET GLOBAL clone_max_concurrency = 8;
310 SET GLOBAL clone_max_data_bandwidth = 5;
311 SET GLOBAL clone_max_network_bandwidth = 4;
312 CLONE LOCAL DATA DIRECTORY = 'CLONE_DATADIR';
313 select ID, STATE, ERROR_NO from performance_schema.clone_status;
314 ID STATE ERROR_NO
315 1 Completed 0
316 select ID, STAGE, STATE from performance_schema.clone_progress;
317 ID STAGE STATE
318 1 DROP DATA Completed
319 1 FILE COPY Completed
320 1 PAGE COPY Completed
321 1 REDO COPY Completed
322 1 FILE SYNC Completed
323 1 RESTART Not Started
324 1 RECOVERY Not Started
325 # Restart cloned database
326 # restart: --datadir=CLONE_DATADIR1
327 select STATE, ERROR_NO, length(BINLOG_FILE) > 0, length(GTID_EXECUTED) > 0,
328 (BINLOG_POSITION > 0) from performance_schema.clone_status;
329 STATE ERROR_NO length(BINLOG_FILE) > 0 length(GTID_EXECUTED) > 0 (BINLOG_POSITION > 0)
330 Completed 0 1 0 1
331 call execute_dml(3, 0, 1, 1, 1, 0);
332 call execute_dml(0, 0, 100, 100, 10, 0);
333 SHOW CREATE TABLE t1;
334 Table Create Table
335 t1 CREATE TABLE `t1` (
336 `col1` int NOT NULL,
337 `col2` int DEFAULT NULL,
338 `col3` varchar(64) DEFAULT NULL,
339 `col4` blob,
340 PRIMARY KEY (`col1`)
341 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
342 SELECT count(*) from t1;
343 count(*)
344 100
345 SELECT col1, col2, col3 FROM t1 ORDER BY col1 LIMIT 10;
346 col1 col2 col3
347 0 0 Clone Test Row - 0
348 1 10 Clone Test Row - 1
349 2 20 Clone Test Row - 2
350 3 30 Clone Test Row - 3
351 4 40 Clone Test Row - 4
352 5 50 Clone Test Row - 5
353 6 60 Clone Test Row - 6
354 7 70 Clone Test Row - 7
355 8 80 Clone Test Row - 8
356 9 90 Clone Test Row - 9
357 SELECT col1, col2, col3 FROM t1 ORDER BY col1 DESC LIMIT 10;
358 col1 col2 col3
359 99 990 Clone Test Row - 99
360 98 980 Clone Test Row - 98
361 97 970 Clone Test Row - 97
362 96 960 Clone Test Row - 96
363 95 950 Clone Test Row - 95
364 94 940 Clone Test Row - 94
365 93 930 Clone Test Row - 93
366 92 920 Clone Test Row - 92
367 91 910 Clone Test Row - 91
368 90 900 Clone Test Row - 90
369 SHOW CREATE TABLE t2;
370 Table Create Table
371 t2 CREATE TABLE `t2` (
372 `col1` int NOT NULL,
373 `col2` int DEFAULT NULL,
374 `col3` varchar(64) DEFAULT NULL,
375 `col4` blob,
376 PRIMARY KEY (`col1`)
377 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
378 /*!50100 PARTITION BY KEY (col1)
379 PARTITIONS 5 */
380 SELECT count(*) from t2;
381 count(*)
382 100
383 SELECT col1, col2, col3 FROM t2 ORDER BY col1 LIMIT 10;
384 col1 col2 col3
385 0 0 Clone Test Row - 0
386 1 10 Clone Test Row - 1
387 2 20 Clone Test Row - 2
388 3 30 Clone Test Row - 3
389 4 40 Clone Test Row - 4
390 5 50 Clone Test Row - 5
391 6 60 Clone Test Row - 6
392 7 70 Clone Test Row - 7
393 8 80 Clone Test Row - 8
394 9 90 Clone Test Row - 9
395 SELECT col1, col2, col3 FROM t2 ORDER BY col1 DESC LIMIT 10;
396 col1 col2 col3
397 99 990 Clone Test Row - 99
398 98 980 Clone Test Row - 98
399 97 970 Clone Test Row - 97
400 96 960 Clone Test Row - 96
401 95 950 Clone Test Row - 95
402 94 940 Clone Test Row - 94
403 93 930 Clone Test Row - 93
404 92 920 Clone Test Row - 92
405 91 910 Clone Test Row - 91
406 90 900 Clone Test Row - 90
407 # restart:
408 ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
409 DROP UNDO TABLESPACE undo_003;
410 ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
411 DROP UNDO TABLESPACE undo_004;
412 SELECT NAME, SPACE_TYPE, STATE
413 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
414 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
415 NAME SPACE_TYPE STATE
416 innodb_undo_001 Undo active
417 innodb_undo_002 Undo active
418 SELECT TABLESPACE_NAME, FILE_NAME, FILE_TYPE, ENGINE
419 FROM INFORMATION_SCHEMA.FILES
420 WHERE FILE_NAME LIKE '%undo_00%'
421 ORDER BY TABLESPACE_NAME;
422 TABLESPACE_NAME FILE_NAME FILE_TYPE ENGINE
423 innodb_undo_001 ./undo_001 UNDO LOG InnoDB
424 innodb_undo_002 ./undo_002 UNDO LOG InnoDB
425 DROP TABLE t1;
426 DROP TABLE t2;
427 DROP PROCEDURE execute_dml;
428 DROP PROCEDURE execute_ddl;
429 UNINSTALL PLUGIN clone;