Debian Codegraph @

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

raw
1 #
2 # WL#6965: Truncate explicitly created UNDO logs.
3 #
4 # While the purge thread is turned off, perform enough DML to cause
5 # undo tablespace sizes grow beyond the truncation threshold.
6 # Then check if truncation is accomplished by restarting mysqld
7 # causing each undo tablespace to be truncated.
8 # Create 2 explicit undo tablespaces.
9 CREATE UNDO TABLESPACE undo_003 ADD DATAFILE 'undo_003.ibu';
10 CREATE UNDO TABLESPACE undo_004 ADD DATAFILE 'undo_004.ibu';
11 # Make the 2 implicit undo tablespaces inactive so they get truncated.
12 SET GLOBAL innodb_purge_rseg_truncate_frequency=1;
13 ALTER UNDO TABLESPACE innodb_undo_001 SET INACTIVE;
14 ALTER UNDO TABLESPACE innodb_undo_002 SET INACTIVE;
15 SET GLOBAL innodb_purge_rseg_truncate_frequency=default;
16 # The two undo tablespaces to truncate at shutdown will be undo_003 and undo_004
17 SELECT NAME, SPACE_TYPE, STATE, AUTOEXTEND_SIZE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
18 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
19 NAME SPACE_TYPE STATE AUTOEXTEND_SIZE
20 innodb_undo_001 Undo empty 0
21 innodb_undo_002 Undo empty 0
22 undo_003 Undo active 0
23 undo_004 Undo active 0
24 # Turn off purging so that undo logs build up in the undo tablespaces.
25 SET GLOBAL innodb_purge_stop_now=ON;
26 # Perform enough DML action so that undo tablespace sizes grow beyond
27 # the threshold set by --innodb-max-undo-log-size.
28 create table t1(
29 keyc int,
30 c1 char(255),
31 c2 char(255),
32 c3 char(255),
33 c4 char(255),
34 c5 char(255),
35 c6 char(255),
36 primary key(keyc)) engine = innodb;
37 CREATE PROCEDURE populate_t1()
38 BEGIN
39 DECLARE i INT DEFAULT 1;
40 while (i <= 16000) DO
41 insert into t1 values (i, 'a', 'b', 'c', 'd', 'e', 'f' );
42 SET i = i + 1;
43 END WHILE;
44 END |
45 call populate_t1();
46 delete from t1 where keyc < 10000;
47 update t1 set c1 = 'mysql' where keyc > 10000;
48 update t1 set c2 = 'mysql' where keyc > 10000;
49 update t1 set c3 = 'mysql' where keyc > 10000;
50 update t1 set c4 = 'mysql' where keyc > 10000;
51 update t1 set c5 = 'mysql' where keyc > 10000;
52 update t1 set c6 = 'mysql' where keyc > 10000;
53 # Start a new transaction for both tablespaces to get utilized
54 create table t2 (
55 keyc int,
56 c1 char(255),
57 c2 char(255),
58 c3 char(255),
59 c4 char(255),
60 c5 char(255),
61 c6 char(255),
62 primary key(keyc)) engine = innodb;
63 CREATE PROCEDURE populate_t2()
64 BEGIN
65 DECLARE i INT DEFAULT 1;
66 while (i <= 16000) DO
67 insert into t2 values (i, 'a', 'b', 'c', 'd', 'e', 'f' );
68 SET i = i + 1;
69 END WHILE;
70 END |
71 call populate_t2();
72 delete from t2 where keyc < 10000;
73 update t2 set c1 = 'mysql' where keyc > 10000;
74 update t2 set c2 = 'mysql' where keyc > 10000;
75 update t2 set c3 = 'mysql' where keyc > 10000;
76 update t2 set c4 = 'mysql' where keyc > 10000;
77 update t2 set c5 = 'mysql' where keyc > 10000;
78 update t2 set c6 = 'mysql' where keyc > 10000;
79 drop PROCEDURE populate_t1;
80 drop PROCEDURE populate_t2;
81 drop table t1, t2;
82 SELECT NAME, SPACE_TYPE, STATE, AUTOEXTEND_SIZE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
83 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
84 NAME SPACE_TYPE STATE AUTOEXTEND_SIZE
85 innodb_undo_001 Undo empty 0
86 innodb_undo_002 Undo empty 0
87 undo_003 Undo active 0
88 undo_004 Undo active 0
89 # Compare the undo tablespace sizes before and after DML growth.
90 # Do a slow shut down so the undo tablespaces can be truncated.
91 SET GLOBAL innodb_max_undo_log_size=20000000;
92 SET GLOBAL innodb_fast_shutdown=0;
93 SET GLOBAL innodb_purge_rseg_truncate_frequency=1;
94 SET GLOBAL innodb_purge_run_now=ON;
95 # restart:
96 # Compare the undo tablespace sizes before and after truncation.
97 SELECT NAME, SPACE_TYPE, STATE, AUTOEXTEND_SIZE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
98 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
99 NAME SPACE_TYPE STATE AUTOEXTEND_SIZE
100 innodb_undo_001 Undo empty 0
101 innodb_undo_002 Undo empty 0
102 undo_003 Undo active 0
103 undo_004 Undo active 0
104 #
105 # Cleanup
106 #
107 ALTER UNDO TABLESPACE innodb_undo_001 SET ACTIVE;
108 ALTER UNDO TABLESPACE innodb_undo_002 SET ACTIVE;
109 ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
110 ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
111 SET GLOBAL innodb_purge_rseg_truncate_frequency=1;
112 SET GLOBAL innodb_purge_rseg_truncate_frequency=default;
113 DROP UNDO TABLESPACE undo_003;
114 DROP UNDO TABLESPACE undo_004;
115 SELECT NAME, SPACE_TYPE, STATE, AUTOEXTEND_SIZE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
116 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
117 NAME SPACE_TYPE STATE AUTOEXTEND_SIZE
118 innodb_undo_001 Undo active 0
119 innodb_undo_002 Undo active 0