Debian Codegraph @

mysql-test/suite/innodb/t/portability_tablespace.test

raw
1 # ********************************************************************
2 # wl#8619 : Testing the functionality of portability of general
3 # and remote tablespace files can be moved into any of the following
4 # listed known directories
5 # 1) --datadir (mysql home)
6 # 2) --innodb-directories= dir1;dir2;etc...
7 # 3) --innodb-undo-directory
8 # 4) --innodb-data-home-dir
9 # Note : --datadir as whole unit should be moved and its
10 # folder hierarchy should be preserved
11 # User has to follow the MySQL rules while moving the files.
12 # Check general tablespaces are allowed to create only in known DIR
13 # Check relative path and absolute path
14 # ********************************************************************
15 --source include/have_innodb_16k.inc
16 --source include/have_innodb_default_undo_tablespaces.inc
17 --source include/not_valgrind.inc
18
19 --echo # Define old data locations
20 let $MYSQLD_OLD_DATADIR = `select @@datadir`;
21 let $MYSQLD_OLD = $MYSQL_TMP_DIR/old;
22 let $MYSQLD_OLD_REMOTE_DIR = $MYSQLD_OLD/remote_dir;
23 let $MYSQLD_OLD_REDO_LOG_DIR = $MYSQLD_OLD_DATADIR/#innodb_redo;
24
25 --disable_query_log
26 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
27 --let $restart_parameters = restart: --innodb-directories=$MYSQLD_OLD_REMOTE_DIR
28 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
29 --source include/restart_mysqld.inc
30 --enable_query_log
31
32 --echo # Define and create new datadir folders
33 let $MYSQLD_NEW = $MYSQL_TMP_DIR/new;
34 let $MYSQLD_NEW_DATADIR = $MYSQLD_NEW/data;
35 let $MYSQLD_NEW_UNDO_DIR = $MYSQLD_NEW/undo_dir;
36 let $MYSQLD_NEW_DATA_HOME_DIR = $MYSQLD_NEW/data_home_dir;
37 let $MYSQLD_NEW_REDO_LOG_DIR = $MYSQLD_NEW_DATA_HOME_DIR/#innodb_redo;
38 let $MYSQLD_NEW_REMOTE_DIR = $MYSQLD_NEW/remote_dir;
39
40 --mkdir $MYSQLD_NEW
41 --mkdir $MYSQLD_NEW_DATADIR
42 --mkdir $MYSQLD_NEW_DATADIR/test
43 --mkdir $MYSQLD_NEW_DATADIR/mysql
44 --mkdir $MYSQLD_NEW_DATADIR/sys
45 --mkdir $MYSQLD_NEW_DATADIR/performance_schema
46 --mkdir $MYSQLD_NEW_DATADIR/mtr
47 --mkdir $MYSQLD_NEW_UNDO_DIR
48 --mkdir $MYSQLD_NEW_DATA_HOME_DIR
49 --mkdir $MYSQLD_NEW_REDO_LOG_DIR
50 --mkdir $MYSQLD_NEW_REMOTE_DIR
51 --mkdir $MYSQLD_NEW_REMOTE_DIR/test
52
53 --echo ## create all kinds of tables including absolute paths
54 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
55
56 CREATE TABLE tab1(c1 int, c2 varchar(10)) TABLESPACE=ts1;
57
58 INSERT INTO tab1 VALUES(1, 'VISH');
59
60 CREATE TABLE tab2(c1 int, c2 varchar(10)) Engine=InnoDB;
61
62 INSERT INTO tab2 VALUES(2, 'VISH');
63
64 CREATE INDEX ix1 ON tab1(c2) USING BTREE;
65
66 CREATE INDEX ix1 ON tab2(c2) ;
67
68 --echo # Create a table with absolute path.
69 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
70 eval CREATE TABLE tab3(
71 empno INT, ename VARCHAR(30),sal NUMERIC(3))
72 ENGINE=InnoDB ROW_FORMAT=COMPRESSED
73 PARTITION by hash(empno) (
74 PARTITION P0 DATA DIRECTORY '$MYSQLD_OLD_REMOTE_DIR',
75 PARTITION P1 DATA DIRECTORY '$MYSQLD_OLD_REMOTE_DIR');
76
77 --echo # Create a tablespace in a known location with absolute path.
78 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
79 eval CREATE TABLESPACE ts2 ADD DATAFILE '$MYSQLD_OLD_REMOTE_DIR/ts2.ibd' Engine=InnoDB;
80 DROP TABLESPACE ts2;
81
82 --echo # Create a tablespace in a known location with relative path.
83 eval CREATE TABLESPACE ts2 ADD DATAFILE '../data/ts2.ibd' Engine=InnoDB;
84 DROP TABLESPACE ts2;
85
86 --echo # Try to create a tablespace in an unknown location.
87 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
88 --error ER_WRONG_FILE_NAME
89 eval CREATE TABLESPACE ts2 ADD DATAFILE '../ts2.ibd' Engine=InnoDB;
90 SHOW WARNINGS;
91
92 CREATE INDEX ix1 ON tab3(ename) USING BTREE;
93
94 INSERT INTO tab3 VALUES (100,'VISWANATH',100);
95
96 INSERT INTO tab3 VALUES (300,'VISWANATH',100);
97
98 SHOW CREATE TABLE tab1;
99
100 SHOW CREATE TABLE tab2;
101
102 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
103 SHOW CREATE TABLE tab3;
104
105 # Create new undo tablespaces
106 CREATE UNDO TABLESPACE undo_003 ADD DATAFILE 'undo_003.ibu';
107 CREATE UNDO TABLESPACE undo_004 ADD DATAFILE 'undo_004.ibu';
108
109 # Show the metadata info.
110 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
111 SELECT file_name,file_type,tablespace_name FROM INFORMATION_SCHEMA.FILES WHERE file_type LIKE '%undo%';
112 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
113 SELECT path FROM INFORMATION_SCHEMA.INNODB_DATAFILES WHERE path LIKE '%undo_0%' ORDER BY path;
114
115 --echo ## Stop DB server
116 --let $shutdown_server_timeout = 300
117 --source include/shutdown_mysqld.inc
118
119 --echo # Copy whole --datadir files into new locations
120 --copy_file $MYSQLD_OLD_DATADIR/auto.cnf $MYSQLD_NEW_DATADIR/auto.cnf
121 --copy_file $MYSQLD_OLD_DATADIR/ib_buffer_pool $MYSQLD_NEW_DATA_HOME_DIR/ib_buffer_pool
122 --copy_file $MYSQLD_OLD_DATADIR/ibdata1 $MYSQLD_NEW_DATA_HOME_DIR/ibdata1
123 --copy_files_wildcard $MYSQLD_OLD_REDO_LOG_DIR $MYSQLD_NEW_REDO_LOG_DIR #ib_redo*
124
125 --copy_files_wildcard $MYSQLD_OLD_DATADIR $MYSQLD_NEW_DATADIR *.ibd
126 --copy_file $MYSQLD_OLD_DATADIR/undo_001 $MYSQLD_NEW_UNDO_DIR/undo_001
127 --copy_file $MYSQLD_OLD_DATADIR/undo_002 $MYSQLD_NEW_UNDO_DIR/undo_002
128 --copy_file $MYSQLD_OLD_DATADIR/undo_003.ibu $MYSQLD_NEW_UNDO_DIR/undo_003.ibu
129 --copy_file $MYSQLD_OLD_DATADIR/undo_004.ibu $MYSQLD_NEW_REMOTE_DIR/undo_004.ibu
130
131 --copy_files_wildcard $MYSQLD_OLD_DATADIR/test/ $MYSQLD_NEW_DATADIR/test/ *
132 --copy_files_wildcard $MYSQLD_OLD_DATADIR/sys/ $MYSQLD_NEW_DATADIR/sys/ *
133 --copy_files_wildcard $MYSQLD_OLD_DATADIR/performance_schema/ $MYSQLD_NEW_DATADIR/performance_schema/ *
134 --copy_files_wildcard $MYSQLD_OLD_DATADIR/mysql/ $MYSQLD_NEW_DATADIR/mysql/ *
135 --copy_files_wildcard $MYSQLD_OLD_DATADIR/mtr/ $MYSQLD_NEW_DATADIR/mtr/ *
136
137 --echo # Copy remote *.ibd files into new remote location
138 --copy_files_wildcard $MYSQLD_OLD_REMOTE_DIR/test/ $MYSQLD_NEW_REMOTE_DIR/test tab3*.ibd
139
140 --echo # Remove the old datadir files and *.ibd files
141 --remove_files_wildcard $MYSQLD_OLD_REMOTE_DIR/test/ *.ibd
142 --remove_files_wildcard $MYSQLD_OLD_REDO_LOG_DIR/ #ib_redo*
143 --remove_files_wildcard $MYSQLD_OLD_DATADIR/ ib_buffer*
144 --remove_files_wildcard $MYSQLD_OLD_DATADIR/ ibdata*
145 --remove_files_wildcard $MYSQLD_OLD_DATADIR/ undo*
146 --remove_files_wildcard $MYSQLD_OLD_DATADIR/ auto*
147 --remove_files_wildcard $MYSQLD_OLD_DATADIR/ *.ibd
148
149 --remove_files_wildcard $MYSQLD_OLD_DATADIR/test/ *
150 --remove_files_wildcard $MYSQLD_OLD_DATADIR/sys/ *
151 --remove_files_wildcard $MYSQLD_OLD_DATADIR/performance_schema/ *
152 --remove_files_wildcard $MYSQLD_OLD_DATADIR/mysql/ *
153 --remove_files_wildcard $MYSQLD_OLD_DATADIR/mtr/ *
154
155 --echo # Start with --innodb-directories along with other initDB options
156 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
157 --let $restart_parameters=restart: --datadir=$MYSQLD_NEW_DATADIR --innodb_undo_directory=$MYSQLD_NEW_UNDO_DIR --innodb_data_home_dir=$MYSQLD_NEW_DATA_HOME_DIR --innodb_log_group_home_dir=$MYSQLD_NEW_DATA_HOME_DIR --innodb-directories=$MYSQLD_NEW_REMOTE_DIR
158 --source include/start_mysqld_no_echo.inc
159
160 --echo # Check new datadir
161 --replace_result $MYSQLD_NEW_DATADIR NEW_DATADIR
162 SELECT @@datadir;
163
164 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
165 SELECT @@innodb_undo_directory;
166
167 --replace_result $MYSQL_TMP_DIR DATA_HOME_DIR
168 SELECT @@innodb_data_home_dir;
169
170 --replace_result $MYSQL_TMP_DIR DATA_HOME_DIR
171 SELECT @@innodb_log_group_home_dir;
172
173 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
174 SHOW CREATE TABLE tab3;
175
176 # Check if the metadata info is correct.
177 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
178 SELECT file_name,file_type,tablespace_name FROM INFORMATION_SCHEMA.FILES WHERE file_type LIKE '%undo%';
179 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
180 SELECT path FROM INFORMATION_SCHEMA.INNODB_DATAFILES WHERE path LIKE '%undo_0%' ORDER BY path;
181
182 --echo # Stop DB server
183 --let $shutdown_server_timeout = 300
184 --source include/shutdown_mysqld.inc
185
186 --echo # Start with new --datadir only
187 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
188 --let $restart_parameters=restart: --datadir=$MYSQLD_NEW_DATADIR --innodb_undo_directory=$MYSQLD_NEW_UNDO_DIR --innodb_data_home_dir=$MYSQLD_NEW_DATA_HOME_DIR --innodb_log_group_home_dir=$MYSQLD_NEW_DATA_HOME_DIR --innodb-directories=$MYSQLD_NEW_REMOTE_DIR
189 --source include/start_mysqld.inc
190
191 --echo # Check with new --datadir
192 --replace_result $MYSQLD_NEW_DATADIR NEW_DATADIR
193 SELECT @@datadir;
194
195 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
196 SELECT @@innodb_undo_directory;
197
198 --replace_result $MYSQL_TMP_DIR DATA_HOME_DIR
199 SELECT @@innodb_data_home_dir;
200
201 --replace_result $MYSQL_TMP_DIR DATA_HOME_DIR
202 SELECT @@innodb_log_group_home_dir;
203
204 --echo # Check the tables with SHOW
205 SHOW CREATE TABLE tab1;
206
207 SHOW CREATE TABLE tab2;
208
209 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
210 SHOW CREATE TABLE tab3;
211
212 # Check if the metadata info is correct.
213 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
214 SELECT file_name,file_type,tablespace_name FROM INFORMATION_SCHEMA.FILES WHERE file_type LIKE '%undo%';
215 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
216 SELECT path FROM INFORMATION_SCHEMA.INNODB_DATAFILES WHERE path LIKE '%undo_0%' ORDER BY path;
217
218 ALTER UNDO TABLESPACE innodb_undo_001 SET INACTIVE;
219 ALTER UNDO TABLESPACE innodb_undo_002 SET INACTIVE;
220
221 --echo # Check with DML & DDL operations
222 SELECT * FROM tab1;
223
224 SELECT * FROM tab2;
225
226 SELECT * FROM tab3;
227
228 DELETE FROM tab1;
229
230 DELETE FROM tab2;
231
232 DELETE FROM tab3;
233
234 ALTER TABLE tab3 ADD COLUMN c3 VARCHAR(15);
235
236 INSERT INTO tab1 VALUES(1, 'VISH');
237
238 INSERT INTO tab2 VALUES(2, 'VISH');
239
240 INSERT INTO tab3 VALUES (100,'VISWANATH',100,'New Column');
241
242 INSERT INTO tab3 VALUES (300,'VISWANATH',100,'New Column');
243
244 SELECT * FROM tab1;
245
246 SELECT * FROM tab2;
247
248 SELECT * FROM tab3;
249
250 let $inactive_undo_space = innodb_undo_001;
251 source include/wait_until_undo_space_is_empty.inc;
252 let $inactive_undo_space = innodb_undo_002;
253 source include/wait_until_undo_space_is_empty.inc;
254
255 SELECT name,space_type,state FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE space_type = 'Undo';
256
257 ALTER UNDO TABLESPACE innodb_undo_001 SET ACTIVE;
258 ALTER UNDO TABLESPACE innodb_undo_002 SET ACTIVE;
259
260 SELECT name,space_type,state FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE space_type = 'Undo';
261
262 --echo # Creating general tablespace in known DIR location
263 --replace_result $MYSQL_TMP_DIR DATA_HOME_DIR
264 eval CREATE TABLESPACE ts2 ADD DATAFILE '$MYSQLD_NEW_DATA_HOME_DIR/ts2.ibd' Engine=InnoDB;
265
266 CREATE TABLE tab4(c1 int, c2 varchar(10)) TABLESPACE=ts2;
267
268 SHOW CREATE TABLE tab4;
269
270 INSERT INTO tab4 VALUES(1, 'VISH');
271
272 --echo # Creating general tablespace in known DIR location
273 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
274 eval CREATE TABLESPACE ts3 ADD DATAFILE '$MYSQLD_NEW_REMOTE_DIR/ts3.ibd' Engine=InnoDB;
275
276 CREATE TABLE tab5(c1 int, c2 varchar(10)) TABLESPACE=ts3;
277
278 SHOW CREATE TABLE tab5;
279
280 INSERT INTO tab5 VALUES(1, 'VISH');
281
282 --echo # Clean up new datadir
283 DROP TABLE tab1;
284 DROP TABLE tab2;
285 DROP TABLESPACE ts1;
286
287 --echo # Stop DB server with new datadir
288 --let $shutdown_server_timeout = 300
289 --source include/shutdown_mysqld.inc
290
291 --echo # Copy back --datadir and *.ibd files into old location
292 --copy_files_wildcard $MYSQLD_NEW_REMOTE_DIR/test $MYSQLD_OLD_REMOTE_DIR/test *.ibd
293 --copy_files_wildcard $MYSQLD_NEW_REMOTE_DIR $MYSQLD_OLD_REMOTE_DIR/test ts3.ibd
294 --copy_files_wildcard $MYSQLD_NEW_UNDO_DIR $MYSQLD_OLD_DATADIR undo*
295 --copy_files_wildcard $MYSQLD_NEW_REMOTE_DIR $MYSQLD_OLD_DATADIR undo*
296 --copy_files_wildcard $MYSQLD_NEW_DATA_HOME_DIR $MYSQLD_OLD_REMOTE_DIR/test ts2.ibd
297
298 --copy_file $MYSQLD_NEW_DATADIR/auto.cnf $MYSQLD_OLD_DATADIR/auto.cnf
299 --copy_file $MYSQLD_NEW_DATA_HOME_DIR/ib_buffer_pool $MYSQLD_OLD_DATADIR/ib_buffer_pool
300 --copy_file $MYSQLD_NEW_DATA_HOME_DIR/ibdata1 $MYSQLD_OLD_DATADIR/ibdata1
301 --copy_files_wildcard $MYSQLD_NEW_REDO_LOG_DIR $MYSQLD_OLD_REDO_LOG_DIR/ #ib_redo*
302 --copy_files_wildcard $MYSQLD_NEW_DATADIR $MYSQLD_OLD_DATADIR *.ibd
303
304 --copy_files_wildcard $MYSQLD_NEW_DATADIR/sys/ $MYSQLD_OLD_DATADIR/sys/ *
305 --copy_files_wildcard $MYSQLD_NEW_DATADIR/performance_schema/ $MYSQLD_OLD_DATADIR/performance_schema/ *
306 --copy_files_wildcard $MYSQLD_NEW_DATADIR/mysql/ $MYSQLD_OLD_DATADIR/mysql/ *
307 --copy_files_wildcard $MYSQLD_NEW_DATADIR/mtr/ $MYSQLD_OLD_DATADIR/mtr/ *
308
309 --echo # Cleanup the new DATA DIRECTORY *.ibd files
310 --force-rmdir $MYSQLD_NEW_UNDO_DIR
311 --force-rmdir $MYSQLD_NEW_DATA_HOME_DIR
312 --force-rmdir $MYSQLD_NEW_REMOTE_DIR
313 --force-rmdir $MYSQLD_NEW
314
315 --echo # Test by providing the relative path
316 --echo # Re-start with old --datadir with --innodb-directories as remote *.ibd files are existing.
317 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
318 --let $restart_parameters= restart: --innodb-directories=$MYSQLD_OLD_REMOTE_DIR
319 --source include/start_mysqld.inc
320
321 --echo # Check --datadir started with old
322 --replace_result $MYSQLD_OLD_DATADIR OLD_DATADIR
323 SELECT @@datadir;
324
325 # Check if the metadata info is correct.
326 SELECT file_name,file_type,tablespace_name FROM INFORMATION_SCHEMA.FILES WHERE file_type LIKE '%undo%';
327 SELECT path FROM INFORMATION_SCHEMA.INNODB_DATAFILES WHERE path LIKE '%undo_0%' ORDER BY path;
328
329 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
330 SHOW CREATE TABLE tab3;
331
332 SHOW CREATE TABLE tab4;
333
334 SHOW CREATE TABLE tab5;
335
336 # Set implicit undo tablespaces inactive
337 ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
338 let $inactive_undo_space = undo_003;
339 source include/wait_until_undo_space_is_empty.inc;
340
341 ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
342 let $inactive_undo_space = undo_004;
343 source include/wait_until_undo_space_is_empty.inc;
344
345 SELECT name,space_type,state FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE space_type = 'Undo';
346
347 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
348 SHOW VARIABLES LIKE '%directories%';
349
350 --echo # Creating general tablespace in known DIR location (DATA DIRECTORY)
351 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
352 eval CREATE TABLESPACE ts4 ADD DATAFILE '$MYSQLD_OLD_REMOTE_DIR/ts4.ibd' Engine=InnoDB;
353
354 CREATE TABLE tab6(c1 int, c2 varchar(10)) TABLESPACE=ts4;
355
356 INSERT INTO tab6 VALUES(1, 'VISH');
357
358 # Check DML
359 DELETE FROM tab3;
360
361 DELETE FROM tab4;
362
363 DELETE FROM tab5;
364
365 DELETE FROM tab6;
366
367 SELECT ENGINE, TABLESPACE_NAME FROM INFORMATION_SCHEMA.FILES ORDER BY TABLESPACE_NAME;
368
369 # Clean up old datadir
370 DROP TABLE tab3;
371
372 DROP TABLE tab4;
373
374 DROP TABLE tab5;
375
376 DROP TABLE tab6;
377
378 DROP TABLESPACE ts4;
379 DROP TABLESPACE ts3;
380 DROP TABLESPACE ts2;
381
382 DROP UNDO TABLESPACE undo_003;
383 DROP UNDO TABLESPACE undo_004;
384
385 --disable_query_log
386 call mtr.add_suppression("Scan path '.*' is ignored because it is a sub-directory of");
387 call mtr.add_suppression("\\[Warning\\].* Tablespace .*, name '.*', file '.*' is missing");
388 call mtr.add_suppression("\\[ERROR\\].* Cannot create tablespace .* because the directory is not a valid location. The DATAFILE location must be in a known directory");
389 --enable_query_log
390
391 --let $restart_parameters= restart:
392 --source include/restart_mysqld.inc
393
394 --rmdir $MYSQLD_OLD_REMOTE_DIR/test
395 --rmdir $MYSQLD_OLD_REMOTE_DIR
396 --rmdir $MYSQLD_OLD