Debian Codegraph @

mysql-test/suite/parts/r/partition_basic_symlink_innodb.result

raw
1 #
2 # Verify that the DATA/INDEX DIR is stored and used if ALTER to MyISAM.
3 #
4 SET @file_per_table= @@GLOBAL.innodb_file_per_table;
5 SET @strict_mode= @@SESSION.innodb_strict_mode;
6 SET SESSION innodb_strict_mode = ON;
7 #
8 # InnoDB only supports DATA DIRECTORY with innodb_file_per_table=ON
9 #
10 SET GLOBAL innodb_file_per_table = OFF;
11 Warnings:
12 Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release
13 CREATE TABLE t1 (c1 INT, KEY(c1)) ENGINE = InnoDB
14 PARTITION BY HASH (c1) (
15 PARTITION p0
16 DATA DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-data-dir'
17 INDEX DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-idx-dir',
18 PARTITION p1
19 DATA DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-data-dir'
20 INDEX DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-idx-dir'
21 );
22 ERROR HY000: Can't create table `test`.`t1` (errno: 140 "Wrong create options")
23 SHOW WARNINGS;
24 Level Code Message
25 Warning 1478 InnoDB: DATA DIRECTORY requires innodb_file_per_table.
26 Error 1005 Can't create table `test`.`t1` (errno: 140 "Wrong create options")
27 Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB
28 Error 6 Error on delete of 'MYSQLD_DATADIR/test/t1.par' (Errcode: 2 "No such file or directory")
29 #
30 # InnoDB is different from MyISAM in that it uses a text file
31 # with an '.isl' extension instead of a symbolic link so that
32 # the tablespace can be re-located on any OS. Also, instead of
33 # putting the file directly into the DATA DIRECTORY,
34 # it adds a folder under it with the name of the database.
35 # Since strict mode is off, InnoDB ignores the INDEX DIRECTORY
36 # and it is no longer part of the definition.
37 #
38 SET SESSION innodb_strict_mode = OFF;
39 SET GLOBAL innodb_file_per_table = ON;
40 Warnings:
41 Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release
42 CREATE TABLE t1 (c1 INT, KEY(c1)) ENGINE = InnoDB
43 PARTITION BY HASH (c1)
44 (PARTITION p0
45 DATA DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-data-dir'
46 INDEX DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-idx-dir',
47 PARTITION p1
48 DATA DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-data-dir'
49 INDEX DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-idx-dir'
50 );
51 Warnings:
52 Note 1618 INDEX DIRECTORY option ignored
53 Note 1618 INDEX DIRECTORY option ignored
54 SHOW WARNINGS;
55 Level Code Message
56 Note 1618 INDEX DIRECTORY option ignored
57 Note 1618 INDEX DIRECTORY option ignored
58 # Verifying .frm, .par, .isl & .ibd files
59 ---- MYSQLD_DATADIR/test
60 db.opt
61 t1#P#p0.isl
62 t1#P#p1.isl
63 t1.frm
64 t1.par
65 ---- MYSQLTEST_VARDIR/mysql-test-data-dir/test
66 t1#P#p0.ibd
67 t1#P#p1.ibd
68 # The ibd tablespaces should not be directly under the DATA DIRECTORY
69 ---- MYSQLTEST_VARDIR/mysql-test-data-dir
70 test
71 ---- MYSQLTEST_VARDIR/mysql-test-idx-dir
72 FLUSH TABLES;
73 SHOW CREATE TABLE t1;
74 Table Create Table
75 t1 CREATE TABLE `t1` (
76 `c1` int(11) DEFAULT NULL,
77 KEY `c1` (`c1`)
78 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
79 PARTITION BY HASH (`c1`)
80 (PARTITION `p0` DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB,
81 PARTITION `p1` DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB)
82 #
83 # Verify that the DATA/INDEX DIRECTORY is stored and used if we
84 # ALTER TABLE to MyISAM.
85 #
86 ALTER TABLE t1 engine=MyISAM;
87 SHOW CREATE TABLE t1;
88 Table Create Table
89 t1 CREATE TABLE `t1` (
90 `c1` int(11) DEFAULT NULL,
91 KEY `c1` (`c1`)
92 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
93 PARTITION BY HASH (`c1`)
94 (PARTITION `p0` DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = MyISAM,
95 PARTITION `p1` DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = MyISAM)
96 # Verifying .frm, .par and MyISAM files (.MYD, MYI)
97 db.opt
98 t1#P#p0.MYD
99 t1#P#p0.MYI
100 t1#P#p1.MYD
101 t1#P#p1.MYI
102 t1.frm
103 t1.par
104 ---- MYSQLTEST_VARDIR/mysql-test-data-dir
105 t1#P#p0.MYD
106 t1#P#p1.MYD
107 test
108 ---- MYSQLTEST_VARDIR/mysql-test-idx-dir
109 #
110 # Now verify that the DATA DIRECTORY is used again if we
111 # ALTER TABLE back to InnoDB.
112 #
113 SET SESSION innodb_strict_mode = ON;
114 ALTER TABLE t1 engine=InnoDB;
115 SHOW CREATE TABLE t1;
116 Table Create Table
117 t1 CREATE TABLE `t1` (
118 `c1` int(11) DEFAULT NULL,
119 KEY `c1` (`c1`)
120 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
121 PARTITION BY HASH (`c1`)
122 (PARTITION `p0` DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB,
123 PARTITION `p1` DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB)
124 # Verifying .frm, .par, .isl and InnoDB .ibd files
125 ---- MYSQLD_DATADIR/test
126 db.opt
127 t1#P#p0.isl
128 t1#P#p1.isl
129 t1.frm
130 t1.par
131 ---- MYSQLTEST_VARDIR/mysql-test-data-dir
132 test
133 ---- MYSQLTEST_VARDIR/mysql-test-idx-dir
134 ---- MYSQLTEST_VARDIR/mysql-test-data-dir/test
135 t1#P#p0.ibd
136 t1#P#p1.ibd
137 DROP TABLE t1;
138 #
139 # MDEV-14611 ALTER TABLE EXCHANGE PARTITION does not work
140 # properly when used with DATA DIRECTORY
141 #
142 SET GLOBAL innodb_file_per_table = ON;
143 Warnings:
144 Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release
145 CREATE TABLE t1
146 (
147 myid INT(11) NOT NULL,
148 myval VARCHAR(10),
149 PRIMARY KEY (myid)
150 ) ENGINE=INNODB PARTITION BY KEY (myid)
151 (
152 PARTITION p0001 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB,
153 PARTITION p0002 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB,
154 PARTITION p0003 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB,
155 PARTITION p0004 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB
156 );
157 CREATE TABLE t2
158 (
159 myid INT(11) NOT NULL,
160 myval VARCHAR(10),
161 PRIMARY KEY (myid)
162 ) ENGINE=INNODB DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir';
163 ALTER TABLE t1 EXCHANGE PARTITION p0001 WITH TABLE t2;
164 SHOW CREATE TABLE t1;
165 Table Create Table
166 t1 CREATE TABLE `t1` (
167 `myid` int(11) NOT NULL,
168 `myval` varchar(10) DEFAULT NULL,
169 PRIMARY KEY (`myid`)
170 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
171 PARTITION BY KEY (`myid`)
172 (PARTITION `p0001` DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB,
173 PARTITION `p0002` DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB,
174 PARTITION `p0003` DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB,
175 PARTITION `p0004` DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB)
176 DROP TABLE t1, t2;
177 CREATE TABLE t1
178 (
179 myid INT(11) NOT NULL,
180 myval VARCHAR(10),
181 PRIMARY KEY (myid)
182 ) ENGINE=INNODB PARTITION BY RANGE (myid)
183 (
184 PARTITION p0001 VALUES LESS THAN (50) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB,
185 PARTITION p0002 VALUES LESS THAN (150) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB,
186 PARTITION p0003 VALUES LESS THAN (1050) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB,
187 PARTITION p0004 VALUES LESS THAN (10050) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB
188 );
189 CREATE TABLE t2
190 (
191 myid INT(11) NOT NULL,
192 myval VARCHAR(10),
193 PRIMARY KEY (myid)
194 ) ENGINE=INNODB DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir';
195 insert into t1 values (1, 'one');
196 insert into t2 values (2, 'two'), (3, 'threee'), (4, 'four');
197 select * from t1;
198 myid myval
199 1 one
200 ALTER TABLE t1 EXCHANGE PARTITION p0001 WITH TABLE t2;
201 SHOW CREATE TABLE t1;
202 Table Create Table
203 t1 CREATE TABLE `t1` (
204 `myid` int(11) NOT NULL,
205 `myval` varchar(10) DEFAULT NULL,
206 PRIMARY KEY (`myid`)
207 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
208 PARTITION BY RANGE (`myid`)
209 (PARTITION `p0001` VALUES LESS THAN (50) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = InnoDB,
210 PARTITION `p0002` VALUES LESS THAN (150) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB,
211 PARTITION `p0003` VALUES LESS THAN (1050) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB,
212 PARTITION `p0004` VALUES LESS THAN (10050) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB)
213 SHOW CREATE TABLE t2;
214 Table Create Table
215 t2 CREATE TABLE `t2` (
216 `myid` int(11) NOT NULL,
217 `myval` varchar(10) DEFAULT NULL,
218 PRIMARY KEY (`myid`)
219 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci DATA DIRECTORY='MYSQLTEST_VARDIR/mysql-test-data-dir/'
220 select * from t1;
221 myid myval
222 2 two
223 3 threee
224 4 four
225 select * from t2;
226 myid myval
227 1 one
228 DROP TABLE t1, t2;
229 CREATE TABLE t1
230 (
231 myid INT(11) NOT NULL,
232 myval VARCHAR(10),
233 PRIMARY KEY (myid)
234 ) ENGINE=INNODB PARTITION BY RANGE (myid)
235 (
236 PARTITION p0001 VALUES LESS THAN (50) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB,
237 PARTITION p0002 VALUES LESS THAN (150) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB,
238 PARTITION p0003 VALUES LESS THAN (1050) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB,
239 PARTITION p0004 VALUES LESS THAN (10050) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB
240 );
241 CREATE TABLE t2
242 (
243 myid INT(11) NOT NULL,
244 myval VARCHAR(10),
245 PRIMARY KEY (myid)
246 ) ENGINE=INNODB;
247 insert into t1 values (1, 'one');
248 insert into t2 values (2, 'two'), (3, 'threee'), (4, 'four');
249 select * from t1;
250 myid myval
251 1 one
252 ALTER TABLE t1 EXCHANGE PARTITION p0001 WITH TABLE t2;
253 SHOW CREATE TABLE t1;
254 Table Create Table
255 t1 CREATE TABLE `t1` (
256 `myid` int(11) NOT NULL,
257 `myval` varchar(10) DEFAULT NULL,
258 PRIMARY KEY (`myid`)
259 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
260 PARTITION BY RANGE (`myid`)
261 (PARTITION `p0001` VALUES LESS THAN (50) ENGINE = InnoDB,
262 PARTITION `p0002` VALUES LESS THAN (150) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB,
263 PARTITION `p0003` VALUES LESS THAN (1050) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB,
264 PARTITION `p0004` VALUES LESS THAN (10050) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB)
265 SHOW CREATE TABLE t2;
266 Table Create Table
267 t2 CREATE TABLE `t2` (
268 `myid` int(11) NOT NULL,
269 `myval` varchar(10) DEFAULT NULL,
270 PRIMARY KEY (`myid`)
271 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci DATA DIRECTORY='MYSQLTEST_VARDIR/mysql-test-data-dir/'
272 select * from t1;
273 myid myval
274 2 two
275 3 threee
276 4 four
277 select * from t2;
278 myid myval
279 1 one
280 DROP TABLE t1, t2;
281 CREATE TABLE t1
282 (
283 myid INT(11) NOT NULL,
284 myval VARCHAR(10),
285 PRIMARY KEY (myid)
286 ) ENGINE=INNODB PARTITION BY RANGE (myid)
287 (
288 PARTITION p0001 VALUES LESS THAN (50) ENGINE = INNODB,
289 PARTITION p0002 VALUES LESS THAN (150) ENGINE = INNODB,
290 PARTITION p0003 VALUES LESS THAN (1050) ENGINE = INNODB,
291 PARTITION p0004 VALUES LESS THAN (10050) ENGINE = INNODB
292 );
293 CREATE TABLE t2
294 (
295 myid INT(11) NOT NULL,
296 myval VARCHAR(10),
297 PRIMARY KEY (myid)
298 ) ENGINE=INNODB DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir';
299 insert into t1 values (1, 'one');
300 insert into t2 values (2, 'two'), (3, 'threee'), (4, 'four');
301 select * from t1;
302 myid myval
303 1 one
304 ALTER TABLE t1 EXCHANGE PARTITION p0001 WITH TABLE t2;
305 SHOW CREATE TABLE t1;
306 Table Create Table
307 t1 CREATE TABLE `t1` (
308 `myid` int(11) NOT NULL,
309 `myval` varchar(10) DEFAULT NULL,
310 PRIMARY KEY (`myid`)
311 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
312 PARTITION BY RANGE (`myid`)
313 (PARTITION `p0001` VALUES LESS THAN (50) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = InnoDB,
314 PARTITION `p0002` VALUES LESS THAN (150) ENGINE = InnoDB,
315 PARTITION `p0003` VALUES LESS THAN (1050) ENGINE = InnoDB,
316 PARTITION `p0004` VALUES LESS THAN (10050) ENGINE = InnoDB)
317 SHOW CREATE TABLE t2;
318 Table Create Table
319 t2 CREATE TABLE `t2` (
320 `myid` int(11) NOT NULL,
321 `myval` varchar(10) DEFAULT NULL,
322 PRIMARY KEY (`myid`)
323 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
324 select * from t1;
325 myid myval
326 2 two
327 3 threee
328 4 four
329 select * from t2;
330 myid myval
331 1 one
332 DROP TABLE t1, t2;
333 #
334 # Cleanup
335 #
336 SET GLOBAL innodb_file_per_table=@file_per_table;
337 Warnings:
338 Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release
339 SET SESSION innodb_strict_mode=@strict_mode;