Debian Codegraph @

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

raw
1 --echo #
2 --echo # A series of tests to show the correct file-per-table tablespace behavior.
3 --echo # See also; parts.partition_basic_symlink_innodb.test for
4 --echo # partition related tests with remote tablespaces.
5 --echo # See innodb.innodb-restart for tablespace migration tests.
6 --echo #
7
8 SET default_storage_engine=InnoDB;
9
10 --echo #
11 --echo # TABLESPACE related tests.
12 --echo #
13
14 # Set up some variables
15 LET $MYSQLD_DATADIR = `select @@datadir`;
16 LET $INNODB_PAGE_SIZE = `select @@innodb_page_size`;
17 LET $data_directory_clause = DATA DIRECTORY='$MYSQL_TMP_DIR/alt_dir';
18 LET $index_directory_clause = INDEX DIRECTORY='$MYSQL_TMP_DIR/alt_dir';
19
20 --echo #
21 --echo # CREATE TABLE ... INDEX DIRECTORY
22 --echo # In non-strict mode INDEX DIRECTORY is ignored.
23 --echo # In strict mode INDEX DIRECTORY is rejected.
24 --echo #
25 SET SESSION innodb_strict_mode = OFF;
26 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
27 eval CREATE TABLE t1 (a int KEY, b text) $index_directory_clause;
28 SHOW WARNINGS;
29 SHOW CREATE TABLE t1;
30 DROP TABLE t1;
31
32 SET SESSION innodb_strict_mode = ON;
33 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
34 --error ER_ILLEGAL_HA
35 eval CREATE TABLE t1 (a int KEY, b text) $index_directory_clause;
36 SHOW WARNINGS;
37
38 --echo #
39 --echo # Innodb does not support DATA DIRECTORY without innodb_file_per_table=ON.
40 --echo # In non-strict mode DATA DIRECTORY is ignored.
41 --echo # In strict mode DATA DIRECTORY without innodb_file_per_table=ON is rejected.
42 --echo # TABLESPACE='innodb_file_per_table' overrides the innodb_file_per_table=OFF setting.
43 --echo #
44 SET GLOBAL innodb_file_per_table=OFF;
45 SET SESSION innodb_strict_mode = OFF;
46 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
47 eval CREATE TABLE t1 (a int KEY, b text) $data_directory_clause;
48 SHOW WARNINGS;
49 SHOW CREATE TABLE t1;
50 DROP TABLE t1;
51
52 SET SESSION innodb_strict_mode = ON;
53 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
54 --error ER_ILLEGAL_HA
55 eval CREATE TABLE t1 (a int KEY, b text) $data_directory_clause;
56 SHOW WARNINGS;
57
58 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
59 eval CREATE TABLE t1 (a int KEY, b text) TABLESPACE=`innodb_file_per_table` $data_directory_clause;
60 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
61 SHOW CREATE TABLE t1;
62 DROP TABLE t1;
63
64 --echo # Now set innodb_file_per_table so that DATA DIRECTORY can be tested.
65 SET GLOBAL innodb_file_per_table=ON;
66
67 --echo #
68 --echo # Create the tablespace in MYSQL_TMP_DIR/alt_dir
69 --echo # InnoDB will create the sub-directories if needed.
70 --echo #
71 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
72 eval CREATE TABLE t1 (a int KEY, b text) $data_directory_clause;
73 SHOW WARNINGS;
74 INSERT INTO t1 VALUES (1, 'Create the tablespace');
75 SELECT * FROM t1;
76
77 --echo #
78 --echo # Check if link file exists in MYSQLD_DATADIR
79 --echo #
80 --echo ---- MYSQLD_DATADIR/test
81 --list_files $MYSQLD_DATADIR/test
82 --echo # Check if tablespace file exists where we specified in DATA DIRECTORY
83 --echo ---- MYSQL_TMP_DIR/alt_dir/test
84 --list_files $MYSQL_TMP_DIR/alt_dir/test
85
86 --echo #
87 --echo # Check that DATA DIRECTORY shows up in the SHOW CREATE TABLE results.
88 --echo #
89 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
90 SHOW CREATE TABLE t1;
91
92 --echo # Show that the system tables have this tablespace in them correctly
93 --source suite/innodb/include/show_i_s_tables.inc
94 --source suite/innodb/include/show_i_s_tablespaces.inc
95
96 --echo #
97 --echo # Show that the system tables are updated on drop table
98 --echo #
99 DROP TABLE t1;
100 --source suite/innodb/include/show_i_s_tables.inc
101 --source suite/innodb/include/show_i_s_tablespaces.inc
102
103 --echo #
104 --echo # Create the same table a second time in the same place
105 --echo #
106 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
107 eval CREATE TABLE t1 (a int KEY, b text) $data_directory_clause;
108
109 INSERT INTO t1 VALUES
110 (2, 'Create the same table a second time in the same place');
111 SELECT * FROM t1;
112 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
113 SHOW CREATE TABLE t1;
114 --source suite/innodb/include/show_i_s_tables.inc
115 --source suite/innodb/include/show_i_s_tablespaces.inc
116 --echo ---- MYSQLD_DATADIR/test
117 --list_files $MYSQLD_DATADIR/test
118 --echo ---- MYSQL_TMP_DIR/alt_dir/test
119 --list_files $MYSQL_TMP_DIR/alt_dir/test
120
121 --echo #
122 --echo # Truncate the table, then insert and verify
123 --echo #
124 TRUNCATE TABLE t1;
125 INSERT INTO t1 VALUES (3, 'Truncate the table, then insert');
126 SELECT * FROM t1;
127 --source suite/innodb/include/show_i_s_tables.inc
128 --source suite/innodb/include/show_i_s_tablespaces.inc
129 --echo ---- MYSQLD_DATADIR/test
130 --list_files $MYSQLD_DATADIR/test
131 --echo ---- MYSQL_TMP_DIR/alt_dir/test
132 --list_files $MYSQL_TMP_DIR/alt_dir/test
133
134 --echo #
135 --echo # Rename the table, then insert and verify
136 --echo #
137 RENAME TABLE t1 TO t2;
138 INSERT INTO t2 VALUES (4, 'Rename the table, then insert');
139 SELECT * FROM t2;
140 --source suite/innodb/include/show_i_s_tables.inc
141 --source suite/innodb/include/show_i_s_tablespaces.inc
142 --echo ---- MYSQLD_DATADIR/test
143 --list_files $MYSQLD_DATADIR/test
144 --echo ---- MYSQL_TMP_DIR/alt_dir/test
145 --list_files $MYSQL_TMP_DIR/alt_dir/test
146
147 --echo #
148 --echo # CREATE TABLE LIKE does not retain DATA DIRECTORY automatically.
149 --echo #
150 CREATE TABLE t3 LIKE t2;
151 INSERT INTO t3 VALUES (5, 'CREATE TABLE LIKE');
152 SELECT * FROM t3;
153 --source suite/innodb/include/show_i_s_tables.inc
154 --source suite/innodb/include/show_i_s_tablespaces.inc
155 --echo ---- MYSQLD_DATADIR/test
156 --list_files $MYSQLD_DATADIR/test
157
158 --echo #
159 --echo # Now make sure the tables can be fully dropped.
160 --echo #
161 DROP TABLE t2, t3;
162 --source suite/innodb/include/show_i_s_tables.inc
163 --source suite/innodb/include/show_i_s_tablespaces.inc
164 --echo ---- MYSQLD_DATADIR/test
165 --list_files $MYSQLD_DATADIR/test
166 --echo ---- MYSQL_TMP_DIR/alt_dir/test
167 --list_files $MYSQL_TMP_DIR/alt_dir/test
168
169 --echo #
170 --echo # Be sure SQL MODE 'NO_DIR_IN_CREATE' prevents the use of DATA DIRECTORY
171 --echo #
172 SET @org_mode=@@sql_mode;
173 SET @@sql_mode='NO_DIR_IN_CREATE';
174 SELECT @@sql_mode;
175 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
176 eval CREATE TABLE t1 (a int, b text) $data_directory_clause;
177 SHOW WARNINGS;
178 INSERT INTO t1 VALUES (6, 'SQL MODE NO_DIR_IN_CREATE prevents DATA DIRECTORY');
179 # Checking if tablespace exists in --datadir since DATA DIRECTORY was ignored.
180 --file_exists $MYSQLD_DATADIR/test/t1.ibd
181 DROP TABLE t1;
182 set @@sql_mode=@org_mode;
183
184 --echo #
185 --echo # The server does not allow any DATA DIRECTORY name to be a relative path
186 --echo # or to be located in or under MYSQLD_DATADIR.
187 --echo #
188 --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
189 --error ER_WRONG_VALUE
190 eval CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY './';
191 SHOW WARNINGS;
192
193 --error ER_WRONG_VALUE
194 eval CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY '../';
195 SHOW WARNINGS;
196
197 --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
198 --error ER_WRONG_ARGUMENTS
199 eval CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY '$MYSQLD_DATADIR';
200 SHOW WARNINGS;
201
202 --mkdir $MYSQLD_DATADIR/under
203 --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
204 --error ER_WRONG_ARGUMENTS
205 eval CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY '$MYSQLD_DATADIR/under';
206 SHOW WARNINGS;
207 --rmdir $MYSQLD_DATADIR/under
208
209 --echo #
210 --echo # TEMPORARY tables are incompatible with DATA DIRECTORY
211 --echo # In strict mode DATA DIRECTORY is rejected. In non-strict mode it
212 --echo # is ignored.
213 --echo #
214 SET SESSION innodb_strict_mode = OFF;
215 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
216 eval CREATE TEMPORARY TABLE t1 (a int KEY, b text) engine=InnoDB $data_directory_clause;
217 SHOW WARNINGS;
218 SHOW CREATE TABLE t1;
219 DROP TABLE t1;
220
221 SET SESSION innodb_strict_mode = ON;
222 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
223 --error ER_ILLEGAL_HA
224 eval CREATE TEMPORARY TABLE t1 (a int KEY, b text) engine=InnoDB $data_directory_clause;
225 SHOW WARNINGS;
226
227 --echo #
228 --echo # Create the remote table via static DDL statements in a stored procedure
229 --echo #
230 DELIMITER |;
231 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
232 eval CREATE PROCEDURE static_proc() BEGIN CREATE TABLE t1 (a int KEY, b text) $data_directory_clause; END |
233 DELIMITER ;|
234 CALL static_proc;
235 --source suite/innodb/include/show_i_s_tablespaces.inc
236 INSERT INTO t1 VALUES (7, 'Create the remote table via static DDL statements');
237 SELECT * FROM t1;
238 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
239 SHOW CREATE TABLE t1;
240 --echo ---- MYSQLD_DATADIR/test
241 --list_files $MYSQLD_DATADIR/test
242 --echo ---- MYSQL_TMP_DIR/alt_dir/test
243 --list_files $MYSQL_TMP_DIR/alt_dir/test
244 DROP PROCEDURE static_proc;
245 DROP TABLE t1;
246
247 --echo #
248 --echo # Create the remote table via dynamic DDL statements in a stored procedure
249 --echo #
250 DELIMITER |;
251 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
252 eval CREATE PROCEDURE dynamic_proc()
253 BEGIN
254 PREPARE stmt1 FROM
255 "CREATE TABLE t1 (a int KEY, b text) $data_directory_clause";
256 EXECUTE stmt1;
257 END |
258 DELIMITER ;|
259 CALL dynamic_proc;
260 INSERT INTO t1 VALUES (8, 'Create the remote table via dynamic DDL statements');
261 SELECT * FROM t1;
262 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
263 SHOW CREATE TABLE t1;
264 --source suite/innodb/include/show_i_s_tablespaces.inc
265 --echo ---- MYSQLD_DATADIR/test
266 --list_files $MYSQLD_DATADIR/test
267 --echo ---- MYSQL_TMP_DIR/alt_dir/test
268 --list_files $MYSQL_TMP_DIR/alt_dir/test
269 DROP PROCEDURE dynamic_proc;
270 DEALLOCATE PREPARE stmt1;
271 DROP TABLE t1;
272
273 --echo #
274 --echo # CREATE, DROP, ADD and TRUNCATE PARTITION with DATA DIRECTORY
275 --echo #
276 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
277 eval CREATE TABLE emp (
278 id INT NOT NULL,
279 store_name VARCHAR(30),
280 parts VARCHAR(30),
281 store_id INT
282 )
283 PARTITION BY LIST(store_id) (
284 PARTITION east VALUES IN (10,20,30)
285 DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_east',
286 PARTITION north VALUES IN (40,50,60)
287 DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_north',
288 PARTITION west VALUES IN (70,80,100)
289 DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_west'
290 );
291
292 INSERT INTO emp values(1,'Oracle','NUTT',10);
293 INSERT INTO emp values(2,'HUAWEI','BOLT',40);
294 INSERT INTO emp values(3,'IBM','NAIL',70);
295
296 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
297 SHOW CREATE TABLE emp;
298 SELECT * FROM emp;
299 # InnoDB always converts table names to lower case on Windows
300 --source suite/innodb/include/show_i_s_tables.inc
301 --source suite/innodb/include/show_i_s_tablespaces.inc
302 --echo ---- MYSQLD_DATADIR/test
303 --replace_regex /emp#P#/emp#p#/
304 --list_files $MYSQLD_DATADIR/test
305 --echo ---- MYSQL_TMP_DIR/alt_dir_east/test
306 --replace_regex /emp#P#/emp#p#/
307 --list_files $MYSQL_TMP_DIR/alt_dir_east/test
308 --echo ---- MYSQL_TMP_DIR/alt_dir_north/test
309 --replace_regex /emp#P#/emp#p#/
310 --list_files $MYSQL_TMP_DIR/alt_dir_north/test
311 --echo ---- MYSQL_TMP_DIR/alt_dir_west/test
312 --replace_regex /emp#P#/emp#p#/
313 --list_files $MYSQL_TMP_DIR/alt_dir_west/test
314
315 --echo #
316 --echo # DROP one PARTITION.
317 --echo #
318 ALTER TABLE emp DROP PARTITION west;
319 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
320 SHOW CREATE TABLE emp;
321 SELECT * FROM emp;
322 --source suite/innodb/include/show_i_s_tables.inc
323 --source suite/innodb/include/show_i_s_tablespaces.inc
324 --echo ---- MYSQLD_DATADIR/test
325 --replace_regex /emp#P#/emp#p#/
326 --list_files $MYSQLD_DATADIR/test
327 --echo ---- MYSQL_TMP_DIR/alt_dir_east/test
328 --replace_regex /emp#P#/emp#p#/
329 --list_files $MYSQL_TMP_DIR/alt_dir_east/test
330 --echo ---- MYSQL_TMP_DIR/alt_dir_north/test
331 --replace_regex /emp#P#/emp#p#/
332 --list_files $MYSQL_TMP_DIR/alt_dir_north/test
333 --echo ---- MYSQL_TMP_DIR/alt_dir_west/test
334 --replace_regex /emp#P#/emp#p#/
335 --list_files $MYSQL_TMP_DIR/alt_dir_west/test
336
337 --echo #
338 --echo # ADD the PARTITION back.
339 --echo #
340 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
341 eval ALTER TABLE emp ADD PARTITION (
342 PARTITION west VALUES IN (70,80,100)
343 DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_west');
344 INSERT INTO emp VALUES(3,'IBM','NAIL',70);
345 SELECT * FROM emp;
346 --source suite/innodb/include/show_i_s_tables.inc
347 --source suite/innodb/include/show_i_s_tablespaces.inc
348 --echo ---- MYSQLD_DATADIR/test
349 --replace_regex /emp#P#/emp#p#/
350 --list_files $MYSQLD_DATADIR/test
351 --echo ---- MYSQL_TMP_DIR/alt_dir_east/test
352 --replace_regex /emp#P#/emp#p#/
353 --list_files $MYSQL_TMP_DIR/alt_dir_east/test
354 --echo ---- MYSQL_TMP_DIR/alt_dir_north/test
355 --replace_regex /emp#P#/emp#p#/
356 --list_files $MYSQL_TMP_DIR/alt_dir_north/test
357 --echo ---- MYSQL_TMP_DIR/alt_dir_west/test
358 --replace_regex /emp#P#/emp#p#/
359 --list_files $MYSQL_TMP_DIR/alt_dir_west/test
360
361 --echo #
362 --echo # TRUNCATE one PARTITION.
363 --echo #
364 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
365 ALTER TABLE emp TRUNCATE PARTITION west;
366 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
367 SHOW CREATE TABLE emp;
368 INSERT INTO emp VALUES(3,'IBM','NAIL',70);
369 SELECT * FROM emp;
370 --source suite/innodb/include/show_i_s_tables.inc
371 --source suite/innodb/include/show_i_s_tablespaces.inc
372 --echo ---- MYSQLD_DATADIR/test
373 --replace_regex /emp#P#/emp#p#/
374 --list_files $MYSQLD_DATADIR/test
375 --echo ---- MYSQL_TMP_DIR/alt_dir_east/test
376 --replace_regex /emp#P#/emp#p#/
377 --list_files $MYSQL_TMP_DIR/alt_dir_east/test
378 --echo ---- MYSQL_TMP_DIR/alt_dir_north/test
379 --replace_regex /emp#P#/emp#p#/
380 --list_files $MYSQL_TMP_DIR/alt_dir_north/test
381 --echo ---- MYSQL_TMP_DIR/alt_dir_west/test
382 --replace_regex /emp#P#/emp#p#/
383 --list_files $MYSQL_TMP_DIR/alt_dir_west/test
384 DROP TABLE emp;
385
386 --echo #
387 --echo # Cleanup
388 --echo #
389
390 --rmdir $MYSQL_TMP_DIR/alt_dir_east/test
391 --rmdir $MYSQL_TMP_DIR/alt_dir_east
392 --rmdir $MYSQL_TMP_DIR/alt_dir_north/test
393 --rmdir $MYSQL_TMP_DIR/alt_dir_north
394 --rmdir $MYSQL_TMP_DIR/alt_dir_west/test
395 --rmdir $MYSQL_TMP_DIR/alt_dir_west
396 --rmdir $MYSQL_TMP_DIR/alt_dir/test
397 --rmdir $MYSQL_TMP_DIR/alt_dir