Debian Codegraph @

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

raw
1 --echo #
2 --echo # InnoDB supports CREATE/ALTER/DROP UNDO TABLESPACE
3 --echo #
4
5 --source include/have_innodb_default_undo_tablespaces.inc
6
7 # Do a slow shutdown and restart to clear out the undo logs
8 SET GLOBAL innodb_fast_shutdown = 0;
9 --let $shutdown_server_timeout = 300
10 --source include/restart_mysqld.inc
11
12 # Let each undo truncation occur explicitly. This does not need to be
13 # set back to default since there is another restart below.
14 SET GLOBAL innodb_undo_log_truncate = OFF;
15
16 CREATE UNDO TABLESPACE undo_003 ADD DATAFILE 'undo_003.ibu';
17 CREATE UNDO TABLESPACE undo_004 ADD DATAFILE 'undo_004.ibu';
18 CREATE UNDO TABLESPACE undo_005 ADD DATAFILE '5.ibu';
19
20 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
21 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
22 SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
23 WHERE FILE_NAME LIKE '%.ibu' ORDER BY TABLESPACE_NAME;
24
25 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
26 CREATE TABLE t1 (a int primary key) TABLESPACE ts1;
27 --echo #
28 --echo # Populate t1 with separate INSERTs so that all rsegs are used.
29 --echo #
30 DELIMITER |;
31 CREATE PROCEDURE populate_t1(IN BASE INT, IN SIZE INT)
32 BEGIN
33 DECLARE i INT DEFAULT BASE;
34 WHILE (i <= SIZE) DO
35 INSERT INTO t1 values (i);
36 SET i = i + 1;
37 END WHILE;
38 END|
39 DELIMITER ;|
40 CALL populate_t1(1, 1000);
41
42 --echo #
43 --echo # Show that the implicit undo tablespaces may be set inactive
44 --echo # and that a minimum of 2 undo tablespaces must remain active.
45 --echo #
46 ALTER UNDO TABLESPACE innodb_undo_001 SET INACTIVE;
47 let $inactive_undo_space = innodb_undo_001;
48 source include/wait_until_undo_space_is_empty.inc;
49
50 ALTER UNDO TABLESPACE innodb_undo_002 SET INACTIVE;
51 let $inactive_undo_space = innodb_undo_002;
52 source include/wait_until_undo_space_is_empty.inc;
53
54 ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
55 let $inactive_undo_space = undo_003;
56 source include/wait_until_undo_space_is_empty.inc;
57
58 --error ER_DISALLOWED_OPERATION
59 ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
60 SHOW WARNINGS;
61 --error ER_DISALLOWED_OPERATION
62 ALTER UNDO TABLESPACE undo_005 SET INACTIVE;
63 SHOW WARNINGS;
64
65 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
66 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
67 SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
68 WHERE FILE_NAME LIKE '%.ibu' ORDER BY TABLESPACE_NAME;
69
70 ALTER UNDO TABLESPACE innodb_undo_001 SET ACTIVE;
71 ALTER UNDO TABLESPACE innodb_undo_002 SET ACTIVE;
72 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
73 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
74
75 --echo #
76 --echo # Show that SET ACTIVE and SET INACTIVE are indempotent.
77 --echo #
78 ALTER UNDO TABLESPACE undo_003 SET ACTIVE;
79 ALTER UNDO TABLESPACE undo_003 SET ACTIVE;
80 ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
81 ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
82 let $inactive_undo_space = undo_003;
83 source include/wait_until_undo_space_is_empty.inc;
84 ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
85
86 --echo #
87 --echo # SET the explicit tablespaces INACTIVE.
88 --echo #
89 ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
90 let $inactive_undo_space = undo_004;
91 source include/wait_until_undo_space_is_empty.inc;
92
93 ALTER UNDO TABLESPACE undo_005 SET INACTIVE;
94 let $inactive_undo_space = undo_005;
95 source include/wait_until_undo_space_is_empty.inc;
96
97 SHOW GLOBAL STATUS LIKE '%undo%';
98 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
99 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
100 SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
101 WHERE FILE_NAME LIKE '%.ibu' ORDER BY TABLESPACE_NAME;
102
103 --echo #
104 --echo # Drop undo_003
105 --echo #
106 DROP UNDO TABLESPACE undo_003;
107 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
108 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
109 SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
110 WHERE FILE_NAME LIKE '%.ibu' ORDER BY TABLESPACE_NAME;
111
112 ALTER UNDO TABLESPACE undo_005 SET ACTIVE;
113 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
114 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
115
116 --echo #
117 --echo # Try various bad CREATE UNDO TABLESPACE commands
118 --echo #
119 # `innodb_undo_001` is an existing implict undo tablespace.
120 --error ER_WRONG_TABLESPACE_NAME
121 CREATE UNDO TABLESPACE innodb_undo_001 ADD DATAFILE 'undo_001.ibu';
122 SHOW WARNINGS;
123
124 # Show that you cannot use an existing file known to the DD.
125 --error ER_TABLESPACE_DUP_FILENAME
126 CREATE UNDO TABLESPACE undo_5 ADD DATAFILE '5.ibu';
127 SHOW WARNINGS;
128
129 # Show that you cannot use an existing file unknown to the DD.
130 let $MYSQLD_DATADIR=`select @@datadir`;
131 --write_file $MYSQLD_DATADIR/undo_99.ibu
132 This is a leftover file with an undo tablespace suffix that the DD does not know about
133 EOF
134 --error ER_WRONG_FILE_NAME
135 CREATE UNDO TABLESPACE undo_99 ADD DATAFILE 'undo_99.ibu';
136 SHOW WARNINGS;
137 --remove_file $MYSQLD_DATADIR/undo_99.ibu
138
139 # Cannot use single quotes for an identifier
140 --error ER_PARSE_ERROR
141 CREATE UNDO TABLESPACE 'undo_99' ADD DATAFILE 'undo_001.ibu';
142
143 # `undo_99` does not exist.
144 --error ER_PARSE_ERROR
145 CREATE UNDO TABLESPACE `undo_99`;
146
147 # An explicit undo tablespace datafile name must end with '.ibu'
148 --error ER_WRONG_FILE_NAME
149 CREATE UNDO TABLESPACE undo_99 ADD DATAFILE 'undo_99';
150 SHOW WARNINGS;
151
152 --error ER_WRONG_FILE_NAME
153 CREATE UNDO TABLESPACE undo_99 ADD DATAFILE 'undo_99.ibd';
154 SHOW WARNINGS;
155
156 # The datafile name must be in an existing directory
157 --error ER_WRONG_FILE_NAME
158 CREATE UNDO TABLESPACE undo_99 ADD DATAFILE '/dir_does_not_exist/undo_99.ibu';
159 --replace_result \\ /
160 SHOW WARNINGS;
161
162 # The location must be a known datafile location, see --innodb-directories
163 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
164 --error ER_WRONG_FILE_NAME
165 CREATE UNDO TABLESPACE undo_99 ADD DATAFILE '../undo_99.ibu';
166 --replace_result \\ /
167 SHOW WARNINGS;
168
169 --echo #
170 --echo # Try various bad ALTER UNDO TABLESPACE commands
171 --echo #
172 # Must ALTER with SET ACTIVE or SET INACTIVE
173 --error ER_PARSE_ERROR
174 ALTER UNDO TABLESPACE `undo_99`;
175
176 # `undo_99` does not exist.
177 --error ER_TABLESPACE_MISSING_WITH_NAME
178 ALTER UNDO TABLESPACE `undo_99` SET INACTIVE;
179 SHOW WARNINGS;
180 --error ER_TABLESPACE_MISSING_WITH_NAME
181 ALTER UNDO TABLESPACE `undo_99` SET ACTIVE;
182 SHOW WARNINGS;
183
184 # Show that ALTER UNDO TABLESPACE with SET ACTIVE or SET INACTIVE
185 # will not work on a general tablespace.
186 --error ER_WRONG_TABLESPACE_NAME
187 ALTER UNDO TABLESPACE `ts1` SET INACTIVE;
188 SHOW WARNINGS;
189 --error ER_WRONG_TABLESPACE_NAME
190 ALTER UNDO TABLESPACE `ts1` SET ACTIVE;
191 SHOW WARNINGS;
192
193 # Show that ALTER TABLESPACE RENAME does not work on an UNDO tablespace.
194 --error ER_WRONG_TABLESPACE_NAME
195 ALTER TABLESPACE undo_005 RENAME TO undo_5;
196 SHOW WARNINGS;
197
198 # SET EMPTY is not a valid phrase.
199 --error ER_PARSE_ERROR
200 ALTER UNDO TABLESPACE undo_005 SET EMPTY;
201
202 --echo #
203 --echo # Try various bad DROP UNDO TABLESPACE commands
204 --echo #
205
206 # undo_001 is an implict undo tablespace, cannot be dropped.
207 --error ER_WRONG_TABLESPACE_NAME
208 DROP UNDO TABLESPACE innodb_undo_001;
209 SHOW WARNINGS;
210
211 # undo_99 does not exist.
212 --error ER_TABLESPACE_MISSING_WITH_NAME
213 DROP UNDO TABLESPACE undo_99;
214 SHOW WARNINGS;
215
216 # undo_005 is currently active, cannot be dropped.
217 --error ER_DROP_FILEGROUP_FAILED
218 DROP UNDO TABLESPACE undo_005;
219 SHOW WARNINGS;
220
221 # Show that DROP TABLESPACE does not work on undo tablespaces.
222 --error ER_WRONG_TABLESPACE_NAME
223 DROP TABLESPACE undo_005;
224 SHOW WARNINGS;
225
226 # Show that DROP UNDO TABLESPACE does not work on general tablespaces.
227 --error ER_WRONG_TABLESPACE_NAME
228 DROP UNDO TABLESPACE ts1;
229 SHOW WARNINGS;
230
231 --echo #
232 --echo # Show that tables cannot be added to an undo tablespace.
233 --echo #
234
235 --error ER_WRONG_TABLESPACE_NAME
236 CREATE TABLE t2 (a int primary key) TABLESPACE undo_004;
237 SHOW WARNINGS;
238
239 --error ER_WRONG_TABLESPACE_NAME
240 ALTER TABLE t1 TABLESPACE undo_004;
241 SHOW WARNINGS;
242
243 --echo #
244 --echo # Show that a missing undo tablespace can be dropped
245 --echo #
246 let $MYSQLD_DATADIR=`select @@datadir`;
247 --source include/shutdown_mysqld.inc
248 --remove_file $MYSQLD_DATADIR/undo_004.ibu
249 --source include/start_mysqld.inc
250 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
251 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
252 SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
253 WHERE FILE_NAME LIKE '%.ibu' ORDER BY TABLESPACE_NAME;
254 DROP UNDO TABLESPACE undo_004;
255
256 --echo #
257 --echo # Show that the setting innodb_validate_tablespace_paths does not affect undo tablespaces.
258 --echo #
259 CREATE UNDO TABLESPACE undo_006 ADD DATAFILE 'undo_006.ibu';
260 SHOW GLOBAL STATUS LIKE '%undo%';
261 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
262 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
263 SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
264 WHERE FILE_NAME LIKE '%.ibu' ORDER BY TABLESPACE_NAME;
265
266 --echo # Restart with validation turned OFF
267 let $restart_parameters = restart: --innodb_validate_tablespace_paths=0;
268 --source include/restart_mysqld.inc
269 SHOW GLOBAL STATUS LIKE '%undo%';
270 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
271 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
272 SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
273 WHERE FILE_NAME LIKE '%.ibu' ORDER BY TABLESPACE_NAME;
274
275 --echo # Kill and restart mysqld with validation turned OFF
276 let $restart_parameters = restart: --innodb_validate_tablespace_paths=0;
277 --source include/kill_and_restart_mysqld.inc
278 SHOW GLOBAL STATUS LIKE '%undo%';
279 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
280 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
281 SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
282 WHERE FILE_NAME LIKE '%.ibu' ORDER BY TABLESPACE_NAME;
283
284 --echo # Restart mysqld with validation turned ON
285 let $restart_parameters = restart:;
286 --source include/restart_mysqld.inc
287 SHOW GLOBAL STATUS LIKE '%undo%';
288 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
289 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
290 SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
291 WHERE FILE_NAME LIKE '%.ibu' ORDER BY TABLESPACE_NAME;
292
293 --echo #
294 --echo # Cleanup
295 --echo #
296 DROP TABLE t1;
297 DROP TABLESPACE ts1;
298 DROP PROCEDURE populate_t1;
299
300 ALTER UNDO TABLESPACE undo_005 SET INACTIVE;
301 let $inactive_undo_space = undo_005;
302 source include/wait_until_undo_space_is_empty.inc;
303 DROP UNDO TABLESPACE undo_005;
304
305 ALTER UNDO TABLESPACE undo_006 SET INACTIVE;
306 let $inactive_undo_space = undo_006;
307 source include/wait_until_undo_space_is_empty.inc;
308 DROP UNDO TABLESPACE undo_006;
309
310 --disable_query_log
311 call mtr.add_suppression("\\[Warning\\] .* Log writer is waiting for checkpointer to to catch up lag");
312 call mtr.add_suppression("\\[Warning\\] .* Tablespace .*, name 'undo_004', file 'undo_004.ibu' is missing");
313 call mtr.add_suppression("\\[Warning\\] .* Trying to access missing tablespace");
314 call mtr.add_suppression("\\[ERROR\\] .* The directory for tablespace .* does not exist or is incorrect.");
315 call mtr.add_suppression("\\[ERROR\\] .* Cannot drop undo tablespace 'undo_005' because it is active. Please do: ALTER UNDO TABLESPACE undo_005 SET INACTIVE");
316 call mtr.add_suppression("\\[ERROR\\] .* Cannot create tablespace undo_99 because the directory is not a valid location. The UNDO DATAFILE location must be in a known directory");
317 call mtr.add_suppression("\\[ERROR\\] .* Cannot find undo tablespace undo_004 with filename '.*' as indicated by the Data Dictionary. Did you move or delete this tablespace. Any undo logs in it cannot be used");
318 --enable_query_log
319