Debian Codegraph @

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

raw
1 #
2 # Truncate explicitly created UNDO logs before and after recovery.
3 #
4 CREATE TABLE t1 (a SERIAL, b INT, c TEXT);
5 #
6 # Test 1: Check affect of transactions on undo truncation online
7 #
8 INSERT INTO t1(b, c) VALUES (0,"zero");
9 # Create 2 explicit undo tablespaces.
10 CREATE UNDO TABLESPACE undo_003 ADD DATAFILE 'undo_003.ibu';
11 CREATE UNDO TABLESPACE undo_004 ADD DATAFILE 'undo_004.ibu';
12 # Make the 2 implicit undo tablespaces inactive.
13 ALTER UNDO TABLESPACE innodb_undo_001 SET INACTIVE;
14 ALTER UNDO TABLESPACE innodb_undo_002 SET INACTIVE;
15 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
16 NAME SPACE_TYPE STATE
17 innodb_undo_001 Undo empty
18 innodb_undo_002 Undo empty
19 undo_003 Undo active
20 undo_004 Undo active
21 # Switch to connection #1
22 XA START 'x1';
23 INSERT INTO t1(b, c) VALUES (1,"one");
24 SELECT * FROM t1;
25 a b c
26 1 0 zero
27 2 1 one
28 XA END 'x1';
29 XA PREPARE 'x1';
30 # Switch to connection #2
31 XA START 'x2';
32 INSERT INTO t1(b, c) VALUES (2,"two");
33 SELECT * FROM t1;
34 a b c
35 1 0 zero
36 3 2 two
37 XA END 'x2';
38 XA PREPARE 'x2';
39 # Switch to connection Default
40 SELECT * FROM t1;
41 a b c
42 1 0 zero
43 ALTER UNDO TABLESPACE innodb_undo_001 SET ACTIVE;
44 ALTER UNDO TABLESPACE innodb_undo_002 SET ACTIVE;
45 ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
46 ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
47 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
48 NAME SPACE_TYPE STATE
49 innodb_undo_001 Undo active
50 innodb_undo_002 Undo active
51 undo_003 Undo inactive
52 undo_004 Undo inactive
53 # Switch to connection #1
54 XA COMMIT 'x1';
55 # Switch to connection #2
56 XA ROLLBACK 'x2';
57 # Switch to connection Default
58 SELECT * FROM t1;
59 a b c
60 1 0 zero
61 2 1 one
62 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
63 NAME SPACE_TYPE STATE
64 innodb_undo_001 Undo active
65 innodb_undo_002 Undo active
66 undo_003 Undo empty
67 undo_004 Undo empty
68 DELETE FROM t1;
69 #
70 # Test 2: Check affect of transactions on undo truncation after a restart.
71 #
72 INSERT INTO t1(b, c) VALUES (0,"zero");
73 # Make the two explicit undo tablespaces active.
74 ALTER UNDO TABLESPACE undo_003 SET ACTIVE;
75 ALTER UNDO TABLESPACE undo_004 SET ACTIVE;
76 # Make the 2 implicit undo tablespaces inactive.
77 ALTER UNDO TABLESPACE innodb_undo_001 SET INACTIVE;
78 ALTER UNDO TABLESPACE innodb_undo_002 SET INACTIVE;
79 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
80 NAME SPACE_TYPE STATE
81 innodb_undo_001 Undo empty
82 innodb_undo_002 Undo empty
83 undo_003 Undo active
84 undo_004 Undo active
85 # Switch to connection #1
86 XA START 'x1';
87 INSERT INTO t1(b, c) VALUES (1,"one");
88 SELECT * FROM t1;
89 a b c
90 4 0 zero
91 5 1 one
92 XA END 'x1';
93 XA PREPARE 'x1';
94 # Switch to connection #2
95 XA START 'x2';
96 INSERT INTO t1(b, c) VALUES (2,"two");
97 SELECT * FROM t1;
98 a b c
99 4 0 zero
100 6 2 two
101 XA END 'x2';
102 XA PREPARE 'x2';
103 # Switch to connection Default
104 # Try to truncate the two explicit undo tablespaces. They will be stuck inactive.
105 ALTER UNDO TABLESPACE innodb_undo_001 SET ACTIVE;
106 ALTER UNDO TABLESPACE innodb_undo_002 SET ACTIVE;
107 ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
108 ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
109 SELECT * FROM t1;
110 a b c
111 4 0 zero
112 SELECT SLEEP(1);
113 SLEEP(1)
114 0
115 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
116 NAME SPACE_TYPE STATE
117 innodb_undo_001 Undo active
118 innodb_undo_002 Undo active
119 undo_003 Undo inactive
120 undo_004 Undo inactive
121 # restart
122 # After a restart, they are still stuck inactive.
123 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
124 NAME SPACE_TYPE STATE
125 innodb_undo_001 Undo active
126 innodb_undo_002 Undo active
127 undo_003 Undo inactive
128 undo_004 Undo inactive
129 XA COMMIT 'x1';
130 XA ROLLBACK 'x2';
131 SELECT * FROM t1;
132 a b c
133 4 0 zero
134 5 1 one
135 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
136 NAME SPACE_TYPE STATE
137 innodb_undo_001 Undo active
138 innodb_undo_002 Undo active
139 undo_003 Undo empty
140 undo_004 Undo empty
141 DELETE FROM t1;
142 #
143 # Test 3: Check affect of transactions on undo truncation after a crash.
144 #
145 INSERT INTO t1(b, c) VALUES (0,"zero");
146 # Make the two explicit undo tablespaces active.
147 ALTER UNDO TABLESPACE undo_003 SET ACTIVE;
148 ALTER UNDO TABLESPACE undo_004 SET ACTIVE;
149 # Make the 2 implicit undo tablespaces inactive.
150 ALTER UNDO TABLESPACE innodb_undo_001 SET INACTIVE;
151 ALTER UNDO TABLESPACE innodb_undo_002 SET INACTIVE;
152 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
153 NAME SPACE_TYPE STATE
154 innodb_undo_001 Undo empty
155 innodb_undo_002 Undo empty
156 undo_003 Undo active
157 undo_004 Undo active
158 # Switch to connection #3
159 XA START 'x3';
160 INSERT INTO t1(b, c) VALUES (1,"one");
161 SELECT * FROM t1;
162 a b c
163 7 0 zero
164 8 1 one
165 XA END 'x3';
166 XA PREPARE 'x3';
167 # Switch to connection #4
168 XA START 'x4';
169 INSERT INTO t1(b, c) VALUES (2,"two");
170 SELECT * FROM t1;
171 a b c
172 7 0 zero
173 9 2 two
174 XA END 'x4';
175 XA PREPARE 'x4';
176 # Switch to connection Default
177 # Try to truncate the two explicit undo tablespaces. They will be stuck inactive.
178 ALTER UNDO TABLESPACE innodb_undo_001 SET ACTIVE;
179 ALTER UNDO TABLESPACE innodb_undo_002 SET ACTIVE;
180 ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
181 ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
182 SELECT * FROM t1;
183 a b c
184 7 0 zero
185 SELECT SLEEP(1);
186 SLEEP(1)
187 0
188 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
189 NAME SPACE_TYPE STATE
190 innodb_undo_001 Undo active
191 innodb_undo_002 Undo active
192 undo_003 Undo inactive
193 undo_004 Undo inactive
194 # Kill and restart
195 # After a kill and restart, they are still stuck inactive.
196 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
197 NAME SPACE_TYPE STATE
198 innodb_undo_001 Undo active
199 innodb_undo_002 Undo active
200 undo_003 Undo inactive
201 undo_004 Undo inactive
202 XA COMMIT 'x3';
203 XA ROLLBACK 'x4';
204 SELECT * FROM t1;
205 a b c
206 7 0 zero
207 8 1 one
208 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
209 NAME SPACE_TYPE STATE
210 innodb_undo_001 Undo active
211 innodb_undo_002 Undo active
212 undo_003 Undo empty
213 undo_004 Undo empty
214 DELETE FROM t1;
215 #
216 # Cleanup
217 #
218 # restart
219 DROP TABLE t1;
220 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
221 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
222 NAME SPACE_TYPE STATE
223 innodb_undo_001 Undo active
224 innodb_undo_002 Undo active
225 undo_003 Undo empty
226 undo_004 Undo empty
227 DROP UNDO TABLESPACE undo_003;
228 DROP UNDO TABLESPACE undo_004;