Debian Codegraph @

mysql-test/suite/innodb_undo/t/truncate_xa.test

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