Debian Codegraph @

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

raw
1 # needed in sourced show_i_s_tablespaces.inc
2 let $MYSQLD_DATADIR = `select @@datadir`;
3 let $INNODB_PAGE_SIZE = `select @@innodb_page_size`;
4 SET @old_innodb_file_per_table=@@global.innodb_file_per_table;
5 SET GLOBAL innodb_file_per_table = OFF;
6
7 --echo #
8 --echo # Put all partitions of a table into a general tablespace.
9 --echo #
10 CREATE TABLESPACE s1 ADD DATAFILE 's1.ibd' ENGINE InnoDB;
11 CREATE TABLESPACE `s``1` ADD DATAFILE 's-1.ibd' ENGINE InnoDB;
12 CREATE TABLE t1 (a INT, b INT)
13 ENGINE = InnoDB
14 TABLESPACE=`s1`
15 PARTITION BY RANGE(a) SUBPARTITION BY KEY(b) (
16 PARTITION tens VALUES LESS THAN (100) TABLESPACE=`innodb_file_per_table`
17 (SUBPARTITION subpart11,
18 SUBPARTITION subpart12),
19 PARTITION hundreds VALUES LESS THAN (1000)
20 (SUBPARTITION subpart21,
21 SUBPARTITION subpart22 TABLESPACE=`innodb_file_per_table`),
22 PARTITION onethousands VALUES LESS THAN (2000)
23 TABLESPACE=`innodb_file_per_table`
24 (SUBPARTITION subpart23 TABLESPACE = `s1`,
25 SUBPARTITION subpart24),
26 PARTITION thousands VALUES LESS THAN (10000)
27 (SUBPARTITION subpart31,
28 SUBPARTITION subpart32));
29
30 SET GLOBAL innodb_file_per_table = ON;
31
32 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
33 eval CREATE TABLE t2 (a INT, b INT)
34 ENGINE = InnoDB
35 PARTITION BY RANGE(a) SUBPARTITION BY KEY(b) (
36 PARTITION tens VALUES LESS THAN (100) TABLESPACE=`s1`
37 (SUBPARTITION subpart11,
38 SUBPARTITION subpart12),
39 PARTITION hundreds VALUES LESS THAN (1000)
40 (SUBPARTITION subpart21,
41 SUBPARTITION subpart22 TABLESPACE=`s``1`),
42 PARTITION thousands VALUES LESS THAN (10000)
43 (SUBPARTITION subpart31 DATA DIRECTORY '$MYSQL_TMP_DIR',
44 SUBPARTITION subpart32 DATA DIRECTORY '$MYSQL_TMP_DIR' TABLESPACE `innodb_file_per_table`));
45
46 ALTER TABLE t1 ALGORITHM=COPY, ADD PARTITION
47 (PARTITION tenthousands VALUES LESS THAN (20000));
48
49 --echo # Only allow tablespace name as ident, not text. I.e. no 'single' quotes.
50 --error ER_PARSE_ERROR
51 ALTER TABLE t1 ALGORITHM=COPY, ADD PARTITION
52 (PARTITION twentythousands VALUES LESS THAN (30000)
53 TABLESPACE = 'innodb_system');
54 --error ER_PARSE_ERROR
55 ALTER TABLE t1 ALGORITHM=COPY, ADD PARTITION
56 (PARTITION twentythousands VALUES LESS THAN (30000)
57 TABLESPACE = "innodb_system");
58 SET @old_sql_mode = @@sql_mode;
59 SET @@sql_mode = 'ANSI_QUOTES';
60 --error ER_PARSE_ERROR
61 ALTER TABLE t1 ALGORITHM=COPY, ADD PARTITION
62 (PARTITION twentythousands VALUES LESS THAN (30000)
63 TABLESPACE = 'innodb_system');
64
65 ALTER TABLE t1 ALGORITHM=DEFAULT, ADD PARTITION
66 (PARTITION twentythousands VALUES LESS THAN (30000)
67 TABLESPACE = "innodb_system");
68
69 ALTER TABLE t1 ALGORITHM=DEFAULT, ADD PARTITION
70 (PARTITION thirtythousands VALUES LESS THAN (40000)
71 TABLESPACE = `innodb_file_per_table`);
72 SET @@sql_mode = @old_sql_mode;
73
74 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
75 --error ER_ILLEGAL_HA_CREATE_OPTION
76 eval ALTER TABLE t1 ALGORITHM=DEFAULT, ADD PARTITION
77 (PARTITION fortythousands VALUES LESS THAN (50000)
78 (SUBPARTITION p40k_1 TABLESPACE = innodb_system DATA DIRECTORY = '$MYSQL_TMP_DIR',
79 SUBPARTITION p40k_2 DATA DIRECTORY = '$MYSQL_TMP_DIR'));
80
81 --echo # Verify that it is OK to have TABLESPACE innodb_system with DATA DIR also on table
82 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
83 --error ER_ILLEGAL_HA_CREATE_OPTION
84 eval CREATE TABLE t3 (a int)
85 ENGINE = InnoDB
86 TABLESPACE = innodb_system DATA DIRECTORY = '$MYSQL_TMP_DIR';
87
88 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
89 eval CREATE TABLE t3 (a int)
90 ENGINE = InnoDB
91 TABLESPACE = innodb_file_per_table DATA DIRECTORY = '$MYSQL_TMP_DIR';
92 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
93 SHOW CREATE TABLE t3;
94 DROP TABLE t3;
95 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
96 eval CREATE TABLE t3 (a int) ENGINE = InnoDB DATA DIRECTORY = '$MYSQL_TMP_DIR';
97 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
98 SHOW CREATE TABLE t3;
99 DROP TABLE t3;
100
101 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
102 eval ALTER TABLE t1 ALGORITHM=DEFAULT, ADD PARTITION
103 (PARTITION fortythousands VALUES LESS THAN (50000)
104 (SUBPARTITION p40k_1 DATA DIRECTORY = '$MYSQL_TMP_DIR',
105 SUBPARTITION p40k_2 TABLESPACE = innodb_file_per_table DATA DIRECTORY = '$MYSQL_TMP_DIR'));
106 SET GLOBAL innodb_file_per_table = OFF;
107
108 ALTER TABLE t1 ALGORITHM=DEFAULT, ADD PARTITION
109 (PARTITION fiftythousands VALUES LESS THAN (60000));
110
111 ALTER TABLE t1 ALGORITHM=DEFAULT, ADD PARTITION
112 (PARTITION sixtythousands VALUES LESS THAN (70000)
113 TABLESPACE = `innodb_system`);
114
115 ALTER TABLE t1 ALGORITHM=DEFAULT, ADD PARTITION
116 (PARTITION seventythousands VALUES LESS THAN (80000)
117 TABLESPACE = `innodb_file_per_table`);
118
119 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
120 eval ALTER TABLE t1 ALGORITHM=DEFAULT, ADD PARTITION
121 (PARTITION eightythousands VALUES LESS THAN (90000)
122 (SUBPARTITION p80k_1 TABLESPACE = `innodb_file_per_table` DATA DIRECTORY = '$MYSQL_TMP_DIR',
123 SUBPARTITION p80k_2 TABLESPACE = `innodb_file_per_table` DATA DIRECTORY = '$MYSQL_TMP_DIR'));
124
125 --echo # Test if both TABLESPACE and DATA DIRECTORY is allowed on table level.
126 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
127 eval CREATE TABLE t3 (a int)
128 ENGINE = InnoDB
129 TABLESPACE = innodb_file_per_table DATA DIRECTORY = '$MYSQL_TMP_DIR';
130
131 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
132 --error ER_ILLEGAL_HA_CREATE_OPTION
133 eval CREATE TABLE t4 (a int)
134 ENGINE = InnoDB
135 TABLESPACE = innodb_system DATA DIRECTORY = '$MYSQL_TMP_DIR';
136
137 ALTER TABLE t1 ALGORITHM=DEFAULT, ADD PARTITION
138 (PARTITION ninetythousands VALUES LESS THAN (100000)
139 (SUBPARTITION p90k_1 TABLESPACE = `innodb_file_per_table`,
140 SUBPARTITION p90k_2 TABLESPACE = `innodb_system`));
141
142
143 FLUSH TABLES;
144 --source suite/innodb/include/show_i_s_tables.inc
145 --source suite/innodb/include/show_i_s_tablespaces.inc
146
147 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
148 SHOW CREATE TABLE t1;
149 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
150 SHOW CREATE TABLE t2;
151
152 SET @@sql_mode = 'ANSI_QUOTES';
153 SET GLOBAL innodb_file_per_table = ON;
154
155 FLUSH TABLES;
156 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
157 SHOW CREATE TABLE t1;
158 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
159 SHOW CREATE TABLE t2;
160
161 --echo # DISCARD will only work on the first partitions that use file_per_table
162 --error ER_ALTER_OPERATION_NOT_SUPPORTED
163 ALTER TABLE t1 ALGORITHM=INPLACE, DISCARD TABLESPACE;
164 --replace_result #P# #p# #SP# #sp#
165 --error ER_NOT_ALLOWED_COMMAND
166 ALTER TABLE t1 ALGORITHM=DEFAULT, DISCARD TABLESPACE;
167 SET @@sql_mode = @old_sql_mode;
168 --replace_result #P# #p# #SP# #sp#
169 --error ER_NOT_ALLOWED_COMMAND
170 ALTER TABLE t2 DISCARD TABLESPACE;
171 call mtr.add_suppression("Operating system error number .* in a file operation.");
172 call mtr.add_suppression("The error means the system cannot find the path specified.");
173 call mtr.add_suppression("Cannot open datafile for read-only: .*");
174 DROP TABLE t1;
175 DROP TABLE t2;
176
177 # From SHOW CREATE TABLE above
178 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
179 eval CREATE TABLE `t1` (
180 `a` int(11) DEFAULT NULL,
181 `b` int(11) DEFAULT NULL
182 ) /*!50100 TABLESPACE `s1` */ ENGINE=InnoDB DEFAULT CHARSET=latin1
183 /*!50100 PARTITION BY RANGE (a)
184 SUBPARTITION BY KEY (b)
185 (PARTITION tens VALUES LESS THAN (100)
186 (SUBPARTITION subpart11 ENGINE = InnoDB,
187 SUBPARTITION subpart12 ENGINE = InnoDB),
188 PARTITION hundreds VALUES LESS THAN (1000)
189 (SUBPARTITION subpart21 TABLESPACE = s1 ENGINE = InnoDB,
190 SUBPARTITION subpart22 ENGINE = InnoDB),
191 PARTITION onethousands VALUES LESS THAN (2000)
192 (SUBPARTITION subpart23 TABLESPACE = s1 ENGINE = InnoDB,
193 SUBPARTITION subpart24 ENGINE = InnoDB),
194 PARTITION thousands VALUES LESS THAN (10000)
195 (SUBPARTITION subpart31 TABLESPACE = s1 ENGINE = InnoDB,
196 SUBPARTITION subpart32 TABLESPACE = s1 ENGINE = InnoDB),
197 PARTITION tenthousands VALUES LESS THAN (20000)
198 (SUBPARTITION tenthousandssp0 TABLESPACE = s1 ENGINE = InnoDB,
199 SUBPARTITION tenthousandssp1 TABLESPACE = s1 ENGINE = InnoDB),
200 PARTITION twentythousands VALUES LESS THAN (30000)
201 (SUBPARTITION twentythousandssp0 TABLESPACE = innodb_system ENGINE = InnoDB,
202 SUBPARTITION twentythousandssp1 TABLESPACE = innodb_system ENGINE = InnoDB),
203 PARTITION thirtythousands VALUES LESS THAN (40000)
204 (SUBPARTITION thirtythousandssp0 ENGINE = InnoDB,
205 SUBPARTITION thirtythousandssp1 ENGINE = InnoDB),
206 PARTITION fortythousands VALUES LESS THAN (50000)
207 (SUBPARTITION p40k_1 TABLESPACE = innodb_system ENGINE = InnoDB,
208 SUBPARTITION p40k_2 DATA DIRECTORY = '$MYSQL_TMP_DIR' ENGINE = InnoDB),
209 PARTITION fiftythousands VALUES LESS THAN (60000)
210 (SUBPARTITION fiftythousandssp0 TABLESPACE = s1 ENGINE = InnoDB,
211 SUBPARTITION fiftythousandssp1 TABLESPACE = s1 ENGINE = InnoDB),
212 PARTITION sixtythousands VALUES LESS THAN (70000)
213 (SUBPARTITION sixtythousandssp0 TABLESPACE = innodb_system ENGINE = InnoDB,
214 SUBPARTITION sixtythousandssp1 TABLESPACE = innodb_system ENGINE = InnoDB),
215 PARTITION seventythousands VALUES LESS THAN (80000)
216 (SUBPARTITION seventythousandssp0 ENGINE = InnoDB,
217 SUBPARTITION seventythousandssp1 ENGINE = InnoDB),
218 PARTITION ninetythousands VALUES LESS THAN (100000)
219 (SUBPARTITION p90k_1 ENGINE = InnoDB,
220 SUBPARTITION p90k_2 TABLESPACE = innodb_system ENGINE = InnoDB)) */;
221 CREATE TABLE `t2` (
222 `a` int(11) DEFAULT NULL,
223 `b` int(11) DEFAULT NULL
224 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
225 /*!50100 PARTITION BY RANGE (a)
226 SUBPARTITION BY KEY (b)
227 (PARTITION tens VALUES LESS THAN (100)
228 (SUBPARTITION subpart11 TABLESPACE = s1 ENGINE = InnoDB,
229 SUBPARTITION subpart12 TABLESPACE = s1 ENGINE = InnoDB),
230 PARTITION hundreds VALUES LESS THAN (1000)
231 (SUBPARTITION subpart21 ENGINE = InnoDB,
232 SUBPARTITION subpart22 TABLESPACE = `s``1` ENGINE = InnoDB),
233 PARTITION thousands VALUES LESS THAN (10000)
234 (SUBPARTITION subpart31 ENGINE = InnoDB,
235 SUBPARTITION subpart32 ENGINE = InnoDB)) */;
236
237 --source suite/innodb/include/show_i_s_tables.inc
238 --source suite/innodb/include/show_i_s_tablespaces.inc
239
240 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
241 SHOW CREATE TABLE t1;
242 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
243 SHOW CREATE TABLE t2;
244 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
245 SHOW CREATE TABLE t3;
246
247 ALTER TABLE t3 DISCARD TABLESPACE;
248 DROP TABLE t1, t2, t3;
249 DROP TABLESPACE s1;
250 DROP TABLESPACE `s``1`;
251
252 CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
253 CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=COMPACT;
254 CREATE TABLE ti
255 (id INT, amount DECIMAL(7,2), tr_date DATE) TABLESPACE ts1
256 ENGINE=INNODB
257 PARTITION BY HASH( MONTH(tr_date) )
258 PARTITIONS 6;
259
260 DROP TABLE t1,ti;
261 DROP TABLESPACE ts1;
262
263 --echo #
264 --echo # Clean-up.
265 --echo #
266
267 SET @@global.innodb_file_per_table=@old_innodb_file_per_table;
268
269 --rmdir $MYSQL_TMP_DIR/test