Debian Codegraph @

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

raw
1 #
2 # InnoDB supports CREATE/ALTER/DROP UNDO TABLESPACE
3 #
4 SET GLOBAL innodb_fast_shutdown = 0;
5 # restart
6 SET GLOBAL innodb_undo_log_truncate = OFF;
7 CREATE UNDO TABLESPACE undo_003 ADD DATAFILE 'undo_003.ibu';
8 CREATE UNDO TABLESPACE undo_004 ADD DATAFILE 'undo_004.ibu';
9 CREATE UNDO TABLESPACE undo_005 ADD DATAFILE '5.ibu';
10 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
11 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
12 NAME SPACE_TYPE STATE
13 innodb_undo_001 Undo active
14 innodb_undo_002 Undo active
15 undo_003 Undo active
16 undo_004 Undo active
17 undo_005 Undo active
18 SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
19 WHERE FILE_NAME LIKE '%.ibu' ORDER BY TABLESPACE_NAME;
20 TABLESPACE_NAME FILE_TYPE FILE_NAME
21 undo_003 UNDO LOG ./undo_003.ibu
22 undo_004 UNDO LOG ./undo_004.ibu
23 undo_005 UNDO LOG ./5.ibu
24 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
25 CREATE TABLE t1 (a int primary key) TABLESPACE ts1;
26 #
27 # Populate t1 with separate INSERTs so that all rsegs are used.
28 #
29 CREATE PROCEDURE populate_t1(IN BASE INT, IN SIZE INT)
30 BEGIN
31 DECLARE i INT DEFAULT BASE;
32 WHILE (i <= SIZE) DO
33 INSERT INTO t1 values (i);
34 SET i = i + 1;
35 END WHILE;
36 END|
37 CALL populate_t1(1, 1000);
38 #
39 # Show that the implicit undo tablespaces may be set inactive
40 # and that a minimum of 2 undo tablespaces must remain active.
41 #
42 ALTER UNDO TABLESPACE innodb_undo_001 SET INACTIVE;
43 ALTER UNDO TABLESPACE innodb_undo_002 SET INACTIVE;
44 ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
45 ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
46 ERROR HY000: Cannot set undo_004 inactive since there would be less than 2 undo tablespaces left active.
47 SHOW WARNINGS;
48 Level Code Message
49 Error 3655 Cannot set undo_004 inactive since there would be less than 2 undo tablespaces left active.
50 Error 1533 Failed to alter: UNDO TABLESPACE undo_004
51 Error 3655 ALTER UNDO TABLEPSPACE operation is disallowed on undo_004
52 ALTER UNDO TABLESPACE undo_005 SET INACTIVE;
53 ERROR HY000: Cannot set undo_005 inactive since there would be less than 2 undo tablespaces left active.
54 SHOW WARNINGS;
55 Level Code Message
56 Error 3655 Cannot set undo_005 inactive since there would be less than 2 undo tablespaces left active.
57 Error 1533 Failed to alter: UNDO TABLESPACE undo_005
58 Error 3655 ALTER UNDO TABLEPSPACE operation is disallowed on undo_005
59 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
60 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
61 NAME SPACE_TYPE STATE
62 innodb_undo_001 Undo empty
63 innodb_undo_002 Undo empty
64 undo_003 Undo empty
65 undo_004 Undo active
66 undo_005 Undo active
67 SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
68 WHERE FILE_NAME LIKE '%.ibu' ORDER BY TABLESPACE_NAME;
69 TABLESPACE_NAME FILE_TYPE FILE_NAME
70 undo_003 UNDO LOG ./undo_003.ibu
71 undo_004 UNDO LOG ./undo_004.ibu
72 undo_005 UNDO LOG ./5.ibu
73 ALTER UNDO TABLESPACE innodb_undo_001 SET ACTIVE;
74 ALTER UNDO TABLESPACE innodb_undo_002 SET ACTIVE;
75 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
76 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
77 NAME SPACE_TYPE STATE
78 innodb_undo_001 Undo active
79 innodb_undo_002 Undo active
80 undo_003 Undo empty
81 undo_004 Undo active
82 undo_005 Undo active
83 #
84 # Show that SET ACTIVE and SET INACTIVE are indempotent.
85 #
86 ALTER UNDO TABLESPACE undo_003 SET ACTIVE;
87 ALTER UNDO TABLESPACE undo_003 SET ACTIVE;
88 ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
89 ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
90 ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
91 #
92 # SET the explicit tablespaces INACTIVE.
93 #
94 ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
95 ALTER UNDO TABLESPACE undo_005 SET INACTIVE;
96 SHOW GLOBAL STATUS LIKE '%undo%';
97 Variable_name Value
98 Innodb_undo_tablespaces_total 5
99 Innodb_undo_tablespaces_implicit 2
100 Innodb_undo_tablespaces_explicit 3
101 Innodb_undo_tablespaces_active 2
102 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
103 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
104 NAME SPACE_TYPE STATE
105 innodb_undo_001 Undo active
106 innodb_undo_002 Undo active
107 undo_003 Undo empty
108 undo_004 Undo empty
109 undo_005 Undo empty
110 SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
111 WHERE FILE_NAME LIKE '%.ibu' ORDER BY TABLESPACE_NAME;
112 TABLESPACE_NAME FILE_TYPE FILE_NAME
113 undo_003 UNDO LOG ./undo_003.ibu
114 undo_004 UNDO LOG ./undo_004.ibu
115 undo_005 UNDO LOG ./5.ibu
116 #
117 # Drop undo_003
118 #
119 DROP UNDO TABLESPACE undo_003;
120 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
121 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
122 NAME SPACE_TYPE STATE
123 innodb_undo_001 Undo active
124 innodb_undo_002 Undo active
125 undo_004 Undo empty
126 undo_005 Undo empty
127 SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
128 WHERE FILE_NAME LIKE '%.ibu' ORDER BY TABLESPACE_NAME;
129 TABLESPACE_NAME FILE_TYPE FILE_NAME
130 undo_004 UNDO LOG ./undo_004.ibu
131 undo_005 UNDO LOG ./5.ibu
132 ALTER UNDO TABLESPACE undo_005 SET ACTIVE;
133 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
134 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
135 NAME SPACE_TYPE STATE
136 innodb_undo_001 Undo active
137 innodb_undo_002 Undo active
138 undo_004 Undo empty
139 undo_005 Undo active
140 #
141 # Try various bad CREATE UNDO TABLESPACE commands
142 #
143 CREATE UNDO TABLESPACE innodb_undo_001 ADD DATAFILE 'undo_001.ibu';
144 ERROR 42000: InnoDB: Tablespace names starting with `innodb_` are reserved.
145 SHOW WARNINGS;
146 Level Code Message
147 Error 3119 InnoDB: Tablespace names starting with `innodb_` are reserved.
148 Error 3119 Incorrect tablespace name `innodb_undo_001`
149 CREATE UNDO TABLESPACE undo_5 ADD DATAFILE '5.ibu';
150 ERROR HY000: Duplicate file name for tablespace 'undo_5'
151 SHOW WARNINGS;
152 Level Code Message
153 Error 3606 Duplicate file name for tablespace 'undo_5'
154 CREATE UNDO TABLESPACE undo_99 ADD DATAFILE 'undo_99.ibu';
155 ERROR HY000: The ADD DATAFILE filepath already exists.
156 SHOW WARNINGS;
157 Level Code Message
158 Error 3121 The ADD DATAFILE filepath already exists.
159 Error 1528 Failed to create UNDO TABLESPACE undo_99
160 Error 3121 Incorrect File Name 'undo_99.ibu'.
161 CREATE UNDO TABLESPACE 'undo_99' ADD DATAFILE 'undo_001.ibu';
162 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''undo_99' ADD DATAFILE 'undo_001.ibu'' at line 1
163 CREATE UNDO TABLESPACE `undo_99`;
164 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
165 CREATE UNDO TABLESPACE undo_99 ADD DATAFILE 'undo_99';
166 ERROR HY000: The ADD DATAFILE filepath must end with '.ibu'.
167 SHOW WARNINGS;
168 Level Code Message
169 Error 3121 The ADD DATAFILE filepath must end with '.ibu'.
170 Error 1528 Failed to create UNDO TABLESPACE undo_99
171 Error 3121 Incorrect File Name 'undo_99'.
172 CREATE UNDO TABLESPACE undo_99 ADD DATAFILE 'undo_99.ibd';
173 ERROR HY000: The ADD DATAFILE filepath must end with '.ibu'.
174 SHOW WARNINGS;
175 Level Code Message
176 Error 3121 The ADD DATAFILE filepath must end with '.ibu'.
177 Error 1528 Failed to create UNDO TABLESPACE undo_99
178 Error 3121 Incorrect File Name 'undo_99.ibd'.
179 CREATE UNDO TABLESPACE undo_99 ADD DATAFILE '/dir_does_not_exist/undo_99.ibu';
180 ERROR HY000: The directory does not exist or is incorrect.
181 SHOW WARNINGS;
182 Level Code Message
183 Error 3121 The directory does not exist or is incorrect.
184 Error 3121 The UNDO DATAFILE location must be in a known directory.
185 Error 1528 Failed to create UNDO TABLESPACE undo_99
186 Error 3121 Incorrect File Name '/dir_does_not_exist/undo_99.ibu'.
187 CREATE UNDO TABLESPACE undo_99 ADD DATAFILE '../undo_99.ibu';
188 ERROR HY000: The ADD DATAFILE filepath for an UNDO TABLESPACE cannot be a relative path.
189 SHOW WARNINGS;
190 Level Code Message
191 Error 3121 The ADD DATAFILE filepath for an UNDO TABLESPACE cannot be a relative path.
192 Error 3121 The UNDO DATAFILE location must be in a known directory.
193 Error 1528 Failed to create UNDO TABLESPACE undo_99
194 Error 3121 Incorrect File Name '../undo_99.ibu'.
195 #
196 # Try various bad ALTER UNDO TABLESPACE commands
197 #
198 ALTER UNDO TABLESPACE `undo_99`;
199 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
200 ALTER UNDO TABLESPACE `undo_99` SET INACTIVE;
201 ERROR HY000: Tablespace undo_99 doesn't exist.
202 SHOW WARNINGS;
203 Level Code Message
204 Error 3510 Tablespace undo_99 doesn't exist.
205 ALTER UNDO TABLESPACE `undo_99` SET ACTIVE;
206 ERROR HY000: Tablespace undo_99 doesn't exist.
207 SHOW WARNINGS;
208 Level Code Message
209 Error 3510 Tablespace undo_99 doesn't exist.
210 ALTER UNDO TABLESPACE `ts1` SET INACTIVE;
211 ERROR 42000: Cannot ALTER UNDO TABLESPACE `ts1` because it is a general tablespace. Please use ALTER TABLESPACE.
212 SHOW WARNINGS;
213 Level Code Message
214 Error 3119 Cannot ALTER UNDO TABLESPACE `ts1` because it is a general tablespace. Please use ALTER TABLESPACE.
215 Error 1533 Failed to alter: UNDO TABLESPACE ts1
216 Error 3655 ALTER UNDO TABLEPSPACE operation is disallowed on ts1
217 ALTER UNDO TABLESPACE `ts1` SET ACTIVE;
218 ERROR 42000: Cannot ALTER UNDO TABLESPACE `ts1` because it is a general tablespace. Please use ALTER TABLESPACE.
219 SHOW WARNINGS;
220 Level Code Message
221 Error 3119 Cannot ALTER UNDO TABLESPACE `ts1` because it is a general tablespace. Please use ALTER TABLESPACE.
222 Error 1533 Failed to alter: UNDO TABLESPACE ts1
223 Error 3655 ALTER UNDO TABLEPSPACE operation is disallowed on ts1
224 ALTER TABLESPACE undo_005 RENAME TO undo_5;
225 ERROR 42000: Cannot ALTER TABLESPACE `undo_005` because it is an undo tablespace. Please use ALTER UNDO TABLESPACE.
226 SHOW WARNINGS;
227 Level Code Message
228 Error 3119 Cannot ALTER TABLESPACE `undo_005` because it is an undo tablespace. Please use ALTER UNDO TABLESPACE.
229 Error 1533 Failed to alter: TABLESPACE undo_005
230 Error 3655 ALTER TABLESPACE ... RENAME TO operation is disallowed on undo_005
231 ALTER UNDO TABLESPACE undo_005 SET EMPTY;
232 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EMPTY' at line 1
233 #
234 # Try various bad DROP UNDO TABLESPACE commands
235 #
236 DROP UNDO TABLESPACE innodb_undo_001;
237 ERROR 42000: InnoDB: Tablespace names starting with `innodb_` are reserved.
238 SHOW WARNINGS;
239 Level Code Message
240 Error 3119 InnoDB: Tablespace names starting with `innodb_` are reserved.
241 Error 3119 Incorrect tablespace name `innodb_undo_001`
242 DROP UNDO TABLESPACE undo_99;
243 ERROR HY000: Tablespace undo_99 doesn't exist.
244 SHOW WARNINGS;
245 Level Code Message
246 Error 3510 Tablespace undo_99 doesn't exist.
247 DROP UNDO TABLESPACE undo_005;
248 ERROR HY000: Failed to drop UNDO TABLESPACE undo_005
249 SHOW WARNINGS;
250 Level Code Message
251 Error 1529 Failed to drop UNDO TABLESPACE undo_005
252 Error 3120 Tablespace `undo_005` is not empty.
253 DROP TABLESPACE undo_005;
254 ERROR 42000: Cannot DROP TABLESPACE `undo_005` because it is an undo tablespace. Please use DROP UNDO TABLESPACE.
255 SHOW WARNINGS;
256 Level Code Message
257 Error 3119 Cannot DROP TABLESPACE `undo_005` because it is an undo tablespace. Please use DROP UNDO TABLESPACE.
258 Error 1529 Failed to drop TABLESPACE undo_005
259 Error 3655 DROP TABLEPSPACE operation is disallowed on undo_005
260 DROP UNDO TABLESPACE ts1;
261 ERROR 42000: Cannot DROP UNDO TABLESPACE `ts1` because it is a general tablespace. Please use DROP TABLESPACE.
262 SHOW WARNINGS;
263 Level Code Message
264 Error 3119 Cannot DROP UNDO TABLESPACE `ts1` because it is a general tablespace. Please use DROP TABLESPACE.
265 Error 1529 Failed to drop UNDO TABLESPACE ts1
266 Error 3655 DROP UNDO TABLEPSPACE operation is disallowed on ts1
267 #
268 # Show that tables cannot be added to an undo tablespace.
269 #
270 CREATE TABLE t2 (a int primary key) TABLESPACE undo_004;
271 ERROR 42000: InnoDB: An undo tablespace cannot contain tables.
272 SHOW WARNINGS;
273 Level Code Message
274 Error 3119 InnoDB: An undo tablespace cannot contain tables.
275 Error 1031 Table storage engine for 't2' doesn't have this option
276 ALTER TABLE t1 TABLESPACE undo_004;
277 ERROR 42000: InnoDB: An undo tablespace cannot contain tables.
278 SHOW WARNINGS;
279 Level Code Message
280 Error 3119 InnoDB: An undo tablespace cannot contain tables.
281 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
282 #
283 # Show that a missing undo tablespace can be dropped
284 #
285 # restart
286 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
287 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
288 NAME SPACE_TYPE STATE
289 innodb_undo_001 Undo active
290 innodb_undo_002 Undo active
291 undo_004 Undo empty
292 undo_005 Undo active
293 SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
294 WHERE FILE_NAME LIKE '%.ibu' ORDER BY TABLESPACE_NAME;
295 TABLESPACE_NAME FILE_TYPE FILE_NAME
296 undo_004 NULL ./undo_004.ibu
297 undo_005 UNDO LOG ./5.ibu
298 Warnings:
299 Warning 1812 Tablespace is missing for table undo_004.
300 DROP UNDO TABLESPACE undo_004;
301 #
302 # Show that the setting innodb_validate_tablespace_paths does not affect undo tablespaces.
303 #
304 CREATE UNDO TABLESPACE undo_006 ADD DATAFILE 'undo_006.ibu';
305 SHOW GLOBAL STATUS LIKE '%undo%';
306 Variable_name Value
307 Innodb_undo_tablespaces_total 4
308 Innodb_undo_tablespaces_implicit 2
309 Innodb_undo_tablespaces_explicit 2
310 Innodb_undo_tablespaces_active 4
311 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
312 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
313 NAME SPACE_TYPE STATE
314 innodb_undo_001 Undo active
315 innodb_undo_002 Undo active
316 undo_005 Undo active
317 undo_006 Undo active
318 SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
319 WHERE FILE_NAME LIKE '%.ibu' ORDER BY TABLESPACE_NAME;
320 TABLESPACE_NAME FILE_TYPE FILE_NAME
321 undo_005 UNDO LOG ./5.ibu
322 undo_006 UNDO LOG ./undo_006.ibu
323 # Restart with validation turned OFF
324 # restart: --innodb_validate_tablespace_paths=0
325 SHOW GLOBAL STATUS LIKE '%undo%';
326 Variable_name Value
327 Innodb_undo_tablespaces_total 4
328 Innodb_undo_tablespaces_implicit 2
329 Innodb_undo_tablespaces_explicit 2
330 Innodb_undo_tablespaces_active 4
331 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
332 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
333 NAME SPACE_TYPE STATE
334 innodb_undo_001 Undo active
335 innodb_undo_002 Undo active
336 undo_005 Undo active
337 undo_006 Undo active
338 SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
339 WHERE FILE_NAME LIKE '%.ibu' ORDER BY TABLESPACE_NAME;
340 TABLESPACE_NAME FILE_TYPE FILE_NAME
341 undo_005 UNDO LOG ./5.ibu
342 undo_006 UNDO LOG ./undo_006.ibu
343 # Kill and restart mysqld with validation turned OFF
344 # Kill and restart: --innodb_validate_tablespace_paths=0
345 SHOW GLOBAL STATUS LIKE '%undo%';
346 Variable_name Value
347 Innodb_undo_tablespaces_total 4
348 Innodb_undo_tablespaces_implicit 2
349 Innodb_undo_tablespaces_explicit 2
350 Innodb_undo_tablespaces_active 4
351 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
352 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
353 NAME SPACE_TYPE STATE
354 innodb_undo_001 Undo active
355 innodb_undo_002 Undo active
356 undo_005 Undo active
357 undo_006 Undo active
358 SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
359 WHERE FILE_NAME LIKE '%.ibu' ORDER BY TABLESPACE_NAME;
360 TABLESPACE_NAME FILE_TYPE FILE_NAME
361 undo_005 UNDO LOG ./5.ibu
362 undo_006 UNDO LOG ./undo_006.ibu
363 # Restart mysqld with validation turned ON
364 # restart:
365 SHOW GLOBAL STATUS LIKE '%undo%';
366 Variable_name Value
367 Innodb_undo_tablespaces_total 4
368 Innodb_undo_tablespaces_implicit 2
369 Innodb_undo_tablespaces_explicit 2
370 Innodb_undo_tablespaces_active 4
371 SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
372 WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
373 NAME SPACE_TYPE STATE
374 innodb_undo_001 Undo active
375 innodb_undo_002 Undo active
376 undo_005 Undo active
377 undo_006 Undo active
378 SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
379 WHERE FILE_NAME LIKE '%.ibu' ORDER BY TABLESPACE_NAME;
380 TABLESPACE_NAME FILE_TYPE FILE_NAME
381 undo_005 UNDO LOG ./5.ibu
382 undo_006 UNDO LOG ./undo_006.ibu
383 #
384 # Cleanup
385 #
386 DROP TABLE t1;
387 DROP TABLESPACE ts1;
388 DROP PROCEDURE populate_t1;
389 ALTER UNDO TABLESPACE undo_005 SET INACTIVE;
390 DROP UNDO TABLESPACE undo_005;
391 ALTER UNDO TABLESPACE undo_006 SET INACTIVE;
392 DROP UNDO TABLESPACE undo_006;