Debian Codegraph @

mysql-test/suite/innodb/t/innodb_autoextend_tbsp_ddl.test

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