Debian Codegraph @

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

raw
1 #
2 # A series of tests to show the correct behavior when using
3 # ALTER TABLE ... REORGANIZE PARTITION with InnoDB
4 # See also; parts.partition_basic_symlink_innodb.test for
5 # partition related tests with remote tablespaces.
6 # See innodb.innodb-restart for tablespace migration tests.
7 #
8 SET default_storage_engine=InnoDB;
9 LET $MYSQLD_DATADIR = `select @@datadir`;
10 LET $INNODB_PAGE_SIZE = `select @@innodb_page_size`;
11
12 # These values can change during the test
13 LET $innodb_file_per_table_orig=`select @@innodb_file_per_table`;
14 LET $innodb_strict_mode_orig=`select @@session.innodb_strict_mode`;
15
16 SET SESSION innodb_strict_mode = ON;
17 SET GLOBAL innodb_file_per_table=ON;
18
19 # Unlike MyISAM, InnoDB creates the subdirectories given to it in the
20 # DATA DIRECTORY clauses. Another difference is that InnoDB uses an extra
21 # directory under DATA DIRECTORY with the name of the database.
22 --echo #
23 --echo # CREATE a table with SUBPARTITIONS
24 --echo #
25 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
26 eval CREATE TABLE emp (
27 id INT NOT NULL,
28 store_name VARCHAR(30),
29 parts VARCHAR(30),
30 store_id INT
31 ) engine InnoDB
32 PARTITION BY RANGE(store_id) SUBPARTITION BY HASH(store_id)
33 (
34 PARTITION northeast VALUES LESS THAN (50)
35 DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_northeast'
36 (SUBPARTITION ne0, SUBPARTITION ne1),
37 PARTITION southwest VALUES LESS THAN (100)
38 DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_southwest'
39 (SUBPARTITION sw2, SUBPARTITION sw3)
40 );
41 INSERT INTO emp values(1,'Oracle','NUT',10);
42 INSERT INTO emp values(2,'SAP','BOLT',40);
43 INSERT INTO emp values(3,'IBM','NAIL',60);
44 INSERT INTO emp values(4,'SUN','SCREW',90);
45 SELECT * FROM emp;
46
47 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
48 SHOW CREATE TABLE emp;
49 --source suite/innodb/include/show_i_s_tables.inc
50 --source suite/innodb/include/show_i_s_tablespaces.inc
51 --echo ---- MYSQLD_DATADIR/test
52 --replace_regex /#P#/#p#/ /#SP#/#sp#/
53 --list_files $MYSQLD_DATADIR/test
54 --echo ---- MYSQL_TMP_DIR/alt_dir_northeast/test
55 --replace_regex /#P#/#p#/ /#SP#/#sp#/
56 --list_files $MYSQL_TMP_DIR/alt_dir_northeast/test
57 --echo ---- MYSQL_TMP_DIR/alt_dir_southwest/test
58 --replace_regex /#P#/#p#/ /#SP#/#sp#/
59 --list_files $MYSQL_TMP_DIR/alt_dir_southwest/test
60
61
62 --echo #
63 --echo # REORGANIZE the PARTITIONS and SUBPARTITIONS
64 --echo #
65 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
66 eval ALTER TABLE emp REORGANIZE PARTITION northeast INTO
67 (
68 PARTITION east VALUES LESS THAN (25)
69 DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_east'
70 (SUBPARTITION e0, SUBPARTITION e1),
71 PARTITION north VALUES LESS THAN (50)
72 DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_north'
73 (SUBPARTITION n0, SUBPARTITION n1)
74 );
75
76 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
77 eval ALTER TABLE emp REORGANIZE PARTITION southwest INTO
78 (
79 PARTITION west VALUES LESS THAN (75)
80 DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_west'
81 (SUBPARTITION w0, SUBPARTITION w1),
82 PARTITION south VALUES LESS THAN (100)
83 DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_south'
84 (SUBPARTITION s0, SUBPARTITION s1)
85 );
86 SELECT * FROM emp;
87 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
88 SHOW CREATE TABLE emp;
89 --source suite/innodb/include/show_i_s_tables.inc
90 --source suite/innodb/include/show_i_s_tablespaces.inc
91 --echo ---- MYSQLD_DATADIR/test
92 --replace_regex /#P#/#p#/ /#SP#/#sp#/
93 --list_files $MYSQLD_DATADIR/test
94 --echo ---- MYSQL_TMP_DIR/alt_dir_northeast/test
95 --replace_regex /#P#/#p#/ /#SP#/#sp#/
96 --list_files $MYSQL_TMP_DIR/alt_dir_northeast/test
97 --echo ---- MYSQL_TMP_DIR/alt_dir_southwest/test
98 --replace_regex /#P#/#p#/ /#SP#/#sp#/
99 --list_files $MYSQL_TMP_DIR/alt_dir_southwest/test
100 --echo ---- MYSQL_TMP_DIR/alt_dir_east/test
101 --replace_regex /#P#/#p#/ /#SP#/#sp#/
102 --list_files $MYSQL_TMP_DIR/alt_dir_east/test
103 --echo ---- MYSQL_TMP_DIR/alt_dir_north/test
104 --replace_regex /#P#/#p#/ /#SP#/#sp#/
105 --list_files $MYSQL_TMP_DIR/alt_dir_north/test
106 --echo ---- MYSQL_TMP_DIR/alt_dir_west/test
107 --replace_regex /#P#/#p#/ /#SP#/#sp#/
108 --list_files $MYSQL_TMP_DIR/alt_dir_west/test
109 --echo ---- MYSQL_TMP_DIR/alt_dir_south/test
110 --replace_regex /#P#/#p#/ /#SP#/#sp#/
111 --list_files $MYSQL_TMP_DIR/alt_dir_south/test
112
113 DROP TABLE emp;
114
115 --echo #
116 --echo # Cleanup
117 --echo #
118
119 --rmdir $MYSQL_TMP_DIR/alt_dir_northeast/test
120 --rmdir $MYSQL_TMP_DIR/alt_dir_northeast
121 --rmdir $MYSQL_TMP_DIR/alt_dir_southwest/test
122 --rmdir $MYSQL_TMP_DIR/alt_dir_southwest
123 --rmdir $MYSQL_TMP_DIR/alt_dir_east/test
124 --rmdir $MYSQL_TMP_DIR/alt_dir_east
125 --rmdir $MYSQL_TMP_DIR/alt_dir_north/test
126 --rmdir $MYSQL_TMP_DIR/alt_dir_north
127 --rmdir $MYSQL_TMP_DIR/alt_dir_west/test
128 --rmdir $MYSQL_TMP_DIR/alt_dir_west
129 --rmdir $MYSQL_TMP_DIR/alt_dir_south/test
130 --rmdir $MYSQL_TMP_DIR/alt_dir_south
131
132
133 # To test if reorganize partition into same partition/subpartition name
134 # would work correctly
135
136 # Scenario one, a partitioned table
137 CREATE TABLE t1 (a INT) ENGINE = InnoDB
138 PARTITION BY RANGE (a)
139 (partition p0 values less than MAXVALUE);
140
141 INSERT INTO t1 VALUES (1), (11), (21), (33);
142 SELECT * FROM t1;
143
144 SHOW CREATE TABLE t1;
145
146 --echo ---- MYSQLD_DATADIR/test
147 --replace_regex /#P#/#p#/ /#SP#/#sp#/
148 --list_files $MYSQLD_DATADIR/test
149
150 ALTER TABLE t1 REORGANIZE PARTITION p0 INTO
151 (PARTITION p0 VALUES LESS THAN (10),
152 PARTITION p10 VALUES LESS THAN MAXVALUE);
153
154 SHOW CREATE TABLE t1;
155
156 SELECT * FROM t1;
157
158 --echo ---- MYSQLD_DATADIR/test
159 --replace_regex /#P#/#p#/ /#SP#/#sp#/
160 --list_files $MYSQLD_DATADIR/test
161
162 DROP TABLE t1;
163
164 # Scenario two, a subpartitioned table
165 CREATE TABLE tsp (
166 a INT,
167 b VARCHAR(55),
168 PRIMARY KEY (a))
169 ENGINE = InnoDB
170 PARTITION BY RANGE (a)
171 SUBPARTITION BY HASH(a)
172 (PARTITION p0 VALUES LESS THAN (10)
173 (SUBPARTITION sp00,
174 SUBPARTITION sp01,
175 SUBPARTITION sp02,
176 SUBPARTITION sp03,
177 SUBPARTITION sp04),
178 PARTITION p1 VALUES LESS THAN (20)
179 (SUBPARTITION sp10,
180 SUBPARTITION sp11,
181 SUBPARTITION sp12,
182 SUBPARTITION sp13,
183 SUBPARTITION sp14),
184 PARTITION p2 VALUES LESS THAN (100)
185 (SUBPARTITION sp20,
186 SUBPARTITION sp21,
187 SUBPARTITION sp22,
188 SUBPARTITION sp23,
189 SUBPARTITION sp24));
190
191 INSERT INTO tsp VALUES(1, 'one'), (5, 'five'), (15, 'fifteen'), (30, 'thirty'), (40, 'forty'), (61, 'sixty one'), (75, 'seventy five'), (79, 'seventy nine'), (81, 'eighty one'), (83, 'eighty three'), (99, 'ninety nine');
192
193 SELECT * FROM tsp PARTITION(p2);
194
195 --echo ---- MYSQLD_DATADIR/test
196 --replace_regex /#P#/#p#/ /#SP#/#sp#/
197 --list_files $MYSQLD_DATADIR/test
198
199 ALTER TABLE tsp REORGANIZE PARTITION p2 INTO(
200 PARTITION p20 VALUES LESS THAN(60) (SUBPARTITION sp200, SUBPARTITION sp201, SUBPARTITION sp202, SUBPARTITION sp203, SUBPARTITION sp204),
201 PARTITION p2 VALUES LESS THAN(100) (SUBPARTITION sp20, SUBPARTITION sp211, SUBPARTITION sp22, SUBPARTITION sp213, SUBPARTITION sp24));
202
203 SHOW CREATE TABLE tsp;
204
205 --echo ---- MYSQLD_DATADIR/test
206 --replace_regex /#P#/#p#/ /#SP#/#sp#/
207 --list_files $MYSQLD_DATADIR/test
208
209 SELECT * FROM tsp PARTITION(p2);
210 SELECT * FROM tsp PARTITION(p20);
211
212 DROP TABLE tsp;
213
214 --disable_query_log
215 EVAL SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig;
216 EVAL SET SESSION innodb_strict_mode=$innodb_strict_mode_orig;
217 --enable_query_log
218