Debian Codegraph @

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

raw
1 SET @old_innodb_file_per_table=@@global.innodb_file_per_table;
2 SET GLOBAL innodb_file_per_table = OFF;
3 #
4 # Put all partitions of a table into a general tablespace.
5 #
6 CREATE TABLESPACE s1 ADD DATAFILE 's1.ibd' ENGINE InnoDB;
7 CREATE TABLESPACE `s``1` ADD DATAFILE 's-1.ibd' ENGINE InnoDB;
8 CREATE TABLE t1 (a INT, b INT)
9 ENGINE = InnoDB
10 TABLESPACE=`s1`
11 PARTITION BY RANGE(a) SUBPARTITION BY KEY(b) (
12 PARTITION tens VALUES LESS THAN (100) TABLESPACE=`innodb_file_per_table`
13 (SUBPARTITION subpart11,
14 SUBPARTITION subpart12),
15 PARTITION hundreds VALUES LESS THAN (1000)
16 (SUBPARTITION subpart21,
17 SUBPARTITION subpart22 TABLESPACE=`innodb_file_per_table`),
18 PARTITION onethousands VALUES LESS THAN (2000)
19 TABLESPACE=`innodb_file_per_table`
20 (SUBPARTITION subpart23 TABLESPACE = `s1`,
21 SUBPARTITION subpart24),
22 PARTITION thousands VALUES LESS THAN (10000)
23 (SUBPARTITION subpart31,
24 SUBPARTITION subpart32));
25 SET GLOBAL innodb_file_per_table = ON;
26 CREATE TABLE t2 (a INT, b INT)
27 ENGINE = InnoDB
28 PARTITION BY RANGE(a) SUBPARTITION BY KEY(b) (
29 PARTITION tens VALUES LESS THAN (100) TABLESPACE=`s1`
30 (SUBPARTITION subpart11,
31 SUBPARTITION subpart12),
32 PARTITION hundreds VALUES LESS THAN (1000)
33 (SUBPARTITION subpart21,
34 SUBPARTITION subpart22 TABLESPACE=`s``1`),
35 PARTITION thousands VALUES LESS THAN (10000)
36 (SUBPARTITION subpart31 DATA DIRECTORY 'MYSQL_TMP_DIR',
37 SUBPARTITION subpart32 DATA DIRECTORY 'MYSQL_TMP_DIR' TABLESPACE `innodb_file_per_table`));
38 ALTER TABLE t1 ALGORITHM=COPY, ADD PARTITION
39 (PARTITION tenthousands VALUES LESS THAN (20000));
40 # Only allow tablespace name as ident, not text. I.e. no 'single' quotes.
41 ALTER TABLE t1 ALGORITHM=COPY, ADD PARTITION
42 (PARTITION twentythousands VALUES LESS THAN (30000)
43 TABLESPACE = 'innodb_system');
44 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''innodb_system')' at line 3
45 ALTER TABLE t1 ALGORITHM=COPY, ADD PARTITION
46 (PARTITION twentythousands VALUES LESS THAN (30000)
47 TABLESPACE = "innodb_system");
48 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"innodb_system")' at line 3
49 SET @old_sql_mode = @@sql_mode;
50 SET @@sql_mode = 'ANSI_QUOTES';
51 ALTER TABLE t1 ALGORITHM=COPY, ADD PARTITION
52 (PARTITION twentythousands VALUES LESS THAN (30000)
53 TABLESPACE = 'innodb_system');
54 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''innodb_system')' at line 3
55 ALTER TABLE t1 ALGORITHM=DEFAULT, ADD PARTITION
56 (PARTITION twentythousands VALUES LESS THAN (30000)
57 TABLESPACE = "innodb_system");
58 ALTER TABLE t1 ALGORITHM=DEFAULT, ADD PARTITION
59 (PARTITION thirtythousands VALUES LESS THAN (40000)
60 TABLESPACE = `innodb_file_per_table`);
61 SET @@sql_mode = @old_sql_mode;
62 ALTER TABLE t1 ALGORITHM=DEFAULT, ADD PARTITION
63 (PARTITION fortythousands VALUES LESS THAN (50000)
64 (SUBPARTITION p40k_1 TABLESPACE = innodb_system DATA DIRECTORY = 'MYSQL_TMP_DIR',
65 SUBPARTITION p40k_2 DATA DIRECTORY = 'MYSQL_TMP_DIR'));
66 ERROR HY000: InnoDB: DATA DIRECTORY cannot be used with a TABLESPACE assignment.
67 # Verify that it is OK to have TABLESPACE innodb_system with DATA DIR also on table
68 CREATE TABLE t3 (a int)
69 ENGINE = InnoDB
70 TABLESPACE = innodb_system DATA DIRECTORY = 'MYSQL_TMP_DIR';
71 ERROR HY000: InnoDB: DATA DIRECTORY cannot be used with a TABLESPACE assignment.
72 CREATE TABLE t3 (a int)
73 ENGINE = InnoDB
74 TABLESPACE = innodb_file_per_table DATA DIRECTORY = 'MYSQL_TMP_DIR';
75 SHOW CREATE TABLE t3;
76 Table Create Table
77 t3 CREATE TABLE `t3` (
78 `a` int(11) DEFAULT NULL
79 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/'
80 DROP TABLE t3;
81 CREATE TABLE t3 (a int) ENGINE = InnoDB DATA DIRECTORY = 'MYSQL_TMP_DIR';
82 SHOW CREATE TABLE t3;
83 Table Create Table
84 t3 CREATE TABLE `t3` (
85 `a` int(11) DEFAULT NULL
86 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/'
87 DROP TABLE t3;
88 ALTER TABLE t1 ALGORITHM=DEFAULT, ADD PARTITION
89 (PARTITION fortythousands VALUES LESS THAN (50000)
90 (SUBPARTITION p40k_1 DATA DIRECTORY = 'MYSQL_TMP_DIR',
91 SUBPARTITION p40k_2 TABLESPACE = innodb_file_per_table DATA DIRECTORY = 'MYSQL_TMP_DIR'));
92 SET GLOBAL innodb_file_per_table = OFF;
93 ALTER TABLE t1 ALGORITHM=DEFAULT, ADD PARTITION
94 (PARTITION fiftythousands VALUES LESS THAN (60000));
95 ALTER TABLE t1 ALGORITHM=DEFAULT, ADD PARTITION
96 (PARTITION sixtythousands VALUES LESS THAN (70000)
97 TABLESPACE = `innodb_system`);
98 ALTER TABLE t1 ALGORITHM=DEFAULT, ADD PARTITION
99 (PARTITION seventythousands VALUES LESS THAN (80000)
100 TABLESPACE = `innodb_file_per_table`);
101 ALTER TABLE t1 ALGORITHM=DEFAULT, ADD PARTITION
102 (PARTITION eightythousands VALUES LESS THAN (90000)
103 (SUBPARTITION p80k_1 TABLESPACE = `innodb_file_per_table` DATA DIRECTORY = 'MYSQL_TMP_DIR',
104 SUBPARTITION p80k_2 TABLESPACE = `innodb_file_per_table` DATA DIRECTORY = 'MYSQL_TMP_DIR'));
105 # Test if both TABLESPACE and DATA DIRECTORY is allowed on table level.
106 CREATE TABLE t3 (a int)
107 ENGINE = InnoDB
108 TABLESPACE = innodb_file_per_table DATA DIRECTORY = 'MYSQL_TMP_DIR';
109 CREATE TABLE t4 (a int)
110 ENGINE = InnoDB
111 TABLESPACE = innodb_system DATA DIRECTORY = 'MYSQL_TMP_DIR';
112 ERROR HY000: InnoDB: DATA DIRECTORY cannot be used with a TABLESPACE assignment.
113 ALTER TABLE t1 ALGORITHM=DEFAULT, ADD PARTITION
114 (PARTITION ninetythousands VALUES LESS THAN (100000)
115 (SUBPARTITION p90k_1 TABLESPACE = `innodb_file_per_table`,
116 SUBPARTITION p90k_2 TABLESPACE = `innodb_system`));
117 FLUSH TABLES;
118 === information_schema.innodb_tables and innodb_tablespaces ===
119 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
120 test/t1#p#eightythousands#sp#p80k_1 test/t1#p#eightythousands#sp#p80k_1 97 5 Dynamic 0 Single
121 test/t1#p#eightythousands#sp#p80k_2 test/t1#p#eightythousands#sp#p80k_2 97 5 Dynamic 0 Single
122 test/t1#p#fiftythousands#sp#fiftythousandssp0 s1 161 5 Dynamic 0 General
123 test/t1#p#fiftythousands#sp#fiftythousandssp1 s1 161 5 Dynamic 0 General
124 test/t1#p#fortythousands#sp#p40k_1 test/t1#p#fortythousands#sp#p40k_1 97 5 Dynamic 0 Single
125 test/t1#p#fortythousands#sp#p40k_2 test/t1#p#fortythousands#sp#p40k_2 97 5 Dynamic 0 Single
126 test/t1#p#hundreds#sp#subpart21 s1 161 5 Dynamic 0 General
127 test/t1#p#hundreds#sp#subpart22 test/t1#p#hundreds#sp#subpart22 33 5 Dynamic 0 Single
128 test/t1#p#ninetythousands#sp#p90k_1 test/t1#p#ninetythousands#sp#p90k_1 33 5 Dynamic 0 Single
129 test/t1#p#ninetythousands#sp#p90k_2 innodb_system 161 5 Dynamic 0 System
130 test/t1#p#onethousands#sp#subpart23 s1 161 5 Dynamic 0 General
131 test/t1#p#onethousands#sp#subpart24 test/t1#p#onethousands#sp#subpart24 33 5 Dynamic 0 Single
132 test/t1#p#seventythousands#sp#seventythousandssp0 test/t1#p#seventythousands#sp#seventythousandssp0 33 5 Dynamic 0 Single
133 test/t1#p#seventythousands#sp#seventythousandssp1 test/t1#p#seventythousands#sp#seventythousandssp1 33 5 Dynamic 0 Single
134 test/t1#p#sixtythousands#sp#sixtythousandssp0 innodb_system 161 5 Dynamic 0 System
135 test/t1#p#sixtythousands#sp#sixtythousandssp1 innodb_system 161 5 Dynamic 0 System
136 test/t1#p#tens#sp#subpart11 test/t1#p#tens#sp#subpart11 33 5 Dynamic 0 Single
137 test/t1#p#tens#sp#subpart12 test/t1#p#tens#sp#subpart12 33 5 Dynamic 0 Single
138 test/t1#p#tenthousands#sp#tenthousandssp0 s1 161 5 Dynamic 0 General
139 test/t1#p#tenthousands#sp#tenthousandssp1 s1 161 5 Dynamic 0 General
140 test/t1#p#thirtythousands#sp#thirtythousandssp0 test/t1#p#thirtythousands#sp#thirtythousandssp0 33 5 Dynamic 0 Single
141 test/t1#p#thirtythousands#sp#thirtythousandssp1 test/t1#p#thirtythousands#sp#thirtythousandssp1 33 5 Dynamic 0 Single
142 test/t1#p#thousands#sp#subpart31 s1 161 5 Dynamic 0 General
143 test/t1#p#thousands#sp#subpart32 s1 161 5 Dynamic 0 General
144 test/t1#p#twentythousands#sp#twentythousandssp0 innodb_system 161 5 Dynamic 0 System
145 test/t1#p#twentythousands#sp#twentythousandssp1 innodb_system 161 5 Dynamic 0 System
146 test/t2#p#hundreds#sp#subpart21 test/t2#p#hundreds#sp#subpart21 33 5 Dynamic 0 Single
147 test/t2#p#hundreds#sp#subpart22 s`1 161 5 Dynamic 0 General
148 test/t2#p#tens#sp#subpart11 s1 161 5 Dynamic 0 General
149 test/t2#p#tens#sp#subpart12 s1 161 5 Dynamic 0 General
150 test/t2#p#thousands#sp#subpart31 test/t2#p#thousands#sp#subpart31 97 5 Dynamic 0 Single
151 test/t2#p#thousands#sp#subpart32 test/t2#p#thousands#sp#subpart32 97 5 Dynamic 0 Single
152 test/t3 test/t3 97 4 Dynamic 0 Single
153 === information_schema.innodb_tablespaces and innodb_datafiles ===
154 Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path
155 s1 General DEFAULT 0 Any s1.ibd
156 s`1 General DEFAULT 0 Any s-1.ibd
157 test/t2#p#hundreds#sp#subpart21 Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t2#p#hundreds#sp#subpart21.ibd
158 test/t2#p#thousands#sp#subpart31 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/test/t2#p#thousands#sp#subpart31.ibd
159 test/t2#p#thousands#sp#subpart32 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/test/t2#p#thousands#sp#subpart32.ibd
160 test/t1#p#tens#sp#subpart11 Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t1#p#tens#sp#subpart11.ibd
161 test/t1#p#tens#sp#subpart12 Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t1#p#tens#sp#subpart12.ibd
162 test/t1#p#hundreds#sp#subpart22 Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t1#p#hundreds#sp#subpart22.ibd
163 test/t1#p#onethousands#sp#subpart24 Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t1#p#onethousands#sp#subpart24.ibd
164 test/t1#p#thirtythousands#sp#thirtythousandssp0 Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t1#p#thirtythousands#sp#thirtythousandssp0.ibd
165 test/t1#p#thirtythousands#sp#thirtythousandssp1 Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t1#p#thirtythousands#sp#thirtythousandssp1.ibd
166 test/t1#p#fortythousands#sp#p40k_1 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/test/t1#p#fortythousands#sp#p40k_1.ibd
167 test/t1#p#fortythousands#sp#p40k_2 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/test/t1#p#fortythousands#sp#p40k_2.ibd
168 test/t1#p#seventythousands#sp#seventythousandssp0 Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t1#p#seventythousands#sp#seventythousandssp0.ibd
169 test/t1#p#seventythousands#sp#seventythousandssp1 Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t1#p#seventythousands#sp#seventythousandssp1.ibd
170 test/t1#p#eightythousands#sp#p80k_1 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/test/t1#p#eightythousands#sp#p80k_1.ibd
171 test/t1#p#eightythousands#sp#p80k_2 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/test/t1#p#eightythousands#sp#p80k_2.ibd
172 test/t3 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/test/t3.ibd
173 test/t1#p#ninetythousands#sp#p90k_1 Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t1#p#ninetythousands#sp#p90k_1.ibd
174 === information_schema.files ===
175 Space_Name File_Type Engine Status Tablespace_Name Path
176 s1 TABLESPACE InnoDB NORMAL s1 MYSQLD_DATADIR/s1.ibd
177 s`1 TABLESPACE InnoDB NORMAL s`1 MYSQLD_DATADIR/s-1.ibd
178 test/t2#p#hundreds#sp#subpart21 TABLESPACE InnoDB NORMAL test/t2#p#hundreds#sp#subpart21 MYSQLD_DATADIR/test/t2#p#hundreds#sp#subpart21.ibd
179 test/t2#p#thousands#sp#subpart31 TABLESPACE InnoDB NORMAL test/t2#p#thousands#sp#subpart31 MYSQL_TMP_DIR/test/t2#p#thousands#sp#subpart31.ibd
180 test/t2#p#thousands#sp#subpart32 TABLESPACE InnoDB NORMAL test/t2#p#thousands#sp#subpart32 MYSQL_TMP_DIR/test/t2#p#thousands#sp#subpart32.ibd
181 test/t1#p#tens#sp#subpart11 TABLESPACE InnoDB NORMAL test/t1#p#tens#sp#subpart11 MYSQLD_DATADIR/test/t1#p#tens#sp#subpart11.ibd
182 test/t1#p#tens#sp#subpart12 TABLESPACE InnoDB NORMAL test/t1#p#tens#sp#subpart12 MYSQLD_DATADIR/test/t1#p#tens#sp#subpart12.ibd
183 test/t1#p#hundreds#sp#subpart22 TABLESPACE InnoDB NORMAL test/t1#p#hundreds#sp#subpart22 MYSQLD_DATADIR/test/t1#p#hundreds#sp#subpart22.ibd
184 test/t1#p#onethousands#sp#subpart24 TABLESPACE InnoDB NORMAL test/t1#p#onethousands#sp#subpart24 MYSQLD_DATADIR/test/t1#p#onethousands#sp#subpart24.ibd
185 test/t1#p#thirtythousands#sp#thirtythousandssp0 TABLESPACE InnoDB NORMAL test/t1#p#thirtythousands#sp#thirtythousandssp0 MYSQLD_DATADIR/test/t1#p#thirtythousands#sp#thirtythousandssp0.ibd
186 test/t1#p#thirtythousands#sp#thirtythousandssp1 TABLESPACE InnoDB NORMAL test/t1#p#thirtythousands#sp#thirtythousandssp1 MYSQLD_DATADIR/test/t1#p#thirtythousands#sp#thirtythousandssp1.ibd
187 test/t1#p#fortythousands#sp#p40k_1 TABLESPACE InnoDB NORMAL test/t1#p#fortythousands#sp#p40k_1 MYSQL_TMP_DIR/test/t1#p#fortythousands#sp#p40k_1.ibd
188 test/t1#p#fortythousands#sp#p40k_2 TABLESPACE InnoDB NORMAL test/t1#p#fortythousands#sp#p40k_2 MYSQL_TMP_DIR/test/t1#p#fortythousands#sp#p40k_2.ibd
189 test/t1#p#seventythousands#sp#seventythousandssp0 TABLESPACE InnoDB NORMAL test/t1#p#seventythousands#sp#seventythousandssp0 MYSQLD_DATADIR/test/t1#p#seventythousands#sp#seventythousandssp0.ibd
190 test/t1#p#seventythousands#sp#seventythousandssp1 TABLESPACE InnoDB NORMAL test/t1#p#seventythousands#sp#seventythousandssp1 MYSQLD_DATADIR/test/t1#p#seventythousands#sp#seventythousandssp1.ibd
191 test/t1#p#eightythousands#sp#p80k_1 TABLESPACE InnoDB NORMAL test/t1#p#eightythousands#sp#p80k_1 MYSQL_TMP_DIR/test/t1#p#eightythousands#sp#p80k_1.ibd
192 test/t1#p#eightythousands#sp#p80k_2 TABLESPACE InnoDB NORMAL test/t1#p#eightythousands#sp#p80k_2 MYSQL_TMP_DIR/test/t1#p#eightythousands#sp#p80k_2.ibd
193 test/t3 TABLESPACE InnoDB NORMAL test/t3 MYSQL_TMP_DIR/test/t3.ibd
194 test/t1#p#ninetythousands#sp#p90k_1 TABLESPACE InnoDB NORMAL test/t1#p#ninetythousands#sp#p90k_1 MYSQLD_DATADIR/test/t1#p#ninetythousands#sp#p90k_1.ibd
195 SHOW CREATE TABLE t1;
196 Table Create Table
197 t1 CREATE TABLE `t1` (
198 `a` int(11) DEFAULT NULL,
199 `b` int(11) DEFAULT NULL
200 ) /*!50100 TABLESPACE `s1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
201 /*!50100 PARTITION BY RANGE (`a`)
202 SUBPARTITION BY KEY (b)
203 (PARTITION tens VALUES LESS THAN (100)
204 (SUBPARTITION subpart11 TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB,
205 SUBPARTITION subpart12 TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB),
206 PARTITION hundreds VALUES LESS THAN (1000)
207 (SUBPARTITION subpart21 TABLESPACE = `s1` ENGINE = InnoDB,
208 SUBPARTITION subpart22 TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB),
209 PARTITION onethousands VALUES LESS THAN (2000)
210 (SUBPARTITION subpart23 TABLESPACE = `s1` ENGINE = InnoDB,
211 SUBPARTITION subpart24 TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB),
212 PARTITION thousands VALUES LESS THAN (10000)
213 (SUBPARTITION subpart31 TABLESPACE = `s1` ENGINE = InnoDB,
214 SUBPARTITION subpart32 TABLESPACE = `s1` ENGINE = InnoDB),
215 PARTITION tenthousands VALUES LESS THAN (20000)
216 (SUBPARTITION tenthousandssp0 TABLESPACE = `s1` ENGINE = InnoDB,
217 SUBPARTITION tenthousandssp1 TABLESPACE = `s1` ENGINE = InnoDB),
218 PARTITION twentythousands VALUES LESS THAN (30000)
219 (SUBPARTITION twentythousandssp0 TABLESPACE = `innodb_system` ENGINE = InnoDB,
220 SUBPARTITION twentythousandssp1 TABLESPACE = `innodb_system` ENGINE = InnoDB),
221 PARTITION thirtythousands VALUES LESS THAN (40000)
222 (SUBPARTITION thirtythousandssp0 TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB,
223 SUBPARTITION thirtythousandssp1 TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB),
224 PARTITION fortythousands VALUES LESS THAN (50000)
225 (SUBPARTITION p40k_1 TABLESPACE = `innodb_file_per_table` DATA DIRECTORY = 'MYSQL_TMP_DIR' ENGINE = InnoDB,
226 SUBPARTITION p40k_2 TABLESPACE = `innodb_file_per_table` DATA DIRECTORY = 'MYSQL_TMP_DIR' ENGINE = InnoDB),
227 PARTITION fiftythousands VALUES LESS THAN (60000)
228 (SUBPARTITION fiftythousandssp0 TABLESPACE = `s1` ENGINE = InnoDB,
229 SUBPARTITION fiftythousandssp1 TABLESPACE = `s1` ENGINE = InnoDB),
230 PARTITION sixtythousands VALUES LESS THAN (70000)
231 (SUBPARTITION sixtythousandssp0 TABLESPACE = `innodb_system` ENGINE = InnoDB,
232 SUBPARTITION sixtythousandssp1 TABLESPACE = `innodb_system` ENGINE = InnoDB),
233 PARTITION seventythousands VALUES LESS THAN (80000)
234 (SUBPARTITION seventythousandssp0 TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB,
235 SUBPARTITION seventythousandssp1 TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB),
236 PARTITION eightythousands VALUES LESS THAN (90000)
237 (SUBPARTITION p80k_1 TABLESPACE = `innodb_file_per_table` DATA DIRECTORY = 'MYSQL_TMP_DIR' ENGINE = InnoDB,
238 SUBPARTITION p80k_2 TABLESPACE = `innodb_file_per_table` DATA DIRECTORY = 'MYSQL_TMP_DIR' ENGINE = InnoDB),
239 PARTITION ninetythousands VALUES LESS THAN (100000)
240 (SUBPARTITION p90k_1 TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB,
241 SUBPARTITION p90k_2 TABLESPACE = `innodb_system` ENGINE = InnoDB)) */
242 SHOW CREATE TABLE t2;
243 Table Create Table
244 t2 CREATE TABLE `t2` (
245 `a` int(11) DEFAULT NULL,
246 `b` int(11) DEFAULT NULL
247 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
248 /*!50100 PARTITION BY RANGE (`a`)
249 SUBPARTITION BY KEY (b)
250 (PARTITION tens VALUES LESS THAN (100)
251 (SUBPARTITION subpart11 TABLESPACE = `s1` ENGINE = InnoDB,
252 SUBPARTITION subpart12 TABLESPACE = `s1` ENGINE = InnoDB),
253 PARTITION hundreds VALUES LESS THAN (1000)
254 (SUBPARTITION subpart21 TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB,
255 SUBPARTITION subpart22 TABLESPACE = `s``1` ENGINE = InnoDB),
256 PARTITION thousands VALUES LESS THAN (10000)
257 (SUBPARTITION subpart31 TABLESPACE = `innodb_file_per_table` DATA DIRECTORY = 'MYSQL_TMP_DIR/' ENGINE = InnoDB,
258 SUBPARTITION subpart32 TABLESPACE = `innodb_file_per_table` DATA DIRECTORY = 'MYSQL_TMP_DIR/' ENGINE = InnoDB)) */
259 SET @@sql_mode = 'ANSI_QUOTES';
260 SET GLOBAL innodb_file_per_table = ON;
261 FLUSH TABLES;
262 SHOW CREATE TABLE t1;
263 Table Create Table
264 t1 CREATE TABLE "t1" (
265 "a" int(11) DEFAULT NULL,
266 "b" int(11) DEFAULT NULL
267 ) /*!50100 TABLESPACE "s1" */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
268 /*!50100 PARTITION BY RANGE ("a")
269 SUBPARTITION BY KEY (b)
270 (PARTITION tens VALUES LESS THAN (100)
271 (SUBPARTITION subpart11 TABLESPACE = "innodb_file_per_table" ENGINE = InnoDB,
272 SUBPARTITION subpart12 TABLESPACE = "innodb_file_per_table" ENGINE = InnoDB),
273 PARTITION hundreds VALUES LESS THAN (1000)
274 (SUBPARTITION subpart21 TABLESPACE = "s1" ENGINE = InnoDB,
275 SUBPARTITION subpart22 TABLESPACE = "innodb_file_per_table" ENGINE = InnoDB),
276 PARTITION onethousands VALUES LESS THAN (2000)
277 (SUBPARTITION subpart23 TABLESPACE = "s1" ENGINE = InnoDB,
278 SUBPARTITION subpart24 TABLESPACE = "innodb_file_per_table" ENGINE = InnoDB),
279 PARTITION thousands VALUES LESS THAN (10000)
280 (SUBPARTITION subpart31 TABLESPACE = "s1" ENGINE = InnoDB,
281 SUBPARTITION subpart32 TABLESPACE = "s1" ENGINE = InnoDB),
282 PARTITION tenthousands VALUES LESS THAN (20000)
283 (SUBPARTITION tenthousandssp0 TABLESPACE = "s1" ENGINE = InnoDB,
284 SUBPARTITION tenthousandssp1 TABLESPACE = "s1" ENGINE = InnoDB),
285 PARTITION twentythousands VALUES LESS THAN (30000)
286 (SUBPARTITION twentythousandssp0 TABLESPACE = "innodb_system" ENGINE = InnoDB,
287 SUBPARTITION twentythousandssp1 TABLESPACE = "innodb_system" ENGINE = InnoDB),
288 PARTITION thirtythousands VALUES LESS THAN (40000)
289 (SUBPARTITION thirtythousandssp0 TABLESPACE = "innodb_file_per_table" ENGINE = InnoDB,
290 SUBPARTITION thirtythousandssp1 TABLESPACE = "innodb_file_per_table" ENGINE = InnoDB),
291 PARTITION fortythousands VALUES LESS THAN (50000)
292 (SUBPARTITION p40k_1 TABLESPACE = "innodb_file_per_table" DATA DIRECTORY = 'MYSQL_TMP_DIR' ENGINE = InnoDB,
293 SUBPARTITION p40k_2 TABLESPACE = "innodb_file_per_table" DATA DIRECTORY = 'MYSQL_TMP_DIR' ENGINE = InnoDB),
294 PARTITION fiftythousands VALUES LESS THAN (60000)
295 (SUBPARTITION fiftythousandssp0 TABLESPACE = "s1" ENGINE = InnoDB,
296 SUBPARTITION fiftythousandssp1 TABLESPACE = "s1" ENGINE = InnoDB),
297 PARTITION sixtythousands VALUES LESS THAN (70000)
298 (SUBPARTITION sixtythousandssp0 TABLESPACE = "innodb_system" ENGINE = InnoDB,
299 SUBPARTITION sixtythousandssp1 TABLESPACE = "innodb_system" ENGINE = InnoDB),
300 PARTITION seventythousands VALUES LESS THAN (80000)
301 (SUBPARTITION seventythousandssp0 TABLESPACE = "innodb_file_per_table" ENGINE = InnoDB,
302 SUBPARTITION seventythousandssp1 TABLESPACE = "innodb_file_per_table" ENGINE = InnoDB),
303 PARTITION eightythousands VALUES LESS THAN (90000)
304 (SUBPARTITION p80k_1 TABLESPACE = "innodb_file_per_table" DATA DIRECTORY = 'MYSQL_TMP_DIR' ENGINE = InnoDB,
305 SUBPARTITION p80k_2 TABLESPACE = "innodb_file_per_table" DATA DIRECTORY = 'MYSQL_TMP_DIR' ENGINE = InnoDB),
306 PARTITION ninetythousands VALUES LESS THAN (100000)
307 (SUBPARTITION p90k_1 TABLESPACE = "innodb_file_per_table" ENGINE = InnoDB,
308 SUBPARTITION p90k_2 TABLESPACE = "innodb_system" ENGINE = InnoDB)) */
309 SHOW CREATE TABLE t2;
310 Table Create Table
311 t2 CREATE TABLE "t2" (
312 "a" int(11) DEFAULT NULL,
313 "b" int(11) DEFAULT NULL
314 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
315 /*!50100 PARTITION BY RANGE ("a")
316 SUBPARTITION BY KEY (b)
317 (PARTITION tens VALUES LESS THAN (100)
318 (SUBPARTITION subpart11 TABLESPACE = "s1" ENGINE = InnoDB,
319 SUBPARTITION subpart12 TABLESPACE = "s1" ENGINE = InnoDB),
320 PARTITION hundreds VALUES LESS THAN (1000)
321 (SUBPARTITION subpart21 TABLESPACE = "innodb_file_per_table" ENGINE = InnoDB,
322 SUBPARTITION subpart22 TABLESPACE = "s`1" ENGINE = InnoDB),
323 PARTITION thousands VALUES LESS THAN (10000)
324 (SUBPARTITION subpart31 TABLESPACE = "innodb_file_per_table" DATA DIRECTORY = 'MYSQL_TMP_DIR/' ENGINE = InnoDB,
325 SUBPARTITION subpart32 TABLESPACE = "innodb_file_per_table" DATA DIRECTORY = 'MYSQL_TMP_DIR/' ENGINE = InnoDB)) */
326 # DISCARD will only work on the first partitions that use file_per_table
327 ALTER TABLE t1 ALGORITHM=INPLACE, DISCARD TABLESPACE;
328 ERROR 0A000: ALGORITHM=COPY/INPLACE/INSTANT is not supported for this operation. Try ALGORITHM=DEFAULT.
329 ALTER TABLE t1 ALGORITHM=DEFAULT, DISCARD TABLESPACE;
330 ERROR 42000: InnoDB: Cannot discard table `test/t1#p#hundreds#sp#subpart21` because it is in a general tablespace. It must be file-per-table.
331 SET @@sql_mode = @old_sql_mode;
332 ALTER TABLE t2 DISCARD TABLESPACE;
333 ERROR 42000: InnoDB: Cannot discard table `test/t2#p#tens#sp#subpart11` because it is in a general tablespace. It must be file-per-table.
334 call mtr.add_suppression("Operating system error number .* in a file operation.");
335 call mtr.add_suppression("The error means the system cannot find the path specified.");
336 call mtr.add_suppression("Cannot open datafile for read-only: .*");
337 DROP TABLE t1;
338 DROP TABLE t2;
339 CREATE TABLE `t1` (
340 `a` int(11) DEFAULT NULL,
341 `b` int(11) DEFAULT NULL
342 ) /*!50100 TABLESPACE `s1` */ ENGINE=InnoDB DEFAULT CHARSET=latin1
343 /*!50100 PARTITION BY RANGE (a)
344 SUBPARTITION BY KEY (b)
345 (PARTITION tens VALUES LESS THAN (100)
346 (SUBPARTITION subpart11 ENGINE = InnoDB,
347 SUBPARTITION subpart12 ENGINE = InnoDB),
348 PARTITION hundreds VALUES LESS THAN (1000)
349 (SUBPARTITION subpart21 TABLESPACE = s1 ENGINE = InnoDB,
350 SUBPARTITION subpart22 ENGINE = InnoDB),
351 PARTITION onethousands VALUES LESS THAN (2000)
352 (SUBPARTITION subpart23 TABLESPACE = s1 ENGINE = InnoDB,
353 SUBPARTITION subpart24 ENGINE = InnoDB),
354 PARTITION thousands VALUES LESS THAN (10000)
355 (SUBPARTITION subpart31 TABLESPACE = s1 ENGINE = InnoDB,
356 SUBPARTITION subpart32 TABLESPACE = s1 ENGINE = InnoDB),
357 PARTITION tenthousands VALUES LESS THAN (20000)
358 (SUBPARTITION tenthousandssp0 TABLESPACE = s1 ENGINE = InnoDB,
359 SUBPARTITION tenthousandssp1 TABLESPACE = s1 ENGINE = InnoDB),
360 PARTITION twentythousands VALUES LESS THAN (30000)
361 (SUBPARTITION twentythousandssp0 TABLESPACE = innodb_system ENGINE = InnoDB,
362 SUBPARTITION twentythousandssp1 TABLESPACE = innodb_system ENGINE = InnoDB),
363 PARTITION thirtythousands VALUES LESS THAN (40000)
364 (SUBPARTITION thirtythousandssp0 ENGINE = InnoDB,
365 SUBPARTITION thirtythousandssp1 ENGINE = InnoDB),
366 PARTITION fortythousands VALUES LESS THAN (50000)
367 (SUBPARTITION p40k_1 TABLESPACE = innodb_system ENGINE = InnoDB,
368 SUBPARTITION p40k_2 DATA DIRECTORY = 'MYSQL_TMP_DIR' ENGINE = InnoDB),
369 PARTITION fiftythousands VALUES LESS THAN (60000)
370 (SUBPARTITION fiftythousandssp0 TABLESPACE = s1 ENGINE = InnoDB,
371 SUBPARTITION fiftythousandssp1 TABLESPACE = s1 ENGINE = InnoDB),
372 PARTITION sixtythousands VALUES LESS THAN (70000)
373 (SUBPARTITION sixtythousandssp0 TABLESPACE = innodb_system ENGINE = InnoDB,
374 SUBPARTITION sixtythousandssp1 TABLESPACE = innodb_system ENGINE = InnoDB),
375 PARTITION seventythousands VALUES LESS THAN (80000)
376 (SUBPARTITION seventythousandssp0 ENGINE = InnoDB,
377 SUBPARTITION seventythousandssp1 ENGINE = InnoDB),
378 PARTITION ninetythousands VALUES LESS THAN (100000)
379 (SUBPARTITION p90k_1 ENGINE = InnoDB,
380 SUBPARTITION p90k_2 TABLESPACE = innodb_system ENGINE = InnoDB)) */;
381 CREATE TABLE `t2` (
382 `a` int(11) DEFAULT NULL,
383 `b` int(11) DEFAULT NULL
384 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
385 /*!50100 PARTITION BY RANGE (a)
386 SUBPARTITION BY KEY (b)
387 (PARTITION tens VALUES LESS THAN (100)
388 (SUBPARTITION subpart11 TABLESPACE = s1 ENGINE = InnoDB,
389 SUBPARTITION subpart12 TABLESPACE = s1 ENGINE = InnoDB),
390 PARTITION hundreds VALUES LESS THAN (1000)
391 (SUBPARTITION subpart21 ENGINE = InnoDB,
392 SUBPARTITION subpart22 TABLESPACE = `s``1` ENGINE = InnoDB),
393 PARTITION thousands VALUES LESS THAN (10000)
394 (SUBPARTITION subpart31 ENGINE = InnoDB,
395 SUBPARTITION subpart32 ENGINE = InnoDB)) */;
396 === information_schema.innodb_tables and innodb_tablespaces ===
397 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
398 test/t1#p#fiftythousands#sp#fiftythousandssp0 s1 161 5 Dynamic 0 General
399 test/t1#p#fiftythousands#sp#fiftythousandssp1 s1 161 5 Dynamic 0 General
400 test/t1#p#fortythousands#sp#p40k_1 innodb_system 161 5 Dynamic 0 System
401 test/t1#p#fortythousands#sp#p40k_2 test/t1#p#fortythousands#sp#p40k_2 97 5 Dynamic 0 Single
402 test/t1#p#hundreds#sp#subpart21 s1 161 5 Dynamic 0 General
403 test/t1#p#hundreds#sp#subpart22 s1 161 5 Dynamic 0 General
404 test/t1#p#ninetythousands#sp#p90k_1 s1 161 5 Dynamic 0 General
405 test/t1#p#ninetythousands#sp#p90k_2 innodb_system 161 5 Dynamic 0 System
406 test/t1#p#onethousands#sp#subpart23 s1 161 5 Dynamic 0 General
407 test/t1#p#onethousands#sp#subpart24 s1 161 5 Dynamic 0 General
408 test/t1#p#seventythousands#sp#seventythousandssp0 s1 161 5 Dynamic 0 General
409 test/t1#p#seventythousands#sp#seventythousandssp1 s1 161 5 Dynamic 0 General
410 test/t1#p#sixtythousands#sp#sixtythousandssp0 innodb_system 161 5 Dynamic 0 System
411 test/t1#p#sixtythousands#sp#sixtythousandssp1 innodb_system 161 5 Dynamic 0 System
412 test/t1#p#tens#sp#subpart11 s1 161 5 Dynamic 0 General
413 test/t1#p#tens#sp#subpart12 s1 161 5 Dynamic 0 General
414 test/t1#p#tenthousands#sp#tenthousandssp0 s1 161 5 Dynamic 0 General
415 test/t1#p#tenthousands#sp#tenthousandssp1 s1 161 5 Dynamic 0 General
416 test/t1#p#thirtythousands#sp#thirtythousandssp0 s1 161 5 Dynamic 0 General
417 test/t1#p#thirtythousands#sp#thirtythousandssp1 s1 161 5 Dynamic 0 General
418 test/t1#p#thousands#sp#subpart31 s1 161 5 Dynamic 0 General
419 test/t1#p#thousands#sp#subpart32 s1 161 5 Dynamic 0 General
420 test/t1#p#twentythousands#sp#twentythousandssp0 innodb_system 161 5 Dynamic 0 System
421 test/t1#p#twentythousands#sp#twentythousandssp1 innodb_system 161 5 Dynamic 0 System
422 test/t2#p#hundreds#sp#subpart21 test/t2#p#hundreds#sp#subpart21 33 5 Dynamic 0 Single
423 test/t2#p#hundreds#sp#subpart22 s`1 161 5 Dynamic 0 General
424 test/t2#p#tens#sp#subpart11 s1 161 5 Dynamic 0 General
425 test/t2#p#tens#sp#subpart12 s1 161 5 Dynamic 0 General
426 test/t2#p#thousands#sp#subpart31 test/t2#p#thousands#sp#subpart31 33 5 Dynamic 0 Single
427 test/t2#p#thousands#sp#subpart32 test/t2#p#thousands#sp#subpart32 33 5 Dynamic 0 Single
428 test/t3 test/t3 97 4 Dynamic 0 Single
429 === information_schema.innodb_tablespaces and innodb_datafiles ===
430 Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path
431 s1 General DEFAULT 0 Any s1.ibd
432 s`1 General DEFAULT 0 Any s-1.ibd
433 test/t3 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/test/t3.ibd
434 test/t1#p#fortythousands#sp#p40k_2 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/test/t1#p#fortythousands#sp#p40k_2.ibd
435 test/t2#p#hundreds#sp#subpart21 Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t2#p#hundreds#sp#subpart21.ibd
436 test/t2#p#thousands#sp#subpart31 Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t2#p#thousands#sp#subpart31.ibd
437 test/t2#p#thousands#sp#subpart32 Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t2#p#thousands#sp#subpart32.ibd
438 === information_schema.files ===
439 Space_Name File_Type Engine Status Tablespace_Name Path
440 s1 TABLESPACE InnoDB NORMAL s1 MYSQLD_DATADIR/s1.ibd
441 s`1 TABLESPACE InnoDB NORMAL s`1 MYSQLD_DATADIR/s-1.ibd
442 test/t3 TABLESPACE InnoDB NORMAL test/t3 MYSQL_TMP_DIR/test/t3.ibd
443 test/t1#p#fortythousands#sp#p40k_2 TABLESPACE InnoDB NORMAL test/t1#p#fortythousands#sp#p40k_2 MYSQL_TMP_DIR/test/t1#p#fortythousands#sp#p40k_2.ibd
444 test/t2#p#hundreds#sp#subpart21 TABLESPACE InnoDB NORMAL test/t2#p#hundreds#sp#subpart21 MYSQLD_DATADIR/test/t2#p#hundreds#sp#subpart21.ibd
445 test/t2#p#thousands#sp#subpart31 TABLESPACE InnoDB NORMAL test/t2#p#thousands#sp#subpart31 MYSQLD_DATADIR/test/t2#p#thousands#sp#subpart31.ibd
446 test/t2#p#thousands#sp#subpart32 TABLESPACE InnoDB NORMAL test/t2#p#thousands#sp#subpart32 MYSQLD_DATADIR/test/t2#p#thousands#sp#subpart32.ibd
447 SHOW CREATE TABLE t1;
448 Table Create Table
449 t1 CREATE TABLE `t1` (
450 `a` int(11) DEFAULT NULL,
451 `b` int(11) DEFAULT NULL
452 ) /*!50100 TABLESPACE `s1` */ ENGINE=InnoDB DEFAULT CHARSET=latin1
453 /*!50100 PARTITION BY RANGE (`a`)
454 SUBPARTITION BY KEY (b)
455 (PARTITION tens VALUES LESS THAN (100)
456 (SUBPARTITION subpart11 TABLESPACE = `s1` ENGINE = InnoDB,
457 SUBPARTITION subpart12 TABLESPACE = `s1` ENGINE = InnoDB),
458 PARTITION hundreds VALUES LESS THAN (1000)
459 (SUBPARTITION subpart21 TABLESPACE = `s1` ENGINE = InnoDB,
460 SUBPARTITION subpart22 TABLESPACE = `s1` ENGINE = InnoDB),
461 PARTITION onethousands VALUES LESS THAN (2000)
462 (SUBPARTITION subpart23 TABLESPACE = `s1` ENGINE = InnoDB,
463 SUBPARTITION subpart24 TABLESPACE = `s1` ENGINE = InnoDB),
464 PARTITION thousands VALUES LESS THAN (10000)
465 (SUBPARTITION subpart31 TABLESPACE = `s1` ENGINE = InnoDB,
466 SUBPARTITION subpart32 TABLESPACE = `s1` ENGINE = InnoDB),
467 PARTITION tenthousands VALUES LESS THAN (20000)
468 (SUBPARTITION tenthousandssp0 TABLESPACE = `s1` ENGINE = InnoDB,
469 SUBPARTITION tenthousandssp1 TABLESPACE = `s1` ENGINE = InnoDB),
470 PARTITION twentythousands VALUES LESS THAN (30000)
471 (SUBPARTITION twentythousandssp0 TABLESPACE = `innodb_system` ENGINE = InnoDB,
472 SUBPARTITION twentythousandssp1 TABLESPACE = `innodb_system` ENGINE = InnoDB),
473 PARTITION thirtythousands VALUES LESS THAN (40000)
474 (SUBPARTITION thirtythousandssp0 TABLESPACE = `s1` ENGINE = InnoDB,
475 SUBPARTITION thirtythousandssp1 TABLESPACE = `s1` ENGINE = InnoDB),
476 PARTITION fortythousands VALUES LESS THAN (50000)
477 (SUBPARTITION p40k_1 TABLESPACE = `innodb_system` ENGINE = InnoDB,
478 SUBPARTITION p40k_2 TABLESPACE = `innodb_file_per_table` DATA DIRECTORY = 'MYSQL_TMP_DIR/' ENGINE = InnoDB),
479 PARTITION fiftythousands VALUES LESS THAN (60000)
480 (SUBPARTITION fiftythousandssp0 TABLESPACE = `s1` ENGINE = InnoDB,
481 SUBPARTITION fiftythousandssp1 TABLESPACE = `s1` ENGINE = InnoDB),
482 PARTITION sixtythousands VALUES LESS THAN (70000)
483 (SUBPARTITION sixtythousandssp0 TABLESPACE = `innodb_system` ENGINE = InnoDB,
484 SUBPARTITION sixtythousandssp1 TABLESPACE = `innodb_system` ENGINE = InnoDB),
485 PARTITION seventythousands VALUES LESS THAN (80000)
486 (SUBPARTITION seventythousandssp0 TABLESPACE = `s1` ENGINE = InnoDB,
487 SUBPARTITION seventythousandssp1 TABLESPACE = `s1` ENGINE = InnoDB),
488 PARTITION ninetythousands VALUES LESS THAN (100000)
489 (SUBPARTITION p90k_1 TABLESPACE = `s1` ENGINE = InnoDB,
490 SUBPARTITION p90k_2 TABLESPACE = `innodb_system` ENGINE = InnoDB)) */
491 SHOW CREATE TABLE t2;
492 Table Create Table
493 t2 CREATE TABLE `t2` (
494 `a` int(11) DEFAULT NULL,
495 `b` int(11) DEFAULT NULL
496 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
497 /*!50100 PARTITION BY RANGE (`a`)
498 SUBPARTITION BY KEY (b)
499 (PARTITION tens VALUES LESS THAN (100)
500 (SUBPARTITION subpart11 TABLESPACE = `s1` ENGINE = InnoDB,
501 SUBPARTITION subpart12 TABLESPACE = `s1` ENGINE = InnoDB),
502 PARTITION hundreds VALUES LESS THAN (1000)
503 (SUBPARTITION subpart21 TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB,
504 SUBPARTITION subpart22 TABLESPACE = `s``1` ENGINE = InnoDB),
505 PARTITION thousands VALUES LESS THAN (10000)
506 (SUBPARTITION subpart31 TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB,
507 SUBPARTITION subpart32 TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB)) */
508 SHOW CREATE TABLE t3;
509 Table Create Table
510 t3 CREATE TABLE `t3` (
511 `a` int(11) DEFAULT NULL
512 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/'
513 ALTER TABLE t3 DISCARD TABLESPACE;
514 DROP TABLE t1, t2, t3;
515 DROP TABLESPACE s1;
516 DROP TABLESPACE `s``1`;
517 CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
518 CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=COMPACT;
519 CREATE TABLE ti
520 (id INT, amount DECIMAL(7,2), tr_date DATE) TABLESPACE ts1
521 ENGINE=INNODB
522 PARTITION BY HASH( MONTH(tr_date) )
523 PARTITIONS 6;
524 DROP TABLE t1,ti;
525 DROP TABLESPACE ts1;
526 #
527 # Clean-up.
528 #
529 SET @@global.innodb_file_per_table=@old_innodb_file_per_table;