Debian Codegraph @

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

raw
1 SET default_storage_engine=InnoDB;
2 #
3 # TABLESPACE related tests.
4 #
5 #
6 # Restart with innodb-directories set to MYSQL_TMP_DIR.
7 #
8 # restart: --innodb-directories=MYSQL_TMP_DIR
9 #
10 # CREATE TABLE ... DATA DIRECTORY
11 # Innodb does not support INDEX DIRECTORY.
12 #
13 SET SESSION innodb_strict_mode = ON;
14 CREATE TABLE t1 (a int KEY, b text) INDEX DIRECTORY='MYSQL_TMP_DIR/alt_dir';
15 ERROR HY000: Table storage engine for 't1' doesn't have this option
16 SHOW WARNINGS;
17 Level Code Message
18 Warning 1478 InnoDB: INDEX DIRECTORY is not supported
19 Error 1031 Table storage engine for 't1' doesn't have this option
20 #
21 # Without strict mode, INDEX DIRECTORY is just ignored
22 #
23 SET SESSION innodb_strict_mode = OFF;
24 CREATE TABLE t1 (a int KEY, b text) INDEX DIRECTORY='MYSQL_TMP_DIR/alt_dir';
25 Warnings:
26 Warning 1618 <INDEX DIRECTORY> option ignored
27 SHOW WARNINGS;
28 Level Code Message
29 Warning 1618 <INDEX DIRECTORY> option ignored
30 SHOW CREATE TABLE t1;
31 Table Create Table
32 t1 CREATE TABLE `t1` (
33 `a` int NOT NULL,
34 `b` text,
35 PRIMARY KEY (`a`)
36 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
37 DROP TABLE t1;
38 #
39 # Innodb does not support DATA DIRECTORY without innodb_file_per_table=ON.
40 #
41 SET SESSION innodb_strict_mode = ON;
42 SET GLOBAL innodb_file_per_table=OFF;
43 CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
44 ERROR HY000: Table storage engine for 't1' doesn't have this option
45 SHOW WARNINGS;
46 Level Code Message
47 Warning 1478 InnoDB: DATA DIRECTORY requires innodb_file_per_table.
48 Error 1031 Table storage engine for 't1' doesn't have this option
49 #
50 # Without strict mode, DATA DIRECTORY without innodb_file_per_table=ON is just ignored.
51 #
52 SET SESSION innodb_strict_mode = OFF;
53 CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
54 Warnings:
55 Warning 1478 InnoDB: DATA DIRECTORY requires innodb_file_per_table.
56 Warning 1618 <DATA DIRECTORY> option ignored
57 SHOW WARNINGS;
58 Level Code Message
59 Warning 1478 InnoDB: DATA DIRECTORY requires innodb_file_per_table.
60 Warning 1618 <DATA DIRECTORY> option ignored
61 SHOW CREATE TABLE t1;
62 Table Create Table
63 t1 CREATE TABLE `t1` (
64 `a` int NOT NULL,
65 `b` text,
66 PRIMARY KEY (`a`)
67 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
68 DROP TABLE t1;
69 # Now set innodb_file_per_table so that DATA DIRECTORY can be tested.
70 SET GLOBAL innodb_file_per_table=ON;
71 #
72 # Create the tablespace in MYSQL_TMP_DIR/alt_dir
73 # InnoDB will create the sub-directories if needed.
74 #
75 CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
76 SHOW WARNINGS;
77 Level Code Message
78 INSERT INTO t1 VALUES (1, "Create the tablespace");
79 SELECT * FROM t1;
80 a b
81 1 Create the tablespace
82 #
83 # Check if link file exists in MYSQLD_DATADIR
84 #
85 ---- MYSQLD_DATADIR/test
86 # Check if tablespace file exists where we specified in DATA DIRECTORY
87 ---- MYSQL_TMP_DIR/alt_dir/test
88 t1.ibd
89 #
90 # Check that DATA DIRECTORY shows up in the SHOW CREATE TABLE results.
91 #
92 SHOW CREATE TABLE t1;
93 Table Create Table
94 t1 CREATE TABLE `t1` (
95 `a` int NOT NULL,
96 `b` text,
97 PRIMARY KEY (`a`)
98 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
99 # Show that the new system tables have this table in them correctly
100 === information_schema.innodb_tables and innodb_tablespaces ===
101 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
102 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
103 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
104 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
105 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
106 test/t1 test/t1 97 5 Dynamic 0 Single
107 === information_schema.innodb_tablespaces and innodb_datafiles ===
108 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
109 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
110 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
111 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
112 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
113 test/t1 Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir/test/t1.ibd
114 === information_schema.files ===
115 Space_Name File_Type Engine Status Tablespace_Name Path
116 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
117 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
118 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
119 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
120 test/t1 TABLESPACE InnoDB NORMAL test/t1 MYSQL_TMP_DIR/alt_dir/test/t1.ibd
121 #
122 # Show that the system tables are updated on drop table
123 #
124 DROP TABLE t1;
125 === information_schema.innodb_tables and innodb_tablespaces ===
126 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
127 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
128 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
129 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
130 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
131 === information_schema.innodb_tablespaces and innodb_datafiles ===
132 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
133 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
134 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
135 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
136 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
137 === information_schema.files ===
138 Space_Name File_Type Engine Status Tablespace_Name Path
139 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
140 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
141 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
142 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
143 #
144 # Create the same table a second time in the same place
145 #
146 CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
147 INSERT INTO t1 VALUES (2, "Create the same table a second time in the same place");
148 SELECT * FROM t1;
149 a b
150 2 Create the same table a second time in the same place
151 SHOW CREATE TABLE t1;
152 Table Create Table
153 t1 CREATE TABLE `t1` (
154 `a` int NOT NULL,
155 `b` text,
156 PRIMARY KEY (`a`)
157 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
158 === information_schema.innodb_tables and innodb_tablespaces ===
159 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
160 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
161 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
162 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
163 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
164 test/t1 test/t1 97 5 Dynamic 0 Single
165 === information_schema.innodb_tablespaces and innodb_datafiles ===
166 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
167 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
168 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
169 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
170 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
171 test/t1 Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir/test/t1.ibd
172 === information_schema.files ===
173 Space_Name File_Type Engine Status Tablespace_Name Path
174 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
175 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
176 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
177 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
178 test/t1 TABLESPACE InnoDB NORMAL test/t1 MYSQL_TMP_DIR/alt_dir/test/t1.ibd
179 ---- MYSQLD_DATADIR/test
180 ---- MYSQL_TMP_DIR/alt_dir/test
181 t1.ibd
182 #
183 # Truncate the table, then insert and verify
184 #
185 TRUNCATE TABLE t1;
186 INSERT INTO t1 VALUES (3, "Truncate the table, then insert");
187 SELECT * FROM t1;
188 a b
189 3 Truncate the table, then insert
190 === information_schema.innodb_tables and innodb_tablespaces ===
191 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
192 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
193 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
194 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
195 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
196 test/t1 test/t1 97 5 Dynamic 0 Single
197 === information_schema.innodb_tablespaces and innodb_datafiles ===
198 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
199 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
200 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
201 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
202 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
203 test/t1 Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir/test/t1.ibd
204 === information_schema.files ===
205 Space_Name File_Type Engine Status Tablespace_Name Path
206 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
207 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
208 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
209 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
210 test/t1 TABLESPACE InnoDB NORMAL test/t1 MYSQL_TMP_DIR/alt_dir/test/t1.ibd
211 SELECT name, server_version, space_version FROM information_schema.innodb_tablespaces where name="mysql";
212 name server_version space_version
213 mysql X.Y.Z 1
214 ---- MYSQLD_DATADIR/test
215 ---- MYSQL_TMP_DIR/alt_dir/test
216 t1.ibd
217 #
218 # Rename the table, then insert and verify
219 #
220 RENAME TABLE t1 TO t2;
221 INSERT INTO t2 VALUES (4, "Rename the table, then insert");
222 SELECT * FROM t2;
223 a b
224 3 Truncate the table, then insert
225 4 Rename the table, then insert
226 === information_schema.innodb_tables and innodb_tablespaces ===
227 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
228 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
229 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
230 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
231 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
232 test/t2 test/t2 97 5 Dynamic 0 Single
233 === information_schema.innodb_tablespaces and innodb_datafiles ===
234 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
235 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
236 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
237 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
238 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
239 test/t2 Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir/test/t2.ibd
240 === information_schema.files ===
241 Space_Name File_Type Engine Status Tablespace_Name Path
242 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
243 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
244 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
245 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
246 test/t2 TABLESPACE InnoDB NORMAL test/t2 MYSQL_TMP_DIR/alt_dir/test/t2.ibd
247 ---- MYSQLD_DATADIR/test
248 ---- MYSQL_TMP_DIR/alt_dir/test
249 t2.ibd
250 #
251 # CREATE TABLE LIKE does not retain DATA DIRECTORY automatically.
252 #
253 CREATE TABLE t3 LIKE t2;
254 INSERT INTO t3 VALUES (5, "CREATE TABLE LIKE");
255 SELECT * FROM t3;
256 a b
257 5 CREATE TABLE LIKE
258 === information_schema.innodb_tables and innodb_tablespaces ===
259 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
260 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
261 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
262 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
263 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
264 test/t2 test/t2 97 5 Dynamic 0 Single
265 test/t3 test/t3 33 5 Dynamic 0 Single
266 === information_schema.innodb_tablespaces and innodb_datafiles ===
267 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
268 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
269 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
270 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
271 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
272 test/t2 Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir/test/t2.ibd
273 test/t3 Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/test/t3.ibd
274 === information_schema.files ===
275 Space_Name File_Type Engine Status Tablespace_Name Path
276 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
277 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
278 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
279 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
280 test/t2 TABLESPACE InnoDB NORMAL test/t2 MYSQL_TMP_DIR/alt_dir/test/t2.ibd
281 test/t3 TABLESPACE InnoDB NORMAL test/t3 MYSQLD_DATADIR/test/t3.ibd
282 ---- MYSQLD_DATADIR/test
283 t3.ibd
284 #
285 # Now make sure the tables can be fully dropped.
286 #
287 DROP TABLE t2, t3;
288 === information_schema.innodb_tables and innodb_tablespaces ===
289 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
290 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
291 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
292 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
293 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
294 === information_schema.innodb_tablespaces and innodb_datafiles ===
295 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
296 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
297 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
298 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
299 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
300 === information_schema.files ===
301 Space_Name File_Type Engine Status Tablespace_Name Path
302 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
303 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
304 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
305 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
306 ---- MYSQLD_DATADIR/test
307 ---- MYSQL_TMP_DIR/alt_dir/test
308 #
309 # Be sure SQL MODE "NO_DIR_IN_CREATE" prevents the use of DATA DIRECTORY
310 #
311 SET @org_mode=@@sql_mode;
312 SET @@sql_mode='NO_DIR_IN_CREATE';
313 SELECT @@sql_mode;
314 @@sql_mode
315 NO_DIR_IN_CREATE
316 CREATE TABLE t1 (a int, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
317 Warnings:
318 Warning 1618 <DATA DIRECTORY> option ignored
319 SHOW WARNINGS;
320 Level Code Message
321 Warning 1618 <DATA DIRECTORY> option ignored
322 INSERT INTO t1 VALUES (6, "SQL MODE NO_DIR_IN_CREATE prevents DATA DIRECTORY");
323 DROP TABLE t1;
324 set @@sql_mode=@org_mode;
325 #
326 # MySQL engine does not allow DATA DIRECTORY to be
327 # within --datadir for any engine, including InnoDB
328 #
329 CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY 'MYSQLD_DATADIR/test';
330 ERROR HY000: Incorrect arguments to DATA DIRECTORY
331 #
332 # TEMPORARY tables are incompatible with DATA DIRECTORY
333 #
334 SET SESSION innodb_strict_mode = ON;
335 #
336 # CREATE TEMPORARY TABLE with DATA DIRECTORY is rejected in strict mode.
337 #
338 CREATE TEMPORARY TABLE t1 (a int KEY, b text) engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
339 ERROR HY000: Table storage engine for 't1' doesn't have this option
340 SHOW WARNINGS;
341 Level Code Message
342 Warning 1478 InnoDB: DATA DIRECTORY cannot be used for TEMPORARY tables.
343 Error 1031 Table storage engine for 't1' doesn't have this option
344 SET SESSION innodb_strict_mode = OFF;
345 #
346 # DATA DIRECTORY is ignored in CREATE TEMPORARY TABLE in non-strict mode.
347 #
348 CREATE TEMPORARY TABLE t1 (a int KEY, b text) engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
349 Warnings:
350 Warning 1478 InnoDB: DATA DIRECTORY cannot be used for TEMPORARY tables.
351 Warning 1618 <DATA DIRECTORY> option ignored
352 SHOW CREATE TABLE t1;
353 Table Create Table
354 t1 CREATE TEMPORARY TABLE `t1` (
355 `a` int NOT NULL,
356 `b` text,
357 PRIMARY KEY (`a`)
358 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
359 DROP TABLE t1;
360 #
361 # A warning should be issued if DATA DIR and TEMP is used without
362 # innodb_file_per_table
363 #
364 SET GLOBAL innodb_file_per_table=OFF;
365 CREATE TEMPORARY TABLE t1 (a int KEY, b text) engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
366 Warnings:
367 Warning 1478 InnoDB: DATA DIRECTORY requires innodb_file_per_table.
368 Warning 1478 InnoDB: DATA DIRECTORY cannot be used for TEMPORARY tables.
369 Warning 1618 <DATA DIRECTORY> option ignored
370 DROP TABLE t1;
371 SET SESSION innodb_strict_mode = ON;
372 CREATE TEMPORARY TABLE t1 (a int KEY, b text) engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
373 ERROR HY000: Table storage engine for 't1' doesn't have this option
374 SHOW WARNINGS;
375 Level Code Message
376 Warning 1478 InnoDB: DATA DIRECTORY requires innodb_file_per_table.
377 Warning 1478 InnoDB: DATA DIRECTORY cannot be used for TEMPORARY tables.
378 Error 1031 Table storage engine for 't1' doesn't have this option
379 SET SESSION innodb_strict_mode = OFF;
380 SET GLOBAL innodb_file_per_table=ON;
381 ---- MYSQLD_DATADIR/test
382 ---- MYSQL_TMP_DIR/alt_dir/test
383 #
384 # Create the remote table via static DDL statements in a stored procedure
385 #
386 CREATE PROCEDURE static_proc() BEGIN CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'; END |
387 CALL static_proc;
388 === information_schema.innodb_tablespaces and innodb_datafiles ===
389 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
390 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
391 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
392 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
393 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
394 test/t1 Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir/test/t1.ibd
395 === information_schema.files ===
396 Space_Name File_Type Engine Status Tablespace_Name Path
397 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
398 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
399 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
400 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
401 test/t1 TABLESPACE InnoDB NORMAL test/t1 MYSQL_TMP_DIR/alt_dir/test/t1.ibd
402 INSERT INTO t1 VALUES (7, "Create the remote table via static DDL statements");
403 SELECT * FROM t1;
404 a b
405 7 Create the remote table via static DDL statements
406 SHOW CREATE TABLE t1;
407 Table Create Table
408 t1 CREATE TABLE `t1` (
409 `a` int NOT NULL,
410 `b` text,
411 PRIMARY KEY (`a`)
412 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
413 ---- MYSQLD_DATADIR/test
414 ---- MYSQL_TMP_DIR/alt_dir/test
415 t1.ibd
416 DROP PROCEDURE static_proc;
417 DROP TABLE t1;
418 #
419 # Create the remote table via dynamic DDL statements in a stored procedure
420 #
421 CREATE PROCEDURE dynamic_proc() BEGIN PREPARE stmt1 FROM "CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'"; EXECUTE stmt1; END |
422 CALL dynamic_proc;
423 === information_schema.innodb_tablespaces and innodb_datafiles ===
424 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
425 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
426 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
427 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
428 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
429 test/t1 Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir/test/t1.ibd
430 === information_schema.files ===
431 Space_Name File_Type Engine Status Tablespace_Name Path
432 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
433 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
434 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
435 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
436 test/t1 TABLESPACE InnoDB NORMAL test/t1 MYSQL_TMP_DIR/alt_dir/test/t1.ibd
437 INSERT INTO t1 VALUES (8, "Create the remote table via dynamic DDL statements");
438 SELECT * FROM t1;
439 a b
440 8 Create the remote table via dynamic DDL statements
441 SHOW CREATE TABLE t1;
442 Table Create Table
443 t1 CREATE TABLE `t1` (
444 `a` int NOT NULL,
445 `b` text,
446 PRIMARY KEY (`a`)
447 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
448 ---- MYSQLD_DATADIR/test
449 ---- MYSQL_TMP_DIR/alt_dir/test
450 t1.ibd
451 DROP PROCEDURE dynamic_proc;
452 DEALLOCATE PREPARE stmt1;
453 DROP TABLE t1;
454 #
455 # CREATE, DROP, ADD and TRUNCATE PARTITION with DATA DIRECTORY
456 #
457 CREATE TABLE emp (
458 id INT NOT NULL,
459 store_name VARCHAR(30),
460 parts VARCHAR(30),
461 store_id INT
462 )
463 PARTITION BY LIST(store_id) (
464 PARTITION east VALUES IN (10,20,30)
465 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east',
466 PARTITION north VALUES IN (40,50,60)
467 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north',
468 PARTITION west VALUES IN (70,80,100)
469 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west'
470 );
471 INSERT INTO emp values(1,'Oracle','NUTT',10);
472 INSERT INTO emp values(2,'HUAWEI','BOLT',40);
473 INSERT INTO emp values(3,'IBM','NAIL',70);
474 SHOW CREATE TABLE emp;
475 Table Create Table
476 emp CREATE TABLE `emp` (
477 `id` int NOT NULL,
478 `store_name` varchar(30) DEFAULT NULL,
479 `parts` varchar(30) DEFAULT NULL,
480 `store_id` int DEFAULT NULL
481 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
482 /*!50100 PARTITION BY LIST (`store_id`)
483 (PARTITION east VALUES IN (10,20,30) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east/' ENGINE = InnoDB,
484 PARTITION north VALUES IN (40,50,60) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north/' ENGINE = InnoDB,
485 PARTITION west VALUES IN (70,80,100) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west/' ENGINE = InnoDB) */
486 === information_schema.innodb_tables and innodb_tablespaces ===
487 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
488 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
489 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
490 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
491 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
492 test/emp#p#east test/emp#p#east 97 7 Dynamic 0 Single
493 test/emp#p#north test/emp#p#north 97 7 Dynamic 0 Single
494 test/emp#p#west test/emp#p#west 97 7 Dynamic 0 Single
495 === information_schema.innodb_tablespaces and innodb_datafiles ===
496 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
497 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
498 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
499 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
500 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
501 test/emp#p#east Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
502 test/emp#p#north Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
503 test/emp#p#west Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west.ibd
504 === information_schema.files ===
505 Space_Name File_Type Engine Status Tablespace_Name Path
506 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
507 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
508 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
509 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
510 test/emp#p#east TABLESPACE InnoDB NORMAL test/emp#p#east MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
511 test/emp#p#north TABLESPACE InnoDB NORMAL test/emp#p#north MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
512 test/emp#p#west TABLESPACE InnoDB NORMAL test/emp#p#west MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west.ibd
513 SELECT * FROM emp;
514 id store_name parts store_id
515 1 Oracle NUTT 10
516 2 HUAWEI BOLT 40
517 3 IBM NAIL 70
518 ---- MYSQLD_DATADIR/test
519 ---- MYSQL_TMP_DIR/alt_dir_east/test
520 emp#p#east.ibd
521 ---- MYSQL_TMP_DIR/alt_dir_north/test
522 emp#p#north.ibd
523 ---- MYSQL_TMP_DIR/alt_dir_west/test
524 emp#p#west.ibd
525 #
526 # DROP one PARTITION.
527 #
528 ALTER TABLE emp DROP PARTITION west;
529 SHOW CREATE TABLE emp;
530 Table Create Table
531 emp CREATE TABLE `emp` (
532 `id` int NOT NULL,
533 `store_name` varchar(30) DEFAULT NULL,
534 `parts` varchar(30) DEFAULT NULL,
535 `store_id` int DEFAULT NULL
536 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
537 /*!50100 PARTITION BY LIST (`store_id`)
538 (PARTITION east VALUES IN (10,20,30) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = InnoDB,
539 PARTITION north VALUES IN (40,50,60) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = InnoDB) */
540 === information_schema.innodb_tables and innodb_tablespaces ===
541 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
542 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
543 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
544 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
545 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
546 test/emp#p#east test/emp#p#east 97 7 Dynamic 0 Single
547 test/emp#p#north test/emp#p#north 97 7 Dynamic 0 Single
548 === information_schema.innodb_tablespaces and innodb_datafiles ===
549 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
550 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
551 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
552 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
553 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
554 test/emp#p#east Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
555 test/emp#p#north Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
556 === information_schema.files ===
557 Space_Name File_Type Engine Status Tablespace_Name Path
558 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
559 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
560 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
561 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
562 test/emp#p#east TABLESPACE InnoDB NORMAL test/emp#p#east MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
563 test/emp#p#north TABLESPACE InnoDB NORMAL test/emp#p#north MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
564 SELECT * FROM emp;
565 id store_name parts store_id
566 1 Oracle NUTT 10
567 2 HUAWEI BOLT 40
568 ---- MYSQLD_DATADIR/test
569 ---- MYSQL_TMP_DIR/alt_dir_east/test
570 emp#p#east.ibd
571 ---- MYSQL_TMP_DIR/alt_dir_north/test
572 emp#p#north.ibd
573 ---- MYSQL_TMP_DIR/alt_dir_west/test
574 #
575 # ADD the PARTITION back.
576 #
577 ALTER TABLE emp ADD PARTITION (
578 PARTITION west VALUES IN (70,80,100)
579 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west');
580 SHOW CREATE TABLE emp;
581 Table Create Table
582 emp CREATE TABLE `emp` (
583 `id` int NOT NULL,
584 `store_name` varchar(30) DEFAULT NULL,
585 `parts` varchar(30) DEFAULT NULL,
586 `store_id` int DEFAULT NULL
587 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
588 /*!50100 PARTITION BY LIST (`store_id`)
589 (PARTITION east VALUES IN (10,20,30) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = InnoDB,
590 PARTITION north VALUES IN (40,50,60) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = InnoDB,
591 PARTITION west VALUES IN (70,80,100) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' ENGINE = InnoDB) */
592 === information_schema.innodb_tables and innodb_tablespaces ===
593 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
594 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
595 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
596 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
597 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
598 test/emp#p#east test/emp#p#east 97 7 Dynamic 0 Single
599 test/emp#p#north test/emp#p#north 97 7 Dynamic 0 Single
600 test/emp#p#west test/emp#p#west 97 7 Dynamic 0 Single
601 === information_schema.innodb_tablespaces and innodb_datafiles ===
602 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
603 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
604 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
605 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
606 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
607 test/emp#p#east Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
608 test/emp#p#north Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
609 test/emp#p#west Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west.ibd
610 === information_schema.files ===
611 Space_Name File_Type Engine Status Tablespace_Name Path
612 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
613 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
614 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
615 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
616 test/emp#p#east TABLESPACE InnoDB NORMAL test/emp#p#east MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
617 test/emp#p#north TABLESPACE InnoDB NORMAL test/emp#p#north MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
618 test/emp#p#west TABLESPACE InnoDB NORMAL test/emp#p#west MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west.ibd
619 INSERT INTO emp VALUES(3,'IBM','NAIL',70);
620 SELECT * FROM emp;
621 id store_name parts store_id
622 1 Oracle NUTT 10
623 2 HUAWEI BOLT 40
624 3 IBM NAIL 70
625 ---- MYSQLD_DATADIR/test
626 ---- MYSQL_TMP_DIR/alt_dir_east/test
627 emp#p#east.ibd
628 ---- MYSQL_TMP_DIR/alt_dir_north/test
629 emp#p#north.ibd
630 ---- MYSQL_TMP_DIR/alt_dir_west/test
631 emp#p#west.ibd
632 #
633 # TRUNCATE one PARTITION.
634 #
635 ALTER TABLE emp TRUNCATE PARTITION west;
636 SHOW CREATE TABLE emp;
637 Table Create Table
638 emp CREATE TABLE `emp` (
639 `id` int NOT NULL,
640 `store_name` varchar(30) DEFAULT NULL,
641 `parts` varchar(30) DEFAULT NULL,
642 `store_id` int DEFAULT NULL
643 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
644 /*!50100 PARTITION BY LIST (`store_id`)
645 (PARTITION east VALUES IN (10,20,30) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = InnoDB,
646 PARTITION north VALUES IN (40,50,60) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = InnoDB,
647 PARTITION west VALUES IN (70,80,100) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' ENGINE = InnoDB) */
648 === information_schema.innodb_tables and innodb_tablespaces ===
649 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
650 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
651 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
652 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
653 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
654 test/emp#p#east test/emp#p#east 97 7 Dynamic 0 Single
655 test/emp#p#north test/emp#p#north 97 7 Dynamic 0 Single
656 test/emp#p#west test/emp#p#west 97 7 Dynamic 0 Single
657 === information_schema.innodb_tablespaces and innodb_datafiles ===
658 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
659 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
660 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
661 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
662 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
663 test/emp#p#east Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
664 test/emp#p#north Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
665 test/emp#p#west Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west.ibd
666 === information_schema.files ===
667 Space_Name File_Type Engine Status Tablespace_Name Path
668 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
669 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
670 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
671 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
672 test/emp#p#east TABLESPACE InnoDB NORMAL test/emp#p#east MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
673 test/emp#p#north TABLESPACE InnoDB NORMAL test/emp#p#north MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
674 test/emp#p#west TABLESPACE InnoDB NORMAL test/emp#p#west MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west.ibd
675 SELECT * FROM emp;
676 id store_name parts store_id
677 1 Oracle NUTT 10
678 2 HUAWEI BOLT 40
679 INSERT INTO emp VALUES(3,'IBM','NAIL',70);
680 SELECT * FROM emp;
681 id store_name parts store_id
682 1 Oracle NUTT 10
683 2 HUAWEI BOLT 40
684 3 IBM NAIL 70
685 ---- MYSQLD_DATADIR/test
686 ---- MYSQL_TMP_DIR/alt_dir_east/test
687 emp#p#east.ibd
688 ---- MYSQL_TMP_DIR/alt_dir_north/test
689 emp#p#north.ibd
690 ---- MYSQL_TMP_DIR/alt_dir_west/test
691 emp#p#west.ibd
692 DROP TABLE emp;
693 #
694 # Cleanup
695 #
696 #
697 # Bug#22899690 FAILING ASSERT: FIL_SPACE_GET(TABLE->SPACE) !=__NULL
698 # IN ROW0QUIESCE.CC LINE 724
699 #
700 CREATE TABLE t1(c1 CHAR (1));
701 ALTER TABLE t1 DISCARD TABLESPACE;
702 FLUSH TABLES t1 FOR EXPORT;
703 ERROR HY000: Tablespace has been discarded for table 't1'
704 UNLOCK TABLES;
705 DROP TABLE t1;
706 #
707 # Bug#27903881 [MYSQL 8.0 GA RELEASE & DEBUG BUILD]
708 # FIL_SPACE_GET(TABLE->SPACE) != __NULL
709 #
710 CREATE TABLE t1(c1 INT,c2 CHAR,c3 DATE)PARTITION BY HASH(DAYOFWEEK(c3));
711 ALTER TABLE t1 DISCARD TABLESPACE;
712 FLUSH TABLES t1 FOR EXPORT;
713 ERROR HY000: Tablespace has been discarded for table 't1'
714 UNLOCK TABLES;
715 DROP TABLE t1;
716 #
717 # Bug#29492113 TABLE NOT ACCESSIBLE AFTER MOVING THE IBD FILE,
718 # WHEN THE DATABASE NAME HAS "/"
719 #
720 #
721 # Scenario-1
722 # create database `abc/def`
723 # create table `abc/def`.`xyz/123` in MYSQL_TMP_DIR/my_dir1
724 # The directory structure after creating the table should be
725 # MYSQL_TMP_DIR/my_dir1/abc@002fdef/[email protected]
726 #
727 CREATE DATABASE `abc/def`;
728 CREATE TABLE `abc/def`.`xyz/123` (
729 c1 INT AUTO_INCREMENT KEY, c2 CHAR(10))
730 PARTITION BY RANGE(c1) SUBPARTITION BY HASH(c1) (
731 PARTITION `p0/part` VALUES LESS THAN (10) (
732 SUBPARTITION s0 DATA DIRECTORY='MYSQL_TMP_DIR/my_dir1',
733 SUBPARTITION `s1/subpart` DATA DIRECTORY='MYSQL_TMP_DIR/my_dir1'),
734 PARTITION p1 VALUES LESS THAN MAXVALUE (
735 SUBPARTITION s2 DATA DIRECTORY='MYSQL_TMP_DIR/my_dir1',
736 SUBPARTITION s3 DATA DIRECTORY='MYSQL_TMP_DIR/my_dir1'));
737 SHOW WARNINGS;
738 Level Code Message
739 #
740 # Validate the directory structure
741 #
742 abc@002fdef
743 xyz@002f123#p#p0@002fpart#sp#s0.ibd
744 xyz@002f123#p#p0@002fpart#sp#[email protected]
745 xyz@002f123#p#p1#sp#s2.ibd
746 xyz@002f123#p#p1#sp#s3.ibd
747 INSERT INTO `abc/def`.`xyz/123` VALUES (0, 'MySQL');
748 INSERT INTO `abc/def`.`xyz/123` (SELECT 0, c2 FROM `abc/def`.`xyz/123`);
749 INSERT INTO `abc/def`.`xyz/123` (SELECT 0, c2 FROM `abc/def`.`xyz/123`);
750 INSERT INTO `abc/def`.`xyz/123` (SELECT 0, c2 FROM `abc/def`.`xyz/123`);
751 INSERT INTO `abc/def`.`xyz/123` (SELECT 0, c2 FROM `abc/def`.`xyz/123`);
752 SELECT COUNT(*) FROM `abc/def`.`xyz/123`;
753 COUNT(*)
754 16
755 SELECT file_name, tablespace_name FROM information_schema.files WHERE file_name LIKE '%123%';
756 FILE_NAME TABLESPACE_NAME
757 MYSQL_TMP_DIR/my_dir1/abc@002fdef/xyz@002f123#p#p0@002fpart#sp#s0.ibd abc/def/xyz/123#p#p0/part#sp#s0
758 MYSQL_TMP_DIR/my_dir1/abc@002fdef/xyz@002f123#p#p0@002fpart#sp#[email protected] abc/def/xyz/123#p#p0/part#sp#s1/subpart
759 MYSQL_TMP_DIR/my_dir1/abc@002fdef/xyz@002f123#p#p1#sp#s2.ibd abc/def/xyz/123#p#p1#sp#s2
760 MYSQL_TMP_DIR/my_dir1/abc@002fdef/xyz@002f123#p#p1#sp#s3.ibd abc/def/xyz/123#p#p1#sp#s3
761 SELECT name, space_type FROM information_schema.innodb_tablespaces WHERE name LIKE '%123%';
762 name space_type
763 abc/def/xyz/123#p#p0/part#sp#s0 Single
764 abc/def/xyz/123#p#p0/part#sp#s1/subpart Single
765 abc/def/xyz/123#p#p1#sp#s2 Single
766 abc/def/xyz/123#p#p1#sp#s3 Single
767 #
768 # Shutdown the server
769 #
770 #
771 # Scenario-2
772 # Move the IBD file from MYSQL_TMP_DIR/my_dir1 to MYSQL_TMP_DIR/my_dir2
773 # Restart the server with --innodb_directories MYSQL_TMP_DIR/my_dir2
774 # Query the table
775 # Restart the server without --innodb_directories option
776 #
777 #
778 # Move IBD file in MYSQL_TMP_DIR/my_dir1 to MYSQL_TMP_DIR/my_dir2
779 #
780 #
781 # Resart server with --innodb_directories MYSQL_TMP_DIR/my_dir2
782 #
783 # restart: --innodb_directories=MYSQL_TMP_DIR/my_dir2
784 SELECT COUNT(*) FROM `abc/def`.`xyz/123`;
785 COUNT(*)
786 16
787 SELECT file_name, tablespace_name FROM information_schema.files WHERE file_name LIKE '%123%';
788 FILE_NAME TABLESPACE_NAME
789 MYSQL_TMP_DIR/my_dir2/abc@002fdef/xyz@002f123#p#p0@002fpart#sp#s0.ibd abc/def/xyz/123#p#p0/part#sp#s0
790 MYSQL_TMP_DIR/my_dir2/abc@002fdef/xyz@002f123#p#p0@002fpart#sp#[email protected] abc/def/xyz/123#p#p0/part#sp#s1/subpart
791 MYSQL_TMP_DIR/my_dir2/abc@002fdef/xyz@002f123#p#p1#sp#s2.ibd abc/def/xyz/123#p#p1#sp#s2
792 MYSQL_TMP_DIR/my_dir2/abc@002fdef/xyz@002f123#p#p1#sp#s3.ibd abc/def/xyz/123#p#p1#sp#s3
793 SELECT name, space_type FROM information_schema.innodb_tablespaces WHERE name LIKE '%123%';
794 name space_type
795 abc/def/xyz/123#p#p0/part#sp#s0 Single
796 abc/def/xyz/123#p#p0/part#sp#s1/subpart Single
797 abc/def/xyz/123#p#p1#sp#s2 Single
798 abc/def/xyz/123#p#p1#sp#s3 Single
799 #
800 # Restart server without --innodb_directories option
801 #
802 # restart:
803 SELECT COUNT(*) FROM `abc/def`.`xyz/123`;
804 COUNT(*)
805 16
806 SELECT file_name, tablespace_name FROM information_schema.files WHERE file_name LIKE '%123%';
807 FILE_NAME TABLESPACE_NAME
808 MYSQL_TMP_DIR/my_dir2/abc@002fdef/xyz@002f123#p#p0@002fpart#sp#s0.ibd abc/def/xyz/123#p#p0/part#sp#s0
809 MYSQL_TMP_DIR/my_dir2/abc@002fdef/xyz@002f123#p#p0@002fpart#sp#[email protected] abc/def/xyz/123#p#p0/part#sp#s1/subpart
810 MYSQL_TMP_DIR/my_dir2/abc@002fdef/xyz@002f123#p#p1#sp#s2.ibd abc/def/xyz/123#p#p1#sp#s2
811 MYSQL_TMP_DIR/my_dir2/abc@002fdef/xyz@002f123#p#p1#sp#s3.ibd abc/def/xyz/123#p#p1#sp#s3
812 SELECT name, space_type FROM information_schema.innodb_tablespaces WHERE name LIKE '%123%';
813 name space_type
814 abc/def/xyz/123#p#p0/part#sp#s0 Single
815 abc/def/xyz/123#p#p0/part#sp#s1/subpart Single
816 abc/def/xyz/123#p#p1#sp#s2 Single
817 abc/def/xyz/123#p#p1#sp#s3 Single
818 #
819 # Shutdown server
820 #
821 #
822 # Scenario-3
823 # Move the IBD file from MYSQL_TMP_DIR/my_dir2 to MYSQL_TMP_DIR/my_dir1
824 # Restart the server with --innodb_directories MYSQL_TMP_DIR/my_dir1
825 #
826 #
827 # Move IBD file from MYSQL_TMP_DIR/my_dir2 to MYSQL_TMP_DIR/my_dir1
828 #
829 #
830 # Restart server with --innodb_directories MYSQL_TMP_DIR/my_dir1
831 #
832 # restart: --innodb_directories=MYSQL_TMP_DIR/my_dir1
833 SELECT COUNT(*) FROM `abc/def`.`xyz/123`;
834 COUNT(*)
835 16
836 SELECT file_name, tablespace_name FROM information_schema.files WHERE file_name LIKE '%123%';
837 FILE_NAME TABLESPACE_NAME
838 MYSQL_TMP_DIR/my_dir1/abc@002fdef/xyz@002f123#p#p0@002fpart#sp#s0.ibd abc/def/xyz/123#p#p0/part#sp#s0
839 MYSQL_TMP_DIR/my_dir1/abc@002fdef/xyz@002f123#p#p0@002fpart#sp#[email protected] abc/def/xyz/123#p#p0/part#sp#s1/subpart
840 MYSQL_TMP_DIR/my_dir1/abc@002fdef/xyz@002f123#p#p1#sp#s2.ibd abc/def/xyz/123#p#p1#sp#s2
841 MYSQL_TMP_DIR/my_dir1/abc@002fdef/xyz@002f123#p#p1#sp#s3.ibd abc/def/xyz/123#p#p1#sp#s3
842 SELECT name, space_type FROM information_schema.innodb_tablespaces WHERE name LIKE '%123%';
843 name space_type
844 abc/def/xyz/123#p#p0/part#sp#s0 Single
845 abc/def/xyz/123#p#p0/part#sp#s1/subpart Single
846 abc/def/xyz/123#p#p1#sp#s2 Single
847 abc/def/xyz/123#p#p1#sp#s3 Single
848 #
849 # Cleanup
850 #
851 DROP DATABASE `abc/def`;
852 # restart: