1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
|
--echo #
--echo # A series of tests to show the correct file-per-table tablespace behavior.
--echo # See also; parts.partition_basic_symlink_innodb.test for
--echo # partition related tests with remote tablespaces.
--echo # See innodb.innodb-restart for tablespace migration tests.
--echo #
SET default_storage_engine=InnoDB;
--echo #
--echo # TABLESPACE related tests.
--echo #
# Set up some variables
LET $MYSQLD_DATADIR = `select @@datadir`;
LET $INNODB_PAGE_SIZE = `select @@innodb_page_size`;
LET $data_directory_clause = DATA DIRECTORY='$MYSQL_TMP_DIR/alt_dir';
LET $index_directory_clause = INDEX DIRECTORY='$MYSQL_TMP_DIR/alt_dir';
--echo #
--echo # CREATE TABLE ... INDEX DIRECTORY
--echo # In non-strict mode INDEX DIRECTORY is ignored.
--echo # In strict mode INDEX DIRECTORY is rejected.
--echo #
SET SESSION innodb_strict_mode = OFF;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TABLE t1 (a int KEY, b text) $index_directory_clause;
SHOW WARNINGS;
SHOW CREATE TABLE t1;
DROP TABLE t1;
SET SESSION innodb_strict_mode = ON;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
--error ER_ILLEGAL_HA
eval CREATE TABLE t1 (a int KEY, b text) $index_directory_clause;
SHOW WARNINGS;
--echo #
--echo # Innodb does not support DATA DIRECTORY without innodb_file_per_table=ON.
--echo # In non-strict mode DATA DIRECTORY is ignored.
--echo # In strict mode DATA DIRECTORY without innodb_file_per_table=ON is rejected.
--echo # TABLESPACE='innodb_file_per_table' overrides the innodb_file_per_table=OFF setting.
--echo #
SET GLOBAL innodb_file_per_table=OFF;
SET SESSION innodb_strict_mode = OFF;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TABLE t1 (a int KEY, b text) $data_directory_clause;
SHOW WARNINGS;
SHOW CREATE TABLE t1;
DROP TABLE t1;
SET SESSION innodb_strict_mode = ON;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
--error ER_ILLEGAL_HA
eval CREATE TABLE t1 (a int KEY, b text) $data_directory_clause;
SHOW WARNINGS;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TABLE t1 (a int KEY, b text) TABLESPACE=`innodb_file_per_table` $data_directory_clause;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t1;
DROP TABLE t1;
--echo # Now set innodb_file_per_table so that DATA DIRECTORY can be tested.
SET GLOBAL innodb_file_per_table=ON;
--echo #
--echo # Create the tablespace in MYSQL_TMP_DIR/alt_dir
--echo # InnoDB will create the sub-directories if needed.
--echo #
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TABLE t1 (a int KEY, b text) $data_directory_clause;
SHOW WARNINGS;
INSERT INTO t1 VALUES (1, 'Create the tablespace');
SELECT * FROM t1;
--echo #
--echo # Check if link file exists in MYSQLD_DATADIR
--echo #
--echo ---- MYSQLD_DATADIR/test
--list_files $MYSQLD_DATADIR/test
--echo # Check if tablespace file exists where we specified in DATA DIRECTORY
--echo ---- MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test
--echo #
--echo # Check that DATA DIRECTORY shows up in the SHOW CREATE TABLE results.
--echo #
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t1;
--echo # Show that the system tables have this tablespace in them correctly
--source suite/innodb/include/show_i_s_tables.inc
--source suite/innodb/include/show_i_s_tablespaces.inc
--echo #
--echo # Show that the system tables are updated on drop table
--echo #
DROP TABLE t1;
--source suite/innodb/include/show_i_s_tables.inc
--source suite/innodb/include/show_i_s_tablespaces.inc
--echo #
--echo # Create the same table a second time in the same place
--echo #
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TABLE t1 (a int KEY, b text) $data_directory_clause;
INSERT INTO t1 VALUES
(2, 'Create the same table a second time in the same place');
SELECT * FROM t1;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t1;
--source suite/innodb/include/show_i_s_tables.inc
--source suite/innodb/include/show_i_s_tablespaces.inc
--echo ---- MYSQLD_DATADIR/test
--list_files $MYSQLD_DATADIR/test
--echo ---- MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test
--echo #
--echo # Truncate the table, then insert and verify
--echo #
TRUNCATE TABLE t1;
INSERT INTO t1 VALUES (3, 'Truncate the table, then insert');
SELECT * FROM t1;
--source suite/innodb/include/show_i_s_tables.inc
--source suite/innodb/include/show_i_s_tablespaces.inc
--echo ---- MYSQLD_DATADIR/test
--list_files $MYSQLD_DATADIR/test
--echo ---- MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test
--echo #
--echo # Rename the table, then insert and verify
--echo #
RENAME TABLE t1 TO t2;
INSERT INTO t2 VALUES (4, 'Rename the table, then insert');
SELECT * FROM t2;
--source suite/innodb/include/show_i_s_tables.inc
--source suite/innodb/include/show_i_s_tablespaces.inc
--echo ---- MYSQLD_DATADIR/test
--list_files $MYSQLD_DATADIR/test
--echo ---- MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test
--echo #
--echo # CREATE TABLE LIKE does not retain DATA DIRECTORY automatically.
--echo #
CREATE TABLE t3 LIKE t2;
INSERT INTO t3 VALUES (5, 'CREATE TABLE LIKE');
SELECT * FROM t3;
--source suite/innodb/include/show_i_s_tables.inc
--source suite/innodb/include/show_i_s_tablespaces.inc
--echo ---- MYSQLD_DATADIR/test
--list_files $MYSQLD_DATADIR/test
--echo #
--echo # Now make sure the tables can be fully dropped.
--echo #
DROP TABLE t2, t3;
--source suite/innodb/include/show_i_s_tables.inc
--source suite/innodb/include/show_i_s_tablespaces.inc
--echo ---- MYSQLD_DATADIR/test
--list_files $MYSQLD_DATADIR/test
--echo ---- MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test
--echo #
--echo # Be sure SQL MODE 'NO_DIR_IN_CREATE' prevents the use of DATA DIRECTORY
--echo #
SET @org_mode=@@sql_mode;
SET @@sql_mode='NO_DIR_IN_CREATE';
SELECT @@sql_mode;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TABLE t1 (a int, b text) $data_directory_clause;
SHOW WARNINGS;
INSERT INTO t1 VALUES (6, 'SQL MODE NO_DIR_IN_CREATE prevents DATA DIRECTORY');
# Checking if tablespace exists in --datadir since DATA DIRECTORY was ignored.
--file_exists $MYSQLD_DATADIR/test/t1.ibd
DROP TABLE t1;
set @@sql_mode=@org_mode;
--echo #
--echo # The server does not allow any DATA DIRECTORY name to be a relative path
--echo # or to be located in or under MYSQLD_DATADIR.
--echo #
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
--error ER_WRONG_VALUE
eval CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY './';
SHOW WARNINGS;
--error ER_WRONG_VALUE
eval CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY '../';
SHOW WARNINGS;
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
--error ER_WRONG_ARGUMENTS
eval CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY '$MYSQLD_DATADIR';
SHOW WARNINGS;
--mkdir $MYSQLD_DATADIR/under
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
--error ER_WRONG_ARGUMENTS
eval CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY '$MYSQLD_DATADIR/under';
SHOW WARNINGS;
--rmdir $MYSQLD_DATADIR/under
--echo #
--echo # TEMPORARY tables are incompatible with DATA DIRECTORY
--echo # In strict mode DATA DIRECTORY is rejected. In non-strict mode it
--echo # is ignored.
--echo #
SET SESSION innodb_strict_mode = OFF;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TEMPORARY TABLE t1 (a int KEY, b text) engine=InnoDB $data_directory_clause;
SHOW WARNINGS;
SHOW CREATE TABLE t1;
DROP TABLE t1;
SET SESSION innodb_strict_mode = ON;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
--error ER_ILLEGAL_HA
eval CREATE TEMPORARY TABLE t1 (a int KEY, b text) engine=InnoDB $data_directory_clause;
SHOW WARNINGS;
--echo #
--echo # Create the remote table via static DDL statements in a stored procedure
--echo #
DELIMITER |;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE PROCEDURE static_proc() BEGIN CREATE TABLE t1 (a int KEY, b text) $data_directory_clause; END |
DELIMITER ;|
CALL static_proc;
--source suite/innodb/include/show_i_s_tablespaces.inc
INSERT INTO t1 VALUES (7, 'Create the remote table via static DDL statements');
SELECT * FROM t1;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t1;
--echo ---- MYSQLD_DATADIR/test
--list_files $MYSQLD_DATADIR/test
--echo ---- MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test
DROP PROCEDURE static_proc;
DROP TABLE t1;
--echo #
--echo # Create the remote table via dynamic DDL statements in a stored procedure
--echo #
DELIMITER |;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE PROCEDURE dynamic_proc()
BEGIN
PREPARE stmt1 FROM
"CREATE TABLE t1 (a int KEY, b text) $data_directory_clause";
EXECUTE stmt1;
END |
DELIMITER ;|
CALL dynamic_proc;
INSERT INTO t1 VALUES (8, 'Create the remote table via dynamic DDL statements');
SELECT * FROM t1;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t1;
--source suite/innodb/include/show_i_s_tablespaces.inc
--echo ---- MYSQLD_DATADIR/test
--list_files $MYSQLD_DATADIR/test
--echo ---- MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test
DROP PROCEDURE dynamic_proc;
DEALLOCATE PREPARE stmt1;
DROP TABLE t1;
--echo #
--echo # CREATE, DROP, ADD and TRUNCATE PARTITION with DATA DIRECTORY
--echo #
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TABLE emp (
id INT NOT NULL,
store_name VARCHAR(30),
parts VARCHAR(30),
store_id INT
)
PARTITION BY LIST(store_id) (
PARTITION east VALUES IN (10,20,30)
DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_east',
PARTITION north VALUES IN (40,50,60)
DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_north',
PARTITION west VALUES IN (70,80,100)
DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_west'
);
INSERT INTO emp values(1,'Oracle','NUTT',10);
INSERT INTO emp values(2,'HUAWEI','BOLT',40);
INSERT INTO emp values(3,'IBM','NAIL',70);
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE emp;
SELECT * FROM emp;
# InnoDB always converts table names to lower case on Windows
--source suite/innodb/include/show_i_s_tables.inc
--source suite/innodb/include/show_i_s_tablespaces.inc
--echo ---- MYSQLD_DATADIR/test
--replace_regex /emp#P#/emp#p#/
--list_files $MYSQLD_DATADIR/test
--echo ---- MYSQL_TMP_DIR/alt_dir_east/test
--replace_regex /emp#P#/emp#p#/
--list_files $MYSQL_TMP_DIR/alt_dir_east/test
--echo ---- MYSQL_TMP_DIR/alt_dir_north/test
--replace_regex /emp#P#/emp#p#/
--list_files $MYSQL_TMP_DIR/alt_dir_north/test
--echo ---- MYSQL_TMP_DIR/alt_dir_west/test
--replace_regex /emp#P#/emp#p#/
--list_files $MYSQL_TMP_DIR/alt_dir_west/test
--echo #
--echo # DROP one PARTITION.
--echo #
ALTER TABLE emp DROP PARTITION west;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE emp;
SELECT * FROM emp;
--source suite/innodb/include/show_i_s_tables.inc
--source suite/innodb/include/show_i_s_tablespaces.inc
--echo ---- MYSQLD_DATADIR/test
--replace_regex /emp#P#/emp#p#/
--list_files $MYSQLD_DATADIR/test
--echo ---- MYSQL_TMP_DIR/alt_dir_east/test
--replace_regex /emp#P#/emp#p#/
--list_files $MYSQL_TMP_DIR/alt_dir_east/test
--echo ---- MYSQL_TMP_DIR/alt_dir_north/test
--replace_regex /emp#P#/emp#p#/
--list_files $MYSQL_TMP_DIR/alt_dir_north/test
--echo ---- MYSQL_TMP_DIR/alt_dir_west/test
--replace_regex /emp#P#/emp#p#/
--list_files $MYSQL_TMP_DIR/alt_dir_west/test
--echo #
--echo # ADD the PARTITION back.
--echo #
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval ALTER TABLE emp ADD PARTITION (
PARTITION west VALUES IN (70,80,100)
DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_west');
INSERT INTO emp VALUES(3,'IBM','NAIL',70);
SELECT * FROM emp;
--source suite/innodb/include/show_i_s_tables.inc
--source suite/innodb/include/show_i_s_tablespaces.inc
--echo ---- MYSQLD_DATADIR/test
--replace_regex /emp#P#/emp#p#/
--list_files $MYSQLD_DATADIR/test
--echo ---- MYSQL_TMP_DIR/alt_dir_east/test
--replace_regex /emp#P#/emp#p#/
--list_files $MYSQL_TMP_DIR/alt_dir_east/test
--echo ---- MYSQL_TMP_DIR/alt_dir_north/test
--replace_regex /emp#P#/emp#p#/
--list_files $MYSQL_TMP_DIR/alt_dir_north/test
--echo ---- MYSQL_TMP_DIR/alt_dir_west/test
--replace_regex /emp#P#/emp#p#/
--list_files $MYSQL_TMP_DIR/alt_dir_west/test
--echo #
--echo # TRUNCATE one PARTITION.
--echo #
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
ALTER TABLE emp TRUNCATE PARTITION west;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE emp;
INSERT INTO emp VALUES(3,'IBM','NAIL',70);
SELECT * FROM emp;
--source suite/innodb/include/show_i_s_tables.inc
--source suite/innodb/include/show_i_s_tablespaces.inc
--echo ---- MYSQLD_DATADIR/test
--replace_regex /emp#P#/emp#p#/
--list_files $MYSQLD_DATADIR/test
--echo ---- MYSQL_TMP_DIR/alt_dir_east/test
--replace_regex /emp#P#/emp#p#/
--list_files $MYSQL_TMP_DIR/alt_dir_east/test
--echo ---- MYSQL_TMP_DIR/alt_dir_north/test
--replace_regex /emp#P#/emp#p#/
--list_files $MYSQL_TMP_DIR/alt_dir_north/test
--echo ---- MYSQL_TMP_DIR/alt_dir_west/test
--replace_regex /emp#P#/emp#p#/
--list_files $MYSQL_TMP_DIR/alt_dir_west/test
DROP TABLE emp;
--echo #
--echo # Cleanup
--echo #
--rmdir $MYSQL_TMP_DIR/alt_dir_east/test
--rmdir $MYSQL_TMP_DIR/alt_dir_east
--rmdir $MYSQL_TMP_DIR/alt_dir_north/test
--rmdir $MYSQL_TMP_DIR/alt_dir_north
--rmdir $MYSQL_TMP_DIR/alt_dir_west/test
--rmdir $MYSQL_TMP_DIR/alt_dir_west
--rmdir $MYSQL_TMP_DIR/alt_dir/test
--rmdir $MYSQL_TMP_DIR/alt_dir
|
