Debian Codegraph @

mysql-test/suite/parts/t/partition_basic_symlink_innodb.test

raw
1 ################################################################################
2 # t/partition_basic_innodb.test #
3 # #
4 # Purpose: #
5 # Tests around Create Partitioned table using DATA/INDEX DIR #
6 # InnoDB branch #
7 # #
8 #------------------------------------------------------------------------------#
9 # Original Author: mleich #
10 # Original Date: 2006-03-05 #
11 # Change Author: mattiasj #
12 # Change Date: 2008-02-05 #
13 # Change: copied it from partition_basic_innodb.test and kept DATA DIR #
14 # Change Author: mattiasj #
15 # Change Date: 2008-03-16 #
16 # Change: Replaced all test with alter -> myisam, since innodb does not support#
17 # Change Author: Kevin lewis #
18 # Change Date: 2012-03-02 #
19 # Change: WL5980 activates DATA DIRECTORY for InnoDB #
20 ################################################################################
21
22 # NOTE: As of WL5980, InnoDB supports DATA DIRECTORY, but not INDEX DIRECTORY.
23 # See innodb.innodb-tablespace for tests using partition engine, innodb
24 # and DATADIRECTORY. The purpose of this test is to show that a
25 # partitioned table remembers the DATA/INDEX DIR and it is used if
26 # altered to MyISAM
27 #
28 --echo #
29 --echo # Verify that the DATA/INDEX DIR is stored and used if ALTER to MyISAM.
30 --echo #
31 --source include/have_innodb.inc
32 # The server must support partitioning.
33 --source include/have_partition.inc
34 # The server must support symlink for DATA/INDEX DIRECTORY.
35 --source include/have_symlink.inc
36 # windows does not support symlink for DATA/INDEX DIRECTORY.
37 --source include/not_windows.inc
38
39 # Does not work with --embedded
40 --source include/not_embedded.inc
41
42 let $MYSQLD_DATADIR= `select @@datadir`;
43
44 # These values can change during the test
45 SET @file_per_table= @@GLOBAL.innodb_file_per_table;
46 SET @strict_mode= @@SESSION.innodb_strict_mode;
47
48 --mkdir $MYSQLTEST_VARDIR/mysql-test-data-dir
49 --mkdir $MYSQLTEST_VARDIR/mysql-test-idx-dir
50
51 SET SESSION innodb_strict_mode = ON;
52
53 --echo #
54 --echo # InnoDB only supports DATA DIRECTORY with innodb_file_per_table=ON
55 --echo #
56 SET GLOBAL innodb_file_per_table = OFF;
57 --error ER_CANT_CREATE_TABLE
58 evalp CREATE TABLE t1 (c1 INT, KEY(c1)) ENGINE = InnoDB
59 PARTITION BY HASH (c1) (
60 PARTITION p0
61 DATA DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-data-dir'
62 INDEX DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-idx-dir',
63 PARTITION p1
64 DATA DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-data-dir'
65 INDEX DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-idx-dir'
66 );
67 --replace_result ./ MYSQLD_DATADIR/ $MYSQLD_DATADIR MYSQLD_DATADIR
68 SHOW WARNINGS;
69
70 --echo #
71 --echo # InnoDB is different from MyISAM in that it uses a text file
72 --echo # with an '.isl' extension instead of a symbolic link so that
73 --echo # the tablespace can be re-located on any OS. Also, instead of
74 --echo # putting the file directly into the DATA DIRECTORY,
75 --echo # it adds a folder under it with the name of the database.
76 --echo # Since strict mode is off, InnoDB ignores the INDEX DIRECTORY
77 --echo # and it is no longer part of the definition.
78 --echo #
79 SET SESSION innodb_strict_mode = OFF;
80 SET GLOBAL innodb_file_per_table = ON;
81 evalp CREATE TABLE t1 (c1 INT, KEY(c1)) ENGINE = InnoDB
82 PARTITION BY HASH (c1)
83 (PARTITION p0
84 DATA DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-data-dir'
85 INDEX DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-idx-dir',
86 PARTITION p1
87 DATA DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-data-dir'
88 INDEX DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-idx-dir'
89 );
90 SHOW WARNINGS;
91
92 --echo # Verifying .frm, .par, .isl & .ibd files
93 --echo ---- MYSQLD_DATADIR/test
94 --list_files $MYSQLD_DATADIR/test
95 --echo ---- MYSQLTEST_VARDIR/mysql-test-data-dir/test
96 --list_files $MYSQLTEST_VARDIR/mysql-test-data-dir/test
97 --echo # The ibd tablespaces should not be directly under the DATA DIRECTORY
98 --echo ---- MYSQLTEST_VARDIR/mysql-test-data-dir
99 --list_files $MYSQLTEST_VARDIR/mysql-test-data-dir
100 --echo ---- MYSQLTEST_VARDIR/mysql-test-idx-dir
101 --list_files $MYSQLTEST_VARDIR/mysql-test-idx-dir
102
103 FLUSH TABLES;
104 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
105 SHOW CREATE TABLE t1;
106
107 --echo #
108 --echo # Verify that the DATA/INDEX DIRECTORY is stored and used if we
109 --echo # ALTER TABLE to MyISAM.
110 --echo #
111 ALTER TABLE t1 engine=MyISAM;
112 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
113 SHOW CREATE TABLE t1;
114 --echo # Verifying .frm, .par and MyISAM files (.MYD, MYI)
115 --list_files $MYSQLD_DATADIR/test
116 --echo ---- MYSQLTEST_VARDIR/mysql-test-data-dir
117 --list_files $MYSQLTEST_VARDIR/mysql-test-data-dir
118 --echo ---- MYSQLTEST_VARDIR/mysql-test-idx-dir
119 --list_files $MYSQLTEST_VARDIR/mysql-test-idx-dir
120
121 --echo #
122 --echo # Now verify that the DATA DIRECTORY is used again if we
123 --echo # ALTER TABLE back to InnoDB.
124 --echo #
125 SET SESSION innodb_strict_mode = ON;
126 ALTER TABLE t1 engine=InnoDB;
127 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
128 SHOW CREATE TABLE t1;
129 --echo # Verifying .frm, .par, .isl and InnoDB .ibd files
130 --echo ---- MYSQLD_DATADIR/test
131 --list_files $MYSQLD_DATADIR/test
132 --echo ---- MYSQLTEST_VARDIR/mysql-test-data-dir
133 --list_files $MYSQLTEST_VARDIR/mysql-test-data-dir
134 --echo ---- MYSQLTEST_VARDIR/mysql-test-idx-dir
135 --list_files $MYSQLTEST_VARDIR/mysql-test-idx-dir
136 --echo ---- MYSQLTEST_VARDIR/mysql-test-data-dir/test
137 --list_files $MYSQLTEST_VARDIR/mysql-test-data-dir/test
138
139 DROP TABLE t1;
140
141 --echo #
142 --echo # MDEV-14611 ALTER TABLE EXCHANGE PARTITION does not work
143 --echo # properly when used with DATA DIRECTORY
144 --echo #
145 let $data_dir_path= $MYSQLTEST_VARDIR/mysql-test-data-dir;
146 let $alt_data_dir_path= $MYSQLTEST_VARDIR/mysql-test-idx-dir;
147 SET GLOBAL innodb_file_per_table = ON;
148 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
149 eval CREATE TABLE t1
150 (
151 myid INT(11) NOT NULL,
152 myval VARCHAR(10),
153 PRIMARY KEY (myid)
154 ) ENGINE=INNODB PARTITION BY KEY (myid)
155 (
156 PARTITION p0001 DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB,
157 PARTITION p0002 DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB,
158 PARTITION p0003 DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB,
159 PARTITION p0004 DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB
160 );
161
162 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
163 eval CREATE TABLE t2
164 (
165 myid INT(11) NOT NULL,
166 myval VARCHAR(10),
167 PRIMARY KEY (myid)
168 ) ENGINE=INNODB DATA DIRECTORY = '$data_dir_path';
169
170 ALTER TABLE t1 EXCHANGE PARTITION p0001 WITH TABLE t2;
171
172 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
173 SHOW CREATE TABLE t1;
174 DROP TABLE t1, t2;
175 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
176 eval CREATE TABLE t1
177 (
178 myid INT(11) NOT NULL,
179 myval VARCHAR(10),
180 PRIMARY KEY (myid)
181 ) ENGINE=INNODB PARTITION BY RANGE (myid)
182 (
183 PARTITION p0001 VALUES LESS THAN (50) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB,
184 PARTITION p0002 VALUES LESS THAN (150) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB,
185 PARTITION p0003 VALUES LESS THAN (1050) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB,
186 PARTITION p0004 VALUES LESS THAN (10050) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB
187 );
188
189 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
190 eval CREATE TABLE t2
191 (
192 myid INT(11) NOT NULL,
193 myval VARCHAR(10),
194 PRIMARY KEY (myid)
195 ) ENGINE=INNODB DATA DIRECTORY = '$alt_data_dir_path';
196
197 insert into t1 values (1, 'one');
198 insert into t2 values (2, 'two'), (3, 'threee'), (4, 'four');
199
200 select * from t1;
201 ALTER TABLE t1 EXCHANGE PARTITION p0001 WITH TABLE t2;
202
203 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
204 SHOW CREATE TABLE t1;
205 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
206 SHOW CREATE TABLE t2;
207 select * from t1;
208 select * from t2;
209 DROP TABLE t1, t2;
210
211 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
212 eval CREATE TABLE t1
213 (
214 myid INT(11) NOT NULL,
215 myval VARCHAR(10),
216 PRIMARY KEY (myid)
217 ) ENGINE=INNODB PARTITION BY RANGE (myid)
218 (
219 PARTITION p0001 VALUES LESS THAN (50) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB,
220 PARTITION p0002 VALUES LESS THAN (150) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB,
221 PARTITION p0003 VALUES LESS THAN (1050) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB,
222 PARTITION p0004 VALUES LESS THAN (10050) DATA DIRECTORY = '$data_dir_path' ENGINE = INNODB
223 );
224
225 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
226 eval CREATE TABLE t2
227 (
228 myid INT(11) NOT NULL,
229 myval VARCHAR(10),
230 PRIMARY KEY (myid)
231 ) ENGINE=INNODB;
232
233 insert into t1 values (1, 'one');
234 insert into t2 values (2, 'two'), (3, 'threee'), (4, 'four');
235
236 select * from t1;
237 ALTER TABLE t1 EXCHANGE PARTITION p0001 WITH TABLE t2;
238
239 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
240 SHOW CREATE TABLE t1;
241 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
242 SHOW CREATE TABLE t2;
243 select * from t1;
244 select * from t2;
245 DROP TABLE t1, t2;
246
247 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
248 eval CREATE TABLE t1
249 (
250 myid INT(11) NOT NULL,
251 myval VARCHAR(10),
252 PRIMARY KEY (myid)
253 ) ENGINE=INNODB PARTITION BY RANGE (myid)
254 (
255 PARTITION p0001 VALUES LESS THAN (50) ENGINE = INNODB,
256 PARTITION p0002 VALUES LESS THAN (150) ENGINE = INNODB,
257 PARTITION p0003 VALUES LESS THAN (1050) ENGINE = INNODB,
258 PARTITION p0004 VALUES LESS THAN (10050) ENGINE = INNODB
259 );
260
261 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
262 eval CREATE TABLE t2
263 (
264 myid INT(11) NOT NULL,
265 myval VARCHAR(10),
266 PRIMARY KEY (myid)
267 ) ENGINE=INNODB DATA DIRECTORY = '$alt_data_dir_path';
268
269 insert into t1 values (1, 'one');
270 insert into t2 values (2, 'two'), (3, 'threee'), (4, 'four');
271
272 select * from t1;
273 ALTER TABLE t1 EXCHANGE PARTITION p0001 WITH TABLE t2;
274
275 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
276 SHOW CREATE TABLE t1;
277 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
278 SHOW CREATE TABLE t2;
279 select * from t1;
280 select * from t2;
281 DROP TABLE t1, t2;
282 --echo #
283 --echo # Cleanup
284 --echo #
285
286 --rmdir $MYSQLTEST_VARDIR/mysql-test-data-dir/test
287 --rmdir $MYSQLTEST_VARDIR/mysql-test-data-dir
288 --rmdir $MYSQLTEST_VARDIR/mysql-test-idx-dir
289
290 SET GLOBAL innodb_file_per_table=@file_per_table;
291 SET SESSION innodb_strict_mode=@strict_mode;