Debian Codegraph @

mysql-test/suite/innodb_zip/r/restart.result

raw
1 ALTER DATABASE test CHARACTER SET latin1 COLLATE latin1_swedish_ci;
2 SET default_storage_engine=InnoDB;
3 #
4 # A series of tests to make sure tables are opened after restart.
5 # Bug#13357607 Compressed file-per-table tablespaces fail to open
6 #
7 #
8 # Create and insert records into a REDUNDANT row formatted table.
9 #
10 CREATE TABLE t1_restart(c1 DOUBLE AUTO_INCREMENT KEY, c2 CHAR(10), c3 VARCHAR(100), c4 DATE, c5 TEXT)
11 ROW_FORMAT=REDUNDANT ENGINE=InnoDB;
12 INSERT INTO t1_restart VALUES (1000000000, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
13 INSERT INTO t1_restart (SELECT 0, c2, c3, c4, c5 FROM t1_restart);
14 INSERT INTO t1_restart (SELECT 0, c2, c3, c4, c5 FROM t1_restart);
15 INSERT INTO t1_restart (SELECT 0, c2, c3, c4, c5 FROM t1_restart);
16 INSERT INTO t1_restart (SELECT 0, c2, c3, c4, c5 FROM t1_restart);
17 SHOW CREATE TABLE t1_restart;
18 Table Create Table
19 t1_restart CREATE TABLE `t1_restart` (
20 `c1` double NOT NULL AUTO_INCREMENT,
21 `c2` char(10) DEFAULT NULL,
22 `c3` varchar(100) DEFAULT NULL,
23 `c4` date DEFAULT NULL,
24 `c5` text DEFAULT NULL,
25 PRIMARY KEY (`c1`)
26 ) ENGINE=InnoDB AUTO_INCREMENT=1000000027 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=REDUNDANT
27 SELECT count(*) FROM t1_restart;
28 count(*)
29 16
30 #
31 # Create and insert records into a COMPACT row formatted table.
32 #
33 CREATE TABLE t2_restart(c1 DOUBLE AUTO_INCREMENT KEY, c2 CHAR(10), c3 VARCHAR(100), c4 DATE, c5 TEXT)
34 ROW_FORMAT=COMPACT ENGINE=InnoDB;
35 INSERT INTO t2_restart VALUES (1000000000, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
36 INSERT INTO t2_restart (SELECT 0, c2, c3, c4, c5 FROM t2_restart);
37 INSERT INTO t2_restart (SELECT 0, c2, c3, c4, c5 FROM t2_restart);
38 INSERT INTO t2_restart (SELECT 0, c2, c3, c4, c5 FROM t2_restart);
39 INSERT INTO t2_restart (SELECT 0, c2, c3, c4, c5 FROM t2_restart);
40 SHOW CREATE TABLE t2_restart;
41 Table Create Table
42 t2_restart CREATE TABLE `t2_restart` (
43 `c1` double NOT NULL AUTO_INCREMENT,
44 `c2` char(10) DEFAULT NULL,
45 `c3` varchar(100) DEFAULT NULL,
46 `c4` date DEFAULT NULL,
47 `c5` text DEFAULT NULL,
48 PRIMARY KEY (`c1`)
49 ) ENGINE=InnoDB AUTO_INCREMENT=1000000027 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPACT
50 SELECT count(*) FROM t2_restart;
51 count(*)
52 16
53 #
54 # Create and insert records into a COMPRESSED row formatted table.
55 #
56 CREATE TABLE t3_restart(c1 DOUBLE AUTO_INCREMENT KEY, c2 CHAR(10), c3 VARCHAR(100), c4 DATE, c5 TEXT)
57 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2 ENGINE=InnoDB;
58 INSERT INTO t3_restart VALUES (1000000000, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
59 INSERT INTO t3_restart (SELECT 0, c2, c3, c4, c5 FROM t3_restart);
60 INSERT INTO t3_restart (SELECT 0, c2, c3, c4, c5 FROM t3_restart);
61 INSERT INTO t3_restart (SELECT 0, c2, c3, c4, c5 FROM t3_restart);
62 INSERT INTO t3_restart (SELECT 0, c2, c3, c4, c5 FROM t3_restart);
63 SHOW CREATE TABLE t3_restart;
64 Table Create Table
65 t3_restart CREATE TABLE `t3_restart` (
66 `c1` double NOT NULL AUTO_INCREMENT,
67 `c2` char(10) DEFAULT NULL,
68 `c3` varchar(100) DEFAULT NULL,
69 `c4` date DEFAULT NULL,
70 `c5` text DEFAULT NULL,
71 PRIMARY KEY (`c1`)
72 ) ENGINE=InnoDB AUTO_INCREMENT=1000000027 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2
73 SELECT count(*) FROM t3_restart;
74 count(*)
75 16
76 #
77 # Create and insert records into a DYNAMIC row formatted table.
78 #
79 CREATE TABLE t4_restart(c1 DOUBLE AUTO_INCREMENT KEY, c2 CHAR(10), c3 VARCHAR(100), c4 DATE, c5 TEXT)
80 ROW_FORMAT=DYNAMIC ENGINE=InnoDB;
81 INSERT INTO t4_restart VALUES (1000000000, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
82 INSERT INTO t4_restart (SELECT 0, c2, c3, c4, c5 FROM t4_restart);
83 INSERT INTO t4_restart (SELECT 0, c2, c3, c4, c5 FROM t4_restart);
84 INSERT INTO t4_restart (SELECT 0, c2, c3, c4, c5 FROM t4_restart);
85 INSERT INTO t4_restart (SELECT 0, c2, c3, c4, c5 FROM t4_restart);
86 SHOW CREATE TABLE t4_restart;
87 Table Create Table
88 t4_restart CREATE TABLE `t4_restart` (
89 `c1` double NOT NULL AUTO_INCREMENT,
90 `c2` char(10) DEFAULT NULL,
91 `c3` varchar(100) DEFAULT NULL,
92 `c4` date DEFAULT NULL,
93 `c5` text DEFAULT NULL,
94 PRIMARY KEY (`c1`)
95 ) ENGINE=InnoDB AUTO_INCREMENT=1000000027 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC
96 SELECT count(*) FROM t4_restart;
97 count(*)
98 16
99 #
100 # Create and insert records into a table that uses a remote DATA DIRECTORY.
101 #
102 CREATE TABLE t5_restart(c1 DOUBLE AUTO_INCREMENT KEY, c2 CHAR(10), c3 VARCHAR(100), c4 DATE, c5 TEXT)
103 ROW_FORMAT=DYNAMIC ENGINE=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
104 INSERT INTO t5_restart VALUES (1000000000, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
105 INSERT INTO t5_restart (SELECT 0, c2, c3, c4, c5 FROM t5_restart);
106 INSERT INTO t5_restart (SELECT 0, c2, c3, c4, c5 FROM t5_restart);
107 INSERT INTO t5_restart (SELECT 0, c2, c3, c4, c5 FROM t5_restart);
108 INSERT INTO t5_restart (SELECT 0, c2, c3, c4, c5 FROM t5_restart);
109 SHOW CREATE TABLE t5_restart;
110 Table Create Table
111 t5_restart CREATE TABLE `t5_restart` (
112 `c1` double NOT NULL AUTO_INCREMENT,
113 `c2` char(10) DEFAULT NULL,
114 `c3` varchar(100) DEFAULT NULL,
115 `c4` date DEFAULT NULL,
116 `c5` text DEFAULT NULL,
117 PRIMARY KEY (`c1`)
118 ) ENGINE=InnoDB AUTO_INCREMENT=1000000027 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
119 SELECT count(*) FROM t5_restart;
120 count(*)
121 16
122 #
123 # Create and insert records into a partitioned table that uses
124 # a remote DATA DIRECTORY for each partition.
125 #
126 CREATE TABLE t6_restart(
127 c1 INT AUTO_INCREMENT KEY, c2 CHAR(10), c3 VARCHAR(100), c4 DATE, c5 TEXT)
128 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2 ENGINE=InnoDB
129 PARTITION BY HASH(c1) (
130 PARTITION p0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir',
131 PARTITION p1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir',
132 PARTITION p2 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir');
133 INSERT INTO t6_restart VALUES (0, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
134 INSERT INTO t6_restart (SELECT 0, c2, c3, c4, c5 FROM t6_restart);
135 INSERT INTO t6_restart (SELECT 0, c2, c3, c4, c5 FROM t6_restart);
136 INSERT INTO t6_restart (SELECT 0, c2, c3, c4, c5 FROM t6_restart);
137 INSERT INTO t6_restart (SELECT 0, c2, c3, c4, c5 FROM t6_restart);
138 SHOW CREATE TABLE t6_restart;
139 Table Create Table
140 t6_restart CREATE TABLE `t6_restart` (
141 `c1` int(11) NOT NULL AUTO_INCREMENT,
142 `c2` char(10) DEFAULT NULL,
143 `c3` varchar(100) DEFAULT NULL,
144 `c4` date DEFAULT NULL,
145 `c5` text DEFAULT NULL,
146 PRIMARY KEY (`c1`)
147 ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2
148 PARTITION BY HASH (`c1`)
149 (PARTITION `p0` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
150 PARTITION `p1` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
151 PARTITION `p2` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB)
152 SELECT count(*) FROM t6_restart;
153 count(*)
154 16
155 #
156 # Create and insert records into a subpartitioned table that uses
157 # a remote DATA DIRECTORY for each subpartition.
158 #
159 CREATE TABLE t7_restart(
160 c1 INT AUTO_INCREMENT KEY, c2 CHAR(10), c3 VARCHAR(100), c4 DATE, c5 TEXT)
161 ROW_FORMAT=DYNAMIC ENGINE=InnoDB
162 PARTITION BY RANGE(c1) SUBPARTITION BY HASH(c1) (
163 PARTITION p0 VALUES LESS THAN (10) (
164 SUBPARTITION s0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir',
165 SUBPARTITION s1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir'),
166 PARTITION p1 VALUES LESS THAN MAXVALUE (
167 SUBPARTITION s2 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir',
168 SUBPARTITION s3 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir'));
169 INSERT INTO t7_restart VALUES (0, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
170 INSERT INTO t7_restart (SELECT 0, c2, c3, c4, c5 FROM t7_restart);
171 INSERT INTO t7_restart (SELECT 0, c2, c3, c4, c5 FROM t7_restart);
172 INSERT INTO t7_restart (SELECT 0, c2, c3, c4, c5 FROM t7_restart);
173 INSERT INTO t7_restart (SELECT 0, c2, c3, c4, c5 FROM t7_restart);
174 SHOW CREATE TABLE t7_restart;
175 Table Create Table
176 t7_restart CREATE TABLE `t7_restart` (
177 `c1` int(11) NOT NULL AUTO_INCREMENT,
178 `c2` char(10) DEFAULT NULL,
179 `c3` varchar(100) DEFAULT NULL,
180 `c4` date DEFAULT NULL,
181 `c5` text DEFAULT NULL,
182 PRIMARY KEY (`c1`)
183 ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC
184 PARTITION BY RANGE (`c1`)
185 SUBPARTITION BY HASH (`c1`)
186 (PARTITION `p0` VALUES LESS THAN (10)
187 (SUBPARTITION `s0` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
188 SUBPARTITION `s1` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB),
189 PARTITION `p1` VALUES LESS THAN MAXVALUE
190 (SUBPARTITION `s2` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
191 SUBPARTITION `s3` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB))
192 SELECT count(*) FROM t7_restart;
193 count(*)
194 16
195 #
196 # Show these tables in information_schema.
197 #
198 === information_schema.innodb_sys_tables and innodb_sys_tablespaces ===
199 Table Name Tablespace Table Flags Columns Row Format Zip Size
200 test/t1_restart test/t1_restart 0 8 Redundant 0
201 test/t2_restart test/t2_restart 1 8 Compact 0
202 test/t3_restart test/t3_restart 37 8 Compressed 2048
203 test/t4_restart test/t4_restart 33 8 Dynamic 0
204 test/t5_restart test/t5_restart 97 8 Dynamic 0
205 test/t6_restart#p#p0 test/t6_restart#p#p0 101 8 Compressed 2048
206 test/t6_restart#p#p1 test/t6_restart#p#p1 101 8 Compressed 2048
207 test/t6_restart#p#p2 test/t6_restart#p#p2 101 8 Compressed 2048
208 test/t7_restart#p#p0#sp#s0 test/t7_restart#p#p0#sp#s0 97 8 Dynamic 0
209 test/t7_restart#p#p0#sp#s1 test/t7_restart#p#p0#sp#s1 97 8 Dynamic 0
210 test/t7_restart#p#p1#sp#s2 test/t7_restart#p#p1#sp#s2 97 8 Dynamic 0
211 test/t7_restart#p#p1#sp#s3 test/t7_restart#p#p1#sp#s3 97 8 Dynamic 0
212 === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles ===
213 Space_Name Page_Size Zip_Size Path
214 innodb_undo001 DEFAULT DEFAULT MYSQLD_DATADIR//undo001
215 innodb_undo002 DEFAULT DEFAULT MYSQLD_DATADIR//undo002
216 innodb_undo003 DEFAULT DEFAULT MYSQLD_DATADIR//undo003
217 test/t1_restart DEFAULT DEFAULT MYSQLD_DATADIR/test/t1_restart.ibd
218 test/t2_restart DEFAULT DEFAULT MYSQLD_DATADIR/test/t2_restart.ibd
219 test/t3_restart DEFAULT 2048 MYSQLD_DATADIR/test/t3_restart.ibd
220 test/t4_restart DEFAULT DEFAULT MYSQLD_DATADIR/test/t4_restart.ibd
221 test/t5_restart DEFAULT DEFAULT MYSQL_TMP_DIR/alt_dir/test/t5_restart.ibd
222 test/t6_restart#p#p0 DEFAULT 2048 MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p0.ibd
223 test/t6_restart#p#p1 DEFAULT 2048 MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p1.ibd
224 test/t6_restart#p#p2 DEFAULT 2048 MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p2.ibd
225 test/t7_restart#p#p0#sp#s0 DEFAULT DEFAULT MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s0.ibd
226 test/t7_restart#p#p0#sp#s1 DEFAULT DEFAULT MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s1.ibd
227 test/t7_restart#p#p1#sp#s2 DEFAULT DEFAULT MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s2.ibd
228 test/t7_restart#p#p1#sp#s3 DEFAULT DEFAULT MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s3.ibd
229 innodb_temporary DEFAULT DEFAULT MYSQLD_DATADIR/ibtmp1
230 #
231 # Shutdown the server and list the tablespace OS files
232 #
233 ---- MYSQL_DATA_DIR/test
234 db.opt
235 t1_restart.frm
236 t1_restart.ibd
237 t2_restart.frm
238 t2_restart.ibd
239 t3_restart.frm
240 t3_restart.ibd
241 t4_restart.frm
242 t4_restart.ibd
243 t5_restart.frm
244 t5_restart.isl
245 t6_restart#p#p0.isl
246 t6_restart#p#p1.isl
247 t6_restart#p#p2.isl
248 t6_restart.frm
249 t6_restart.par
250 t7_restart#p#p0#sp#s0.isl
251 t7_restart#p#p0#sp#s1.isl
252 t7_restart#p#p1#sp#s2.isl
253 t7_restart#p#p1#sp#s3.isl
254 t7_restart.frm
255 t7_restart.par
256 ---- MYSQL_TMP_DIR/alt_dir
257 test
258 ---- MYSQL_TMP_DIR/alt_dir/test
259 t5_restart.ibd
260 t6_restart#p#p0.ibd
261 t6_restart#p#p1.ibd
262 t6_restart#p#p2.ibd
263 t7_restart#p#p0#sp#s0.ibd
264 t7_restart#p#p0#sp#s1.ibd
265 t7_restart#p#p1#sp#s2.ibd
266 t7_restart#p#p1#sp#s3.ibd
267 #
268 # Start the server and show that tables are still visible and accessible.
269 #
270 # restart
271 SHOW CREATE TABLE t1_restart;
272 Table Create Table
273 t1_restart CREATE TABLE `t1_restart` (
274 `c1` double NOT NULL AUTO_INCREMENT,
275 `c2` char(10) DEFAULT NULL,
276 `c3` varchar(100) DEFAULT NULL,
277 `c4` date DEFAULT NULL,
278 `c5` text DEFAULT NULL,
279 PRIMARY KEY (`c1`)
280 ) ENGINE=InnoDB AUTO_INCREMENT=1000000020 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=REDUNDANT
281 SHOW CREATE TABLE t2_restart;
282 Table Create Table
283 t2_restart CREATE TABLE `t2_restart` (
284 `c1` double NOT NULL AUTO_INCREMENT,
285 `c2` char(10) DEFAULT NULL,
286 `c3` varchar(100) DEFAULT NULL,
287 `c4` date DEFAULT NULL,
288 `c5` text DEFAULT NULL,
289 PRIMARY KEY (`c1`)
290 ) ENGINE=InnoDB AUTO_INCREMENT=1000000020 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPACT
291 SHOW CREATE TABLE t3_restart;
292 Table Create Table
293 t3_restart CREATE TABLE `t3_restart` (
294 `c1` double NOT NULL AUTO_INCREMENT,
295 `c2` char(10) DEFAULT NULL,
296 `c3` varchar(100) DEFAULT NULL,
297 `c4` date DEFAULT NULL,
298 `c5` text DEFAULT NULL,
299 PRIMARY KEY (`c1`)
300 ) ENGINE=InnoDB AUTO_INCREMENT=1000000020 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2
301 SHOW CREATE TABLE t4_restart;
302 Table Create Table
303 t4_restart CREATE TABLE `t4_restart` (
304 `c1` double NOT NULL AUTO_INCREMENT,
305 `c2` char(10) DEFAULT NULL,
306 `c3` varchar(100) DEFAULT NULL,
307 `c4` date DEFAULT NULL,
308 `c5` text DEFAULT NULL,
309 PRIMARY KEY (`c1`)
310 ) ENGINE=InnoDB AUTO_INCREMENT=1000000020 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC
311 SHOW CREATE TABLE t5_restart;
312 Table Create Table
313 t5_restart CREATE TABLE `t5_restart` (
314 `c1` double NOT NULL AUTO_INCREMENT,
315 `c2` char(10) DEFAULT NULL,
316 `c3` varchar(100) DEFAULT NULL,
317 `c4` date DEFAULT NULL,
318 `c5` text DEFAULT NULL,
319 PRIMARY KEY (`c1`)
320 ) ENGINE=InnoDB AUTO_INCREMENT=1000000020 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
321 SHOW CREATE TABLE t6_restart;
322 Table Create Table
323 t6_restart CREATE TABLE `t6_restart` (
324 `c1` int(11) NOT NULL AUTO_INCREMENT,
325 `c2` char(10) DEFAULT NULL,
326 `c3` varchar(100) DEFAULT NULL,
327 `c4` date DEFAULT NULL,
328 `c5` text DEFAULT NULL,
329 PRIMARY KEY (`c1`)
330 ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2
331 PARTITION BY HASH (`c1`)
332 (PARTITION `p0` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
333 PARTITION `p1` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
334 PARTITION `p2` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB)
335 SHOW CREATE TABLE t7_restart;
336 Table Create Table
337 t7_restart CREATE TABLE `t7_restart` (
338 `c1` int(11) NOT NULL AUTO_INCREMENT,
339 `c2` char(10) DEFAULT NULL,
340 `c3` varchar(100) DEFAULT NULL,
341 `c4` date DEFAULT NULL,
342 `c5` text DEFAULT NULL,
343 PRIMARY KEY (`c1`)
344 ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC
345 PARTITION BY RANGE (`c1`)
346 SUBPARTITION BY HASH (`c1`)
347 (PARTITION `p0` VALUES LESS THAN (10)
348 (SUBPARTITION `s0` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
349 SUBPARTITION `s1` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB),
350 PARTITION `p1` VALUES LESS THAN MAXVALUE
351 (SUBPARTITION `s2` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
352 SUBPARTITION `s3` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB))
353 INSERT INTO t1_restart (SELECT 0, c2, c3, c4, c5 FROM t1_restart);
354 INSERT INTO t2_restart (SELECT 0, c2, c3, c4, c5 FROM t2_restart);
355 INSERT INTO t3_restart (SELECT 0, c2, c3, c4, c5 FROM t3_restart);
356 INSERT INTO t4_restart (SELECT 0, c2, c3, c4, c5 FROM t4_restart);
357 INSERT INTO t5_restart (SELECT 0, c2, c3, c4, c5 FROM t5_restart);
358 INSERT INTO t6_restart (SELECT 0, c2, c3, c4, c5 FROM t6_restart);
359 INSERT INTO t7_restart (SELECT 0, c2, c3, c4, c5 FROM t7_restart);
360 SELECT count(*) FROM t1_restart;
361 count(*)
362 32
363 SELECT count(*) FROM t2_restart;
364 count(*)
365 32
366 SELECT count(*) FROM t3_restart;
367 count(*)
368 32
369 SELECT count(*) FROM t4_restart;
370 count(*)
371 32
372 SELECT count(*) FROM t5_restart;
373 count(*)
374 32
375 SELECT count(*) FROM t6_restart;
376 count(*)
377 32
378 SELECT count(*) FROM t7_restart;
379 count(*)
380 32
381 #
382 # Show these tables in information_schema.
383 #
384 === information_schema.innodb_sys_tables and innodb_sys_tablespaces ===
385 Table Name Tablespace Table Flags Columns Row Format Zip Size
386 test/t1_restart test/t1_restart 0 8 Redundant 0
387 test/t2_restart test/t2_restart 1 8 Compact 0
388 test/t3_restart test/t3_restart 37 8 Compressed 2048
389 test/t4_restart test/t4_restart 33 8 Dynamic 0
390 test/t5_restart test/t5_restart 97 8 Dynamic 0
391 test/t6_restart#p#p0 test/t6_restart#p#p0 101 8 Compressed 2048
392 test/t6_restart#p#p1 test/t6_restart#p#p1 101 8 Compressed 2048
393 test/t6_restart#p#p2 test/t6_restart#p#p2 101 8 Compressed 2048
394 test/t7_restart#p#p0#sp#s0 test/t7_restart#p#p0#sp#s0 97 8 Dynamic 0
395 test/t7_restart#p#p0#sp#s1 test/t7_restart#p#p0#sp#s1 97 8 Dynamic 0
396 test/t7_restart#p#p1#sp#s2 test/t7_restart#p#p1#sp#s2 97 8 Dynamic 0
397 test/t7_restart#p#p1#sp#s3 test/t7_restart#p#p1#sp#s3 97 8 Dynamic 0
398 === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles ===
399 Space_Name Page_Size Zip_Size Path
400 innodb_undo001 DEFAULT DEFAULT MYSQLD_DATADIR//undo001
401 innodb_undo002 DEFAULT DEFAULT MYSQLD_DATADIR//undo002
402 innodb_undo003 DEFAULT DEFAULT MYSQLD_DATADIR//undo003
403 test/t1_restart DEFAULT DEFAULT MYSQLD_DATADIR/test/t1_restart.ibd
404 test/t2_restart DEFAULT DEFAULT MYSQLD_DATADIR/test/t2_restart.ibd
405 test/t3_restart DEFAULT 2048 MYSQLD_DATADIR/test/t3_restart.ibd
406 test/t4_restart DEFAULT DEFAULT MYSQLD_DATADIR/test/t4_restart.ibd
407 test/t5_restart DEFAULT DEFAULT MYSQL_TMP_DIR/alt_dir/test/t5_restart.ibd
408 test/t6_restart#p#p0 DEFAULT 2048 MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p0.ibd
409 test/t6_restart#p#p1 DEFAULT 2048 MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p1.ibd
410 test/t6_restart#p#p2 DEFAULT 2048 MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p2.ibd
411 test/t7_restart#p#p0#sp#s0 DEFAULT DEFAULT MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s0.ibd
412 test/t7_restart#p#p0#sp#s1 DEFAULT DEFAULT MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s1.ibd
413 test/t7_restart#p#p1#sp#s2 DEFAULT DEFAULT MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s2.ibd
414 test/t7_restart#p#p1#sp#s3 DEFAULT DEFAULT MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s3.ibd
415 innodb_temporary DEFAULT DEFAULT MYSQLD_DATADIR/ibtmp1
416 DROP TABLE t1_restart;
417 DROP TABLE t2_restart;
418 DROP TABLE t3_restart;
419 #
420 # Truncate the remote tablespaces.
421 #
422 TRUNCATE TABLE t5_restart;
423 ALTER TABLE t6_restart TRUNCATE PARTITION p2;
424 ALTER TABLE t7_restart TRUNCATE PARTITION p1;
425 === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles ===
426 Space_Name Page_Size Zip_Size Path
427 innodb_undo001 DEFAULT DEFAULT MYSQLD_DATADIR//undo001
428 innodb_undo002 DEFAULT DEFAULT MYSQLD_DATADIR//undo002
429 innodb_undo003 DEFAULT DEFAULT MYSQLD_DATADIR//undo003
430 test/t4_restart DEFAULT DEFAULT MYSQLD_DATADIR/test/t4_restart.ibd
431 test/t6_restart#p#p0 DEFAULT 2048 MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p0.ibd
432 test/t6_restart#p#p1 DEFAULT 2048 MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p1.ibd
433 test/t7_restart#p#p0#sp#s0 DEFAULT DEFAULT MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s0.ibd
434 test/t7_restart#p#p0#sp#s1 DEFAULT DEFAULT MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s1.ibd
435 test/t5_restart DEFAULT DEFAULT MYSQL_TMP_DIR/alt_dir/test/t5_restart.ibd
436 test/t6_restart#p#p2 DEFAULT 2048 MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p2.ibd
437 test/t7_restart#p#p1#sp#s2 DEFAULT DEFAULT MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s2.ibd
438 test/t7_restart#p#p1#sp#s3 DEFAULT DEFAULT MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s3.ibd
439 innodb_temporary DEFAULT DEFAULT MYSQLD_DATADIR/ibtmp1
440 INSERT INTO t5_restart VALUES (1000000000, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
441 INSERT INTO t5_restart (SELECT 0, c2, c3, c4, c5 FROM t5_restart);
442 INSERT INTO t5_restart (SELECT 0, c2, c3, c4, c5 FROM t5_restart);
443 INSERT INTO t5_restart (SELECT 0, c2, c3, c4, c5 FROM t5_restart);
444 SELECT count(*) FROM t5_restart;
445 count(*)
446 8
447 SHOW CREATE TABLE t5_restart;
448 Table Create Table
449 t5_restart CREATE TABLE `t5_restart` (
450 `c1` double NOT NULL AUTO_INCREMENT,
451 `c2` char(10) DEFAULT NULL,
452 `c3` varchar(100) DEFAULT NULL,
453 `c4` date DEFAULT NULL,
454 `c5` text DEFAULT NULL,
455 PRIMARY KEY (`c1`)
456 ) ENGINE=InnoDB AUTO_INCREMENT=1000000012 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
457 SELECT count(*) FROM t6_restart;
458 count(*)
459 21
460 SHOW CREATE TABLE t6_restart;
461 Table Create Table
462 t6_restart CREATE TABLE `t6_restart` (
463 `c1` int(11) NOT NULL AUTO_INCREMENT,
464 `c2` char(10) DEFAULT NULL,
465 `c3` varchar(100) DEFAULT NULL,
466 `c4` date DEFAULT NULL,
467 `c5` text DEFAULT NULL,
468 PRIMARY KEY (`c1`)
469 ) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2
470 PARTITION BY HASH (`c1`)
471 (PARTITION `p0` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
472 PARTITION `p1` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
473 PARTITION `p2` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB)
474 SELECT count(*) FROM t7_restart;
475 count(*)
476 9
477 SHOW CREATE TABLE t7_restart;
478 Table Create Table
479 t7_restart CREATE TABLE `t7_restart` (
480 `c1` int(11) NOT NULL AUTO_INCREMENT,
481 `c2` char(10) DEFAULT NULL,
482 `c3` varchar(100) DEFAULT NULL,
483 `c4` date DEFAULT NULL,
484 `c5` text DEFAULT NULL,
485 PRIMARY KEY (`c1`)
486 ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC
487 PARTITION BY RANGE (`c1`)
488 SUBPARTITION BY HASH (`c1`)
489 (PARTITION `p0` VALUES LESS THAN (10)
490 (SUBPARTITION `s0` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
491 SUBPARTITION `s1` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB),
492 PARTITION `p1` VALUES LESS THAN MAXVALUE
493 (SUBPARTITION `s2` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
494 SUBPARTITION `s3` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB))
495 #
496 # Shutdown the server and make a backup of a tablespace
497 #
498 ---- MYSQL_DATA_DIR/test
499 db.opt
500 t4_restart.frm
501 t4_restart.ibd
502 t5_restart.frm
503 t5_restart.frm.bak
504 t5_restart.isl
505 t5_restart.isl.bak
506 t6_restart#p#p0.isl
507 t6_restart#p#p1.isl
508 t6_restart#p#p2.isl
509 t6_restart.frm
510 t6_restart.par
511 t7_restart#p#p0#sp#s0.isl
512 t7_restart#p#p0#sp#s1.isl
513 t7_restart#p#p1#sp#s2.isl
514 t7_restart#p#p1#sp#s3.isl
515 t7_restart.frm
516 t7_restart.par
517 ---- MYSQL_TMP_DIR/alt_dir/test
518 t5_restart.ibd
519 t5_restart.ibd.bak
520 t6_restart#p#p0.ibd
521 t6_restart#p#p1.ibd
522 t6_restart#p#p2.ibd
523 t7_restart#p#p0#sp#s0.ibd
524 t7_restart#p#p0#sp#s1.ibd
525 t7_restart#p#p1#sp#s2.ibd
526 t7_restart#p#p1#sp#s3.ibd
527 #
528 # Start the server and show the tablespaces.
529 #
530 # restart
531 === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles ===
532 Space_Name Page_Size Zip_Size Path
533 innodb_undo001 DEFAULT DEFAULT MYSQLD_DATADIR//undo001
534 innodb_undo002 DEFAULT DEFAULT MYSQLD_DATADIR//undo002
535 innodb_undo003 DEFAULT DEFAULT MYSQLD_DATADIR//undo003
536 innodb_temporary DEFAULT DEFAULT MYSQLD_DATADIR/ibtmp1
537 SELECT count(*) FROM t5_restart;
538 count(*)
539 8
540 SHOW CREATE TABLE t5_restart;
541 Table Create Table
542 t5_restart CREATE TABLE `t5_restart` (
543 `c1` double NOT NULL AUTO_INCREMENT,
544 `c2` char(10) DEFAULT NULL,
545 `c3` varchar(100) DEFAULT NULL,
546 `c4` date DEFAULT NULL,
547 `c5` text DEFAULT NULL,
548 PRIMARY KEY (`c1`)
549 ) ENGINE=InnoDB AUTO_INCREMENT=1000000009 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
550 SELECT count(*) FROM t6_restart;
551 count(*)
552 21
553 SHOW CREATE TABLE t6_restart;
554 Table Create Table
555 t6_restart CREATE TABLE `t6_restart` (
556 `c1` int(11) NOT NULL AUTO_INCREMENT,
557 `c2` char(10) DEFAULT NULL,
558 `c3` varchar(100) DEFAULT NULL,
559 `c4` date DEFAULT NULL,
560 `c5` text DEFAULT NULL,
561 PRIMARY KEY (`c1`)
562 ) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2
563 PARTITION BY HASH (`c1`)
564 (PARTITION `p0` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
565 PARTITION `p1` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
566 PARTITION `p2` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB)
567 SELECT count(*) FROM t7_restart;
568 count(*)
569 9
570 SHOW CREATE TABLE t7_restart;
571 Table Create Table
572 t7_restart CREATE TABLE `t7_restart` (
573 `c1` int(11) NOT NULL AUTO_INCREMENT,
574 `c2` char(10) DEFAULT NULL,
575 `c3` varchar(100) DEFAULT NULL,
576 `c4` date DEFAULT NULL,
577 `c5` text DEFAULT NULL,
578 PRIMARY KEY (`c1`)
579 ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC
580 PARTITION BY RANGE (`c1`)
581 SUBPARTITION BY HASH (`c1`)
582 (PARTITION `p0` VALUES LESS THAN (10)
583 (SUBPARTITION `s0` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
584 SUBPARTITION `s1` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB),
585 PARTITION `p1` VALUES LESS THAN MAXVALUE
586 (SUBPARTITION `s2` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
587 SUBPARTITION `s3` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB))
588 #
589 # Try to rename a tablespace to a file that already exists
590 #
591 RENAME TABLE t5_restart TO t55_restart;
592 ERROR 42S01: Table 't55_restart' already exists
593 RENAME TABLE t5_restart TO t55_restart;
594 ERROR HY000: Error on rename of './test/t5_restart' to './test/t55_restart' (errno: 184 "Tablespace already exists")
595 ---- MYSQL_DATA_DIR/test
596 db.opt
597 t4_restart.frm
598 t4_restart.ibd
599 t5_restart.frm
600 t5_restart.isl
601 t6_restart#p#p0.isl
602 t6_restart#p#p1.isl
603 t6_restart#p#p2.isl
604 t6_restart.frm
605 t6_restart.par
606 t7_restart#p#p0#sp#s0.isl
607 t7_restart#p#p0#sp#s1.isl
608 t7_restart#p#p1#sp#s2.isl
609 t7_restart#p#p1#sp#s3.isl
610 t7_restart.frm
611 t7_restart.par
612 ---- MYSQL_TMP_DIR/alt_dir/test
613 t5_restart.ibd
614 t6_restart#p#p0.ibd
615 t6_restart#p#p1.ibd
616 t6_restart#p#p2.ibd
617 t7_restart#p#p0#sp#s0.ibd
618 t7_restart#p#p0#sp#s1.ibd
619 t7_restart#p#p1#sp#s2.ibd
620 t7_restart#p#p1#sp#s3.ibd
621 #
622 # Rename file table and tablespace
623 #
624 RENAME TABLE t5_restart TO t55_restart;
625 RENAME TABLE t6_restart TO t66_restart;
626 RENAME TABLE t7_restart TO t77_restart;
627 === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles ===
628 Space_Name Page_Size Zip_Size Path
629 innodb_undo001 DEFAULT DEFAULT MYSQLD_DATADIR//undo001
630 innodb_undo002 DEFAULT DEFAULT MYSQLD_DATADIR//undo002
631 innodb_undo003 DEFAULT DEFAULT MYSQLD_DATADIR//undo003
632 test/t66_restart#p#p0 DEFAULT 2048 MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p0.ibd
633 test/t66_restart#p#p1 DEFAULT 2048 MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p1.ibd
634 test/t77_restart#p#p0#sp#s0 DEFAULT DEFAULT MYSQL_TMP_DIR/alt_dir/test/t77_restart#p#p0#sp#s0.ibd
635 test/t77_restart#p#p0#sp#s1 DEFAULT DEFAULT MYSQL_TMP_DIR/alt_dir/test/t77_restart#p#p0#sp#s1.ibd
636 test/t55_restart DEFAULT DEFAULT MYSQL_TMP_DIR/alt_dir/test/t55_restart.ibd
637 test/t66_restart#p#p2 DEFAULT 2048 MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p2.ibd
638 test/t77_restart#p#p1#sp#s2 DEFAULT DEFAULT MYSQL_TMP_DIR/alt_dir/test/t77_restart#p#p1#sp#s2.ibd
639 test/t77_restart#p#p1#sp#s3 DEFAULT DEFAULT MYSQL_TMP_DIR/alt_dir/test/t77_restart#p#p1#sp#s3.ibd
640 innodb_temporary DEFAULT DEFAULT MYSQLD_DATADIR/ibtmp1
641 INSERT INTO t55_restart (SELECT 0, c2, c3, c4, c5 FROM t55_restart);
642 SELECT count(*) FROM t55_restart;
643 count(*)
644 16
645 SHOW CREATE TABLE t55_restart;
646 Table Create Table
647 t55_restart CREATE TABLE `t55_restart` (
648 `c1` double NOT NULL AUTO_INCREMENT,
649 `c2` char(10) DEFAULT NULL,
650 `c3` varchar(100) DEFAULT NULL,
651 `c4` date DEFAULT NULL,
652 `c5` text DEFAULT NULL,
653 PRIMARY KEY (`c1`)
654 ) ENGINE=InnoDB AUTO_INCREMENT=1000000024 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
655 INSERT INTO t66_restart (SELECT 0, c2, c3, c4, c5 FROM t66_restart);
656 SELECT count(*) FROM t66_restart;
657 count(*)
658 42
659 SHOW CREATE TABLE t66_restart;
660 Table Create Table
661 t66_restart CREATE TABLE `t66_restart` (
662 `c1` int(11) NOT NULL AUTO_INCREMENT,
663 `c2` char(10) DEFAULT NULL,
664 `c3` varchar(100) DEFAULT NULL,
665 `c4` date DEFAULT NULL,
666 `c5` text DEFAULT NULL,
667 PRIMARY KEY (`c1`)
668 ) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2
669 PARTITION BY HASH (`c1`)
670 (PARTITION `p0` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
671 PARTITION `p1` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
672 PARTITION `p2` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB)
673 INSERT INTO t77_restart (SELECT 0, c2, c3, c4, c5 FROM t77_restart);
674 SELECT count(*) FROM t77_restart;
675 count(*)
676 18
677 SHOW CREATE TABLE t77_restart;
678 Table Create Table
679 t77_restart CREATE TABLE `t77_restart` (
680 `c1` int(11) NOT NULL AUTO_INCREMENT,
681 `c2` char(10) DEFAULT NULL,
682 `c3` varchar(100) DEFAULT NULL,
683 `c4` date DEFAULT NULL,
684 `c5` text DEFAULT NULL,
685 PRIMARY KEY (`c1`)
686 ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC
687 PARTITION BY RANGE (`c1`)
688 SUBPARTITION BY HASH (`c1`)
689 (PARTITION `p0` VALUES LESS THAN (10)
690 (SUBPARTITION `s0` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
691 SUBPARTITION `s1` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB),
692 PARTITION `p1` VALUES LESS THAN MAXVALUE
693 (SUBPARTITION `s2` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
694 SUBPARTITION `s3` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB))
695 ---- MYSQL_DATA_DIR/test
696 db.opt
697 t4_restart.frm
698 t4_restart.ibd
699 t55_restart.frm
700 t55_restart.isl
701 t66_restart#p#p0.isl
702 t66_restart#p#p1.isl
703 t66_restart#p#p2.isl
704 t66_restart.frm
705 t66_restart.par
706 t77_restart#p#p0#sp#s0.isl
707 t77_restart#p#p0#sp#s1.isl
708 t77_restart#p#p1#sp#s2.isl
709 t77_restart#p#p1#sp#s3.isl
710 t77_restart.frm
711 t77_restart.par
712 ---- MYSQL_TMP_DIR/alt_dir/test
713 t55_restart.ibd
714 t66_restart#p#p0.ibd
715 t66_restart#p#p1.ibd
716 t66_restart#p#p2.ibd
717 t77_restart#p#p0#sp#s0.ibd
718 t77_restart#p#p0#sp#s1.ibd
719 t77_restart#p#p1#sp#s2.ibd
720 t77_restart#p#p1#sp#s3.ibd
721 #
722 # Restart the server
723 #
724 # restart
725 === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles ===
726 Space_Name Page_Size Zip_Size Path
727 innodb_undo001 DEFAULT DEFAULT MYSQLD_DATADIR//undo001
728 innodb_undo002 DEFAULT DEFAULT MYSQLD_DATADIR//undo002
729 innodb_undo003 DEFAULT DEFAULT MYSQLD_DATADIR//undo003
730 innodb_temporary DEFAULT DEFAULT MYSQLD_DATADIR/ibtmp1
731 INSERT INTO t55_restart (SELECT 0, c2, c3, c4, c5 FROM t55_restart);
732 SELECT count(*) FROM t55_restart;
733 count(*)
734 32
735 SHOW CREATE TABLE t55_restart;
736 Table Create Table
737 t55_restart CREATE TABLE `t55_restart` (
738 `c1` double NOT NULL AUTO_INCREMENT,
739 `c2` char(10) DEFAULT NULL,
740 `c3` varchar(100) DEFAULT NULL,
741 `c4` date DEFAULT NULL,
742 `c5` text DEFAULT NULL,
743 PRIMARY KEY (`c1`)
744 ) ENGINE=InnoDB AUTO_INCREMENT=1000000048 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
745 INSERT INTO t66_restart (SELECT 0, c2, c3, c4, c5 FROM t66_restart);
746 SELECT count(*) FROM t66_restart;
747 count(*)
748 84
749 SHOW CREATE TABLE t66_restart;
750 Table Create Table
751 t66_restart CREATE TABLE `t66_restart` (
752 `c1` int(11) NOT NULL AUTO_INCREMENT,
753 `c2` char(10) DEFAULT NULL,
754 `c3` varchar(100) DEFAULT NULL,
755 `c4` date DEFAULT NULL,
756 `c5` text DEFAULT NULL,
757 PRIMARY KEY (`c1`)
758 ) ENGINE=InnoDB AUTO_INCREMENT=95 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2
759 PARTITION BY HASH (`c1`)
760 (PARTITION `p0` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
761 PARTITION `p1` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
762 PARTITION `p2` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB)
763 INSERT INTO t77_restart (SELECT 0, c2, c3, c4, c5 FROM t77_restart);
764 SELECT count(*) FROM t77_restart;
765 count(*)
766 36
767 SHOW CREATE TABLE t77_restart;
768 Table Create Table
769 t77_restart CREATE TABLE `t77_restart` (
770 `c1` int(11) NOT NULL AUTO_INCREMENT,
771 `c2` char(10) DEFAULT NULL,
772 `c3` varchar(100) DEFAULT NULL,
773 `c4` date DEFAULT NULL,
774 `c5` text DEFAULT NULL,
775 PRIMARY KEY (`c1`)
776 ) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC
777 PARTITION BY RANGE (`c1`)
778 SUBPARTITION BY HASH (`c1`)
779 (PARTITION `p0` VALUES LESS THAN (10)
780 (SUBPARTITION `s0` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
781 SUBPARTITION `s1` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB),
782 PARTITION `p1` VALUES LESS THAN MAXVALUE
783 (SUBPARTITION `s2` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
784 SUBPARTITION `s3` DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB))
785 #
786 # Shutdown the server
787 #
788 #
789 # Move the remote tablespaces to a new location and change the ISL files
790 #
791 ---- MYSQL_DATA_DIR/test
792 db.opt
793 t4_restart.frm
794 t4_restart.ibd
795 t55_restart.frm
796 t55_restart.isl
797 t66_restart#p#p0.isl
798 t66_restart#p#p1.isl
799 t66_restart#p#p2.isl
800 t66_restart.frm
801 t66_restart.par
802 t77_restart#p#p0#sp#s0.isl
803 t77_restart#p#p0#sp#s1.isl
804 t77_restart#p#p1#sp#s2.isl
805 t77_restart#p#p1#sp#s3.isl
806 t77_restart.frm
807 t77_restart.par
808 ---- MYSQL_TMP_DIR/alt_dir/test
809 t55_restart.ibd
810 t66_restart#p#p0.ibd
811 t66_restart#p#p1.ibd
812 t66_restart#p#p2.ibd
813 t77_restart#p#p0#sp#s0.ibd
814 t77_restart#p#p0#sp#s1.ibd
815 t77_restart#p#p1#sp#s2.ibd
816 t77_restart#p#p1#sp#s3.ibd
817 ---- MYSQL_TMP_DIR/new_dir/test
818 # Moving tablespace 't4_restart' from MYSQL_DATA_DIR to MYSQL_TMP_DIR/new_dir
819 # Moving tablespace 't55_restart' from MYSQL_TMP_DIR/alt_dir to MYSQL_TMP_DIR/new_dir
820 # Moving tablespace 't66_restart' from MYSQL_TMP_DIR/alt_dir to MYSQL_TMP_DIR/new_dir
821 # Moving tablespace 't77_restart' from MYSQL_TMP_DIR/alt_dir to MYSQL_TMP_DIR/new_dir
822 ---- MYSQL_DATA_DIR/test
823 db.opt
824 t4_restart.frm
825 t4_restart.isl
826 t55_restart.frm
827 t55_restart.isl
828 t66_restart#p#p0.isl
829 t66_restart#p#p1.isl
830 t66_restart#p#p2.isl
831 t66_restart.frm
832 t66_restart.par
833 t77_restart#p#p0#sp#s0.isl
834 t77_restart#p#p0#sp#s1.isl
835 t77_restart#p#p1#sp#s2.isl
836 t77_restart#p#p1#sp#s3.isl
837 t77_restart.frm
838 t77_restart.par
839 ---- MYSQL_TMP_DIR/alt_dir/test
840 ---- MYSQL_TMP_DIR/new_dir/test
841 t4_restart.ibd
842 t55_restart.ibd
843 t66_restart#p#p0.ibd
844 t66_restart#p#p1.ibd
845 t66_restart#p#p2.ibd
846 t77_restart#p#p0#sp#s0.ibd
847 t77_restart#p#p0#sp#s1.ibd
848 t77_restart#p#p1#sp#s2.ibd
849 t77_restart#p#p1#sp#s3.ibd
850 #
851 # Start the server and check tablespaces.
852 #
853 # restart
854 === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles ===
855 Space_Name Page_Size Zip_Size Path
856 innodb_undo001 DEFAULT DEFAULT MYSQLD_DATADIR//undo001
857 innodb_undo002 DEFAULT DEFAULT MYSQLD_DATADIR//undo002
858 innodb_undo003 DEFAULT DEFAULT MYSQLD_DATADIR//undo003
859 innodb_temporary DEFAULT DEFAULT MYSQLD_DATADIR/ibtmp1
860 INSERT INTO t4_restart (SELECT 0, c2, c3, c4, c5 FROM t4_restart);
861 SELECT count(*) FROM t4_restart;
862 count(*)
863 64
864 SHOW CREATE TABLE t4_restart;
865 Table Create Table
866 t4_restart CREATE TABLE `t4_restart` (
867 `c1` double NOT NULL AUTO_INCREMENT,
868 `c2` char(10) DEFAULT NULL,
869 `c3` varchar(100) DEFAULT NULL,
870 `c4` date DEFAULT NULL,
871 `c5` text DEFAULT NULL,
872 PRIMARY KEY (`c1`)
873 ) ENGINE=InnoDB AUTO_INCREMENT=1000000099 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC DATA DIRECTORY='MYSQL_TMP_DIR/new_dir/'
874 INSERT INTO t55_restart (SELECT 0, c2, c3, c4, c5 FROM t55_restart);
875 SELECT count(*) FROM t55_restart;
876 count(*)
877 64
878 SHOW CREATE TABLE t55_restart;
879 Table Create Table
880 t55_restart CREATE TABLE `t55_restart` (
881 `c1` double NOT NULL AUTO_INCREMENT,
882 `c2` char(10) DEFAULT NULL,
883 `c3` varchar(100) DEFAULT NULL,
884 `c4` date DEFAULT NULL,
885 `c5` text DEFAULT NULL,
886 PRIMARY KEY (`c1`)
887 ) ENGINE=InnoDB AUTO_INCREMENT=1000000096 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC DATA DIRECTORY='MYSQL_TMP_DIR/new_dir/'
888 INSERT INTO t66_restart (SELECT 0, c2, c3, c4, c5 FROM t66_restart);
889 SELECT count(*) FROM t66_restart;
890 count(*)
891 168
892 SHOW CREATE TABLE t66_restart;
893 Table Create Table
894 t66_restart CREATE TABLE `t66_restart` (
895 `c1` int(11) NOT NULL AUTO_INCREMENT,
896 `c2` char(10) DEFAULT NULL,
897 `c3` varchar(100) DEFAULT NULL,
898 `c4` date DEFAULT NULL,
899 `c5` text DEFAULT NULL,
900 PRIMARY KEY (`c1`)
901 ) ENGINE=InnoDB AUTO_INCREMENT=179 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2
902 PARTITION BY HASH (`c1`)
903 (PARTITION `p0` DATA DIRECTORY = 'MYSQL_TMP_DIR/new_dir' ENGINE = InnoDB,
904 PARTITION `p1` DATA DIRECTORY = 'MYSQL_TMP_DIR/new_dir' ENGINE = InnoDB,
905 PARTITION `p2` DATA DIRECTORY = 'MYSQL_TMP_DIR/new_dir' ENGINE = InnoDB)
906 INSERT INTO t77_restart (SELECT 0, c2, c3, c4, c5 FROM t77_restart);
907 SELECT count(*) FROM t77_restart;
908 count(*)
909 72
910 SHOW CREATE TABLE t77_restart;
911 Table Create Table
912 t77_restart CREATE TABLE `t77_restart` (
913 `c1` int(11) NOT NULL AUTO_INCREMENT,
914 `c2` char(10) DEFAULT NULL,
915 `c3` varchar(100) DEFAULT NULL,
916 `c4` date DEFAULT NULL,
917 `c5` text DEFAULT NULL,
918 PRIMARY KEY (`c1`)
919 ) ENGINE=InnoDB AUTO_INCREMENT=73 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC
920 PARTITION BY RANGE (`c1`)
921 SUBPARTITION BY HASH (`c1`)
922 (PARTITION `p0` VALUES LESS THAN (10)
923 (SUBPARTITION `s0` DATA DIRECTORY = 'MYSQL_TMP_DIR/new_dir' ENGINE = InnoDB,
924 SUBPARTITION `s1` DATA DIRECTORY = 'MYSQL_TMP_DIR/new_dir' ENGINE = InnoDB),
925 PARTITION `p1` VALUES LESS THAN MAXVALUE
926 (SUBPARTITION `s2` DATA DIRECTORY = 'MYSQL_TMP_DIR/new_dir' ENGINE = InnoDB,
927 SUBPARTITION `s3` DATA DIRECTORY = 'MYSQL_TMP_DIR/new_dir' ENGINE = InnoDB))
928 #
929 # Shutdown the server
930 #
931 #
932 # Move the remote tablespaces back to the default datadir and delete the ISL file.
933 #
934 ---- MYSQL_DATA_DIR/test
935 db.opt
936 t4_restart.frm
937 t4_restart.isl
938 t55_restart.frm
939 t55_restart.isl
940 t66_restart#p#p0.isl
941 t66_restart#p#p1.isl
942 t66_restart#p#p2.isl
943 t66_restart.frm
944 t66_restart.par
945 t77_restart#p#p0#sp#s0.isl
946 t77_restart#p#p0#sp#s1.isl
947 t77_restart#p#p1#sp#s2.isl
948 t77_restart#p#p1#sp#s3.isl
949 t77_restart.frm
950 t77_restart.par
951 ---- MYSQL_TMP_DIR/new_dir/test
952 t4_restart.ibd
953 t55_restart.ibd
954 t66_restart#p#p0.ibd
955 t66_restart#p#p1.ibd
956 t66_restart#p#p2.ibd
957 t77_restart#p#p0#sp#s0.ibd
958 t77_restart#p#p0#sp#s1.ibd
959 t77_restart#p#p1#sp#s2.ibd
960 t77_restart#p#p1#sp#s3.ibd
961 # Moving 't4_restart' from MYSQL_TMP_DIR/new_dir to MYSQL_DATA_DIR
962 # Moving 't55_restart' from MYSQL_TMP_DIR/new_dir to MYSQL_DATA_DIR
963 # Moving 't66_restart' from MYSQL_TMP_DIR/new_dir to MYSQL_DATA_DIR
964 # Moving 't77_restart' from MYSQL_TMP_DIR/new_dir to MYSQL_DATA_DIR
965 ---- MYSQL_DATA_DIR/test
966 db.opt
967 t4_restart.frm
968 t4_restart.ibd
969 t55_restart.frm
970 t55_restart.ibd
971 t66_restart#p#p0.ibd
972 t66_restart#p#p1.ibd
973 t66_restart#p#p2.ibd
974 t66_restart.frm
975 t66_restart.par
976 t77_restart#p#p0#sp#s0.ibd
977 t77_restart#p#p0#sp#s1.ibd
978 t77_restart#p#p1#sp#s2.ibd
979 t77_restart#p#p1#sp#s3.ibd
980 t77_restart.frm
981 t77_restart.par
982 ---- MYSQL_TMP_DIR/new_dir/test
983 #
984 # Start the server and check tablespaces.
985 #
986 # restart
987 === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles ===
988 Space_Name Page_Size Zip_Size Path
989 innodb_undo001 DEFAULT DEFAULT MYSQLD_DATADIR//undo001
990 innodb_undo002 DEFAULT DEFAULT MYSQLD_DATADIR//undo002
991 innodb_undo003 DEFAULT DEFAULT MYSQLD_DATADIR//undo003
992 innodb_temporary DEFAULT DEFAULT MYSQLD_DATADIR/ibtmp1
993 INSERT INTO t4_restart (SELECT 0, c2, c3, c4, c5 FROM t4_restart);
994 SELECT count(*) FROM t4_restart;
995 count(*)
996 128
997 SHOW CREATE TABLE t4_restart;
998 Table Create Table
999 t4_restart CREATE TABLE `t4_restart` (
1000 `c1` double NOT NULL AUTO_INCREMENT,
1001 `c2` char(10) DEFAULT NULL,
1002 `c3` varchar(100) DEFAULT NULL,
1003 `c4` date DEFAULT NULL,
1004 `c5` text DEFAULT NULL,
1005 PRIMARY KEY (`c1`)
1006 ) ENGINE=InnoDB AUTO_INCREMENT=1000000195 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC
1007 INSERT INTO t55_restart (SELECT 0, c2, c3, c4, c5 FROM t55_restart);
1008 SELECT count(*) FROM t55_restart;
1009 count(*)
1010 128
1011 SHOW CREATE TABLE t55_restart;
1012 Table Create Table
1013 t55_restart CREATE TABLE `t55_restart` (
1014 `c1` double NOT NULL AUTO_INCREMENT,
1015 `c2` char(10) DEFAULT NULL,
1016 `c3` varchar(100) DEFAULT NULL,
1017 `c4` date DEFAULT NULL,
1018 `c5` text DEFAULT NULL,
1019 PRIMARY KEY (`c1`)
1020 ) ENGINE=InnoDB AUTO_INCREMENT=1000000192 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC
1021 INSERT INTO t66_restart (SELECT 0, c2, c3, c4, c5 FROM t66_restart);
1022 SELECT count(*) FROM t66_restart;
1023 count(*)
1024 336
1025 SHOW CREATE TABLE t66_restart;
1026 Table Create Table
1027 t66_restart CREATE TABLE `t66_restart` (
1028 `c1` int(11) NOT NULL AUTO_INCREMENT,
1029 `c2` char(10) DEFAULT NULL,
1030 `c3` varchar(100) DEFAULT NULL,
1031 `c4` date DEFAULT NULL,
1032 `c5` text DEFAULT NULL,
1033 PRIMARY KEY (`c1`)
1034 ) ENGINE=InnoDB AUTO_INCREMENT=347 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2
1035 PARTITION BY HASH (`c1`)
1036 (PARTITION `p0` ENGINE = InnoDB,
1037 PARTITION `p1` ENGINE = InnoDB,
1038 PARTITION `p2` ENGINE = InnoDB)
1039 INSERT INTO t77_restart (SELECT 0, c2, c3, c4, c5 FROM t77_restart);
1040 SELECT count(*) FROM t77_restart;
1041 count(*)
1042 144
1043 SHOW CREATE TABLE t77_restart;
1044 Table Create Table
1045 t77_restart CREATE TABLE `t77_restart` (
1046 `c1` int(11) NOT NULL AUTO_INCREMENT,
1047 `c2` char(10) DEFAULT NULL,
1048 `c3` varchar(100) DEFAULT NULL,
1049 `c4` date DEFAULT NULL,
1050 `c5` text DEFAULT NULL,
1051 PRIMARY KEY (`c1`)
1052 ) ENGINE=InnoDB AUTO_INCREMENT=145 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC
1053 PARTITION BY RANGE (`c1`)
1054 SUBPARTITION BY HASH (`c1`)
1055 (PARTITION `p0` VALUES LESS THAN (10)
1056 (SUBPARTITION `s0` ENGINE = InnoDB,
1057 SUBPARTITION `s1` ENGINE = InnoDB),
1058 PARTITION `p1` VALUES LESS THAN MAXVALUE
1059 (SUBPARTITION `s2` ENGINE = InnoDB,
1060 SUBPARTITION `s3` ENGINE = InnoDB))
1061 #
1062 # Cleanup
1063 #
1064 DROP TABLE t4_restart;
1065 DROP TABLE t55_restart;
1066 DROP TABLE t66_restart;
1067 DROP TABLE t77_restart;
1068 ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;