Debian Codegraph @

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

raw
1 CREATE TABLE tab(c1 int);
2 INSERT INTO tab VALUES(10);
3 # copy the win datadir zip into destination location
4 # Unzip the zip file on windows platform
5 ## Stop DB server
6 ## Start Linx DB with --innodb-directories
7 ## Check new datadir
8 SELECT @@datadir;
9 @@datadir
10 NEW_DATADIR/db1/
11 SELECT @@innodb_undo_directory;
12 @@innodb_undo_directory
13 UNDO_FILES/undo_files
14 SELECT @@innodb_data_home_dir;
15 @@innodb_data_home_dir
16 DATA_HOME/data_home
17 SELECT @@innodb_log_group_home_dir;
18 @@innodb_log_group_home_dir
19 DATA_HOME_DIR/data_home
20 SELECT @@innodb_data_file_path;
21 @@innodb_data_file_path
22 data01:20M;data02:20M:autoextend
23 SELECT @@innodb_undo_tablespaces;
24 @@innodb_undo_tablespaces
25 2
26 SHOW CREATE TABLE tab3;
27 Table Create Table
28 tab3 CREATE TABLE `tab3` (
29 `empno` int DEFAULT NULL,
30 `ename` varchar(30) DEFAULT NULL,
31 `sal` decimal(3,0) DEFAULT NULL,
32 KEY `ix1` (`ename`) USING BTREE
33 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED
34 /*!50100 PARTITION BY HASH (`empno`)
35 (PARTITION P0 DATA DIRECTORY = '../datadir1' ENGINE = InnoDB,
36 PARTITION P1 DATA DIRECTORY = '../datadir1' ENGINE = InnoDB) */
37 SHOW CREATE TABLE purchase;
38 Table Create Table
39 purchase CREATE TABLE `purchase` (
40 `id` int DEFAULT NULL,
41 `purchased` date DEFAULT NULL
42 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
43 /*!50100 PARTITION BY RANGE (year(`purchased`))
44 SUBPARTITION BY HASH (to_days(`purchased`))
45 (PARTITION p0 VALUES LESS THAN (1990)
46 (SUBPARTITION s0 DATA DIRECTORY = '../part0' ENGINE = InnoDB,
47 SUBPARTITION s1 DATA DIRECTORY = '../part1' ENGINE = InnoDB),
48 PARTITION p1 VALUES LESS THAN (2000)
49 (SUBPARTITION s2 DATA DIRECTORY = '../part2' ENGINE = InnoDB,
50 SUBPARTITION s3 DATA DIRECTORY = '../part3' ENGINE = InnoDB)) */
51 SELECT TABLESPACE_NAME,FILE_TYPE,TABLE_NAME,ENGINE FROM INFORMATION_SCHEMA.FILES;
52 TABLESPACE_NAME FILE_TYPE TABLE_NAME ENGINE
53 # TABLESPACE NULL InnoDB
54 # TABLESPACE NULL InnoDB
55 # TABLESPACE NULL InnoDB
56 # TEMPORARY NULL InnoDB
57 # UNDO LOG NULL InnoDB
58 # UNDO LOG NULL InnoDB
59 # UNDO LOG NULL InnoDB
60 # UNDO LOG NULL InnoDB
61 # UNDO LOG NULL InnoDB
62 # TABLESPACE NULL InnoDB
63 # TABLESPACE NULL InnoDB
64 # TABLESPACE NULL InnoDB
65 # TABLESPACE NULL InnoDB
66 # TABLESPACE NULL InnoDB
67 # TABLESPACE NULL InnoDB
68 # TABLESPACE NULL InnoDB
69 # TABLESPACE NULL InnoDB
70 # TABLESPACE NULL InnoDB
71 # TABLESPACE NULL InnoDB
72 ## Stop DB server
73 ## restart the server with --datadir and innodb-directories
74 ## Check new datadir
75 SELECT @@datadir;
76 @@datadir
77 NEW_DATADIR/db1/
78 SELECT @@innodb_undo_directory;
79 @@innodb_undo_directory
80 UNDO_FILES/undo_files
81 SELECT @@innodb_data_home_dir;
82 @@innodb_data_home_dir
83 DATA_HOME/data_home
84 SELECT @@innodb_log_group_home_dir;
85 @@innodb_log_group_home_dir
86 DATA_HOME_DIR/data_home
87 SELECT @@innodb_data_file_path;
88 @@innodb_data_file_path
89 data01:20M;data02:20M:autoextend
90 SELECT @@innodb_undo_tablespaces;
91 @@innodb_undo_tablespaces
92 2
93 SHOW CREATE TABLE tab3;
94 Table Create Table
95 tab3 CREATE TABLE `tab3` (
96 `empno` int DEFAULT NULL,
97 `ename` varchar(30) DEFAULT NULL,
98 `sal` decimal(3,0) DEFAULT NULL,
99 KEY `ix1` (`ename`) USING BTREE
100 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED
101 /*!50100 PARTITION BY HASH (`empno`)
102 (PARTITION P0 DATA DIRECTORY = '../datadir1' ENGINE = InnoDB,
103 PARTITION P1 DATA DIRECTORY = '../datadir1' ENGINE = InnoDB) */
104 SHOW CREATE TABLE tab4;
105 Table Create Table
106 tab4 CREATE TABLE `tab4` (
107 `c1` int DEFAULT NULL,
108 `c2` varchar(10) DEFAULT NULL
109 ) /*!50100 TABLESPACE `ts2` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
110 SHOW CREATE TABLE purchase;
111 Table Create Table
112 purchase CREATE TABLE `purchase` (
113 `id` int DEFAULT NULL,
114 `purchased` date DEFAULT NULL
115 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
116 /*!50100 PARTITION BY RANGE (year(`purchased`))
117 SUBPARTITION BY HASH (to_days(`purchased`))
118 (PARTITION p0 VALUES LESS THAN (1990)
119 (SUBPARTITION s0 DATA DIRECTORY = '../part0' ENGINE = InnoDB,
120 SUBPARTITION s1 DATA DIRECTORY = '../part1' ENGINE = InnoDB),
121 PARTITION p1 VALUES LESS THAN (2000)
122 (SUBPARTITION s2 DATA DIRECTORY = '../part2' ENGINE = InnoDB,
123 SUBPARTITION s3 DATA DIRECTORY = '../part3' ENGINE = InnoDB)) */
124 ## Check with DML & DDL operations
125 SELECT * FROM tab1;
126 c1 c2
127 1 VISH
128 SELECT * FROM tab2;
129 c1 c2
130 2 VISH
131 SELECT * FROM tab3;
132 empno ename sal
133 100 VISWANATH 100
134 300 VISWANATH 100
135 DELETE FROM tab1;
136 DELETE FROM tab2;
137 DELETE FROM tab3;
138 DELETE FROM tab4;
139 DELETE FROM purchase;
140 ALTER TABLE tab3 ADD COLUMN c3 VARCHAR(15);
141 ALTER TABLE tab4 ADD COLUMN c3 VARCHAR(15);
142 INSERT INTO tab1 VALUES(1, 'VISH');
143 INSERT INTO tab2 VALUES(2, 'VISH');
144 INSERT INTO tab3 VALUES (100,'VISWANATH',100,'Test1');
145 INSERT INTO tab3 VALUES (300,'VISWANATH',100,'Test2');
146 INSERT INTO tab4 VALUES(2, 'VISH', 'NATH');
147 INSERT INTO purchase VALUES(1,'1980-05-31');
148 INSERT INTO purchase VALUES(2,'1999-05-31');
149 INSERT INTO purchase VALUES(3,'1998-05-31');
150 INSERT INTO purchase VALUES(4,'1979-05-31');
151 INSERT INTO purchase VALUES(5,'1978-05-31');
152 INSERT INTO purchase VALUES(6,'1997-05-31');
153 SELECT * FROM tab1;
154 c1 c2
155 1 VISH
156 SELECT * FROM tab2;
157 c1 c2
158 2 VISH
159 SELECT * FROM tab3;
160 empno ename sal c3
161 100 VISWANATH 100 Test1
162 300 VISWANATH 100 Test2
163 SELECT * FROM tab4;
164 c1 c2 c3
165 2 VISH NATH
166 SELECT * FROM purchase;
167 id purchased
168 5 1978-05-31
169 1 1980-05-31
170 4 1979-05-31
171 2 1999-05-31
172 6 1997-05-31
173 3 1998-05-31
174 ## Stop DB server for moving tablespaces around
175 ## Moving tablespace files in criss-cross form
176 ## Start Linx DB with --innodb-directories
177 ## Check new datadir
178 SELECT @@datadir;
179 @@datadir
180 NEW_DATADIR/db1/
181 SELECT @@innodb_undo_directory;
182 @@innodb_undo_directory
183 UNDO_FILES/undo_files
184 SELECT @@innodb_data_home_dir;
185 @@innodb_data_home_dir
186 DATA_HOME/data_home
187 SELECT @@innodb_log_group_home_dir;
188 @@innodb_log_group_home_dir
189 DATA_HOME_DIR/data_home
190 SELECT @@innodb_data_file_path;
191 @@innodb_data_file_path
192 data01:20M;data02:20M:autoextend
193 SELECT @@innodb_undo_tablespaces;
194 @@innodb_undo_tablespaces
195 2
196 SHOW CREATE TABLE tab3;
197 Table Create Table
198 tab3 CREATE TABLE `tab3` (
199 `empno` int DEFAULT NULL,
200 `ename` varchar(30) DEFAULT NULL,
201 `sal` decimal(3,0) DEFAULT NULL,
202 `c3` varchar(15) DEFAULT NULL,
203 KEY `ix1` (`ename`) USING BTREE
204 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED
205 /*!50100 PARTITION BY HASH (`empno`)
206 (PARTITION P0 DATA DIRECTORY = 'db_directory1/db1/../part0' ENGINE = InnoDB,
207 PARTITION P1 DATA DIRECTORY = 'db_directory1/db1/../part1' ENGINE = InnoDB) */
208 SHOW CREATE TABLE purchase;
209 Table Create Table
210 purchase CREATE TABLE `purchase` (
211 `id` int DEFAULT NULL,
212 `purchased` date DEFAULT NULL
213 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
214 /*!50100 PARTITION BY RANGE (year(`purchased`))
215 SUBPARTITION BY HASH (to_days(`purchased`))
216 (PARTITION p0 VALUES LESS THAN (1990)
217 (SUBPARTITION s0 DATA DIRECTORY = 'db_directory1/db1/../datadir1' ENGINE = InnoDB,
218 SUBPARTITION s1 DATA DIRECTORY = 'db_directory1/db1/../datadir1' ENGINE = InnoDB),
219 PARTITION p1 VALUES LESS THAN (2000)
220 (SUBPARTITION s2 DATA DIRECTORY = '../part2' ENGINE = InnoDB,
221 SUBPARTITION s3 DATA DIRECTORY = '../part3' ENGINE = InnoDB)) */
222 SHOW CREATE TABLE tab4;
223 Table Create Table
224 tab4 CREATE TABLE `tab4` (
225 `c1` int DEFAULT NULL,
226 `c2` varchar(10) DEFAULT NULL,
227 `c3` varchar(15) DEFAULT NULL
228 ) /*!50100 TABLESPACE `ts2` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
229 ## Stop DB server after scan DIR
230 ## restart the server with --datadir and innodb-directories
231 ## Check new datadir
232 SELECT @@datadir;
233 @@datadir
234 NEW_DATADIR/db1/
235 SELECT @@innodb_undo_directory;
236 @@innodb_undo_directory
237 UNDO_FILES/undo_files
238 SELECT @@innodb_data_home_dir;
239 @@innodb_data_home_dir
240 DATA_HOME/data_home
241 SELECT @@innodb_log_group_home_dir;
242 @@innodb_log_group_home_dir
243 DATA_HOME_DIR/data_home
244 SELECT @@innodb_data_file_path;
245 @@innodb_data_file_path
246 data01:20M;data02:20M:autoextend
247 SELECT @@innodb_undo_tablespaces;
248 @@innodb_undo_tablespaces
249 2
250 SHOW CREATE TABLE tab3;
251 Table Create Table
252 tab3 CREATE TABLE `tab3` (
253 `empno` int DEFAULT NULL,
254 `ename` varchar(30) DEFAULT NULL,
255 `sal` decimal(3,0) DEFAULT NULL,
256 `c3` varchar(15) DEFAULT NULL,
257 KEY `ix1` (`ename`) USING BTREE
258 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED
259 /*!50100 PARTITION BY HASH (`empno`)
260 (PARTITION P0 DATA DIRECTORY = 'db_directory1/db1/../part0' ENGINE = InnoDB,
261 PARTITION P1 DATA DIRECTORY = 'db_directory1/db1/../part1' ENGINE = InnoDB) */
262 SHOW CREATE TABLE purchase;
263 Table Create Table
264 purchase CREATE TABLE `purchase` (
265 `id` int DEFAULT NULL,
266 `purchased` date DEFAULT NULL
267 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
268 /*!50100 PARTITION BY RANGE (year(`purchased`))
269 SUBPARTITION BY HASH (to_days(`purchased`))
270 (PARTITION p0 VALUES LESS THAN (1990)
271 (SUBPARTITION s0 DATA DIRECTORY = 'db_directory1/db1/../datadir1' ENGINE = InnoDB,
272 SUBPARTITION s1 DATA DIRECTORY = 'db_directory1/db1/../datadir1' ENGINE = InnoDB),
273 PARTITION p1 VALUES LESS THAN (2000)
274 (SUBPARTITION s2 DATA DIRECTORY = '../part2' ENGINE = InnoDB,
275 SUBPARTITION s3 DATA DIRECTORY = '../part3' ENGINE = InnoDB)) */
276 SHOW CREATE TABLE tab4;
277 Table Create Table
278 tab4 CREATE TABLE `tab4` (
279 `c1` int DEFAULT NULL,
280 `c2` varchar(10) DEFAULT NULL,
281 `c3` varchar(15) DEFAULT NULL
282 ) /*!50100 TABLESPACE `ts2` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
283 ## Check with DML operations
284 DELETE FROM tab1;
285 DELETE FROM tab2;
286 DELETE FROM tab3;
287 DELETE FROM tab4;
288 DELETE FROM purchase;
289 ALTER TABLE tab3 ADD COLUMN c4 VARCHAR(15);
290 ALTER TABLE tab4 DROP COLUMN c3;
291 INSERT INTO tab1 VALUES(1, 'VISH');
292 INSERT INTO tab2 VALUES(2, 'VISH');
293 INSERT INTO tab3 VALUES (100,'VISWANATH',100,'Test1','Check1');
294 INSERT INTO tab3 VALUES (300,'VISWANATH',100,'Test2','Check2');
295 INSERT INTO tab4 VALUES(2, 'VISH');
296 INSERT INTO purchase VALUES(1,'1980-05-31');
297 INSERT INTO purchase VALUES(2,'1999-05-31');
298 INSERT INTO purchase VALUES(3,'1998-05-31');
299 INSERT INTO purchase VALUES(4,'1979-05-31');
300 INSERT INTO purchase VALUES(5,'1978-05-31');
301 INSERT INTO purchase VALUES(6,'1997-05-31');
302 SELECT * FROM tab1;
303 c1 c2
304 1 VISH
305 SELECT * FROM tab2;
306 c1 c2
307 2 VISH
308 SELECT * FROM tab3;
309 empno ename sal c3 c4
310 100 VISWANATH 100 Test1 Check1
311 300 VISWANATH 100 Test2 Check2
312 SELECT * FROM tab4;
313 c1 c2
314 2 VISH
315 DROP TABLE tab1;
316 DROP TABLE tab2;
317 DROP TABLE tab3;
318 DROP TABLE tab4;
319 DROP TABLE purchase;
320 DROP TABLESPACE ts2;
321 ## Stop DB server for clean up
322 ## Cleanup the Windows --datadir location and its *.ibd file location
323 ## Restart the server with MTR default location
324 # restart: --datadir=OLD_DATADIR
325 SELECT @@datadir;
326 @@datadir
327 OLD_DATADIR
328 SELECT * FROM tab;
329 c1
330 10
331 DROP TABLE tab;