Debian Codegraph @

mysql-test/suite/innodb/r/innodb_autoextend_tbsp_ddl.result

raw
1 #
2 # Tests for CREATE/ALTER TABLESPACE with AUTOEXTEND_SIZE clause
3 #
4 # Test error conditions for CREATE TABLESPACE
5 CREATE TABLESPACE myspace ADD DATAFILE 'myspace1.ibd' AUTOEXTEND_SIZE 48K;
6 ERROR HY000: AUTOEXTEND_SIZE value should be between 4M and 4096M
7 CREATE TABLESPACE myspace ADD DATAFILE 'myspace1.ibd' AUTOEXTEND_SIZE 5G;
8 ERROR HY000: AUTOEXTEND_SIZE value should be between 4M and 4096M
9 CREATE TABLESPACE myspace ADD DATAFILE 'myspace1.ibd' AUTOEXTEND_SIZE 5M;
10 ERROR HY000: AUTOEXTEND_SIZE should be a multiple of 4M
11 CREATE TABLESPACE myspace ADD DATAFILE 'myspace1.ibd' AUTOEXTEND_SIZE=8M;
12 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
13 WHERE NAME = 'myspace';
14 NAME FILE_SIZE AUTOEXTEND_SIZE
15 myspace 8388608 8388608
16 # Test error conditions with ALTER TABLESPACE
17 ALTER TABLESPACE myspace AUTOEXTEND_SIZE 2M;
18 ERROR HY000: AUTOEXTEND_SIZE value should be between 4M and 4096M
19 ALTER TABLESPACE myspace AUTOEXTEND_SIZE 5G;
20 ERROR HY000: AUTOEXTEND_SIZE value should be between 4M and 4096M
21 ALTER TABLESPACE myspace AUTOEXTEND_SIZE=4M;
22 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
23 WHERE NAME = 'myspace';
24 NAME FILE_SIZE AUTOEXTEND_SIZE
25 myspace 8388608 4194304
26 # Validate that the autoextend_size attribute is persisted properly
27 # restart
28 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
29 WHERE NAME = 'myspace';
30 NAME FILE_SIZE AUTOEXTEND_SIZE
31 myspace 8388608 4194304
32 DROP TABLESPACE myspace;
33 # CREATE/ALTER TABLESPACE with AUTOEXTEND_SIZE value set to absolute sizes
34 # in bytes not qualified with K, M or G
35 CREATE TABLESPACE myspace ADD DATAFILE 'myspace.ibd' AUTOEXTEND_SIZE 5000000;
36 ERROR HY000: AUTOEXTEND_SIZE should be a multiple of 4M
37 CREATE TABLESPACE myspace ADD DATAFILE 'myspace.ibd' AUTOEXTEND_SIZE 4194304;
38 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
39 WHERE NAME = 'myspace';
40 NAME FILE_SIZE AUTOEXTEND_SIZE
41 myspace 4194304 4194304
42 DROP TABLESPACE myspace;
43 # CREATE TABLESPACE with autoextend_size > 0
44 CREATE TABLESPACE myspace AUTOEXTEND_SIZE 4M;
45 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
46 WHERE NAME = 'myspace';
47 NAME FILE_SIZE AUTOEXTEND_SIZE
48 myspace 4194304 4194304
49 ALTER TABLESPACE myspace AUTOEXTEND_SIZE 0;
50 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
51 WHERE NAME = 'myspace';
52 NAME FILE_SIZE AUTOEXTEND_SIZE
53 myspace 4194304 0
54 DROP TABLESPACE myspace;
55 # CREATE TABLESPACE with default AUTOEXTEND_SIZE
56 CREATE TABLESPACE myspace ADD DATAFILE 'myspace1.ibd';
57 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
58 WHERE NAME = 'myspace';
59 NAME FILE_SIZE AUTOEXTEND_SIZE
60 myspace 114688 0
61 ALTER TABLESPACE myspace AUTOEXTEND_SIZE=4M;
62 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
63 WHERE NAME = 'myspace';
64 NAME FILE_SIZE AUTOEXTEND_SIZE
65 myspace 114688 4194304
66 ALTER TABLESPACE myspace AUTOEXTEND_SIZE 0;
67 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
68 WHERE NAME = 'myspace';
69 NAME FILE_SIZE AUTOEXTEND_SIZE
70 myspace 114688 0
71 DROP TABLESPACE myspace;
72 # Validate ALTER TABLESPACE cannot alter AUTOEXTEND_SIZE for a table with
73 # file_per_tablespace
74 CREATE TABLE mytable(c1 INT);
75 ALTER TABLESPACE test.mytable AUTOEXTEND_SIZE=1M;
76 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 '.mytable AUTOEXTEND_SIZE=1M' at line 1
77 DROP TABLE mytable;
78 # CREATE TABLE in a general tablespace with AUTOEXTEND_SIZE attribute
79 CREATE TABLESPACE myspace ADD DATAFILE 'myspace.ibd';
80 CREATE TABLE mytable (c1 INT) TABLESPACE myspace AUTOEXTEND_SIZE 4M;
81 ERROR HY000: InnoDB: "AUTOEXTEND_SIZE" not allowed with general tablespaces
82 CREATE TABLE mytable (c1 INT) TABLESPACE myspace AUTOEXTEND_SIZE=1M;
83 ERROR HY000: InnoDB: "AUTOEXTEND_SIZE" not allowed with general tablespaces
84 CREATE TABLE mytable (c1 INT) TABLESPACE myspace;
85 SHOW CREATE TABLE mytable;
86 Table Create Table
87 mytable CREATE TABLE `mytable` (
88 `c1` int DEFAULT NULL
89 ) /*!50100 TABLESPACE `myspace` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
90 ALTER TABLE mytable AUTOEXTEND_SIZE 8M;
91 ERROR HY000: InnoDB: "AUTOEXTEND_SIZE" not allowed with general tablespaces
92 SHOW CREATE TABLE mytable;
93 Table Create Table
94 mytable CREATE TABLE `mytable` (
95 `c1` int DEFAULT NULL
96 ) /*!50100 TABLESPACE `myspace` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
97 DROP TABLE mytable;
98 DROP TABLESPACE myspace;
99 # Validate that AUTOEXTEND_SIZE value is effective when the server
100 # is started with --skip-innodb-validate-tablespace-paths command line option
101 CREATE TABLESPACE myspace ADD DATAFILE 'myspace.ibd' AUTOEXTEND_SIZE 4M;
102 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
103 WHERE NAME LIKE '%myspace%';
104 NAME FILE_SIZE AUTOEXTEND_SIZE
105 myspace 4194304 4194304
106 CREATE TABLE tsp(c1 INT, c2 TEXT) TABLESPACE myspace;
107 CREATE PROCEDURE bulk_insert()
108 BEGIN
109 DECLARE i INT DEFAULT 1;
110 WHILE i < 10000 DO
111 INSERT INTO tsp VALUE(i, repeat('aaaaaa', 10000));
112 COMMIT;
113 SET i = i + 1;
114 END WHILE;
115 END
116 |
117 # restart: --skip-innodb-validate-tablespace-paths
118 call bulk_insert();
119 SELECT COUNT(*) FROM tsp;
120 COUNT(*)
121 9999
122 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
123 WHERE NAME LIKE '%myspace%';
124 NAME FILE_SIZE AUTOEXTEND_SIZE
125 myspace 662700032 4194304
126 DROP TABLE tsp;
127 DROP TABLESPACE myspace;
128 # Validate movement of table from file_per_table tablespace to general tablespace
129 # and vice versa
130 CREATE TABLESPACE myspace ADD DATAFILE 'myspace.ibd' AUTOEXTEND_SIZE 8M;
131 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
132 WHERE NAME LIKE '%myspace%';
133 NAME FILE_SIZE AUTOEXTEND_SIZE
134 myspace 8388608 8388608
135 CREATE TABLE tsp(c1 int) AUTOEXTEND_SIZE 4m;
136 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
137 WHERE NAME LIKE '%tsp%';
138 NAME FILE_SIZE AUTOEXTEND_SIZE
139 test/tsp 4194304 4194304
140 ALTER TABLE tsp TABLESPACE myspace;
141 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
142 WHERE NAME LIKE '%tsp%' OR NAME LIKE '%myspace%';
143 NAME FILE_SIZE AUTOEXTEND_SIZE
144 myspace 8388608 8388608
145 ALTER TABLE tsp TABLESPACE innodb_file_per_table;
146 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
147 WHERE NAME LIKE '%tsp%' OR NAME LIKE '%myspace%';
148 NAME FILE_SIZE AUTOEXTEND_SIZE
149 myspace 8388608 8388608
150 test/tsp 114688 0
151 DROP TABLE tsp;
152 DROP TABLESPACE myspace;
153 DROP PROCEDURE bulk_insert;
154 # Test scenarios with autoextend_size specified while moving the table between
155 # different tablespaces
156 CREATE TABLESPACE myspace ADD DATAFILE 'myspace.ibd' AUTOEXTEND_SIZE 64m;
157 CREATE TABLE tsp(c1 int, c2 text) AUTOEXTEND_SIZE 4m;
158 INSERT INTO tsp VALUES (1, repeat('aaaaaa', 10000));
159 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
160 WHERE NAME LIKE '%tsp%' OR NAME LIKE '%myspace%';
161 NAME FILE_SIZE AUTOEXTEND_SIZE
162 myspace 67108864 67108864
163 test/tsp 4194304 4194304
164 SHOW CREATE TABLE tsp;
165 Table Create Table
166 tsp CREATE TABLE `tsp` (
167 `c1` int DEFAULT NULL,
168 `c2` text
169 ) /*!80023 AUTOEXTEND_SIZE=4194304 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
170 ALTER TABLE tsp TABLESPACE myspace AUTOEXTEND_SIZE 32m;
171 ERROR HY000: InnoDB: "AUTOEXTEND_SIZE" not allowed with general tablespaces
172 ALTER TABLE tsp ADD COLUMN c3 INT, TABLESPACE myspace, AUTOEXTEND_SIZE 32m;
173 ERROR HY000: InnoDB: "AUTOEXTEND_SIZE" not allowed with general tablespaces
174 ALTER TABLE tsp TABLESPACE myspace;
175 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
176 WHERE NAME LIKE '%tsp%' OR NAME LIKE '%myspace%';
177 NAME FILE_SIZE AUTOEXTEND_SIZE
178 myspace 67108864 67108864
179 SHOW CREATE TABLE tsp;
180 Table Create Table
181 tsp CREATE TABLE `tsp` (
182 `c1` int DEFAULT NULL,
183 `c2` text
184 ) /*!50100 TABLESPACE `myspace` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
185 SELECT COUNT(*) FROM tsp;
186 COUNT(*)
187 1
188 CREATE TABLESPACE myspace1 ADD DATAFILE 'myspace1.ibd' AUTOEXTEND_SIZE 4m;
189 ALTER TABLE tsp TABLESPACE myspace1, AUTOEXTEND_SIZE 16M;
190 ERROR HY000: InnoDB: "AUTOEXTEND_SIZE" not allowed with general tablespaces
191 ALTER TABLE tsp TABLESPACE myspace1;
192 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
193 WHERE NAME LIKE '%tsp%' OR NAME LIKE '%myspace%';
194 NAME FILE_SIZE AUTOEXTEND_SIZE
195 myspace 67108864 67108864
196 myspace1 4194304 4194304
197 SHOW CREATE TABLE tsp;
198 Table Create Table
199 tsp CREATE TABLE `tsp` (
200 `c1` int DEFAULT NULL,
201 `c2` text
202 ) /*!50100 TABLESPACE `myspace1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
203 SELECT COUNT(*) FROM tsp;
204 COUNT(*)
205 1
206 ALTER TABLE tsp TABLESPACE innodb_system, AUTOEXTEND_SIZE 4m;
207 ERROR HY000: InnoDB : AUTOEXTEND_SIZE is not accepted for system tablespace.
208 ALTER TABLE tsp TABLESPACE innodb_system;
209 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
210 WHERE NAME LIKE '%tsp%' OR NAME LIKE '%myspace%' OR NAME LIKE '%innodb_system%';
211 NAME FILE_SIZE AUTOEXTEND_SIZE
212 myspace 67108864 67108864
213 myspace1 4194304 4194304
214 SHOW CREATE TABLE tsp;
215 Table Create Table
216 tsp CREATE TABLE `tsp` (
217 `c1` int DEFAULT NULL,
218 `c2` text
219 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
220 SELECT COUNT(*) FROM tsp;
221 COUNT(*)
222 1
223 ALTER TABLE tsp TABLESPACE innodb_file_per_table, AUTOEXTEND_SIZE 8m;
224 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
225 WHERE NAME LIKE '%tsp%' OR NAME LIKE '%myspace%';
226 NAME FILE_SIZE AUTOEXTEND_SIZE
227 myspace 67108864 67108864
228 myspace1 4194304 4194304
229 test/tsp 8388608 8388608
230 SHOW CREATE TABLE tsp;
231 Table Create Table
232 tsp CREATE TABLE `tsp` (
233 `c1` int DEFAULT NULL,
234 `c2` text
235 ) /*!50100 TABLESPACE `innodb_file_per_table` */ /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
236 SELECT COUNT(*) FROM tsp;
237 COUNT(*)
238 1
239 DROP TABLE tsp;
240 DROP TABLESPACE myspace1;
241 DROP TABLESPACE myspace;
242 # restart
243 # Validate AUTOEXTEND_SIZE is not supported on system tablespace
244 # Validate that CREATE TABLE returns error when the table is created with
245 # AUTOEXTEND_SIZE attribute on a system tablespace
246 # Set the default tablespace as a system tablespace
247 SET GLOBAL innodb_file_per_table=0;
248 CREATE TABLE tsystem(c1 INT) ENGINE=InnoDB AUTOEXTEND_SIZE 4M;
249 ERROR HY000: InnoDB : AUTOEXTEND_SIZE is not accepted for system tablespace.
250 SET GLOBAL innodb_file_per_table=1;
251 # Validate AUTOEXTEND_SIZE option works for the data dictionary
252 # tablespace mysql
253 SELECT NAME, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
254 WHERE NAME = 'mysql';
255 NAME AUTOEXTEND_SIZE
256 mysql 0
257 ALTER TABLESPACE mysql AUTOEXTEND_SIZE 4M;
258 SELECT NAME, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
259 WHERE NAME = 'mysql';
260 NAME AUTOEXTEND_SIZE
261 mysql 4194304
262 ALTER TABLESPACE mysql AUTOEXTEND_SIZE 0;
263 SELECT NAME, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
264 WHERE NAME = 'mysql';
265 NAME AUTOEXTEND_SIZE
266 mysql 0
267 # Validate that autoextend_size is not supported for
268 # innodb_temporary and undo tablespaces
269 CREATE UNDO TABLESPACE undotest ADD DATAFILE 'undotest.ibu' AUTOEXTEND_SIZE 4M;
270 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 'AUTOEXTEND_SIZE 4M' at line 1
271 ALTER TABLESPACE innodb_undo_001 AUTOEXTEND_SIZE 4M;
272 ERROR 42000: InnoDB: Tablespace names starting with `innodb_` are reserved.
273 ALTER TABLESPACE innodb_temporary AUTOEXTEND_SIZE 4M;
274 ERROR 42000: InnoDB: `innodb_temporary` is a reserved tablespace name.