Debian Codegraph @

mysql-test/suite/innodb_undo/r/undo_settings.result

raw
1 # This test changes undo related settings both at runtime, between restarts,
2 # and also after crashes.
3 # Note that innodb_undo_tablespaces is now fixed at 2 so only
4 # innodb_rollback_segments is tested here.
5 # Also make sure that --innodb_validate_tablespace_paths=OFF does not
6 # affect the validation of undo tablespaces. (See undo_settings-master.opt)
7 SET DEFAULT_STORAGE_ENGINE=InnoDB;
8 SET GLOBAL innodb_monitor_enable='module_buffer_page';
9 SET GLOBAL innodb_monitor_enable='trx_rseg_current_size';
10 #
11 # Create tables and check the default values.
12 #
13 CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd';
14 CREATE TABLE t1 (a INT, b TEXT) TABLESPACE=`ts1`;
15 CREATE TABLE t2 (a INT, b TEXT) TABLESPACE=`innodb_system`;
16 CREATE TABLE t3 (a INT, b TEXT) TABLESPACE=`innodb_file_per_table`;
17 # Connection 1
18 INSERT INTO t1 VALUES (1, repeat('a',15));
19 INSERT INTO t2 VALUES (1, repeat('a',15));
20 INSERT INTO t3 VALUES (1, repeat('a',15));
21 SELECT * FROM t1 ORDER BY a;
22 a b
23 1 aaaaaaaaaaaaaaa
24 SELECT COUNT(*) FROM t2;
25 COUNT(*)
26 1
27 SELECT COUNT(*) FROM t3;
28 COUNT(*)
29 1
30 # Connection 2
31 SELECT * FROM t1 ORDER BY a;
32 a b
33 1 aaaaaaaaaaaaaaa
34 SELECT COUNT(*) FROM t2;
35 COUNT(*)
36 1
37 SELECT COUNT(*) FROM t3;
38 COUNT(*)
39 1
40 XA START 'x1';
41 INSERT INTO t1 VALUES (101, repeat('x1 ',5));
42 INSERT INTO t2 VALUES (101, repeat('x1 ',5));
43 INSERT INTO t3 VALUES (101, repeat('x1 ',5));
44 # Connection default
45 SHOW VARIABLES LIKE 'innodb_rollback_segments';
46 Variable_name Value
47 innodb_rollback_segments 1
48 SHOW GLOBAL STATUS LIKE '%undo%';
49 Variable_name Value
50 Innodb_undo_tablespaces_total 2
51 Innodb_undo_tablespaces_implicit 2
52 Innodb_undo_tablespaces_explicit 0
53 Innodb_undo_tablespaces_active 2
54 SELECT NAME, ROW_FORMAT, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
55 WHERE name LIKE '%undo%' ORDER BY NAME;
56 NAME ROW_FORMAT SPACE_TYPE STATE
57 innodb_undo_001 Undo Undo active
58 innodb_undo_002 Undo Undo active
59 SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
60 WHERE FILE_NAME LIKE '%undo%' ORDER BY TABLESPACE_NAME;
61 TABLESPACE_NAME FILE_TYPE FILE_NAME
62 innodb_undo_001 UNDO LOG ./undo_001
63 innodb_undo_002 UNDO LOG ./undo_002
64 # Directory listing of UNDO tablespace files
65 undo_001
66 undo_002
67 #
68 # Change number of UNDO tablespaces from 2 to 3 while online.
69 #
70 CREATE UNDO TABLESPACE `undo_003` ADD DATAFILE 'undo_003.ibu';
71 SHOW VARIABLES LIKE 'innodb_rollback_segments';
72 Variable_name Value
73 innodb_rollback_segments 1
74 SHOW GLOBAL STATUS LIKE '%undo%';
75 Variable_name Value
76 Innodb_undo_tablespaces_total 3
77 Innodb_undo_tablespaces_implicit 2
78 Innodb_undo_tablespaces_explicit 1
79 Innodb_undo_tablespaces_active 3
80 SELECT NAME, ROW_FORMAT, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
81 WHERE name LIKE '%undo%' ORDER BY NAME;
82 NAME ROW_FORMAT SPACE_TYPE STATE
83 innodb_undo_001 Undo Undo active
84 innodb_undo_002 Undo Undo active
85 undo_003 Undo Undo active
86 SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
87 WHERE FILE_NAME LIKE '%undo%' ORDER BY TABLESPACE_NAME;
88 TABLESPACE_NAME FILE_TYPE FILE_NAME
89 innodb_undo_001 UNDO LOG ./undo_001
90 innodb_undo_002 UNDO LOG ./undo_002
91 undo_003 UNDO LOG ./undo_003.ibu
92 # Directory listing of UNDO tablespace files
93 undo_001
94 undo_002
95 undo_003.ibu
96 # Connection 1
97 BEGIN;
98 INSERT INTO t1 VALUES (2, repeat('b',15));
99 INSERT INTO t2 VALUES (2, repeat('b',15));
100 INSERT INTO t3 VALUES (2, repeat('b',15));
101 # Connection 2
102 XA END 'x1';
103 XA PREPARE 'x1';
104 XA COMMIT 'x1';
105 SELECT * FROM t1 ORDER BY a;
106 a b
107 1 aaaaaaaaaaaaaaa
108 101 x1 x1 x1 x1 x1
109 SELECT COUNT(*) FROM t2;
110 COUNT(*)
111 2
112 SELECT COUNT(*) FROM t3;
113 COUNT(*)
114 2
115 XA START 'x2';
116 INSERT INTO t1 VALUES (102, repeat('x2 ',5));
117 INSERT INTO t2 VALUES (102, repeat('x2 ',5));
118 INSERT INTO t3 VALUES (102, repeat('x2 ',5));
119 XA END 'x2';
120 # Connection default
121 #
122 # Change number of UNDO tablespaces from 3 to 4 while online.
123 # Change number of Rollback Segments from 1 to 4 while online.
124 #
125 SHOW VARIABLES LIKE 'innodb_rollback_segments';
126 Variable_name Value
127 innodb_rollback_segments 1
128 SET GLOBAL innodb_rollback_segments=4;
129 SHOW VARIABLES LIKE 'innodb_rollback_segments';
130 Variable_name Value
131 innodb_rollback_segments 4
132 CREATE UNDO TABLESPACE `undo_004` ADD DATAFILE 'undo_004.ibu';
133 SHOW GLOBAL STATUS LIKE '%undo%';
134 Variable_name Value
135 Innodb_undo_tablespaces_total 4
136 Innodb_undo_tablespaces_implicit 2
137 Innodb_undo_tablespaces_explicit 2
138 Innodb_undo_tablespaces_active 4
139 SELECT NAME, ROW_FORMAT, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
140 WHERE name LIKE '%undo%' ORDER BY NAME;
141 NAME ROW_FORMAT SPACE_TYPE STATE
142 innodb_undo_001 Undo Undo active
143 innodb_undo_002 Undo Undo active
144 undo_003 Undo Undo active
145 undo_004 Undo Undo active
146 SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
147 WHERE FILE_NAME LIKE '%undo%' ORDER BY TABLESPACE_NAME;
148 TABLESPACE_NAME FILE_TYPE FILE_NAME
149 innodb_undo_001 UNDO LOG ./undo_001
150 innodb_undo_002 UNDO LOG ./undo_002
151 undo_003 UNDO LOG ./undo_003.ibu
152 undo_004 UNDO LOG ./undo_004.ibu
153 # Directory listing of UNDO tablespace files
154 undo_001
155 undo_002
156 undo_003.ibu
157 undo_004.ibu
158 SELECT * FROM t1 ORDER BY a;
159 a b
160 1 aaaaaaaaaaaaaaa
161 101 x1 x1 x1 x1 x1
162 SELECT COUNT(*) FROM t2;
163 COUNT(*)
164 2
165 SELECT COUNT(*) FROM t3;
166 COUNT(*)
167 2
168 # Connection 1
169 COMMIT;
170 SELECT * FROM t1 ORDER BY a;
171 a b
172 1 aaaaaaaaaaaaaaa
173 2 bbbbbbbbbbbbbbb
174 101 x1 x1 x1 x1 x1
175 SELECT COUNT(*) FROM t2;
176 COUNT(*)
177 3
178 SELECT COUNT(*) FROM t3;
179 COUNT(*)
180 3
181 INSERT INTO t1 VALUES (3, repeat('c',15));
182 INSERT INTO t2 VALUES (3, repeat('c',15));
183 INSERT INTO t3 VALUES (3, repeat('c',15));
184 # Connection 2
185 XA PREPARE 'x2';
186 XA COMMIT 'x2';
187 SELECT * FROM t1 ORDER BY a;
188 a b
189 1 aaaaaaaaaaaaaaa
190 2 bbbbbbbbbbbbbbb
191 3 ccccccccccccccc
192 101 x1 x1 x1 x1 x1
193 102 x2 x2 x2 x2 x2
194 SELECT COUNT(*) FROM t2;
195 COUNT(*)
196 5
197 SELECT COUNT(*) FROM t3;
198 COUNT(*)
199 5
200 XA START 'x3';
201 INSERT INTO t1 VALUES (103, repeat('x3 ',5));
202 INSERT INTO t2 VALUES (103, repeat('x3 ',5));
203 INSERT INTO t3 VALUES (103, repeat('x3 ',5));
204 XA END 'x3';
205 XA PREPARE 'x3';
206 XA COMMIT 'x3';
207 SELECT * FROM t1 ORDER BY a;
208 a b
209 1 aaaaaaaaaaaaaaa
210 2 bbbbbbbbbbbbbbb
211 3 ccccccccccccccc
212 101 x1 x1 x1 x1 x1
213 102 x2 x2 x2 x2 x2
214 103 x3 x3 x3 x3 x3
215 SELECT COUNT(*) FROM t2;
216 COUNT(*)
217 6
218 SELECT COUNT(*) FROM t3;
219 COUNT(*)
220 6
221 # Connection default
222 SELECT * FROM t1 ORDER BY a;
223 a b
224 1 aaaaaaaaaaaaaaa
225 2 bbbbbbbbbbbbbbb
226 3 ccccccccccccccc
227 101 x1 x1 x1 x1 x1
228 102 x2 x2 x2 x2 x2
229 103 x3 x3 x3 x3 x3
230 SELECT COUNT(*) FROM t2;
231 COUNT(*)
232 6
233 SELECT COUNT(*) FROM t3;
234 COUNT(*)
235 6
236 #
237 # Change number of active UNDO tablespaces from 4 to 2 while online.
238 # Change number of Rollback Segments from 4 to 2 while online.
239 #
240 SHOW VARIABLES LIKE 'innodb_rollback_segments';
241 Variable_name Value
242 innodb_rollback_segments 4
243 SET GLOBAL innodb_rollback_segments=2;
244 SHOW VARIABLES LIKE 'innodb_rollback_segments';
245 Variable_name Value
246 innodb_rollback_segments 2
247 ALTER UNDO TABLESPACE `undo_003` SET INACTIVE;
248 ALTER UNDO TABLESPACE `undo_004` SET INACTIVE;
249 SHOW GLOBAL STATUS LIKE '%undo%';
250 Variable_name Value
251 Innodb_undo_tablespaces_total 4
252 Innodb_undo_tablespaces_implicit 2
253 Innodb_undo_tablespaces_explicit 2
254 Innodb_undo_tablespaces_active 2
255 SELECT NAME, ROW_FORMAT, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
256 WHERE name LIKE '%undo%' ORDER BY NAME;
257 NAME ROW_FORMAT SPACE_TYPE STATE
258 innodb_undo_001 Undo Undo active
259 innodb_undo_002 Undo Undo active
260 undo_003 Undo Undo empty
261 undo_004 Undo Undo empty
262 SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
263 WHERE FILE_NAME LIKE '%undo%' ORDER BY TABLESPACE_NAME;
264 TABLESPACE_NAME FILE_TYPE FILE_NAME
265 innodb_undo_001 UNDO LOG ./undo_001
266 innodb_undo_002 UNDO LOG ./undo_002
267 undo_003 UNDO LOG ./undo_003.ibu
268 undo_004 UNDO LOG ./undo_004.ibu
269 # Directory listing of UNDO tablespace files
270 undo_001
271 undo_002
272 undo_003.ibu
273 undo_004.ibu
274 INSERT INTO t1 VALUES (4, repeat('d',15));
275 INSERT INTO t2 VALUES (4, repeat('d',15));
276 INSERT INTO t3 VALUES (4, repeat('d',15));
277 SELECT * FROM t1 ORDER BY a;
278 a b
279 1 aaaaaaaaaaaaaaa
280 2 bbbbbbbbbbbbbbb
281 3 ccccccccccccccc
282 4 ddddddddddddddd
283 101 x1 x1 x1 x1 x1
284 102 x2 x2 x2 x2 x2
285 103 x3 x3 x3 x3 x3
286 SELECT COUNT(*) FROM t2;
287 COUNT(*)
288 7
289 SELECT COUNT(*) FROM t3;
290 COUNT(*)
291 7
292 #
293 # Change number of UNDO tablespaces from 2 to 5 while online.
294 # Change number of Rollback Segments from 2 to 5 while online.
295 #
296 SHOW VARIABLES LIKE 'innodb_rollback_segments';
297 Variable_name Value
298 innodb_rollback_segments 2
299 SHOW GLOBAL STATUS LIKE '%undo%';
300 Variable_name Value
301 Innodb_undo_tablespaces_total 4
302 Innodb_undo_tablespaces_implicit 2
303 Innodb_undo_tablespaces_explicit 2
304 Innodb_undo_tablespaces_active 2
305 SET GLOBAL innodb_rollback_segments=5;
306 ALTER UNDO TABLESPACE `undo_003` SET ACTIVE;
307 ALTER UNDO TABLESPACE `undo_004` SET ACTIVE;
308 CREATE UNDO TABLESPACE `undo_005` ADD DATAFILE 'undo_005.ibu';
309 SHOW VARIABLES LIKE 'innodb_rollback_segments';
310 Variable_name Value
311 innodb_rollback_segments 5
312 SHOW GLOBAL STATUS LIKE '%undo%';
313 Variable_name Value
314 Innodb_undo_tablespaces_total 5
315 Innodb_undo_tablespaces_implicit 2
316 Innodb_undo_tablespaces_explicit 3
317 Innodb_undo_tablespaces_active 5
318 SELECT NAME, ROW_FORMAT, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
319 WHERE name LIKE '%undo%' ORDER BY NAME;
320 NAME ROW_FORMAT SPACE_TYPE STATE
321 innodb_undo_001 Undo Undo active
322 innodb_undo_002 Undo Undo active
323 undo_003 Undo Undo active
324 undo_004 Undo Undo active
325 undo_005 Undo Undo active
326 SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
327 WHERE FILE_NAME LIKE '%undo%' ORDER BY TABLESPACE_NAME;
328 TABLESPACE_NAME FILE_TYPE FILE_NAME
329 innodb_undo_001 UNDO LOG ./undo_001
330 innodb_undo_002 UNDO LOG ./undo_002
331 undo_003 UNDO LOG ./undo_003.ibu
332 undo_004 UNDO LOG ./undo_004.ibu
333 undo_005 UNDO LOG ./undo_005.ibu
334 # Directory listing of UNDO tablespace files
335 undo_001
336 undo_002
337 undo_003.ibu
338 undo_004.ibu
339 undo_005.ibu
340 # Connection 1
341 INSERT INTO t1 VALUES (5, repeat('e',15));
342 INSERT INTO t2 VALUES (5, repeat('e',15));
343 INSERT INTO t3 VALUES (5, repeat('e',15));
344 # Connection default
345 SELECT * FROM t1 ORDER BY a;
346 a b
347 1 aaaaaaaaaaaaaaa
348 2 bbbbbbbbbbbbbbb
349 3 ccccccccccccccc
350 4 ddddddddddddddd
351 5 eeeeeeeeeeeeeee
352 101 x1 x1 x1 x1 x1
353 102 x2 x2 x2 x2 x2
354 103 x3 x3 x3 x3 x3
355 SELECT COUNT(*) FROM t2;
356 COUNT(*)
357 8
358 SELECT COUNT(*) FROM t3;
359 COUNT(*)
360 8
361 #
362 # Shutdown mysqld cleanly.
363 # Delete one of the implicit UNDO tablespaces.
364 # Change number of Rollback Segments from 5 to 6 while offline.
365 # Restart mysqld with these larger settings.
366 #
367 # restart: --innodb_rollback_segments=6
368 SHOW VARIABLES LIKE 'innodb_rollback_segments';
369 Variable_name Value
370 innodb_rollback_segments 6
371 SHOW GLOBAL STATUS LIKE '%undo%';
372 Variable_name Value
373 Innodb_undo_tablespaces_total 5
374 Innodb_undo_tablespaces_implicit 2
375 Innodb_undo_tablespaces_explicit 3
376 Innodb_undo_tablespaces_active 5
377 SELECT NAME, ROW_FORMAT, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
378 WHERE name LIKE '%undo%' ORDER BY NAME;
379 NAME ROW_FORMAT SPACE_TYPE STATE
380 innodb_undo_001 Undo Undo active
381 innodb_undo_002 Undo Undo active
382 undo_003 Undo Undo active
383 undo_004 Undo Undo active
384 undo_005 Undo Undo active
385 SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
386 WHERE FILE_NAME LIKE '%undo%' ORDER BY TABLESPACE_NAME;
387 TABLESPACE_NAME FILE_TYPE FILE_NAME
388 innodb_undo_001 UNDO LOG ./undo_001
389 innodb_undo_002 UNDO LOG ./undo_002
390 undo_003 UNDO LOG ./undo_003.ibu
391 undo_004 UNDO LOG ./undo_004.ibu
392 undo_005 UNDO LOG ./undo_005.ibu
393 # Directory listing of UNDO tablespace files
394 undo_001
395 undo_002
396 undo_003.ibu
397 undo_004.ibu
398 undo_005.ibu
399 #
400 # Shutdown mysqld cleanly.
401 # Change number of Rollback Segments from 6 to 4 while offline.
402 # Restart mysqld with these smaller settings.
403 #
404 ALTER UNDO TABLESPACE `undo_003` SET INACTIVE;
405 # restart: --innodb_rollback_segments=4 --innodb_validate_tablespace_paths=0
406 SHOW VARIABLES LIKE 'innodb_rollback_segments';
407 Variable_name Value
408 innodb_rollback_segments 4
409 SHOW GLOBAL STATUS LIKE '%undo%';
410 Variable_name Value
411 Innodb_undo_tablespaces_total 5
412 Innodb_undo_tablespaces_implicit 2
413 Innodb_undo_tablespaces_explicit 3
414 Innodb_undo_tablespaces_active 4
415 SELECT NAME, ROW_FORMAT, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
416 WHERE name LIKE '%undo%' ORDER BY NAME;
417 NAME ROW_FORMAT SPACE_TYPE STATE
418 innodb_undo_001 Undo Undo active
419 innodb_undo_002 Undo Undo active
420 undo_003 Undo Undo empty
421 undo_004 Undo Undo active
422 undo_005 Undo Undo active
423 SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
424 WHERE FILE_NAME LIKE '%undo%' ORDER BY TABLESPACE_NAME;
425 TABLESPACE_NAME FILE_TYPE FILE_NAME
426 innodb_undo_001 UNDO LOG ./undo_001
427 innodb_undo_002 UNDO LOG ./undo_002
428 undo_003 UNDO LOG ./undo_003.ibu
429 undo_004 UNDO LOG ./undo_004.ibu
430 undo_005 UNDO LOG ./undo_005.ibu
431 # Directory listing of UNDO tablespace files
432 undo_001
433 undo_002
434 undo_003.ibu
435 undo_004.ibu
436 undo_005.ibu
437 #
438 # Kill and restart mysqld with one less active undo tablespace
439 # and less rollback segments=2.
440 #
441 ALTER UNDO TABLESPACE `undo_004` SET INACTIVE;
442 # Connection 1
443 SELECT * FROM t1 ORDER BY a;
444 a b
445 1 aaaaaaaaaaaaaaa
446 2 bbbbbbbbbbbbbbb
447 3 ccccccccccccccc
448 4 ddddddddddddddd
449 5 eeeeeeeeeeeeeee
450 101 x1 x1 x1 x1 x1
451 102 x2 x2 x2 x2 x2
452 103 x3 x3 x3 x3 x3
453 SELECT COUNT(*) FROM t2;
454 COUNT(*)
455 8
456 SELECT COUNT(*) FROM t3;
457 COUNT(*)
458 8
459 BEGIN;
460 INSERT INTO t1 VALUES (6, repeat('f',15));
461 INSERT INTO t2 VALUES (6, repeat('f',15));
462 INSERT INTO t3 VALUES (6, repeat('f',15));
463 # Connection default
464 # Kill and restart: --innodb_rollback_segments=2 --innodb_validate_tablespace_paths=0
465 SHOW VARIABLES LIKE 'innodb_rollback_segments';
466 Variable_name Value
467 innodb_rollback_segments 2
468 SHOW GLOBAL STATUS LIKE '%undo%';
469 Variable_name Value
470 Innodb_undo_tablespaces_total 5
471 Innodb_undo_tablespaces_implicit 2
472 Innodb_undo_tablespaces_explicit 3
473 Innodb_undo_tablespaces_active 3
474 SELECT NAME, ROW_FORMAT, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
475 WHERE name LIKE '%undo%' ORDER BY NAME;
476 NAME ROW_FORMAT SPACE_TYPE STATE
477 innodb_undo_001 Undo Undo active
478 innodb_undo_002 Undo Undo active
479 undo_003 Undo Undo empty
480 undo_004 Undo Undo empty
481 undo_005 Undo Undo active
482 SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
483 WHERE FILE_NAME LIKE '%undo%' ORDER BY TABLESPACE_NAME;
484 TABLESPACE_NAME FILE_TYPE FILE_NAME
485 innodb_undo_001 UNDO LOG ./undo_001
486 innodb_undo_002 UNDO LOG ./undo_002
487 undo_003 UNDO LOG ./undo_003.ibu
488 undo_004 UNDO LOG ./undo_004.ibu
489 undo_005 UNDO LOG ./undo_005.ibu
490 # Directory listing of UNDO tablespace files
491 undo_001
492 undo_002
493 undo_003.ibu
494 undo_004.ibu
495 undo_005.ibu
496 SELECT * FROM t1 ORDER BY a;
497 a b
498 1 aaaaaaaaaaaaaaa
499 2 bbbbbbbbbbbbbbb
500 3 ccccccccccccccc
501 4 ddddddddddddddd
502 5 eeeeeeeeeeeeeee
503 101 x1 x1 x1 x1 x1
504 102 x2 x2 x2 x2 x2
505 103 x3 x3 x3 x3 x3
506 SELECT COUNT(*) FROM t2;
507 COUNT(*)
508 8
509 SELECT COUNT(*) FROM t3;
510 COUNT(*)
511 8
512 #
513 # Kill and restart mysqld with more undo tablespaces=6 and more rollback segments=7.
514 #
515 # Connection 1
516 BEGIN;
517 INSERT INTO t1 VALUES (7, repeat('g',15));
518 INSERT INTO t2 VALUES (7, repeat('g',15));
519 INSERT INTO t3 VALUES (7, repeat('g',15));
520 # Connection default
521 ALTER UNDO TABLESPACE `undo_003` SET ACTIVE;
522 ALTER UNDO TABLESPACE `undo_004` SET ACTIVE;
523 CREATE UNDO TABLESPACE `undo_006` ADD DATAFILE 'undo_006.ibu';
524 SHOW WARNINGS;
525 Level Code Message
526 # Kill and restart: --innodb_rollback_segments=7
527 SHOW VARIABLES LIKE 'innodb_rollback_segments';
528 Variable_name Value
529 innodb_rollback_segments 7
530 SHOW GLOBAL STATUS LIKE '%undo%';
531 Variable_name Value
532 Innodb_undo_tablespaces_total 6
533 Innodb_undo_tablespaces_implicit 2
534 Innodb_undo_tablespaces_explicit 4
535 Innodb_undo_tablespaces_active 6
536 SELECT NAME, ROW_FORMAT, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
537 WHERE name LIKE '%undo%' ORDER BY NAME;
538 NAME ROW_FORMAT SPACE_TYPE STATE
539 innodb_undo_001 Undo Undo active
540 innodb_undo_002 Undo Undo active
541 undo_003 Undo Undo active
542 undo_004 Undo Undo active
543 undo_005 Undo Undo active
544 undo_006 Undo Undo active
545 SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
546 WHERE FILE_NAME LIKE '%undo%' ORDER BY TABLESPACE_NAME;
547 TABLESPACE_NAME FILE_TYPE FILE_NAME
548 innodb_undo_001 UNDO LOG ./undo_001
549 innodb_undo_002 UNDO LOG ./undo_002
550 undo_003 UNDO LOG ./undo_003.ibu
551 undo_004 UNDO LOG ./undo_004.ibu
552 undo_005 UNDO LOG ./undo_005.ibu
553 undo_006 UNDO LOG ./undo_006.ibu
554 # Directory listing of UNDO tablespace files
555 undo_001
556 undo_002
557 undo_003.ibu
558 undo_004.ibu
559 undo_005.ibu
560 undo_006.ibu
561 SELECT * FROM t1 ORDER BY a;
562 a b
563 1 aaaaaaaaaaaaaaa
564 2 bbbbbbbbbbbbbbb
565 3 ccccccccccccccc
566 4 ddddddddddddddd
567 5 eeeeeeeeeeeeeee
568 101 x1 x1 x1 x1 x1
569 102 x2 x2 x2 x2 x2
570 103 x3 x3 x3 x3 x3
571 SELECT COUNT(*) FROM t2;
572 COUNT(*)
573 8
574 SELECT COUNT(*) FROM t3;
575 COUNT(*)
576 8
577 #
578 # Restart mysqld in read-only mode with more rollback segments=8.
579 #
580 # Connection 1
581 INSERT INTO t1 VALUES (8, repeat('h',15));
582 INSERT INTO t2 VALUES (8, repeat('h',15));
583 INSERT INTO t3 VALUES (8, repeat('h',15));
584 # Connection default
585 SELECT * FROM t1 ORDER BY a;
586 a b
587 1 aaaaaaaaaaaaaaa
588 2 bbbbbbbbbbbbbbb
589 3 ccccccccccccccc
590 4 ddddddddddddddd
591 5 eeeeeeeeeeeeeee
592 8 hhhhhhhhhhhhhhh
593 101 x1 x1 x1 x1 x1
594 102 x2 x2 x2 x2 x2
595 103 x3 x3 x3 x3 x3
596 SELECT COUNT(*) FROM t2;
597 COUNT(*)
598 9
599 SELECT COUNT(*) FROM t3;
600 COUNT(*)
601 9
602 SET GLOBAL innodb_fast_shutdown = 0;
603 # restart: --innodb-read-only --innodb_rollback_segments=8
604 CREATE UNDO TABLESPACE `undo_007` ADD DATAFILE 'undo_007.ibu';
605 ERROR HY000: Running in read-only mode
606 SHOW VARIABLES LIKE 'innodb_rollback_segments';
607 Variable_name Value
608 innodb_rollback_segments 7
609 SHOW GLOBAL STATUS LIKE '%undo%';
610 Variable_name Value
611 Innodb_undo_tablespaces_total 6
612 Innodb_undo_tablespaces_implicit 2
613 Innodb_undo_tablespaces_explicit 4
614 Innodb_undo_tablespaces_active 6
615 SELECT NAME, ROW_FORMAT, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
616 WHERE name LIKE '%undo%' ORDER BY NAME;
617 NAME ROW_FORMAT SPACE_TYPE STATE
618 innodb_undo_001 Undo Undo active
619 innodb_undo_002 Undo Undo active
620 undo_003 Undo Undo active
621 undo_004 Undo Undo active
622 undo_005 Undo Undo active
623 undo_006 Undo Undo active
624 SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
625 WHERE FILE_NAME LIKE '%undo%' ORDER BY TABLESPACE_NAME;
626 TABLESPACE_NAME FILE_TYPE FILE_NAME
627 innodb_undo_001 UNDO LOG ./undo_001
628 innodb_undo_002 UNDO LOG ./undo_002
629 undo_003 UNDO LOG ./undo_003.ibu
630 undo_004 UNDO LOG ./undo_004.ibu
631 undo_005 UNDO LOG ./undo_005.ibu
632 undo_006 UNDO LOG ./undo_006.ibu
633 # Directory listing of UNDO tablespace files
634 undo_001
635 undo_002
636 undo_003.ibu
637 undo_004.ibu
638 undo_005.ibu
639 undo_006.ibu
640 SET GLOBAL innodb_rollback_segments=8;
641 SHOW VARIABLES LIKE 'innodb_rollback_segments';
642 Variable_name Value
643 innodb_rollback_segments 7
644 SET GLOBAL innodb_rollback_segments=3;
645 SHOW VARIABLES LIKE 'innodb_rollback_segments';
646 Variable_name Value
647 innodb_rollback_segments 7
648 SELECT * FROM t1 ORDER BY a;
649 a b
650 1 aaaaaaaaaaaaaaa
651 2 bbbbbbbbbbbbbbb
652 3 ccccccccccccccc
653 4 ddddddddddddddd
654 5 eeeeeeeeeeeeeee
655 8 hhhhhhhhhhhhhhh
656 101 x1 x1 x1 x1 x1
657 102 x2 x2 x2 x2 x2
658 103 x3 x3 x3 x3 x3
659 SELECT COUNT(*) FROM t2;
660 COUNT(*)
661 9
662 SELECT COUNT(*) FROM t3;
663 COUNT(*)
664 9
665 #
666 # Restart mysqld in force_recovery mode with more rollback segments=8.
667 #
668 # restart: --innodb_force_recovery=1 --innodb_rollback_segments=8
669 CREATE UNDO TABLESPACE `undo_007` ADD DATAFILE 'undo_007.ibu';
670 ERROR HY000: Changes to undo tablespaces are not allowed in force_recovery mode
671 SHOW GLOBAL STATUS LIKE '%undo%';
672 Variable_name Value
673 Innodb_undo_tablespaces_total 6
674 Innodb_undo_tablespaces_implicit 2
675 Innodb_undo_tablespaces_explicit 4
676 Innodb_undo_tablespaces_active 6
677 SELECT NAME, ROW_FORMAT, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
678 WHERE name LIKE '%undo%' ORDER BY NAME;
679 NAME ROW_FORMAT SPACE_TYPE STATE
680 innodb_undo_001 Undo Undo active
681 innodb_undo_002 Undo Undo active
682 undo_003 Undo Undo active
683 undo_004 Undo Undo active
684 undo_005 Undo Undo active
685 undo_006 Undo Undo active
686 SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
687 WHERE FILE_NAME LIKE '%undo%' ORDER BY TABLESPACE_NAME;
688 TABLESPACE_NAME FILE_TYPE FILE_NAME
689 innodb_undo_001 UNDO LOG ./undo_001
690 innodb_undo_002 UNDO LOG ./undo_002
691 undo_003 UNDO LOG ./undo_003.ibu
692 undo_004 UNDO LOG ./undo_004.ibu
693 undo_005 UNDO LOG ./undo_005.ibu
694 undo_006 UNDO LOG ./undo_006.ibu
695 # Directory listing of UNDO tablespace files
696 undo_001
697 undo_002
698 undo_003.ibu
699 undo_004.ibu
700 undo_005.ibu
701 undo_006.ibu
702 SHOW VARIABLES LIKE 'innodb_rollback_segments';
703 Variable_name Value
704 innodb_rollback_segments 7
705 SET GLOBAL innodb_rollback_segments=8;
706 SHOW VARIABLES LIKE 'innodb_rollback_segments';
707 Variable_name Value
708 innodb_rollback_segments 7
709 SET GLOBAL innodb_rollback_segments=3;
710 SHOW VARIABLES LIKE 'innodb_rollback_segments';
711 Variable_name Value
712 innodb_rollback_segments 7
713 SELECT * FROM t1 ORDER BY a;
714 a b
715 1 aaaaaaaaaaaaaaa
716 2 bbbbbbbbbbbbbbb
717 3 ccccccccccccccc
718 4 ddddddddddddddd
719 5 eeeeeeeeeeeeeee
720 8 hhhhhhhhhhhhhhh
721 101 x1 x1 x1 x1 x1
722 102 x2 x2 x2 x2 x2
723 103 x3 x3 x3 x3 x3
724 SELECT COUNT(*) FROM t2;
725 COUNT(*)
726 9
727 SELECT COUNT(*) FROM t3;
728 COUNT(*)
729 9
730 #
731 # Use SET PERSIST to change rollback segments=11
732 # and make some undo logs that must be rolled back on restart.
733 #
734 # restart: --innodb_rollback_segments=8
735 BEGIN;
736 INSERT INTO t1 VALUES (9, repeat('i',15));
737 INSERT INTO t2 VALUES (9, repeat('i',15));
738 INSERT INTO t3 VALUES (9, repeat('i',15));
739 SET PERSIST innodb_rollback_segments=11;
740 SHOW VARIABLES LIKE 'innodb_rollback_segments';
741 Variable_name Value
742 innodb_rollback_segments 11
743 INSERT INTO t1 VALUES (10, repeat('j',15));
744 INSERT INTO t2 VALUES (10, repeat('j',15));
745 INSERT INTO t3 VALUES (10, repeat('j',15));
746 #
747 # Restart in normal mode with default undo tablespaces and rollback segments.
748 # The persisted settings will be set just after startup.
749 #
750 # restart
751 SHOW VARIABLES LIKE 'innodb_rollback_segments';
752 Variable_name Value
753 innodb_rollback_segments 11
754 SHOW GLOBAL STATUS LIKE '%undo%';
755 Variable_name Value
756 Innodb_undo_tablespaces_total 6
757 Innodb_undo_tablespaces_implicit 2
758 Innodb_undo_tablespaces_explicit 4
759 Innodb_undo_tablespaces_active 6
760 SELECT NAME, ROW_FORMAT, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
761 WHERE name LIKE '%undo%' ORDER BY NAME;
762 NAME ROW_FORMAT SPACE_TYPE STATE
763 innodb_undo_001 Undo Undo active
764 innodb_undo_002 Undo Undo active
765 undo_003 Undo Undo active
766 undo_004 Undo Undo active
767 undo_005 Undo Undo active
768 undo_006 Undo Undo active
769 SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
770 WHERE FILE_NAME LIKE '%undo%' ORDER BY TABLESPACE_NAME;
771 TABLESPACE_NAME FILE_TYPE FILE_NAME
772 innodb_undo_001 UNDO LOG ./undo_001
773 innodb_undo_002 UNDO LOG ./undo_002
774 undo_003 UNDO LOG ./undo_003.ibu
775 undo_004 UNDO LOG ./undo_004.ibu
776 undo_005 UNDO LOG ./undo_005.ibu
777 undo_006 UNDO LOG ./undo_006.ibu
778 # Directory listing of UNDO tablespace files
779 undo_001
780 undo_002
781 undo_003.ibu
782 undo_004.ibu
783 undo_005.ibu
784 undo_006.ibu
785 SELECT * FROM t1 ORDER BY a;
786 a b
787 1 aaaaaaaaaaaaaaa
788 2 bbbbbbbbbbbbbbb
789 3 ccccccccccccccc
790 4 ddddddddddddddd
791 5 eeeeeeeeeeeeeee
792 8 hhhhhhhhhhhhhhh
793 101 x1 x1 x1 x1 x1
794 102 x2 x2 x2 x2 x2
795 103 x3 x3 x3 x3 x3
796 SELECT COUNT(*) FROM t2;
797 COUNT(*)
798 9
799 SELECT COUNT(*) FROM t3;
800 COUNT(*)
801 9
802 INSERT INTO t1 VALUES (11, repeat('k',15));
803 INSERT INTO t2 VALUES (11, repeat('k',15));
804 INSERT INTO t3 VALUES (11, repeat('k',15));
805 SELECT * FROM t1 ORDER BY a;
806 a b
807 1 aaaaaaaaaaaaaaa
808 2 bbbbbbbbbbbbbbb
809 3 ccccccccccccccc
810 4 ddddddddddddddd
811 5 eeeeeeeeeeeeeee
812 8 hhhhhhhhhhhhhhh
813 11 kkkkkkkkkkkkkkk
814 101 x1 x1 x1 x1 x1
815 102 x2 x2 x2 x2 x2
816 103 x3 x3 x3 x3 x3
817 SELECT COUNT(*) FROM t2;
818 COUNT(*)
819 10
820 SELECT COUNT(*) FROM t3;
821 COUNT(*)
822 10
823 #
824 # Cleanup
825 #
826 DROP TABLE t1;
827 DROP TABLE t2;
828 DROP TABLE t3;
829 DROP TABLESPACE ts1;
830 ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
831 ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
832 ALTER UNDO TABLESPACE undo_005 SET INACTIVE;
833 ALTER UNDO TABLESPACE undo_006 SET INACTIVE;
834 DROP UNDO TABLESPACE undo_003;
835 DROP UNDO TABLESPACE undo_004;
836 DROP UNDO TABLESPACE undo_005;
837 DROP UNDO TABLESPACE undo_006;
838 SET PERSIST innodb_rollback_segments=20;
839 SHOW VARIABLES LIKE 'innodb_rollback_segments';
840 Variable_name Value
841 innodb_rollback_segments 20
842 RESET PERSIST innodb_rollback_segments;
843 SHOW VARIABLES LIKE 'innodb_rollback_segments';
844 Variable_name Value
845 innodb_rollback_segments 20
846 SET GLOBAL innodb_rollback_segments=default;
847 SHOW VARIABLES LIKE 'innodb_rollback_segments';
848 Variable_name Value
849 innodb_rollback_segments 128
850 SET GLOBAL innodb_fast_shutdown=0;
851 # restart
852 SHOW VARIABLES LIKE 'innodb_rollback_segments';
853 Variable_name Value
854 innodb_rollback_segments 1