Debian Codegraph @

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

raw
1 #
2 # A series of tests to show the correct file-per-table tablespace behavior.
3 # See also; parts.partition_basic_symlink_innodb.test for
4 # partition related tests with remote tablespaces.
5 # See innodb.innodb-restart for tablespace migration tests.
6 #
7 SET default_storage_engine=InnoDB;
8 #
9 # TABLESPACE related tests.
10 #
11 #
12 # CREATE TABLE ... INDEX DIRECTORY
13 # In non-strict mode INDEX DIRECTORY is ignored.
14 # In strict mode INDEX DIRECTORY is rejected.
15 #
16 SET SESSION innodb_strict_mode = OFF;
17 CREATE TABLE t1 (a int KEY, b text) INDEX DIRECTORY='MYSQL_TMP_DIR/alt_dir';
18 Warnings:
19 Warning 1618 <INDEX DIRECTORY> option ignored
20 SHOW WARNINGS;
21 Level Code Message
22 Warning 1618 <INDEX DIRECTORY> option ignored
23 SHOW CREATE TABLE t1;
24 Table Create Table
25 t1 CREATE TABLE `t1` (
26 `a` int NOT NULL,
27 `b` text,
28 PRIMARY KEY (`a`)
29 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
30 DROP TABLE t1;
31 SET SESSION innodb_strict_mode = ON;
32 CREATE TABLE t1 (a int KEY, b text) INDEX DIRECTORY='MYSQL_TMP_DIR/alt_dir';
33 ERROR HY000: Table storage engine for 't1' doesn't have this option
34 SHOW WARNINGS;
35 Level Code Message
36 Warning 1478 InnoDB: INDEX DIRECTORY is not supported
37 Error 1031 Table storage engine for 't1' doesn't have this option
38 #
39 # Innodb does not support DATA DIRECTORY without innodb_file_per_table=ON.
40 # In non-strict mode DATA DIRECTORY is ignored.
41 # In strict mode DATA DIRECTORY without innodb_file_per_table=ON is rejected.
42 # TABLESPACE='innodb_file_per_table' overrides the innodb_file_per_table=OFF setting.
43 #
44 SET GLOBAL innodb_file_per_table=OFF;
45 SET SESSION innodb_strict_mode = OFF;
46 CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
47 Warnings:
48 Warning 1478 InnoDB: DATA DIRECTORY requires innodb_file_per_table.
49 Warning 1618 <DATA DIRECTORY> option ignored
50 SHOW WARNINGS;
51 Level Code Message
52 Warning 1478 InnoDB: DATA DIRECTORY requires innodb_file_per_table.
53 Warning 1618 <DATA DIRECTORY> option ignored
54 SHOW CREATE TABLE t1;
55 Table Create Table
56 t1 CREATE TABLE `t1` (
57 `a` int NOT NULL,
58 `b` text,
59 PRIMARY KEY (`a`)
60 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
61 DROP TABLE t1;
62 SET SESSION innodb_strict_mode = ON;
63 CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
64 ERROR HY000: Table storage engine for 't1' doesn't have this option
65 SHOW WARNINGS;
66 Level Code Message
67 Warning 1478 InnoDB: DATA DIRECTORY requires innodb_file_per_table.
68 Error 1031 Table storage engine for 't1' doesn't have this option
69 CREATE TABLE t1 (a int KEY, b text) TABLESPACE=`innodb_file_per_table` DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
70 SHOW CREATE TABLE t1;
71 Table Create Table
72 t1 CREATE TABLE `t1` (
73 `a` int NOT NULL,
74 `b` text,
75 PRIMARY KEY (`a`)
76 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
77 DROP TABLE t1;
78 # Now set innodb_file_per_table so that DATA DIRECTORY can be tested.
79 SET GLOBAL innodb_file_per_table=ON;
80 #
81 # Create the tablespace in MYSQL_TMP_DIR/alt_dir
82 # InnoDB will create the sub-directories if needed.
83 #
84 CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
85 SHOW WARNINGS;
86 Level Code Message
87 INSERT INTO t1 VALUES (1, 'Create the tablespace');
88 SELECT * FROM t1;
89 a b
90 1 Create the tablespace
91 #
92 # Check if link file exists in MYSQLD_DATADIR
93 #
94 ---- MYSQLD_DATADIR/test
95 # Check if tablespace file exists where we specified in DATA DIRECTORY
96 ---- MYSQL_TMP_DIR/alt_dir/test
97 t1.ibd
98 #
99 # Check that DATA DIRECTORY shows up in the SHOW CREATE TABLE results.
100 #
101 SHOW CREATE TABLE t1;
102 Table Create Table
103 t1 CREATE TABLE `t1` (
104 `a` int NOT NULL,
105 `b` text,
106 PRIMARY KEY (`a`)
107 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
108 # Show that the system tables have this tablespace in them correctly
109 === information_schema.innodb_tables and innodb_tablespaces ===
110 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
111 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
112 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
113 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
114 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
115 test/t1 test/t1 97 5 Dynamic 0 Single
116 === information_schema.innodb_tablespaces and innodb_datafiles ===
117 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
118 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
119 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
120 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
121 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
122 test/t1 Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir/test/t1.ibd
123 === information_schema.files ===
124 Space_Name File_Type Engine Status Tablespace_Name Path
125 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
126 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
127 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
128 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
129 test/t1 TABLESPACE InnoDB NORMAL test/t1 MYSQL_TMP_DIR/alt_dir/test/t1.ibd
130 #
131 # Show that the system tables are updated on drop table
132 #
133 DROP TABLE t1;
134 === information_schema.innodb_tables and innodb_tablespaces ===
135 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
136 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
137 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
138 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
139 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
140 === information_schema.innodb_tablespaces and innodb_datafiles ===
141 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
142 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
143 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
144 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
145 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
146 === information_schema.files ===
147 Space_Name File_Type Engine Status Tablespace_Name Path
148 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
149 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
150 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
151 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
152 #
153 # Create the same table a second time in the same place
154 #
155 CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
156 INSERT INTO t1 VALUES
157 (2, 'Create the same table a second time in the same place');
158 SELECT * FROM t1;
159 a b
160 2 Create the same table a second time in the same place
161 SHOW CREATE TABLE t1;
162 Table Create Table
163 t1 CREATE TABLE `t1` (
164 `a` int NOT NULL,
165 `b` text,
166 PRIMARY KEY (`a`)
167 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
168 === information_schema.innodb_tables and innodb_tablespaces ===
169 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
170 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
171 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
172 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
173 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
174 test/t1 test/t1 97 5 Dynamic 0 Single
175 === information_schema.innodb_tablespaces and innodb_datafiles ===
176 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
177 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
178 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
179 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
180 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
181 test/t1 Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir/test/t1.ibd
182 === information_schema.files ===
183 Space_Name File_Type Engine Status Tablespace_Name Path
184 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
185 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
186 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
187 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
188 test/t1 TABLESPACE InnoDB NORMAL test/t1 MYSQL_TMP_DIR/alt_dir/test/t1.ibd
189 ---- MYSQLD_DATADIR/test
190 ---- MYSQL_TMP_DIR/alt_dir/test
191 t1.ibd
192 #
193 # Truncate the table, then insert and verify
194 #
195 TRUNCATE TABLE t1;
196 INSERT INTO t1 VALUES (3, 'Truncate the table, then insert');
197 SELECT * FROM t1;
198 a b
199 3 Truncate the table, then insert
200 === information_schema.innodb_tables and innodb_tablespaces ===
201 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
202 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
203 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
204 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
205 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
206 test/t1 test/t1 97 5 Dynamic 0 Single
207 === information_schema.innodb_tablespaces and innodb_datafiles ===
208 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
209 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
210 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
211 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
212 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
213 test/t1 Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir/test/t1.ibd
214 === information_schema.files ===
215 Space_Name File_Type Engine Status Tablespace_Name Path
216 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
217 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
218 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
219 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
220 test/t1 TABLESPACE InnoDB NORMAL test/t1 MYSQL_TMP_DIR/alt_dir/test/t1.ibd
221 ---- MYSQLD_DATADIR/test
222 ---- MYSQL_TMP_DIR/alt_dir/test
223 t1.ibd
224 #
225 # Rename the table, then insert and verify
226 #
227 RENAME TABLE t1 TO t2;
228 INSERT INTO t2 VALUES (4, 'Rename the table, then insert');
229 SELECT * FROM t2;
230 a b
231 3 Truncate the table, then insert
232 4 Rename the table, then insert
233 === information_schema.innodb_tables and innodb_tablespaces ===
234 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
235 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
236 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
237 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
238 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
239 test/t2 test/t2 97 5 Dynamic 0 Single
240 === information_schema.innodb_tablespaces and innodb_datafiles ===
241 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
242 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
243 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
244 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
245 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
246 test/t2 Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir/test/t2.ibd
247 === information_schema.files ===
248 Space_Name File_Type Engine Status Tablespace_Name Path
249 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
250 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
251 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
252 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
253 test/t2 TABLESPACE InnoDB NORMAL test/t2 MYSQL_TMP_DIR/alt_dir/test/t2.ibd
254 ---- MYSQLD_DATADIR/test
255 ---- MYSQL_TMP_DIR/alt_dir/test
256 t2.ibd
257 #
258 # CREATE TABLE LIKE does not retain DATA DIRECTORY automatically.
259 #
260 CREATE TABLE t3 LIKE t2;
261 INSERT INTO t3 VALUES (5, 'CREATE TABLE LIKE');
262 SELECT * FROM t3;
263 a b
264 5 CREATE TABLE LIKE
265 === information_schema.innodb_tables and innodb_tablespaces ===
266 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
267 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
268 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
269 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
270 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
271 test/t2 test/t2 97 5 Dynamic 0 Single
272 test/t3 test/t3 33 5 Dynamic 0 Single
273 === information_schema.innodb_tablespaces and innodb_datafiles ===
274 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
275 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
276 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
277 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
278 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
279 test/t2 Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir/test/t2.ibd
280 test/t3 Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/test/t3.ibd
281 === information_schema.files ===
282 Space_Name File_Type Engine Status Tablespace_Name Path
283 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
284 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
285 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
286 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
287 test/t2 TABLESPACE InnoDB NORMAL test/t2 MYSQL_TMP_DIR/alt_dir/test/t2.ibd
288 test/t3 TABLESPACE InnoDB NORMAL test/t3 MYSQLD_DATADIR/test/t3.ibd
289 ---- MYSQLD_DATADIR/test
290 t3.ibd
291 #
292 # Now make sure the tables can be fully dropped.
293 #
294 DROP TABLE t2, t3;
295 === information_schema.innodb_tables and innodb_tablespaces ===
296 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
297 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
298 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
299 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
300 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
301 === information_schema.innodb_tablespaces and innodb_datafiles ===
302 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
303 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
304 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
305 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
306 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
307 === information_schema.files ===
308 Space_Name File_Type Engine Status Tablespace_Name Path
309 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
310 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
311 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
312 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
313 ---- MYSQLD_DATADIR/test
314 ---- MYSQL_TMP_DIR/alt_dir/test
315 #
316 # Be sure SQL MODE 'NO_DIR_IN_CREATE' prevents the use of DATA DIRECTORY
317 #
318 SET @org_mode=@@sql_mode;
319 SET @@sql_mode='NO_DIR_IN_CREATE';
320 SELECT @@sql_mode;
321 @@sql_mode
322 NO_DIR_IN_CREATE
323 CREATE TABLE t1 (a int, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
324 Warnings:
325 Warning 1618 <DATA DIRECTORY> option ignored
326 SHOW WARNINGS;
327 Level Code Message
328 Warning 1618 <DATA DIRECTORY> option ignored
329 INSERT INTO t1 VALUES (6, 'SQL MODE NO_DIR_IN_CREATE prevents DATA DIRECTORY');
330 DROP TABLE t1;
331 set @@sql_mode=@org_mode;
332 #
333 # The server does not allow any DATA DIRECTORY name to be a relative path
334 # or to be located in or under MYSQLD_DATADIR.
335 #
336 CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY './';
337 ERROR HY000: Incorrect path value: './'
338 SHOW WARNINGS;
339 Level Code Message
340 Error 1525 Incorrect path value: './'
341 CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY '../';
342 ERROR HY000: Incorrect path value: '../'
343 SHOW WARNINGS;
344 Level Code Message
345 Error 1525 Incorrect path value: '../'
346 CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY 'MYSQLD_DATADIR';
347 ERROR HY000: Incorrect arguments to DATA DIRECTORY
348 SHOW WARNINGS;
349 Level Code Message
350 Error 1210 Incorrect arguments to DATA DIRECTORY
351 CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY 'MYSQLD_DATADIR/under';
352 ERROR HY000: Incorrect arguments to DATA DIRECTORY
353 SHOW WARNINGS;
354 Level Code Message
355 Error 1210 Incorrect arguments to DATA DIRECTORY
356 #
357 # TEMPORARY tables are incompatible with DATA DIRECTORY
358 # In strict mode DATA DIRECTORY is rejected. In non-strict mode it
359 # is ignored.
360 #
361 SET SESSION innodb_strict_mode = OFF;
362 CREATE TEMPORARY TABLE t1 (a int KEY, b text) engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
363 Warnings:
364 Warning 1478 InnoDB: DATA DIRECTORY cannot be used for TEMPORARY tables.
365 Warning 1618 <DATA DIRECTORY> option ignored
366 SHOW WARNINGS;
367 Level Code Message
368 Warning 1478 InnoDB: DATA DIRECTORY cannot be used for TEMPORARY tables.
369 Warning 1618 <DATA DIRECTORY> option ignored
370 SHOW CREATE TABLE t1;
371 Table Create Table
372 t1 CREATE TEMPORARY TABLE `t1` (
373 `a` int NOT NULL,
374 `b` text,
375 PRIMARY KEY (`a`)
376 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
377 DROP TABLE t1;
378 SET SESSION innodb_strict_mode = ON;
379 CREATE TEMPORARY TABLE t1 (a int KEY, b text) engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
380 ERROR HY000: Table storage engine for 't1' doesn't have this option
381 SHOW WARNINGS;
382 Level Code Message
383 Warning 1478 InnoDB: DATA DIRECTORY cannot be used for TEMPORARY tables.
384 Error 1031 Table storage engine for 't1' doesn't have this option
385 #
386 # Create the remote table via static DDL statements in a stored procedure
387 #
388 CREATE PROCEDURE static_proc() BEGIN CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'; END |
389 CALL static_proc;
390 === information_schema.innodb_tablespaces and innodb_datafiles ===
391 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
392 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
393 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
394 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
395 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
396 test/t1 Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir/test/t1.ibd
397 === information_schema.files ===
398 Space_Name File_Type Engine Status Tablespace_Name Path
399 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
400 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
401 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
402 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
403 test/t1 TABLESPACE InnoDB NORMAL test/t1 MYSQL_TMP_DIR/alt_dir/test/t1.ibd
404 INSERT INTO t1 VALUES (7, 'Create the remote table via static DDL statements');
405 SELECT * FROM t1;
406 a b
407 7 Create the remote table via static DDL statements
408 SHOW CREATE TABLE t1;
409 Table Create Table
410 t1 CREATE TABLE `t1` (
411 `a` int NOT NULL,
412 `b` text,
413 PRIMARY KEY (`a`)
414 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
415 ---- MYSQLD_DATADIR/test
416 ---- MYSQL_TMP_DIR/alt_dir/test
417 t1.ibd
418 DROP PROCEDURE static_proc;
419 DROP TABLE t1;
420 #
421 # Create the remote table via dynamic DDL statements in a stored procedure
422 #
423 CREATE PROCEDURE dynamic_proc()
424 BEGIN
425 PREPARE stmt1 FROM
426 "CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'";
427 EXECUTE stmt1;
428 END |
429 CALL dynamic_proc;
430 INSERT INTO t1 VALUES (8, 'Create the remote table via dynamic DDL statements');
431 SELECT * FROM t1;
432 a b
433 8 Create the remote table via dynamic DDL statements
434 SHOW CREATE TABLE t1;
435 Table Create Table
436 t1 CREATE TABLE `t1` (
437 `a` int NOT NULL,
438 `b` text,
439 PRIMARY KEY (`a`)
440 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
441 === information_schema.innodb_tablespaces and innodb_datafiles ===
442 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
443 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
444 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
445 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
446 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
447 test/t1 Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir/test/t1.ibd
448 === information_schema.files ===
449 Space_Name File_Type Engine Status Tablespace_Name Path
450 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
451 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
452 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
453 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
454 test/t1 TABLESPACE InnoDB NORMAL test/t1 MYSQL_TMP_DIR/alt_dir/test/t1.ibd
455 ---- MYSQLD_DATADIR/test
456 ---- MYSQL_TMP_DIR/alt_dir/test
457 t1.ibd
458 DROP PROCEDURE dynamic_proc;
459 DEALLOCATE PREPARE stmt1;
460 DROP TABLE t1;
461 #
462 # CREATE, DROP, ADD and TRUNCATE PARTITION with DATA DIRECTORY
463 #
464 CREATE TABLE emp (
465 id INT NOT NULL,
466 store_name VARCHAR(30),
467 parts VARCHAR(30),
468 store_id INT
469 )
470 PARTITION BY LIST(store_id) (
471 PARTITION east VALUES IN (10,20,30)
472 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east',
473 PARTITION north VALUES IN (40,50,60)
474 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north',
475 PARTITION west VALUES IN (70,80,100)
476 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west'
477 );
478 INSERT INTO emp values(1,'Oracle','NUTT',10);
479 INSERT INTO emp values(2,'HUAWEI','BOLT',40);
480 INSERT INTO emp values(3,'IBM','NAIL',70);
481 SHOW CREATE TABLE emp;
482 Table Create Table
483 emp CREATE TABLE `emp` (
484 `id` int NOT NULL,
485 `store_name` varchar(30) DEFAULT NULL,
486 `parts` varchar(30) DEFAULT NULL,
487 `store_id` int DEFAULT NULL
488 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
489 /*!50100 PARTITION BY LIST (`store_id`)
490 (PARTITION east VALUES IN (10,20,30) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east/' ENGINE = InnoDB,
491 PARTITION north VALUES IN (40,50,60) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north/' ENGINE = InnoDB,
492 PARTITION west VALUES IN (70,80,100) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west/' ENGINE = InnoDB) */
493 SELECT * FROM emp;
494 id store_name parts store_id
495 1 Oracle NUTT 10
496 2 HUAWEI BOLT 40
497 3 IBM NAIL 70
498 === information_schema.innodb_tables and innodb_tablespaces ===
499 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
500 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
501 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
502 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
503 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
504 test/emp#p#east test/emp#p#east 97 7 Dynamic 0 Single
505 test/emp#p#north test/emp#p#north 97 7 Dynamic 0 Single
506 test/emp#p#west test/emp#p#west 97 7 Dynamic 0 Single
507 === information_schema.innodb_tablespaces and innodb_datafiles ===
508 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
509 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
510 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
511 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
512 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
513 test/emp#p#east Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
514 test/emp#p#north Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
515 test/emp#p#west Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west.ibd
516 === information_schema.files ===
517 Space_Name File_Type Engine Status Tablespace_Name Path
518 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
519 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
520 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
521 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
522 test/emp#p#east TABLESPACE InnoDB NORMAL test/emp#p#east MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
523 test/emp#p#north TABLESPACE InnoDB NORMAL test/emp#p#north MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
524 test/emp#p#west TABLESPACE InnoDB NORMAL test/emp#p#west MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west.ibd
525 ---- MYSQLD_DATADIR/test
526 ---- MYSQL_TMP_DIR/alt_dir_east/test
527 emp#p#east.ibd
528 ---- MYSQL_TMP_DIR/alt_dir_north/test
529 emp#p#north.ibd
530 ---- MYSQL_TMP_DIR/alt_dir_west/test
531 emp#p#west.ibd
532 #
533 # DROP one PARTITION.
534 #
535 ALTER TABLE emp DROP PARTITION west;
536 SHOW CREATE TABLE emp;
537 Table Create Table
538 emp CREATE TABLE `emp` (
539 `id` int NOT NULL,
540 `store_name` varchar(30) DEFAULT NULL,
541 `parts` varchar(30) DEFAULT NULL,
542 `store_id` int DEFAULT NULL
543 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
544 /*!50100 PARTITION BY LIST (`store_id`)
545 (PARTITION east VALUES IN (10,20,30) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = InnoDB,
546 PARTITION north VALUES IN (40,50,60) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = InnoDB) */
547 SELECT * FROM emp;
548 id store_name parts store_id
549 1 Oracle NUTT 10
550 2 HUAWEI BOLT 40
551 === information_schema.innodb_tables and innodb_tablespaces ===
552 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
553 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
554 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
555 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
556 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
557 test/emp#p#east test/emp#p#east 97 7 Dynamic 0 Single
558 test/emp#p#north test/emp#p#north 97 7 Dynamic 0 Single
559 === information_schema.innodb_tablespaces and innodb_datafiles ===
560 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
561 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
562 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
563 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
564 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
565 test/emp#p#east Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
566 test/emp#p#north Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
567 === information_schema.files ===
568 Space_Name File_Type Engine Status Tablespace_Name Path
569 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
570 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
571 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
572 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
573 test/emp#p#east TABLESPACE InnoDB NORMAL test/emp#p#east MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
574 test/emp#p#north TABLESPACE InnoDB NORMAL test/emp#p#north MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
575 ---- MYSQLD_DATADIR/test
576 ---- MYSQL_TMP_DIR/alt_dir_east/test
577 emp#p#east.ibd
578 ---- MYSQL_TMP_DIR/alt_dir_north/test
579 emp#p#north.ibd
580 ---- MYSQL_TMP_DIR/alt_dir_west/test
581 #
582 # ADD the PARTITION back.
583 #
584 ALTER TABLE emp ADD PARTITION (
585 PARTITION west VALUES IN (70,80,100)
586 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west');
587 INSERT INTO emp VALUES(3,'IBM','NAIL',70);
588 SELECT * FROM emp;
589 id store_name parts store_id
590 1 Oracle NUTT 10
591 2 HUAWEI BOLT 40
592 3 IBM NAIL 70
593 === information_schema.innodb_tables and innodb_tablespaces ===
594 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
595 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
596 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
597 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
598 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
599 test/emp#p#east test/emp#p#east 97 7 Dynamic 0 Single
600 test/emp#p#north test/emp#p#north 97 7 Dynamic 0 Single
601 test/emp#p#west test/emp#p#west 97 7 Dynamic 0 Single
602 === information_schema.innodb_tablespaces and innodb_datafiles ===
603 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
604 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
605 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
606 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
607 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
608 test/emp#p#east Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
609 test/emp#p#north Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
610 test/emp#p#west Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west.ibd
611 === information_schema.files ===
612 Space_Name File_Type Engine Status Tablespace_Name Path
613 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
614 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
615 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
616 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
617 test/emp#p#east TABLESPACE InnoDB NORMAL test/emp#p#east MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
618 test/emp#p#north TABLESPACE InnoDB NORMAL test/emp#p#north MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
619 test/emp#p#west TABLESPACE InnoDB NORMAL test/emp#p#west MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west.ibd
620 ---- MYSQLD_DATADIR/test
621 ---- MYSQL_TMP_DIR/alt_dir_east/test
622 emp#p#east.ibd
623 ---- MYSQL_TMP_DIR/alt_dir_north/test
624 emp#p#north.ibd
625 ---- MYSQL_TMP_DIR/alt_dir_west/test
626 emp#p#west.ibd
627 #
628 # TRUNCATE one PARTITION.
629 #
630 ALTER TABLE emp TRUNCATE PARTITION west;
631 SHOW CREATE TABLE emp;
632 Table Create Table
633 emp CREATE TABLE `emp` (
634 `id` int NOT NULL,
635 `store_name` varchar(30) DEFAULT NULL,
636 `parts` varchar(30) DEFAULT NULL,
637 `store_id` int DEFAULT NULL
638 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
639 /*!50100 PARTITION BY LIST (`store_id`)
640 (PARTITION east VALUES IN (10,20,30) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = InnoDB,
641 PARTITION north VALUES IN (40,50,60) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = InnoDB,
642 PARTITION west VALUES IN (70,80,100) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' ENGINE = InnoDB) */
643 INSERT INTO emp VALUES(3,'IBM','NAIL',70);
644 SELECT * FROM emp;
645 id store_name parts store_id
646 1 Oracle NUTT 10
647 2 HUAWEI BOLT 40
648 3 IBM NAIL 70
649 === information_schema.innodb_tables and innodb_tablespaces ===
650 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
651 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
652 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
653 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
654 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
655 test/emp#p#east test/emp#p#east 97 7 Dynamic 0 Single
656 test/emp#p#north test/emp#p#north 97 7 Dynamic 0 Single
657 test/emp#p#west test/emp#p#west 97 7 Dynamic 0 Single
658 === information_schema.innodb_tablespaces and innodb_datafiles ===
659 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
660 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
661 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
662 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
663 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
664 test/emp#p#east Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
665 test/emp#p#north Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
666 test/emp#p#west Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west.ibd
667 === information_schema.files ===
668 Space_Name File_Type Engine Status Tablespace_Name Path
669 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
670 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
671 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
672 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
673 test/emp#p#east TABLESPACE InnoDB NORMAL test/emp#p#east MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
674 test/emp#p#north TABLESPACE InnoDB NORMAL test/emp#p#north MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
675 test/emp#p#west TABLESPACE InnoDB NORMAL test/emp#p#west MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west.ibd
676 ---- MYSQLD_DATADIR/test
677 ---- MYSQL_TMP_DIR/alt_dir_east/test
678 emp#p#east.ibd
679 ---- MYSQL_TMP_DIR/alt_dir_north/test
680 emp#p#north.ibd
681 ---- MYSQL_TMP_DIR/alt_dir_west/test
682 emp#p#west.ibd
683 DROP TABLE emp;
684 #
685 # Cleanup
686 #