Debian Codegraph @

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

raw
1 #
2 #Bug #33398681 Innodb ibd tablespace file is moved back from innodb_directories to datadir after ALTER TABLE
3 #
4 # Set different paths for --datadir
5 # Create innodb_directories folder
6 # Create external datadir folder
7 # Create external datadir2 folder
8 # Restart the engine to make the external directory known.
9 # restart: --innodb-directories=MYSQL_TMP_DIR/external_dir;MYSQL_TMP_DIR/external_dir2
10 # Create table
11 CREATE DATABASE dbtest;
12 USE dbtest;
13 # Normal tables
14 CREATE TABLE `t1a` (`c1` INT);
15 CREATE TABLE `t1b` (`c1` INT);
16 CREATE TABLE `t1c` (`c1` INT);
17 # Normal tables with data directory clause
18 CREATE TABLE `t2a` (`c1` INT) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir';
19 CREATE TABLE `t2b` (`c1` INT) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir';
20 CREATE TABLE `t2c` (`c1` INT) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir';
21 # Partition tables
22 CREATE TABLE `t3a` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20));
23 CREATE TABLE `t3b` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20));
24 CREATE TABLE `t3c` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20));
25 # Partition tables with data directory clause
26 CREATE TABLE `t4a` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir', PARTITION p1 VALUES LESS THAN (20) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir');
27 CREATE TABLE `t4b` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir', PARTITION p1 VALUES LESS THAN (20) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir');
28 CREATE TABLE `t4c` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir', PARTITION p1 VALUES LESS THAN (20) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir');
29 # Normal tables with data directory clause 2
30 CREATE TABLE `t5a` (`c1` INT) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir2';
31 CREATE TABLE `t5b` (`c1` INT) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir2';
32 CREATE TABLE `t5c` (`c1` INT) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir2';
33 # Partition tables with data directory clause
34 CREATE TABLE `t6a` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir2', PARTITION p1 VALUES LESS THAN (20) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir2');
35 CREATE TABLE `t6b` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir2', PARTITION p1 VALUES LESS THAN (20) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir2');
36 CREATE TABLE `t6c` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir2', PARTITION p1 VALUES LESS THAN (20) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir2');
37 # Shutdown server
38 # 1. ibd files for t1a, t1b, t1c, t3a, t3b, t3c
39 # Check that the ibd files for each table exist in the working (dbtest) folder
40 t1a.ibd
41 t1b.ibd
42 t1c.ibd
43 t3a#p#p0.ibd
44 t3a#p#p1.ibd
45 t3b#p#p0.ibd
46 t3b#p#p1.ibd
47 t3c#p#p0.ibd
48 t3c#p#p1.ibd
49 # Move ibd files from default directory to new innodb directory
50 # Check that the files exist in the innodb_directories folder before alter after moving
51 t1a.ibd
52 t1b.ibd
53 t1c.ibd
54 t3a#p#p0.ibd
55 t3a#p#p1.ibd
56 t3b#p#p0.ibd
57 t3b#p#p1.ibd
58 t3c#p#p0.ibd
59 t3c#p#p1.ibd
60 # 2. ibd files for t2a, t2b, t2c, t4a, t4b, t4c
61 # Check that the ibd files for each table exist in the external directory where the tables are created
62 t2a.ibd
63 t2b.ibd
64 t2c.ibd
65 t4a#p#p0.ibd
66 t4a#p#p1.ibd
67 t4b#p#p0.ibd
68 t4b#p#p1.ibd
69 t4c#p#p0.ibd
70 t4c#p#p1.ibd
71 # 3. ibd files for t5a, t5b, t5c, t6a, t6b, t6c
72 # Check that the ibd files for each table exist in the external directory2 where the tables are created
73 t5a.ibd
74 t5b.ibd
75 t5c.ibd
76 t6a#p#p0.ibd
77 t6a#p#p1.ibd
78 t6b#p#p0.ibd
79 t6b#p#p1.ibd
80 t6c#p#p0.ibd
81 t6c#p#p1.ibd
82 # Move ibd files for t5a, t5b, t5c, t6a, t6b, t6c from external directory2 to default directory
83 # Check that the ibd files for t5a, t5b, t5c, t6a, t6b, t6c now exist in working (dbtest) folder
84 t5a.ibd
85 t5b.ibd
86 t5c.ibd
87 t6a#p#p0.ibd
88 t6a#p#p1.ibd
89 t6b#p#p0.ibd
90 t6b#p#p1.ibd
91 t6c#p#p0.ibd
92 t6c#p#p1.ibd
93 # Restart the server
94 # restart: --innodb-directories=MYSQLTEST_VARDIR/innodb_directories;MYSQL_TMP_DIR/external_dir;MYSQL_TMP_DIR/external_dir2
95 # Alter table
96 ALTER TABLE dbtest.t1a FORCE;
97 ALTER TABLE dbtest.t1b FORCE, ALGORITHM=INPLACE;
98 ALTER TABLE dbtest.t1c FORCE, ALGORITHM=COPY;
99 ALTER TABLE dbtest.t2a FORCE;
100 ALTER TABLE dbtest.t2b FORCE, ALGORITHM=INPLACE;
101 ALTER TABLE dbtest.t2c FORCE, ALGORITHM=COPY;
102 ALTER TABLE dbtest.t3a FORCE;
103 ALTER TABLE dbtest.t3b FORCE, ALGORITHM=INPLACE;
104 ALTER TABLE dbtest.t3c FORCE, ALGORITHM=COPY;
105 ALTER TABLE dbtest.t4a FORCE;
106 ALTER TABLE dbtest.t4b FORCE, ALGORITHM=INPLACE;
107 ALTER TABLE dbtest.t4c FORCE, ALGORITHM=COPY;
108 ALTER TABLE dbtest.t5a FORCE;
109 ALTER TABLE dbtest.t5b FORCE, ALGORITHM=INPLACE;
110 ALTER TABLE dbtest.t5c FORCE, ALGORITHM=COPY;
111 ALTER TABLE dbtest.t6a FORCE;
112 ALTER TABLE dbtest.t6b FORCE, ALGORITHM=INPLACE;
113 ALTER TABLE dbtest.t6c FORCE, ALGORITHM=COPY;
114 # Check that the ibd files for t1a, t1b, t1c, t3a, t3b, t3c still exist in the innodb_directories folder
115 t1a.ibd
116 t1b.ibd
117 t1c.ibd
118 t3a#p#p0.ibd
119 t3a#p#p1.ibd
120 t3b#p#p0.ibd
121 t3b#p#p1.ibd
122 t3c#p#p0.ibd
123 t3c#p#p1.ibd
124 # Check that the ibd files for t1a, t1b, t1c, t3a, t3b, t3c do not exist and ibd files for t5a, t5b, t5c, t6a, t6b, t6c exist in the data directory folder
125 t5a.ibd
126 t5b.ibd
127 t5c.ibd
128 t6a#p#p0.ibd
129 t6a#p#p1.ibd
130 t6b#p#p0.ibd
131 t6b#p#p1.ibd
132 t6c#p#p0.ibd
133 t6c#p#p1.ibd
134 # Check that the ibd files for t5a, t5b, t5c, t6a, t6b, t6c do not exist in external directory2 where the tables are created
135 # Check that the ibd files for t2a, t2b, t2c, t4a, t4b, t4c still exist in the external directory where the tables are created
136 t2a.ibd
137 t2b.ibd
138 t2c.ibd
139 t4a#p#p0.ibd
140 t4a#p#p1.ibd
141 t4b#p#p0.ibd
142 t4b#p#p1.ibd
143 t4c#p#p0.ibd
144 t4c#p#p1.ibd
145 # Shutdown server
146 # Now move ibd files for t1a, t1b, t1c, t3a, t3b, t3c from innodb_directories folder to default directory
147 # Restart the server
148 # restart: --innodb-directories=MYSQLTEST_VARDIR/innodb_directories;MYSQL_TMP_DIR/external_dir;MYSQL_TMP_DIR/external_dir2
149 # Alter table
150 ALTER TABLE dbtest.t1a FORCE;
151 ALTER TABLE dbtest.t1b FORCE, ALGORITHM=INPLACE;
152 ALTER TABLE dbtest.t1c FORCE, ALGORITHM=COPY;
153 ALTER TABLE dbtest.t3a FORCE;
154 ALTER TABLE dbtest.t3b FORCE, ALGORITHM=INPLACE;
155 ALTER TABLE dbtest.t3c FORCE, ALGORITHM=COPY;
156 # Check ibd files for t1a, t1b, t1c, t3a, t3b, t3c exist in default directory
157 t1a.ibd
158 t1b.ibd
159 t1c.ibd
160 t3a#p#p0.ibd
161 t3a#p#p1.ibd
162 t3b#p#p0.ibd
163 t3b#p#p1.ibd
164 t3c#p#p0.ibd
165 t3c#p#p1.ibd
166 t5a.ibd
167 t5b.ibd
168 t5c.ibd
169 t6a#p#p0.ibd
170 t6a#p#p1.ibd
171 t6b#p#p0.ibd
172 t6b#p#p1.ibd
173 t6c#p#p0.ibd
174 t6c#p#p1.ibd
175 # Check ibd files for t1a, t1b, t1c, t3a, t3b, t3c do not exist in innodb_directories
176 # Clean Up
177 # Drop tables
178 DROP TABLE dbtest.t1a;
179 DROP TABLE dbtest.t1b;
180 DROP TABLE dbtest.t1c;
181 DROP TABLE dbtest.t2a;
182 DROP TABLE dbtest.t2b;
183 DROP TABLE dbtest.t2c;
184 DROP TABLE dbtest.t3a;
185 DROP TABLE dbtest.t3b;
186 DROP TABLE dbtest.t3c;
187 DROP TABLE dbtest.t4a;
188 DROP TABLE dbtest.t4b;
189 DROP TABLE dbtest.t4c;
190 DROP TABLE dbtest.t5a;
191 DROP TABLE dbtest.t5b;
192 DROP TABLE dbtest.t5c;
193 DROP TABLE dbtest.t6a;
194 DROP TABLE dbtest.t6b;
195 DROP TABLE dbtest.t6c;
196 # Clean up for other tests
197 # Additional test for tables with data directory clause
198 # Move non-partitioned table with data dir clause at EXTERNAL_DIR1 to EXTERNAL_DIR2
199 CREATE TABLE `t7a` (`c1` INT) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir';
200 CREATE TABLE `t7b` (`c1` INT) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir';
201 CREATE TABLE `t7c` (`c1` INT) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir';
202 # Shutdown server
203 # Check that the ibd files for t7a, t7b, t7c exist in EXTERNAL_DIR
204 t7a.ibd
205 t7b.ibd
206 t7c.ibd
207 # Move files from EXTERNAL_DIR to EXTERNAL_DIR2
208 # Check that the ibd files now exist in EXTERNAL_DIR2
209 t7a.ibd
210 t7b.ibd
211 t7c.ibd
212 # Restart the server
213 # restart: --innodb-directories=MYSQL_TMP_DIR/external_dir;MYSQL_TMP_DIR/external_dir2
214 # Alter table
215 ALTER TABLE dbtest.t7a FORCE;
216 ALTER TABLE dbtest.t7b FORCE, ALGORITHM=INPLACE;
217 ALTER TABLE dbtest.t7c FORCE, ALGORITHM=COPY;
218 # Check that the ibd files for t7a, t7b, t7c still exist in EXTERNAL_DIR2
219 t7a.ibd
220 t7b.ibd
221 t7c.ibd
222 # Check that the ibd files for t7a, t7b, t7c do not exist in EXTERNAL_DIR
223 # Clean up
224 # Drop tables
225 DROP TABLE dbtest.t7a;
226 DROP TABLE dbtest.t7b;
227 DROP TABLE dbtest.t7c;
228 # Clean up for other tests
229 # Additional tests for partitioned tables
230 # 1. Creating partitioned table with data directory clause and both partitions in different directories
231 # Partition tables with data directory clause
232 CREATE TABLE `t8a` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir', PARTITION p1 VALUES LESS THAN (20) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir2');
233 CREATE TABLE `t8b` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir', PARTITION p1 VALUES LESS THAN (20) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir2');
234 CREATE TABLE `t8c` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir', PARTITION p1 VALUES LESS THAN (20) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir2');
235 # Partition table with one partition in external directory and one in default directory
236 CREATE TABLE `t8d` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY='MYSQL_TMP_DIR/external_dir', PARTITION p1 VALUES LESS THAN (20) );
237 # Shutdown server
238 # Check that for partition p0 for t8a, t8b, t8c and t8d exist in EXTERNAL_DIR and partition p1 for for t8a, t8b, t8c exist in EXTERNAL_DIR2
239 # Listing EXTERNAL_DIR
240 t8a#p#p0.ibd
241 t8b#p#p0.ibd
242 t8c#p#p0.ibd
243 t8d#p#p0.ibd
244 # Listing EXTERNAL_DIR2
245 t8a#p#p1.ibd
246 t8b#p#p1.ibd
247 t8c#p#p1.ibd
248 # List default directory. t8d's p1 partition should be present
249 t8d#p#p1.ibd
250 # Restart the server
251 # restart: --innodb-directories=MYSQL_TMP_DIR/external_dir;MYSQL_TMP_DIR/external_dir2
252 # Alter table
253 ALTER TABLE dbtest.t8a FORCE;
254 ALTER TABLE dbtest.t8b FORCE, ALGORITHM=INPLACE;
255 ALTER TABLE dbtest.t8c FORCE, ALGORITHM=COPY;
256 ALTER TABLE dbtest.t8d FORCE, ALGORITHM=COPY;
257 # Check that for partition p0 for t8a, t8b, t8c still exist in EXTERNAL_DIR and partition p1 for for t8a, t8b, t8c still exist in EXTERNAL_DIR2
258 # Listing EXTERNAL_DIR
259 t8a#p#p0.ibd
260 t8b#p#p0.ibd
261 t8c#p#p0.ibd
262 t8d#p#p0.ibd
263 # Listing EXTERNAL_DIR2
264 t8a#p#p1.ibd
265 t8b#p#p1.ibd
266 t8c#p#p1.ibd
267 # List default directory. t8d's p1 partition should be present
268 t8d#p#p1.ibd
269 # Shutdown server
270 # Now lets move all partitioned tables p0 of t8a, t8b, t8c, t8d from EXTERNAL_DIR and partitioned tables p1 of t8a, t8b, t8c from EXTERNAL_DIR2 to default directory
271 # Restart the server
272 # restart: --innodb-directories=MYSQL_TMP_DIR/external_dir;MYSQL_TMP_DIR/external_dir2
273 # Alter table
274 ALTER TABLE dbtest.t8a FORCE;
275 ALTER TABLE dbtest.t8b FORCE, ALGORITHM=INPLACE;
276 ALTER TABLE dbtest.t8c FORCE, ALGORITHM=COPY;
277 # Check that all the partitioned tables for t8a, t8b, t8c and t8d now exist in default directory
278 t8a#p#p0.ibd
279 t8a#p#p1.ibd
280 t8b#p#p0.ibd
281 t8b#p#p1.ibd
282 t8c#p#p0.ibd
283 t8c#p#p1.ibd
284 t8d#p#p0.ibd
285 t8d#p#p1.ibd
286 # Check that EXTERNAL_DIR and EXTERNAL_DIR2 are empty
287 # Listing EXTERNAL_DIR
288 # Listing EXTERNAL_DIR2
289 ALTER TABLE dbtest.t8d FORCE, ALGORITHM=COPY;
290 # Check that all the partitioned tables for t8a, t8b, t8c and t8d now exist in default directory
291 t8a#p#p0.ibd
292 t8a#p#p1.ibd
293 t8b#p#p0.ibd
294 t8b#p#p1.ibd
295 t8c#p#p0.ibd
296 t8c#p#p1.ibd
297 t8d#p#p0.ibd
298 t8d#p#p1.ibd
299 # Check that EXTERNAL_DIR and EXTERNAL_DIR2 are empty
300 # Listing EXTERNAL_DIR
301 # Listing EXTERNAL_DIR2
302 # Drop tables
303 DROP TABLE dbtest.t8a;
304 DROP TABLE dbtest.t8b;
305 DROP TABLE dbtest.t8c;
306 DROP TABLE dbtest.t8d;
307 # Clean up for other tests
308 # 2. Creating partitioned table without data directory clause but moving each partition to different external directories
309 # Partition tables
310 CREATE TABLE `t9a` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20));
311 CREATE TABLE `t9b` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20));
312 CREATE TABLE `t9c` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20));
313 # Shutdown server
314 # Check that the ibd files for all p0 and p1 partitions of t9a, t9b, t9c table exist in the working (dbtest) folder
315 t9a#p#p0.ibd
316 t9a#p#p1.ibd
317 t9b#p#p0.ibd
318 t9b#p#p1.ibd
319 t9c#p#p0.ibd
320 t9c#p#p1.ibd
321 # Now move only p1 partitioned tables of t9a, t9b, t9c to EXTERNAL_DIR
322 # Check that the ibd files for all p0 of t9a, t9b, t9c table exist in the working (dbtest) folder
323 t9a#p#p0.ibd
324 t9b#p#p0.ibd
325 t9c#p#p0.ibd
326 # Check that the ibd files for all p1 of t9a, t9b, t9c table exist in EXTERNAL_DIR
327 t9a#p#p1.ibd
328 t9b#p#p1.ibd
329 t9c#p#p1.ibd
330 # Restart the server
331 # restart: --innodb-directories=MYSQL_TMP_DIR/external_dir
332 # Alter table
333 ALTER TABLE dbtest.t9a FORCE;
334 ALTER TABLE dbtest.t9b FORCE, ALGORITHM=INPLACE;
335 ALTER TABLE dbtest.t9c FORCE, ALGORITHM=COPY;
336 # Check that the ibd files for all p0 of t9a, t9b, t9c table still exist in the working (dbtest) folder
337 t9a#p#p0.ibd
338 t9b#p#p0.ibd
339 t9c#p#p0.ibd
340 # Check that the ibd files for all p1 of t9a, t9b, t9c table still exist in EXTERNAL_DIR
341 t9a#p#p1.ibd
342 t9b#p#p1.ibd
343 t9c#p#p1.ibd
344 # Drop tables and database
345 DROP TABLE dbtest.t9a;
346 DROP TABLE dbtest.t9b;
347 DROP TABLE dbtest.t9c;
348 DROP DATABASE dbtest;
349 # restart:
350 # End Test