1#
2# A series of tests to show the correct behavior when using
3# ALTER TABLE ... REORGANIZE PARTITION with TokuDB
4# See also; parts.partition_basic_symlink_tokudb.test for
5# partition related tests with remote tablespaces.
6# See tokudb.tokudb-restart for tablespace migration tests.
7#
8--source include/have_tokudb.inc
9--source include/have_partition.inc
10SET default_storage_engine=TokuDB;
11LET $MYSQLD_DATADIR = `select @@datadir`;
12
13# These values can change during the test
14LET $tokudb_file_format_orig=`select @@tokudb_file_format`;
15LET $tokudb_file_per_table_orig=`select @@tokudb_file_per_table`;
16LET $tokudb_strict_mode_orig=`select @@session.tokudb_strict_mode`;
17
18SET SESSION tokudb_strict_mode = ON;
19SET GLOBAL tokudb_file_per_table=ON;
20SET GLOBAL tokudb_file_format=barracuda;
21
22# Unlike MyISAM, TokuDB creates the subdirectories given to it in the
23# DATA DIRECTORY clauses.  Another difference is that TokuDB uses an extra
24# directory under DATA DIRECTORY with the name of the database.
25--echo #
26--echo # CREATE a table with SUBPARTITIONS
27--echo #
28--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
29eval CREATE TABLE emp (
30    id INT NOT NULL,
31    store_name VARCHAR(30),
32    parts VARCHAR(30),
33    store_id INT
34) engine TokuDB
35PARTITION BY RANGE(store_id) SUBPARTITION BY HASH(store_id)
36(
37  PARTITION northeast VALUES LESS THAN (50)
38    DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_northeast'
39    (SUBPARTITION ne0, SUBPARTITION ne1),
40  PARTITION southwest VALUES LESS THAN (100)
41    DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_southwest'
42    (SUBPARTITION sw2, SUBPARTITION sw3)
43);
44INSERT INTO emp values(1,'Oracle','NUT',10);
45INSERT INTO emp values(2,'SAP','BOLT',40);
46INSERT INTO emp values(3,'IBM','NAIL',60);
47INSERT INTO emp values(4,'SUN','SCREW',90);
48SELECT * FROM emp;
49
50--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
51SHOW CREATE TABLE emp;
52--replace_regex  /#P#/#p#/  /#SP#/#sp#/
53SELECT name,n_cols,file_format,row_format
54       FROM information_schema.tokudb_sys_tables
55       WHERE name LIKE 'test%' ORDER BY name;
56--replace_regex  /#P#/#p#/  /#SP#/#sp#/
57SELECT name,file_format,row_format
58       FROM information_schema.tokudb_sys_tablespaces
59       WHERE name LIKE 'test%' ORDER BY name;
60--replace_regex  /#P#/#p#/  /#SP#/#sp#/
61--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
62SELECT path FROM information_schema.tokudb_sys_datafiles
63       WHERE path LIKE '%test%' ORDER BY space;
64--echo ---- MYSQLD_DATADIR/test
65--replace_regex  /#P#/#p#/  /#SP#/#sp#/
66--list_files $MYSQLD_DATADIR/test
67--echo ---- MYSQL_TMP_DIR/alt_dir_northeast/test
68--replace_regex  /#P#/#p#/  /#SP#/#sp#/
69--list_files $MYSQL_TMP_DIR/alt_dir_northeast/test
70--echo ---- MYSQL_TMP_DIR/alt_dir_southwest/test
71--replace_regex  /#P#/#p#/  /#SP#/#sp#/
72--list_files $MYSQL_TMP_DIR/alt_dir_southwest/test
73
74
75--echo #
76--echo # REORGANIZE the PARTITIONS and SUBPARTITIONS
77--echo #
78--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
79eval ALTER TABLE emp REORGANIZE PARTITION northeast INTO
80(
81  PARTITION east VALUES LESS THAN (25)
82    DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_east'
83    (SUBPARTITION e0, SUBPARTITION e1),
84  PARTITION north VALUES LESS THAN (50)
85    DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_north'
86    (SUBPARTITION n0, SUBPARTITION n1)
87);
88
89--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
90eval ALTER TABLE emp REORGANIZE PARTITION southwest INTO
91(
92  PARTITION west VALUES LESS THAN (75)
93    DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_west'
94    (SUBPARTITION w0, SUBPARTITION w1),
95  PARTITION south VALUES LESS THAN (100)
96    DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_south'
97    (SUBPARTITION s0, SUBPARTITION s1)
98);
99SELECT * FROM emp;
100--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
101SHOW CREATE TABLE emp;
102--replace_regex  /#P#/#p#/  /#SP#/#sp#/
103SELECT name,n_cols,file_format,row_format
104       FROM information_schema.tokudb_sys_tables
105       WHERE name LIKE 'test%' ORDER BY name;
106--replace_regex  /#P#/#p#/  /#SP#/#sp#/
107SELECT name,file_format,row_format
108       FROM information_schema.tokudb_sys_tablespaces
109       WHERE name LIKE 'test%' ORDER BY name;
110--replace_regex  /#P#/#p#/  /#SP#/#sp#/
111--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
112SELECT path FROM information_schema.tokudb_sys_datafiles
113       WHERE path LIKE '%test%' ORDER BY space;
114--echo ---- MYSQLD_DATADIR/test
115--replace_regex  /#P#/#p#/  /#SP#/#sp#/
116--list_files $MYSQLD_DATADIR/test
117--echo ---- MYSQL_TMP_DIR/alt_dir_northeast/test
118--replace_regex  /#P#/#p#/  /#SP#/#sp#/
119--list_files $MYSQL_TMP_DIR/alt_dir_northeast/test
120--echo ---- MYSQL_TMP_DIR/alt_dir_southwest/test
121--replace_regex  /#P#/#p#/  /#SP#/#sp#/
122--list_files $MYSQL_TMP_DIR/alt_dir_southwest/test
123--echo ---- MYSQL_TMP_DIR/alt_dir_east/test
124--replace_regex  /#P#/#p#/  /#SP#/#sp#/
125--list_files $MYSQL_TMP_DIR/alt_dir_east/test
126--echo ---- MYSQL_TMP_DIR/alt_dir_north/test
127--replace_regex  /#P#/#p#/  /#SP#/#sp#/
128--list_files $MYSQL_TMP_DIR/alt_dir_north/test
129--echo ---- MYSQL_TMP_DIR/alt_dir_west/test
130--replace_regex  /#P#/#p#/  /#SP#/#sp#/
131--list_files $MYSQL_TMP_DIR/alt_dir_west/test
132--echo ---- MYSQL_TMP_DIR/alt_dir_south/test
133--replace_regex  /#P#/#p#/  /#SP#/#sp#/
134--list_files $MYSQL_TMP_DIR/alt_dir_south/test
135
136DROP TABLE emp;
137
138--echo #
139--echo # Cleanup
140--echo #
141
142--rmdir $MYSQL_TMP_DIR/alt_dir_northeast/test
143--rmdir $MYSQL_TMP_DIR/alt_dir_northeast
144--rmdir $MYSQL_TMP_DIR/alt_dir_southwest/test
145--rmdir $MYSQL_TMP_DIR/alt_dir_southwest
146--rmdir $MYSQL_TMP_DIR/alt_dir_east/test
147--rmdir $MYSQL_TMP_DIR/alt_dir_east
148--rmdir $MYSQL_TMP_DIR/alt_dir_north/test
149--rmdir $MYSQL_TMP_DIR/alt_dir_north
150--rmdir $MYSQL_TMP_DIR/alt_dir_west/test
151--rmdir $MYSQL_TMP_DIR/alt_dir_west
152--rmdir $MYSQL_TMP_DIR/alt_dir_south/test
153--rmdir $MYSQL_TMP_DIR/alt_dir_south
154
155--disable_query_log
156EVAL SET GLOBAL tokudb_file_format=$tokudb_file_format_orig;
157EVAL SET GLOBAL tokudb_file_per_table=$tokudb_file_per_table_orig;
158EVAL SET SESSION tokudb_strict_mode=$tokudb_strict_mode_orig;
159--enable_query_log
160
161