Debian Codegraph @

mysql-test/suite/innodb/r/innodb_autoextend_table_ddl.result

raw
1 #
2 # Tests for CREATE/ALTER TABLE with AUTOEXTEND_SIZE clause
3 #
4 # Test error situations for CREATE TABLE
5 CREATE TABLE mytable(c1 INT) AUTOEXTEND_SIZE 48k;
6 ERROR HY000: AUTOEXTEND_SIZE value should be between 4M and 4096M
7 CREATE TABLE mytable(c1 INT) AUTOEXTEND_SIZE 1M;
8 ERROR HY000: AUTOEXTEND_SIZE value should be between 4M and 4096M
9 CREATE TABLE mytable(c1 INT) AUTOEXTEND_SIZE 5G;
10 ERROR HY000: AUTOEXTEND_SIZE value should be between 4M and 4096M
11 CREATE TABLE mytable(c1 INT) AUTOEXTEND_SIZE 5M;
12 ERROR HY000: AUTOEXTEND_SIZE should be a multiple of 4M
13 CREATE TABLE mytable(c1 INT) AUTOEXTEND_SIZE=1.5M;
14 ERROR 42000: Only integers allowed as number here near '1.5M' at line 1
15 # Create table with autoextend_size > 0
16 CREATE TABLE mytable(c1 INT) AUTOEXTEND_SIZE 4M;
17 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
18 WHERE NAME LIKE '%mytable%';
19 NAME FILE_SIZE AUTOEXTEND_SIZE
20 test/mytable 4194304 4194304
21 SHOW CREATE TABLE mytable;
22 Table Create Table
23 mytable CREATE TABLE `mytable` (
24 `c1` int DEFAULT NULL
25 ) /*!80023 AUTOEXTEND_SIZE=4194304 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
26 ALTER TABLE mytable AUTOEXTEND_SIZE 0;
27 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
28 WHERE NAME LIKE '%mytable%';
29 NAME FILE_SIZE AUTOEXTEND_SIZE
30 test/mytable 4194304 0
31 SHOW CREATE TABLE mytable;
32 Table Create Table
33 mytable CREATE TABLE `mytable` (
34 `c1` int DEFAULT NULL
35 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
36 ALTER TABLE mytable AUTOEXTEND_SIZE 48k;
37 ERROR HY000: AUTOEXTEND_SIZE value should be between 4M and 4096M
38 ALTER TABLE mytable AUTOEXTEND_SIZE 1M;
39 ERROR HY000: AUTOEXTEND_SIZE value should be between 4M and 4096M
40 ALTER TABLE mytable AUTOEXTEND_SIZE 5G;
41 ERROR HY000: AUTOEXTEND_SIZE value should be between 4M and 4096M
42 ALTER TABLE mytable AUTOEXTEND_SIZE 5M;
43 ERROR HY000: AUTOEXTEND_SIZE should be a multiple of 4M
44 DROP TABLE mytable;
45 # Create table with autoextend_size = 0
46 CREATE TABLE mytable(c1 INT) AUTOEXTEND_SIZE=0;
47 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
48 WHERE NAME LIKE '%mytable%';
49 NAME FILE_SIZE AUTOEXTEND_SIZE
50 test/mytable 114688 0
51 SHOW CREATE TABLE mytable;
52 Table Create Table
53 mytable CREATE TABLE `mytable` (
54 `c1` int DEFAULT NULL
55 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
56 DROP TABLE mytable;
57 # Test CREATE/ALTER TABLE with AUTOEXTEND_SIZE value specified as
58 # absolute number of bytes without K, M or G qualifiers
59 CREATE TABLE mytable (c1 INT) AUTOEXTEND_SIZE 4194304;
60 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
61 WHERE NAME LIKE '%mytable%';
62 NAME FILE_SIZE AUTOEXTEND_SIZE
63 test/mytable 4194304 4194304
64 ALTER TABLE mytable AUTOEXTEND_SIZE 8388608;
65 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
66 WHERE NAME LIKE '%mytable%';
67 NAME FILE_SIZE AUTOEXTEND_SIZE
68 test/mytable 4194304 8388608
69 DROP TABLE mytable;
70 # Test CREATE TABLE LIKE statement for non-partitioned tables
71 CREATE TABLE mytable (c1 INT) AUTOEXTEND_SIZE 4M;
72 SHOW CREATE TABLE mytable;
73 Table Create Table
74 mytable CREATE TABLE `mytable` (
75 `c1` int DEFAULT NULL
76 ) /*!80023 AUTOEXTEND_SIZE=4194304 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
77 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
78 WHERE NAME LIKE '%mytable%';
79 NAME FILE_SIZE AUTOEXTEND_SIZE
80 test/mytable 4194304 4194304
81 CREATE TABLE mytable_copy LIKE mytable;
82 SHOW CREATE TABLE mytable_copy;
83 Table Create Table
84 mytable_copy CREATE TABLE `mytable_copy` (
85 `c1` int DEFAULT NULL
86 ) /*!80023 AUTOEXTEND_SIZE=4194304 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
87 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
88 WHERE NAME LIKE '%mytable_copy%';
89 NAME FILE_SIZE AUTOEXTEND_SIZE
90 test/mytable_copy 4194304 4194304
91 DROP TABLE mytable_copy;
92 ALTER TABLE mytable AUTOEXTEND_SIZE 0;
93 SHOW CREATE TABLE mytable;
94 Table Create Table
95 mytable CREATE TABLE `mytable` (
96 `c1` int DEFAULT NULL
97 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
98 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
99 WHERE NAME LIKE '%mytable%';
100 NAME FILE_SIZE AUTOEXTEND_SIZE
101 test/mytable 4194304 0
102 CREATE TABLE mytable_copy LIKE mytable;
103 SHOW CREATE TABLE mytable_copy;
104 Table Create Table
105 mytable_copy CREATE TABLE `mytable_copy` (
106 `c1` int DEFAULT NULL
107 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
108 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
109 WHERE NAME LIKE '%mytable_copy%';
110 NAME FILE_SIZE AUTOEXTEND_SIZE
111 test/mytable_copy 114688 0
112 DROP TABLE mytable_copy;
113 ALTER TABLE mytable AUTOEXTEND_SIZE 64M;
114 SHOW CREATE TABLE mytable;
115 Table Create Table
116 mytable CREATE TABLE `mytable` (
117 `c1` int DEFAULT NULL
118 ) /*!80023 AUTOEXTEND_SIZE=67108864 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
119 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
120 WHERE NAME LIKE '%mytable%';
121 NAME FILE_SIZE AUTOEXTEND_SIZE
122 test/mytable 4194304 67108864
123 CREATE TABLE mytable_copy LIKE mytable;
124 SHOW CREATE TABLE mytable_copy;
125 Table Create Table
126 mytable_copy CREATE TABLE `mytable_copy` (
127 `c1` int DEFAULT NULL
128 ) /*!80023 AUTOEXTEND_SIZE=67108864 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
129 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
130 WHERE NAME LIKE '%mytable_copy%';
131 NAME FILE_SIZE AUTOEXTEND_SIZE
132 test/mytable_copy 67108864 67108864
133 DROP TABLE mytable_copy;
134 DROP TABLE mytable;
135 # Validate the initial size of the table when created without the AUTOEXTEND_SIZE clause
136 CREATE TABLE mytable (c1 INT);
137 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
138 WHERE NAME LIKE '%mytable%';
139 NAME FILE_SIZE AUTOEXTEND_SIZE
140 test/mytable 114688 0
141 SHOW CREATE TABLE mytable;
142 Table Create Table
143 mytable CREATE TABLE `mytable` (
144 `c1` int DEFAULT NULL
145 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
146 # Verify the autoextend_size attribute is updated in the data dictionary
147 ALTER TABLE mytable AUTOEXTEND_SIZE=4M;
148 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
149 WHERE NAME LIKE '%mytable%';
150 NAME FILE_SIZE AUTOEXTEND_SIZE
151 test/mytable 114688 4194304
152 # Validate SHOW CREATE TABLE displays autoextend_size clause
153 SHOW CREATE TABLE mytable;
154 Table Create Table
155 mytable CREATE TABLE `mytable` (
156 `c1` int DEFAULT NULL
157 ) /*!80023 AUTOEXTEND_SIZE=4194304 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
158 # Validate that the AUTOEXTEND_SIZE attribute is persisted properly and seen beyond server restart
159 # restart
160 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
161 WHERE NAME LIKE '%mytable%';
162 NAME FILE_SIZE AUTOEXTEND_SIZE
163 test/mytable 114688 4194304
164 DROP TABLE mytable;
165 # Test that the initial size of the table is same as AUTOEXTEND_SIZE, if the clause if mentioned
166 CREATE TABLE mytable(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 8M;
167 INSERT INTO mytable VALUES (1, 'aaaaaa');
168 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
169 WHERE NAME LIKE '%mytable%';
170 NAME FILE_SIZE AUTOEXTEND_SIZE
171 test/mytable 8388608 8388608
172 # Test ALTER TABLE statement
173 ALTER TABLE mytable ADD COLUMN c3 INT, AUTOEXTEND_SIZE 4M;
174 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
175 WHERE NAME LIKE '%mytable%';
176 NAME FILE_SIZE AUTOEXTEND_SIZE
177 test/mytable 4194304 4194304
178 SELECT * FROM mytable;
179 c1 c2 c3
180 1 aaaaaa NULL
181 ALTER TABLE mytable DROP COLUMN c3, AUTOEXTEND_SIZE=0;
182 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
183 WHERE NAME LIKE '%mytable%';
184 NAME FILE_SIZE AUTOEXTEND_SIZE
185 test/mytable 114688 0
186 SHOW CREATE TABLE mytable;
187 Table Create Table
188 mytable CREATE TABLE `mytable` (
189 `c1` int DEFAULT NULL,
190 `c2` text
191 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
192 SELECT * FROM mytable;
193 c1 c2
194 1 aaaaaa
195 DROP TABLE mytable;
196 # Test that AUTOEXTEND_SIZE does not change after ALTER TABLE ... ALGORITHM COPY
197 CREATE TABLE t1 (c1 INT , c2 varchar(100)) AUTOEXTEND_SIZE =4m;
198 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
199 WHERE NAME LIKE '%t1%';
200 NAME FILE_SIZE AUTOEXTEND_SIZE
201 test/t1 4194304 4194304
202 ALTER TABLE t1 ADD INDEX idx (c2), ALGORITHM=copy;
203 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
204 WHERE NAME LIKE '%t1%';
205 NAME FILE_SIZE AUTOEXTEND_SIZE
206 test/t1 4194304 4194304
207 DROP TABLE t1;
208 # Tests for partitioned tables. The autoextend_size should be updated
209 # for each partitioned tablespace
210 # Create a non-partitioned table and add partitions to it and verify that the
211 # autoextend_size attribute is applied to all the partitions
212 CREATE TABLE tpart(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M;
213 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
214 WHERE NAME LIKE '%tpart%';
215 NAME FILE_SIZE AUTOEXTEND_SIZE
216 test/tpart 4194304 4194304
217 ALTER TABLE tpart PARTITION BY RANGE (c1) (PARTITION p0 VALUES LESS THAN (50),
218 PARTITION p1 VALUES LESS THAN (100));
219 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
220 WHERE NAME LIKE '%tpart%';
221 NAME FILE_SIZE AUTOEXTEND_SIZE
222 test/tpart#p#p0 4194304 4194304
223 test/tpart#p#p1 4194304 4194304
224 SHOW CREATE TABLE tpart;
225 Table Create Table
226 tpart CREATE TABLE `tpart` (
227 `c1` int DEFAULT NULL,
228 `c2` text
229 ) /*!80023 AUTOEXTEND_SIZE=4194304 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
230 /*!50100 PARTITION BY RANGE (`c1`)
231 (PARTITION p0 VALUES LESS THAN (50) ENGINE = InnoDB,
232 PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB) */
233 ALTER TABLE tpart ADD PARTITION (PARTITION p2 VALUES LESS THAN (200));
234 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
235 WHERE NAME LIKE '%tpart%';
236 NAME FILE_SIZE AUTOEXTEND_SIZE
237 test/tpart#p#p0 4194304 4194304
238 test/tpart#p#p1 4194304 4194304
239 test/tpart#p#p2 4194304 4194304
240 ALTER TABLE tpart DROP PARTITION p1;
241 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
242 WHERE NAME LIKE '%tpart%';
243 NAME FILE_SIZE AUTOEXTEND_SIZE
244 test/tpart#p#p0 4194304 4194304
245 test/tpart#p#p2 4194304 4194304
246 DROP TABLE tpart;
247 # Create a non-partitioned table and alter it to add partitions while also modifying
248 # the autoextend_size
249 CREATE TABLE tpart(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M;
250 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
251 WHERE NAME LIKE '%tpart%';
252 NAME FILE_SIZE AUTOEXTEND_SIZE
253 test/tpart 4194304 4194304
254 ALTER TABLE tpart AUTOEXTEND_SIZE 1M PARTITION BY RANGE (c1) (PARTITION p0 VALUES LESS THAN (50),
255 PARTITION p1 VALUES LESS THAN (100));
256 ERROR HY000: AUTOEXTEND_SIZE value should be between 4M and 4096M
257 ALTER TABLE tpart AUTOEXTEND_SIZE 8M PARTITION BY RANGE (c1) (PARTITION p0 VALUES LESS THAN (50),
258 PARTITION p1 VALUES LESS THAN (100));
259 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
260 WHERE NAME LIKE '%tpart%';
261 NAME FILE_SIZE AUTOEXTEND_SIZE
262 test/tpart#p#p0 8388608 8388608
263 test/tpart#p#p1 8388608 8388608
264 SHOW CREATE TABLE tpart;
265 Table Create Table
266 tpart CREATE TABLE `tpart` (
267 `c1` int DEFAULT NULL,
268 `c2` text
269 ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
270 /*!50100 PARTITION BY RANGE (`c1`)
271 (PARTITION p0 VALUES LESS THAN (50) ENGINE = InnoDB,
272 PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB) */
273 ALTER TABLE tpart ADD PARTITION (PARTITION p2 VALUES LESS THAN (200));
274 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
275 WHERE NAME LIKE '%tpart%';
276 NAME FILE_SIZE AUTOEXTEND_SIZE
277 test/tpart#p#p0 8388608 8388608
278 test/tpart#p#p1 8388608 8388608
279 test/tpart#p#p2 8388608 8388608
280 DROP TABLE tpart;
281 # Create a partitioned table and verify autoextend_size value for the partitions
282 CREATE TABLE tpart(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 1M PARTITION BY RANGE (c1) (
283 PARTITION p0 VALUES LESS THAN (50),
284 PARTITION p1 VALUES LESS THAN (100));
285 ERROR HY000: AUTOEXTEND_SIZE value should be between 4M and 4096M
286 CREATE TABLE tpart(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M PARTITION BY RANGE (c1) (
287 PARTITION p0 VALUES LESS THAN (50),
288 PARTITION p1 VALUES LESS THAN (100));
289 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
290 WHERE NAME LIKE '%tpart%';
291 NAME FILE_SIZE AUTOEXTEND_SIZE
292 test/tpart#p#p0 4194304 4194304
293 test/tpart#p#p1 4194304 4194304
294 SHOW CREATE TABLE tpart;
295 Table Create Table
296 tpart CREATE TABLE `tpart` (
297 `c1` int DEFAULT NULL,
298 `c2` text
299 ) /*!80023 AUTOEXTEND_SIZE=4194304 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
300 /*!50100 PARTITION BY RANGE (`c1`)
301 (PARTITION p0 VALUES LESS THAN (50) ENGINE = InnoDB,
302 PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB) */
303 DROP TABLE tpart;
304 CREATE TABLE tpart(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M PARTITION BY RANGE (c1) (
305 PARTITION p0 VALUES LESS THAN (50),
306 PARTITION p1 VALUES LESS THAN (100));
307 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
308 WHERE NAME LIKE '%tpart%';
309 NAME FILE_SIZE AUTOEXTEND_SIZE
310 test/tpart#p#p0 4194304 4194304
311 test/tpart#p#p1 4194304 4194304
312 SHOW CREATE TABLE tpart;
313 Table Create Table
314 tpart CREATE TABLE `tpart` (
315 `c1` int DEFAULT NULL,
316 `c2` text
317 ) /*!80023 AUTOEXTEND_SIZE=4194304 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
318 /*!50100 PARTITION BY RANGE (`c1`)
319 (PARTITION p0 VALUES LESS THAN (50) ENGINE = InnoDB,
320 PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB) */
321 CREATE TABLE tpart_copy LIKE tpart;
322 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
323 WHERE NAME LIKE '%tpart_copy%';
324 NAME FILE_SIZE AUTOEXTEND_SIZE
325 test/tpart_copy#p#p0 4194304 4194304
326 test/tpart_copy#p#p1 4194304 4194304
327 SHOW CREATE TABLE tpart_copy;
328 Table Create Table
329 tpart_copy CREATE TABLE `tpart_copy` (
330 `c1` int DEFAULT NULL,
331 `c2` text
332 ) /*!80023 AUTOEXTEND_SIZE=4194304 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
333 /*!50100 PARTITION BY RANGE (`c1`)
334 (PARTITION p0 VALUES LESS THAN (50) ENGINE = InnoDB,
335 PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB) */
336 DROP TABLE tpart_copy;
337 # Test ALTER TABLE ... AUTOEXTEND_SIZE changes on partitioned tableѕ
338 ALTER TABLE tpart AUTOEXTEND_SIZE 48k;
339 ERROR HY000: AUTOEXTEND_SIZE value should be between 4M and 4096M
340 ALTER TABLE tpart AUTOEXTEND_SIZE 1M;
341 ERROR HY000: AUTOEXTEND_SIZE value should be between 4M and 4096M
342 ALTER TABLE tpart AUTOEXTEND_SIZE 5G;
343 ERROR HY000: AUTOEXTEND_SIZE value should be between 4M and 4096M
344 ALTER TABLE tpart AUTOEXTEND_SIZE 5M;
345 ERROR HY000: AUTOEXTEND_SIZE should be a multiple of 4M
346 ALTER TABLE tpart AUTOEXTEND_SIZE 8M;
347 SHOW CREATE TABLE tpart;
348 Table Create Table
349 tpart CREATE TABLE `tpart` (
350 `c1` int DEFAULT NULL,
351 `c2` text
352 ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
353 /*!50100 PARTITION BY RANGE (`c1`)
354 (PARTITION p0 VALUES LESS THAN (50) ENGINE = InnoDB,
355 PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB) */
356 DROP TABLE tpart;
357 # Test ALTER TABLE ... REMOVE PARTITIONING and ALTER TABLE ... EXCHANGE PARTITION
358 # for partitioned table
359 CREATE TABLE tpart (
360 id INT NOT NULL,
361 fname VARCHAR(30),
362 lname VARCHAR(30)
363 ) AUTOEXTEND_SIZE = 8M
364 PARTITION BY RANGE (id) (
365 PARTITION p0 VALUES LESS THAN (50),
366 PARTITION p1 VALUES LESS THAN (100),
367 PARTITION p2 VALUES LESS THAN (150),
368 PARTITION p3 VALUES LESS THAN (MAXVALUE));
369 SHOW CREATE TABLE tpart;
370 Table Create Table
371 tpart CREATE TABLE `tpart` (
372 `id` int NOT NULL,
373 `fname` varchar(30) DEFAULT NULL,
374 `lname` varchar(30) DEFAULT NULL
375 ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
376 /*!50100 PARTITION BY RANGE (`id`)
377 (PARTITION p0 VALUES LESS THAN (50) ENGINE = InnoDB,
378 PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,
379 PARTITION p2 VALUES LESS THAN (150) ENGINE = InnoDB,
380 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
381 INSERT INTO tpart VALUES (1669, "Jim", "Smith"), (337, "Mary", "Jones"), (16, "Frank", "White"), (2005, "Linda", "Black");
382 SELECT COUNT(*) FROM tpart;
383 COUNT(*)
384 4
385 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
386 WHERE NAME LIKE '%tpart%';
387 NAME FILE_SIZE AUTOEXTEND_SIZE
388 test/tpart#p#p0 8388608 8388608
389 test/tpart#p#p1 8388608 8388608
390 test/tpart#p#p2 8388608 8388608
391 test/tpart#p#p3 8388608 8388608
392 CREATE TABLE tcopy_part LIKE tpart;
393 SHOW CREATE TABLE tcopy_part;
394 Table Create Table
395 tcopy_part CREATE TABLE `tcopy_part` (
396 `id` int NOT NULL,
397 `fname` varchar(30) DEFAULT NULL,
398 `lname` varchar(30) DEFAULT NULL
399 ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
400 /*!50100 PARTITION BY RANGE (`id`)
401 (PARTITION p0 VALUES LESS THAN (50) ENGINE = InnoDB,
402 PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,
403 PARTITION p2 VALUES LESS THAN (150) ENGINE = InnoDB,
404 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
405 # Remove partitioning and validate the value of AUTOEXTEND_SIZE
406 ALTER TABLE tcopy_part REMOVE PARTITIONING;
407 SHOW CREATE TABLE tcopy_part;
408 Table Create Table
409 tcopy_part CREATE TABLE `tcopy_part` (
410 `id` int NOT NULL,
411 `fname` varchar(30) DEFAULT NULL,
412 `lname` varchar(30) DEFAULT NULL
413 ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
414 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
415 WHERE NAME LIKE '%tcopy_part%';
416 NAME FILE_SIZE AUTOEXTEND_SIZE
417 test/tcopy_part 8388608 8388608
418 # Exchange a partition
419 SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'tpart';
420 PARTITION_NAME TABLE_ROWS
421 p0 1
422 p1 0
423 p2 0
424 p3 3
425 ALTER TABLE tpart EXCHANGE PARTITION p3 WITH TABLE tcopy_part;
426 SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'tpart';
427 PARTITION_NAME TABLE_ROWS
428 p0 0
429 p1 0
430 p2 0
431 p3 0
432 SELECT COUNT(*) FROM tpart;
433 COUNT(*)
434 1
435 SELECT COUNT(*) FROM tcopy_part;
436 COUNT(*)
437 3
438 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
439 WHERE NAME LIKE '%tpart%';
440 NAME FILE_SIZE AUTOEXTEND_SIZE
441 test/tpart#p#p0 8388608 8388608
442 test/tpart#p#p1 8388608 8388608
443 test/tpart#p#p2 8388608 8388608
444 test/tpart#p#p3 8388608 8388608
445 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
446 WHERE NAME LIKE '%tcopy_part%';
447 NAME FILE_SIZE AUTOEXTEND_SIZE
448 test/tcopy_part 8388608 8388608
449 DROP TABLE tpart;
450 DROP TABLE tcopy_part;
451 # Tests for sub-partitioned tables. The autoextend_size should be updated
452 # for each sub-partition
453 # Create a non-partitioned table and add sub-partitions to it
454 CREATE TABLE tsubpart(a INT NOT NULL PRIMARY KEY, b INT) AUTOEXTEND_SIZE 4M;
455 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
456 WHERE NAME LIKE '%tsubpart%';
457 NAME FILE_SIZE AUTOEXTEND_SIZE
458 test/tsubpart 4194304 4194304
459 ALTER TABLE tsubpart PARTITION BY RANGE (a) SUBPARTITION BY HASH (a)
460 SUBPARTITIONS 3 (
461 PARTITION p1 VALUES LESS THAN (10),
462 PARTITION p2 VALUES LESS THAN (20),
463 PARTITION p3 VALUES LESS THAN maxvalue);
464 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
465 WHERE NAME LIKE '%tsubpart%';
466 NAME FILE_SIZE AUTOEXTEND_SIZE
467 test/tsubpart#p#p1#sp#p1sp0 4194304 4194304
468 test/tsubpart#p#p1#sp#p1sp1 4194304 4194304
469 test/tsubpart#p#p1#sp#p1sp2 4194304 4194304
470 test/tsubpart#p#p2#sp#p2sp0 4194304 4194304
471 test/tsubpart#p#p2#sp#p2sp1 4194304 4194304
472 test/tsubpart#p#p2#sp#p2sp2 4194304 4194304
473 test/tsubpart#p#p3#sp#p3sp0 4194304 4194304
474 test/tsubpart#p#p3#sp#p3sp1 4194304 4194304
475 test/tsubpart#p#p3#sp#p3sp2 4194304 4194304
476 SHOW CREATE TABLE tsubpart;
477 Table Create Table
478 tsubpart CREATE TABLE `tsubpart` (
479 `a` int NOT NULL,
480 `b` int DEFAULT NULL,
481 PRIMARY KEY (`a`)
482 ) /*!80023 AUTOEXTEND_SIZE=4194304 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
483 /*!50100 PARTITION BY RANGE (`a`)
484 SUBPARTITION BY HASH (`a`)
485 SUBPARTITIONS 3
486 (PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
487 PARTITION p2 VALUES LESS THAN (20) ENGINE = InnoDB,
488 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
489 ALTER TABLE tsubpart DROP PARTITION p3;
490 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
491 WHERE NAME LIKE '%tsubpart%';
492 NAME FILE_SIZE AUTOEXTEND_SIZE
493 test/tsubpart#p#p1#sp#p1sp0 4194304 4194304
494 test/tsubpart#p#p1#sp#p1sp1 4194304 4194304
495 test/tsubpart#p#p1#sp#p1sp2 4194304 4194304
496 test/tsubpart#p#p2#sp#p2sp0 4194304 4194304
497 test/tsubpart#p#p2#sp#p2sp1 4194304 4194304
498 test/tsubpart#p#p2#sp#p2sp2 4194304 4194304
499 DROP TABLE tsubpart;
500 # Create a non-partitioned table and add sub-partitions to it whle also modifying
501 # autoextend_size
502 CREATE TABLE tsubpart(a INT NOT NULL PRIMARY KEY, b INT) AUTOEXTEND_SIZE 4M;
503 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
504 WHERE NAME LIKE '%tsubpart%';
505 NAME FILE_SIZE AUTOEXTEND_SIZE
506 test/tsubpart 4194304 4194304
507 ALTER TABLE tsubpart AUTOEXTEND_SIZE 8M PARTITION BY RANGE (a) SUBPARTITION BY HASH (a)
508 SUBPARTITIONS 3 (
509 PARTITION p1 VALUES LESS THAN (10),
510 PARTITION p2 VALUES LESS THAN (20),
511 PARTITION p3 VALUES LESS THAN maxvalue);
512 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
513 WHERE NAME LIKE '%tsubpart%';
514 NAME FILE_SIZE AUTOEXTEND_SIZE
515 test/tsubpart#p#p1#sp#p1sp0 8388608 8388608
516 test/tsubpart#p#p1#sp#p1sp1 8388608 8388608
517 test/tsubpart#p#p1#sp#p1sp2 8388608 8388608
518 test/tsubpart#p#p2#sp#p2sp0 8388608 8388608
519 test/tsubpart#p#p2#sp#p2sp1 8388608 8388608
520 test/tsubpart#p#p2#sp#p2sp2 8388608 8388608
521 test/tsubpart#p#p3#sp#p3sp0 8388608 8388608
522 test/tsubpart#p#p3#sp#p3sp1 8388608 8388608
523 test/tsubpart#p#p3#sp#p3sp2 8388608 8388608
524 SHOW CREATE TABLE tsubpart;
525 Table Create Table
526 tsubpart CREATE TABLE `tsubpart` (
527 `a` int NOT NULL,
528 `b` int DEFAULT NULL,
529 PRIMARY KEY (`a`)
530 ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
531 /*!50100 PARTITION BY RANGE (`a`)
532 SUBPARTITION BY HASH (`a`)
533 SUBPARTITIONS 3
534 (PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
535 PARTITION p2 VALUES LESS THAN (20) ENGINE = InnoDB,
536 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
537 ALTER TABLE tsubpart DROP PARTITION p3;
538 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
539 WHERE NAME LIKE '%tsubpart%';
540 NAME FILE_SIZE AUTOEXTEND_SIZE
541 test/tsubpart#p#p1#sp#p1sp0 8388608 8388608
542 test/tsubpart#p#p1#sp#p1sp1 8388608 8388608
543 test/tsubpart#p#p1#sp#p1sp2 8388608 8388608
544 test/tsubpart#p#p2#sp#p2sp0 8388608 8388608
545 test/tsubpart#p#p2#sp#p2sp1 8388608 8388608
546 test/tsubpart#p#p2#sp#p2sp2 8388608 8388608
547 DROP TABLE tsubpart;
548 # Create a sub-partitioned table and verify that changes to AUTOEXTEND_SIZE
549 # are applied to all the subpartitions
550 CREATE TABLE tsubpart (a INT NOT NULL PRIMARY KEY, b INT)
551 AUTOEXTEND_SIZE 4M
552 PARTITION BY RANGE (a)
553 SUBPARTITION BY HASH (a)
554 SUBPARTITIONS 3 (
555 PARTITION p1 values less than (10),
556 PARTITION p2 values less than (20),
557 PARTITION p3 values less than maxvalue);
558 SHOW CREATE TABLE tsubpart;
559 Table Create Table
560 tsubpart CREATE TABLE `tsubpart` (
561 `a` int NOT NULL,
562 `b` int DEFAULT NULL,
563 PRIMARY KEY (`a`)
564 ) /*!80023 AUTOEXTEND_SIZE=4194304 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
565 /*!50100 PARTITION BY RANGE (`a`)
566 SUBPARTITION BY HASH (`a`)
567 SUBPARTITIONS 3
568 (PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
569 PARTITION p2 VALUES LESS THAN (20) ENGINE = InnoDB,
570 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
571 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
572 WHERE NAME LIKE '%tsubpart%';
573 NAME FILE_SIZE AUTOEXTEND_SIZE
574 test/tsubpart#p#p1#sp#p1sp0 4194304 4194304
575 test/tsubpart#p#p1#sp#p1sp1 4194304 4194304
576 test/tsubpart#p#p1#sp#p1sp2 4194304 4194304
577 test/tsubpart#p#p2#sp#p2sp0 4194304 4194304
578 test/tsubpart#p#p2#sp#p2sp1 4194304 4194304
579 test/tsubpart#p#p2#sp#p2sp2 4194304 4194304
580 test/tsubpart#p#p3#sp#p3sp0 4194304 4194304
581 test/tsubpart#p#p3#sp#p3sp1 4194304 4194304
582 test/tsubpart#p#p3#sp#p3sp2 4194304 4194304
583 CREATE TABLE tsubpart_copy LIKE tsubpart;
584 SHOW CREATE TABLE tsubpart_copy;
585 Table Create Table
586 tsubpart_copy CREATE TABLE `tsubpart_copy` (
587 `a` int NOT NULL,
588 `b` int DEFAULT NULL,
589 PRIMARY KEY (`a`)
590 ) /*!80023 AUTOEXTEND_SIZE=4194304 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
591 /*!50100 PARTITION BY RANGE (`a`)
592 SUBPARTITION BY HASH (`a`)
593 SUBPARTITIONS 3
594 (PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
595 PARTITION p2 VALUES LESS THAN (20) ENGINE = InnoDB,
596 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
597 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
598 WHERE NAME LIKE '%tsubpart_copy%';
599 NAME FILE_SIZE AUTOEXTEND_SIZE
600 test/tsubpart_copy#p#p1#sp#p1sp0 4194304 4194304
601 test/tsubpart_copy#p#p1#sp#p1sp1 4194304 4194304
602 test/tsubpart_copy#p#p1#sp#p1sp2 4194304 4194304
603 test/tsubpart_copy#p#p2#sp#p2sp0 4194304 4194304
604 test/tsubpart_copy#p#p2#sp#p2sp1 4194304 4194304
605 test/tsubpart_copy#p#p2#sp#p2sp2 4194304 4194304
606 test/tsubpart_copy#p#p3#sp#p3sp0 4194304 4194304
607 test/tsubpart_copy#p#p3#sp#p3sp1 4194304 4194304
608 test/tsubpart_copy#p#p3#sp#p3sp2 4194304 4194304
609 DROP TABLE tsubpart_copy;
610 ALTER TABLE tsubpart AUTOEXTEND_SIZE 8M;
611 SHOW CREATE TABLE tsubpart;
612 Table Create Table
613 tsubpart CREATE TABLE `tsubpart` (
614 `a` int NOT NULL,
615 `b` int DEFAULT NULL,
616 PRIMARY KEY (`a`)
617 ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
618 /*!50100 PARTITION BY RANGE (`a`)
619 SUBPARTITION BY HASH (`a`)
620 SUBPARTITIONS 3
621 (PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
622 PARTITION p2 VALUES LESS THAN (20) ENGINE = InnoDB,
623 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
624 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
625 WHERE NAME LIKE '%tsubpart%';
626 NAME FILE_SIZE AUTOEXTEND_SIZE
627 test/tsubpart#p#p1#sp#p1sp0 4194304 8388608
628 test/tsubpart#p#p1#sp#p1sp1 4194304 8388608
629 test/tsubpart#p#p1#sp#p1sp2 4194304 8388608
630 test/tsubpart#p#p2#sp#p2sp0 4194304 8388608
631 test/tsubpart#p#p2#sp#p2sp1 4194304 8388608
632 test/tsubpart#p#p2#sp#p2sp2 4194304 8388608
633 test/tsubpart#p#p3#sp#p3sp0 4194304 8388608
634 test/tsubpart#p#p3#sp#p3sp1 4194304 8388608
635 test/tsubpart#p#p3#sp#p3sp2 4194304 8388608
636 ALTER TABLE tsubpart AUTOEXTEND_SIZE 0;
637 SHOW CREATE TABLE tsubpart;
638 Table Create Table
639 tsubpart CREATE TABLE `tsubpart` (
640 `a` int NOT NULL,
641 `b` int DEFAULT NULL,
642 PRIMARY KEY (`a`)
643 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
644 /*!50100 PARTITION BY RANGE (`a`)
645 SUBPARTITION BY HASH (`a`)
646 SUBPARTITIONS 3
647 (PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
648 PARTITION p2 VALUES LESS THAN (20) ENGINE = InnoDB,
649 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
650 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
651 WHERE NAME LIKE '%tsubpart%';
652 NAME FILE_SIZE AUTOEXTEND_SIZE
653 test/tsubpart#p#p1#sp#p1sp0 4194304 0
654 test/tsubpart#p#p1#sp#p1sp1 4194304 0
655 test/tsubpart#p#p1#sp#p1sp2 4194304 0
656 test/tsubpart#p#p2#sp#p2sp0 4194304 0
657 test/tsubpart#p#p2#sp#p2sp1 4194304 0
658 test/tsubpart#p#p2#sp#p2sp2 4194304 0
659 test/tsubpart#p#p3#sp#p3sp0 4194304 0
660 test/tsubpart#p#p3#sp#p3sp1 4194304 0
661 test/tsubpart#p#p3#sp#p3sp2 4194304 0
662 DROP TABLE tsubpart;
663 # Test ALTER TABLE ... REMOVE PARTITIONING and ALTER TABLE ... EXCHANGE PARTITION
664 # for sub-partitioned table
665 CREATE TABLE tsubpart (
666 id INT NOT NULL,
667 fname VARCHAR(30),
668 lname VARCHAR(30)
669 ) AUTOEXTEND_SIZE = 8M
670 PARTITION BY RANGE (id)
671 SUBPARTITION BY KEY (lname)
672 SUBPARTITIONS 2 (
673 PARTITION p0 VALUES LESS THAN (50),
674 PARTITION p1 VALUES LESS THAN (100),
675 PARTITION p2 VALUES LESS THAN (150),
676 PARTITION p3 VALUES LESS THAN (MAXVALUE));
677 SHOW CREATE TABLE tsubpart;
678 Table Create Table
679 tsubpart CREATE TABLE `tsubpart` (
680 `id` int NOT NULL,
681 `fname` varchar(30) DEFAULT NULL,
682 `lname` varchar(30) DEFAULT NULL
683 ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
684 /*!50100 PARTITION BY RANGE (`id`)
685 SUBPARTITION BY KEY (lname)
686 SUBPARTITIONS 2
687 (PARTITION p0 VALUES LESS THAN (50) ENGINE = InnoDB,
688 PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,
689 PARTITION p2 VALUES LESS THAN (150) ENGINE = InnoDB,
690 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
691 INSERT INTO tsubpart VALUES (1669, "Jim", "Smith"), (337, "Mary", "Jones"), (16, "Frank", "White"), (2005, "Linda", "Black");
692 SELECT COUNT(*) FROM tsubpart;
693 COUNT(*)
694 4
695 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
696 WHERE NAME LIKE '%tsubpart%';
697 NAME FILE_SIZE AUTOEXTEND_SIZE
698 test/tsubpart#p#p0#sp#p0sp0 8388608 8388608
699 test/tsubpart#p#p0#sp#p0sp1 8388608 8388608
700 test/tsubpart#p#p1#sp#p1sp0 8388608 8388608
701 test/tsubpart#p#p1#sp#p1sp1 8388608 8388608
702 test/tsubpart#p#p2#sp#p2sp0 8388608 8388608
703 test/tsubpart#p#p2#sp#p2sp1 8388608 8388608
704 test/tsubpart#p#p3#sp#p3sp0 8388608 8388608
705 test/tsubpart#p#p3#sp#p3sp1 8388608 8388608
706 CREATE TABLE tcopy_subpart LIKE tsubpart;
707 SHOW CREATE TABLE tcopy_subpart;
708 Table Create Table
709 tcopy_subpart CREATE TABLE `tcopy_subpart` (
710 `id` int NOT NULL,
711 `fname` varchar(30) DEFAULT NULL,
712 `lname` varchar(30) DEFAULT NULL
713 ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
714 /*!50100 PARTITION BY RANGE (`id`)
715 SUBPARTITION BY KEY (lname)
716 SUBPARTITIONS 2
717 (PARTITION p0 VALUES LESS THAN (50) ENGINE = InnoDB,
718 PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,
719 PARTITION p2 VALUES LESS THAN (150) ENGINE = InnoDB,
720 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
721 # Remove partitioning and validate the value of AUTOEXTEND_SIZE
722 ALTER TABLE tcopy_subpart REMOVE PARTITIONING;
723 SHOW CREATE TABLE tcopy_subpart;
724 Table Create Table
725 tcopy_subpart CREATE TABLE `tcopy_subpart` (
726 `id` int NOT NULL,
727 `fname` varchar(30) DEFAULT NULL,
728 `lname` varchar(30) DEFAULT NULL
729 ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
730 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
731 WHERE NAME LIKE '%tcopy_subpart%';
732 NAME FILE_SIZE AUTOEXTEND_SIZE
733 test/tcopy_subpart 8388608 8388608
734 # Exchange a partition
735 SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'tsubpart';
736 PARTITION_NAME TABLE_ROWS
737 p0 1
738 p0 0
739 p1 0
740 p1 0
741 p2 0
742 p2 0
743 p3 1
744 p3 2
745 ALTER TABLE tsubpart EXCHANGE PARTITION p3sp0 WITH TABLE tcopy_subpart;
746 SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'tsubpart';
747 PARTITION_NAME TABLE_ROWS
748 p0 0
749 p0 0
750 p1 0
751 p1 0
752 p2 0
753 p2 0
754 p3 0
755 p3 2
756 SELECT COUNT(*) FROM tsubpart;
757 COUNT(*)
758 3
759 SELECT COUNT(*) FROM tcopy_subpart;
760 COUNT(*)
761 1
762 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
763 WHERE NAME LIKE '%tsubpart%';
764 NAME FILE_SIZE AUTOEXTEND_SIZE
765 test/tsubpart#p#p0#sp#p0sp0 8388608 8388608
766 test/tsubpart#p#p0#sp#p0sp1 8388608 8388608
767 test/tsubpart#p#p1#sp#p1sp0 8388608 8388608
768 test/tsubpart#p#p1#sp#p1sp1 8388608 8388608
769 test/tsubpart#p#p2#sp#p2sp0 8388608 8388608
770 test/tsubpart#p#p2#sp#p2sp1 8388608 8388608
771 test/tsubpart#p#p3#sp#p3sp1 8388608 8388608
772 test/tsubpart#p#p3#sp#p3sp0 8388608 8388608
773 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
774 WHERE NAME LIKE '%tcopy_subpart%';
775 NAME FILE_SIZE AUTOEXTEND_SIZE
776 test/tcopy_subpart 8388608 8388608
777 DROP TABLE tsubpart;
778 DROP TABLE tcopy_subpart;
779 # Validate that AUTOEXTEND_SIZE clause is not supported with temporary tables
780 CREATE TEMPORARY TABLE temp1 (c1 int) AUTOEXTEND_SIZE 4M;
781 ERROR HY000: AUTOEXTEND_SIZE clause is not valid for temporary tablespace.
782 CREATE TEMPORARY TABLE temp1(c1 INT);
783 ALTER TABLE temp1 AUTOEXTEND_SIZE 4M;
784 ERROR HY000: AUTOEXTEND_SIZE clause is not valid for temporary tablespace.
785 DROP TEMPORARY TABLE temp1;
786 # Validate that AUTOEXTEND_SIZE value is effective when the server is started
787 # with --skip-innodb-validate-tablespace-paths command like option
788 CREATE TABLE t1(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M;
789 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
790 WHERE NAME LIKE '%t1%';
791 NAME FILE_SIZE AUTOEXTEND_SIZE
792 test/t1 4194304 4194304
793 CREATE PROCEDURE bulk_insert()
794 BEGIN
795 DECLARE i INT DEFAULT 1;
796 WHILE i < 10000 DO
797 INSERT INTO t1 VALUES (i, repeat('aaaaaa', 10000));
798 COMMIT;
799 SET i = i + 1;
800 END WHILE;
801 END
802 |
803 # restart: --skip-innodb-validate-tablespace-paths
804 call bulk_insert();
805 SELECT COUNT(*) FROM t1;
806 COUNT(*)
807 9999
808 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
809 WHERE NAME LIKE '%t1%';
810 NAME FILE_SIZE AUTOEXTEND_SIZE
811 test/t1 662700032 4194304
812 DROP PROCEDURE bulk_insert;
813 DROP TABLE t1;
814 # restart
815 # Bug#32235621 - Concurrent execution of ALTER TABLE and SHOW CREATE TABLE crashes
816 # the server
817 CREATE TABLE t1(
818 `c0001` int NOT NULL /*!80023 INVISIBLE */,
819 `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
820 `c0002` decimal(65,14) DEFAULT NULL,
821 `c0004` tinyblob NOT NULL,
822 KEY `i0001` (`c0002`, `c0001`),
823 KEY `i0002` (`c0000`(112))
824 ) AUTOEXTEND_SIZE=8M ENGINE=InnoDB;
825 CREATE PROCEDURE alter_table()
826 BEGIN
827 DECLARE i INT DEFAULT 0;
828 WHILE i < 20 DO
829 ALTER TABLE t1 FORCE;
830 SET i = i + 1;
831 END WHILE;
832 END |
833 CREATE PROCEDURE show_create_table()
834 BEGIN
835 DECLARE i INT DEFAULT 0;
836 WHILE i < 20 DO
837 SHOW CREATE TABLE t1;
838 SET i = i + 1;
839 END WHILE;
840 END |
841 CALL show_create_table();
842 CALL alter_table();
843 Table Create Table
844 t1 CREATE TABLE `t1` (
845 `c0001` int NOT NULL /*!80023 INVISIBLE */,
846 `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
847 `c0002` decimal(65,14) DEFAULT NULL,
848 `c0004` tinyblob NOT NULL,
849 KEY `i0001` (`c0002`,`c0001`),
850 KEY `i0002` (`c0000`(112))
851 ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
852 Table Create Table
853 t1 CREATE TABLE `t1` (
854 `c0001` int NOT NULL /*!80023 INVISIBLE */,
855 `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
856 `c0002` decimal(65,14) DEFAULT NULL,
857 `c0004` tinyblob NOT NULL,
858 KEY `i0001` (`c0002`,`c0001`),
859 KEY `i0002` (`c0000`(112))
860 ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
861 Table Create Table
862 t1 CREATE TABLE `t1` (
863 `c0001` int NOT NULL /*!80023 INVISIBLE */,
864 `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
865 `c0002` decimal(65,14) DEFAULT NULL,
866 `c0004` tinyblob NOT NULL,
867 KEY `i0001` (`c0002`,`c0001`),
868 KEY `i0002` (`c0000`(112))
869 ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
870 Table Create Table
871 t1 CREATE TABLE `t1` (
872 `c0001` int NOT NULL /*!80023 INVISIBLE */,
873 `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
874 `c0002` decimal(65,14) DEFAULT NULL,
875 `c0004` tinyblob NOT NULL,
876 KEY `i0001` (`c0002`,`c0001`),
877 KEY `i0002` (`c0000`(112))
878 ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
879 Table Create Table
880 t1 CREATE TABLE `t1` (
881 `c0001` int NOT NULL /*!80023 INVISIBLE */,
882 `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
883 `c0002` decimal(65,14) DEFAULT NULL,
884 `c0004` tinyblob NOT NULL,
885 KEY `i0001` (`c0002`,`c0001`),
886 KEY `i0002` (`c0000`(112))
887 ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
888 Table Create Table
889 t1 CREATE TABLE `t1` (
890 `c0001` int NOT NULL /*!80023 INVISIBLE */,
891 `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
892 `c0002` decimal(65,14) DEFAULT NULL,
893 `c0004` tinyblob NOT NULL,
894 KEY `i0001` (`c0002`,`c0001`),
895 KEY `i0002` (`c0000`(112))
896 ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
897 Table Create Table
898 t1 CREATE TABLE `t1` (
899 `c0001` int NOT NULL /*!80023 INVISIBLE */,
900 `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
901 `c0002` decimal(65,14) DEFAULT NULL,
902 `c0004` tinyblob NOT NULL,
903 KEY `i0001` (`c0002`,`c0001`),
904 KEY `i0002` (`c0000`(112))
905 ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
906 Table Create Table
907 t1 CREATE TABLE `t1` (
908 `c0001` int NOT NULL /*!80023 INVISIBLE */,
909 `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
910 `c0002` decimal(65,14) DEFAULT NULL,
911 `c0004` tinyblob NOT NULL,
912 KEY `i0001` (`c0002`,`c0001`),
913 KEY `i0002` (`c0000`(112))
914 ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
915 Table Create Table
916 t1 CREATE TABLE `t1` (
917 `c0001` int NOT NULL /*!80023 INVISIBLE */,
918 `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
919 `c0002` decimal(65,14) DEFAULT NULL,
920 `c0004` tinyblob NOT NULL,
921 KEY `i0001` (`c0002`,`c0001`),
922 KEY `i0002` (`c0000`(112))
923 ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
924 Table Create Table
925 t1 CREATE TABLE `t1` (
926 `c0001` int NOT NULL /*!80023 INVISIBLE */,
927 `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
928 `c0002` decimal(65,14) DEFAULT NULL,
929 `c0004` tinyblob NOT NULL,
930 KEY `i0001` (`c0002`,`c0001`),
931 KEY `i0002` (`c0000`(112))
932 ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
933 Table Create Table
934 t1 CREATE TABLE `t1` (
935 `c0001` int NOT NULL /*!80023 INVISIBLE */,
936 `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
937 `c0002` decimal(65,14) DEFAULT NULL,
938 `c0004` tinyblob NOT NULL,
939 KEY `i0001` (`c0002`,`c0001`),
940 KEY `i0002` (`c0000`(112))
941 ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
942 Table Create Table
943 t1 CREATE TABLE `t1` (
944 `c0001` int NOT NULL /*!80023 INVISIBLE */,
945 `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
946 `c0002` decimal(65,14) DEFAULT NULL,
947 `c0004` tinyblob NOT NULL,
948 KEY `i0001` (`c0002`,`c0001`),
949 KEY `i0002` (`c0000`(112))
950 ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
951 Table Create Table
952 t1 CREATE TABLE `t1` (
953 `c0001` int NOT NULL /*!80023 INVISIBLE */,
954 `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
955 `c0002` decimal(65,14) DEFAULT NULL,
956 `c0004` tinyblob NOT NULL,
957 KEY `i0001` (`c0002`,`c0001`),
958 KEY `i0002` (`c0000`(112))
959 ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
960 Table Create Table
961 t1 CREATE TABLE `t1` (
962 `c0001` int NOT NULL /*!80023 INVISIBLE */,
963 `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
964 `c0002` decimal(65,14) DEFAULT NULL,
965 `c0004` tinyblob NOT NULL,
966 KEY `i0001` (`c0002`,`c0001`),
967 KEY `i0002` (`c0000`(112))
968 ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
969 Table Create Table
970 t1 CREATE TABLE `t1` (
971 `c0001` int NOT NULL /*!80023 INVISIBLE */,
972 `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
973 `c0002` decimal(65,14) DEFAULT NULL,
974 `c0004` tinyblob NOT NULL,
975 KEY `i0001` (`c0002`,`c0001`),
976 KEY `i0002` (`c0000`(112))
977 ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
978 Table Create Table
979 t1 CREATE TABLE `t1` (
980 `c0001` int NOT NULL /*!80023 INVISIBLE */,
981 `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
982 `c0002` decimal(65,14) DEFAULT NULL,
983 `c0004` tinyblob NOT NULL,
984 KEY `i0001` (`c0002`,`c0001`),
985 KEY `i0002` (`c0000`(112))
986 ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
987 Table Create Table
988 t1 CREATE TABLE `t1` (
989 `c0001` int NOT NULL /*!80023 INVISIBLE */,
990 `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
991 `c0002` decimal(65,14) DEFAULT NULL,
992 `c0004` tinyblob NOT NULL,
993 KEY `i0001` (`c0002`,`c0001`),
994 KEY `i0002` (`c0000`(112))
995 ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
996 Table Create Table
997 t1 CREATE TABLE `t1` (
998 `c0001` int NOT NULL /*!80023 INVISIBLE */,
999 `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
1000 `c0002` decimal(65,14) DEFAULT NULL,
1001 `c0004` tinyblob NOT NULL,
1002 KEY `i0001` (`c0002`,`c0001`),
1003 KEY `i0002` (`c0000`(112))
1004 ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1005 Table Create Table
1006 t1 CREATE TABLE `t1` (
1007 `c0001` int NOT NULL /*!80023 INVISIBLE */,
1008 `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
1009 `c0002` decimal(65,14) DEFAULT NULL,
1010 `c0004` tinyblob NOT NULL,
1011 KEY `i0001` (`c0002`,`c0001`),
1012 KEY `i0002` (`c0000`(112))
1013 ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1014 Table Create Table
1015 t1 CREATE TABLE `t1` (
1016 `c0001` int NOT NULL /*!80023 INVISIBLE */,
1017 `c0000` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
1018 `c0002` decimal(65,14) DEFAULT NULL,
1019 `c0004` tinyblob NOT NULL,
1020 KEY `i0001` (`c0002`,`c0001`),
1021 KEY `i0002` (`c0000`(112))
1022 ) /*!80023 AUTOEXTEND_SIZE=8388608 */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1023 DROP TABLE t1;
1024 DROP PROCEDURE show_create_table;
1025 DROP PROCEDURE alter_table;