Debian Codegraph @

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

raw
1 # Define old data locations
2 # restart: --innodb-directories=MYSQL_TMP_DIR/old/remote_dir
3 # Define and create new datadir folders
4 ## create all kinds of tables including absolute paths
5 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
6 CREATE TABLE tab1(c1 int, c2 varchar(10)) TABLESPACE=ts1;
7 INSERT INTO tab1 VALUES(1, 'VISH');
8 CREATE TABLE tab2(c1 int, c2 varchar(10)) Engine=InnoDB;
9 INSERT INTO tab2 VALUES(2, 'VISH');
10 CREATE INDEX ix1 ON tab1(c2) USING BTREE;
11 CREATE INDEX ix1 ON tab2(c2) ;
12 # Create a table with absolute path.
13 CREATE TABLE tab3(
14 empno INT, ename VARCHAR(30),sal NUMERIC(3))
15 ENGINE=InnoDB ROW_FORMAT=COMPRESSED
16 PARTITION by hash(empno) (
17 PARTITION P0 DATA DIRECTORY 'MYSQL_TMP_DIR/old/remote_dir',
18 PARTITION P1 DATA DIRECTORY 'MYSQL_TMP_DIR/old/remote_dir');
19 # Create a tablespace in a known location with absolute path.
20 CREATE TABLESPACE ts2 ADD DATAFILE 'MYSQL_TMP_DIR/old/remote_dir/ts2.ibd' Engine=InnoDB;
21 DROP TABLESPACE ts2;
22 # Create a tablespace in a known location with relative path.
23 CREATE TABLESPACE ts2 ADD DATAFILE '../data/ts2.ibd' Engine=InnoDB;
24 DROP TABLESPACE ts2;
25 # Try to create a tablespace in an unknown location.
26 CREATE TABLESPACE ts2 ADD DATAFILE '../ts2.ibd' Engine=InnoDB;
27 ERROR HY000: The DATAFILE location must be in a known directory.
28 SHOW WARNINGS;
29 Level Code Message
30 Error 3121 The DATAFILE location must be in a known directory.
31 Error 1528 Failed to create TABLESPACE ts2
32 Error 3121 Incorrect File Name '../ts2.ibd'.
33 CREATE INDEX ix1 ON tab3(ename) USING BTREE;
34 INSERT INTO tab3 VALUES (100,'VISWANATH',100);
35 INSERT INTO tab3 VALUES (300,'VISWANATH',100);
36 SHOW CREATE TABLE tab1;
37 Table Create Table
38 tab1 CREATE TABLE `tab1` (
39 `c1` int DEFAULT NULL,
40 `c2` varchar(10) DEFAULT NULL,
41 KEY `ix1` (`c2`) USING BTREE
42 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
43 SHOW CREATE TABLE tab2;
44 Table Create Table
45 tab2 CREATE TABLE `tab2` (
46 `c1` int DEFAULT NULL,
47 `c2` varchar(10) DEFAULT NULL,
48 KEY `ix1` (`c2`)
49 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
50 SHOW CREATE TABLE tab3;
51 Table Create Table
52 tab3 CREATE TABLE `tab3` (
53 `empno` int DEFAULT NULL,
54 `ename` varchar(30) DEFAULT NULL,
55 `sal` decimal(3,0) DEFAULT NULL,
56 KEY `ix1` (`ename`) USING BTREE
57 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED
58 /*!50100 PARTITION BY HASH (`empno`)
59 (PARTITION P0 DATA DIRECTORY = 'MYSQL_TMP_DIR/old/remote_dir/' ENGINE = InnoDB,
60 PARTITION P1 DATA DIRECTORY = 'MYSQL_TMP_DIR/old/remote_dir/' ENGINE = InnoDB) */
61 CREATE UNDO TABLESPACE undo_003 ADD DATAFILE 'undo_003.ibu';
62 CREATE UNDO TABLESPACE undo_004 ADD DATAFILE 'undo_004.ibu';
63 SELECT file_name,file_type,tablespace_name FROM INFORMATION_SCHEMA.FILES WHERE file_type LIKE '%undo%';
64 FILE_NAME FILE_TYPE TABLESPACE_NAME
65 ./undo_001 UNDO LOG innodb_undo_001
66 ./undo_002 UNDO LOG innodb_undo_002
67 ./undo_003.ibu UNDO LOG undo_003
68 ./undo_004.ibu UNDO LOG undo_004
69 SELECT path FROM INFORMATION_SCHEMA.INNODB_DATAFILES WHERE path LIKE '%undo_0%' ORDER BY path;
70 PATH
71 ./undo_001
72 ./undo_002
73 undo_003.ibu
74 undo_004.ibu
75 ## Stop DB server
76 # Copy whole --datadir files into new locations
77 # Copy remote *.ibd files into new remote location
78 # Remove the old datadir files and *.ibd files
79 # Start with --innodb-directories along with other initDB options
80 # Check new datadir
81 SELECT @@datadir;
82 @@datadir
83 NEW_DATADIR/
84 SELECT @@innodb_undo_directory;
85 @@innodb_undo_directory
86 MYSQL_TMP_DIR/new/undo_dir
87 SELECT @@innodb_data_home_dir;
88 @@innodb_data_home_dir
89 DATA_HOME_DIR/new/data_home_dir
90 SELECT @@innodb_log_group_home_dir;
91 @@innodb_log_group_home_dir
92 DATA_HOME_DIR/new/data_home_dir
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 = 'MYSQL_TMP_DIR/new/remote_dir' ENGINE = InnoDB,
103 PARTITION P1 DATA DIRECTORY = 'MYSQL_TMP_DIR/new/remote_dir' ENGINE = InnoDB) */
104 SELECT file_name,file_type,tablespace_name FROM INFORMATION_SCHEMA.FILES WHERE file_type LIKE '%undo%';
105 FILE_NAME FILE_TYPE TABLESPACE_NAME
106 MYSQL_TMP_DIR/new/undo_dir/undo_001 UNDO LOG innodb_undo_001
107 MYSQL_TMP_DIR/new/undo_dir/undo_002 UNDO LOG innodb_undo_002
108 MYSQL_TMP_DIR/new/undo_dir/undo_003.ibu UNDO LOG undo_003
109 MYSQL_TMP_DIR/new/remote_dir/undo_004.ibu UNDO LOG undo_004
110 SELECT path FROM INFORMATION_SCHEMA.INNODB_DATAFILES WHERE path LIKE '%undo_0%' ORDER BY path;
111 PATH
112 MYSQL_TMP_DIR/new/remote_dir/undo_004.ibu
113 MYSQL_TMP_DIR/new/undo_dir/undo_001
114 MYSQL_TMP_DIR/new/undo_dir/undo_002
115 MYSQL_TMP_DIR/new/undo_dir/undo_003.ibu
116 # Stop DB server
117 # Start with new --datadir only
118 # restart: --datadir=MYSQL_TMP_DIR/new/data --innodb_undo_directory=MYSQL_TMP_DIR/new/undo_dir --innodb_data_home_dir=MYSQL_TMP_DIR/new/data_home_dir --innodb_log_group_home_dir=MYSQL_TMP_DIR/new/data_home_dir --innodb-directories=MYSQL_TMP_DIR/new/remote_dir
119 # Check with new --datadir
120 SELECT @@datadir;
121 @@datadir
122 NEW_DATADIR/
123 SELECT @@innodb_undo_directory;
124 @@innodb_undo_directory
125 MYSQL_TMP_DIR/new/undo_dir
126 SELECT @@innodb_data_home_dir;
127 @@innodb_data_home_dir
128 DATA_HOME_DIR/new/data_home_dir
129 SELECT @@innodb_log_group_home_dir;
130 @@innodb_log_group_home_dir
131 DATA_HOME_DIR/new/data_home_dir
132 # Check the tables with SHOW
133 SHOW CREATE TABLE tab1;
134 Table Create Table
135 tab1 CREATE TABLE `tab1` (
136 `c1` int DEFAULT NULL,
137 `c2` varchar(10) DEFAULT NULL,
138 KEY `ix1` (`c2`) USING BTREE
139 ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
140 SHOW CREATE TABLE tab2;
141 Table Create Table
142 tab2 CREATE TABLE `tab2` (
143 `c1` int DEFAULT NULL,
144 `c2` varchar(10) DEFAULT NULL,
145 KEY `ix1` (`c2`)
146 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
147 SHOW CREATE TABLE tab3;
148 Table Create Table
149 tab3 CREATE TABLE `tab3` (
150 `empno` int DEFAULT NULL,
151 `ename` varchar(30) DEFAULT NULL,
152 `sal` decimal(3,0) DEFAULT NULL,
153 KEY `ix1` (`ename`) USING BTREE
154 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED
155 /*!50100 PARTITION BY HASH (`empno`)
156 (PARTITION P0 DATA DIRECTORY = 'MYSQL_TMP_DIR/new/remote_dir' ENGINE = InnoDB,
157 PARTITION P1 DATA DIRECTORY = 'MYSQL_TMP_DIR/new/remote_dir' ENGINE = InnoDB) */
158 SELECT file_name,file_type,tablespace_name FROM INFORMATION_SCHEMA.FILES WHERE file_type LIKE '%undo%';
159 FILE_NAME FILE_TYPE TABLESPACE_NAME
160 MYSQL_TMP_DIR/new/undo_dir/undo_001 UNDO LOG innodb_undo_001
161 MYSQL_TMP_DIR/new/undo_dir/undo_002 UNDO LOG innodb_undo_002
162 MYSQL_TMP_DIR/new/undo_dir/undo_003.ibu UNDO LOG undo_003
163 MYSQL_TMP_DIR/new/remote_dir/undo_004.ibu UNDO LOG undo_004
164 SELECT path FROM INFORMATION_SCHEMA.INNODB_DATAFILES WHERE path LIKE '%undo_0%' ORDER BY path;
165 PATH
166 MYSQL_TMP_DIR/new/remote_dir/undo_004.ibu
167 MYSQL_TMP_DIR/new/undo_dir/undo_001
168 MYSQL_TMP_DIR/new/undo_dir/undo_002
169 MYSQL_TMP_DIR/new/undo_dir/undo_003.ibu
170 ALTER UNDO TABLESPACE innodb_undo_001 SET INACTIVE;
171 ALTER UNDO TABLESPACE innodb_undo_002 SET INACTIVE;
172 # Check with DML & DDL operations
173 SELECT * FROM tab1;
174 c1 c2
175 1 VISH
176 SELECT * FROM tab2;
177 c1 c2
178 2 VISH
179 SELECT * FROM tab3;
180 empno ename sal
181 100 VISWANATH 100
182 300 VISWANATH 100
183 DELETE FROM tab1;
184 DELETE FROM tab2;
185 DELETE FROM tab3;
186 ALTER TABLE tab3 ADD COLUMN c3 VARCHAR(15);
187 INSERT INTO tab1 VALUES(1, 'VISH');
188 INSERT INTO tab2 VALUES(2, 'VISH');
189 INSERT INTO tab3 VALUES (100,'VISWANATH',100,'New Column');
190 INSERT INTO tab3 VALUES (300,'VISWANATH',100,'New Column');
191 SELECT * FROM tab1;
192 c1 c2
193 1 VISH
194 SELECT * FROM tab2;
195 c1 c2
196 2 VISH
197 SELECT * FROM tab3;
198 empno ename sal c3
199 100 VISWANATH 100 New Column
200 300 VISWANATH 100 New Column
201 SELECT name,space_type,state FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE space_type = 'Undo';
202 name space_type state
203 innodb_undo_001 Undo empty
204 innodb_undo_002 Undo empty
205 undo_003 Undo active
206 undo_004 Undo active
207 ALTER UNDO TABLESPACE innodb_undo_001 SET ACTIVE;
208 ALTER UNDO TABLESPACE innodb_undo_002 SET ACTIVE;
209 SELECT name,space_type,state FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE space_type = 'Undo';
210 name space_type state
211 innodb_undo_001 Undo active
212 innodb_undo_002 Undo active
213 undo_003 Undo active
214 undo_004 Undo active
215 # Creating general tablespace in known DIR location
216 CREATE TABLESPACE ts2 ADD DATAFILE 'DATA_HOME_DIR/new/data_home_dir/ts2.ibd' Engine=InnoDB;
217 CREATE TABLE tab4(c1 int, c2 varchar(10)) TABLESPACE=ts2;
218 SHOW CREATE TABLE tab4;
219 Table Create Table
220 tab4 CREATE TABLE `tab4` (
221 `c1` int DEFAULT NULL,
222 `c2` varchar(10) DEFAULT NULL
223 ) /*!50100 TABLESPACE `ts2` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
224 INSERT INTO tab4 VALUES(1, 'VISH');
225 # Creating general tablespace in known DIR location
226 CREATE TABLESPACE ts3 ADD DATAFILE 'MYSQL_TMP_DIR/new/remote_dir/ts3.ibd' Engine=InnoDB;
227 CREATE TABLE tab5(c1 int, c2 varchar(10)) TABLESPACE=ts3;
228 SHOW CREATE TABLE tab5;
229 Table Create Table
230 tab5 CREATE TABLE `tab5` (
231 `c1` int DEFAULT NULL,
232 `c2` varchar(10) DEFAULT NULL
233 ) /*!50100 TABLESPACE `ts3` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
234 INSERT INTO tab5 VALUES(1, 'VISH');
235 # Clean up new datadir
236 DROP TABLE tab1;
237 DROP TABLE tab2;
238 DROP TABLESPACE ts1;
239 # Stop DB server with new datadir
240 # Copy back --datadir and *.ibd files into old location
241 # Cleanup the new DATA DIRECTORY *.ibd files
242 # Test by providing the relative path
243 # Re-start with old --datadir with --innodb-directories as remote *.ibd files are existing.
244 # restart: --innodb-directories=MYSQL_TMP_DIR/old/remote_dir
245 # Check --datadir started with old
246 SELECT @@datadir;
247 @@datadir
248 OLD_DATADIR
249 SELECT file_name,file_type,tablespace_name FROM INFORMATION_SCHEMA.FILES WHERE file_type LIKE '%undo%';
250 FILE_NAME FILE_TYPE TABLESPACE_NAME
251 ./undo_001 UNDO LOG innodb_undo_001
252 ./undo_002 UNDO LOG innodb_undo_002
253 ./undo_003.ibu UNDO LOG undo_003
254 ./undo_004.ibu UNDO LOG undo_004
255 SELECT path FROM INFORMATION_SCHEMA.INNODB_DATAFILES WHERE path LIKE '%undo_0%' ORDER BY path;
256 PATH
257 ./undo_001
258 ./undo_002
259 ./undo_003.ibu
260 ./undo_004.ibu
261 SHOW CREATE TABLE tab3;
262 Table Create Table
263 tab3 CREATE TABLE `tab3` (
264 `empno` int DEFAULT NULL,
265 `ename` varchar(30) DEFAULT NULL,
266 `sal` decimal(3,0) DEFAULT NULL,
267 `c3` varchar(15) DEFAULT NULL,
268 KEY `ix1` (`ename`) USING BTREE
269 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED
270 /*!50100 PARTITION BY HASH (`empno`)
271 (PARTITION P0 DATA DIRECTORY = 'MYSQL_TMP_DIR/old/remote_dir' ENGINE = InnoDB,
272 PARTITION P1 DATA DIRECTORY = 'MYSQL_TMP_DIR/old/remote_dir' ENGINE = InnoDB) */
273 SHOW CREATE TABLE tab4;
274 Table Create Table
275 tab4 CREATE TABLE `tab4` (
276 `c1` int DEFAULT NULL,
277 `c2` varchar(10) DEFAULT NULL
278 ) /*!50100 TABLESPACE `ts2` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
279 SHOW CREATE TABLE tab5;
280 Table Create Table
281 tab5 CREATE TABLE `tab5` (
282 `c1` int DEFAULT NULL,
283 `c2` varchar(10) DEFAULT NULL
284 ) /*!50100 TABLESPACE `ts3` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
285 ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
286 ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
287 SELECT name,space_type,state FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE space_type = 'Undo';
288 name space_type state
289 innodb_undo_001 Undo active
290 innodb_undo_002 Undo active
291 undo_003 Undo empty
292 undo_004 Undo empty
293 SHOW VARIABLES LIKE '%directories%';
294 Variable_name Value
295 innodb_directories MYSQL_TMP_DIR/old/remote_dir
296 # Creating general tablespace in known DIR location (DATA DIRECTORY)
297 CREATE TABLESPACE ts4 ADD DATAFILE 'MYSQL_TMP_DIR/old/remote_dir/ts4.ibd' Engine=InnoDB;
298 CREATE TABLE tab6(c1 int, c2 varchar(10)) TABLESPACE=ts4;
299 INSERT INTO tab6 VALUES(1, 'VISH');
300 DELETE FROM tab3;
301 DELETE FROM tab4;
302 DELETE FROM tab5;
303 DELETE FROM tab6;
304 SELECT ENGINE, TABLESPACE_NAME FROM INFORMATION_SCHEMA.FILES ORDER BY TABLESPACE_NAME;
305 ENGINE TABLESPACE_NAME
306 InnoDB innodb_system
307 InnoDB innodb_temporary
308 InnoDB innodb_undo_001
309 InnoDB innodb_undo_002
310 InnoDB mtr/asserted_test_suppressions
311 InnoDB mtr/global_suppressions
312 InnoDB mtr/test_ignored_global_suppressions
313 InnoDB mtr/test_suppressions
314 InnoDB mysql
315 InnoDB sys/sys_config
316 InnoDB test/tab3#p#p0
317 InnoDB test/tab3#p#p1
318 InnoDB ts2
319 InnoDB ts3
320 InnoDB ts4
321 InnoDB undo_003
322 InnoDB undo_004
323 DROP TABLE tab3;
324 DROP TABLE tab4;
325 DROP TABLE tab5;
326 DROP TABLE tab6;
327 DROP TABLESPACE ts4;
328 DROP TABLESPACE ts3;
329 DROP TABLESPACE ts2;
330 DROP UNDO TABLESPACE undo_003;
331 DROP UNDO TABLESPACE undo_004;
332 # restart: