Debian Codegraph @

mysql-test/suite/innodb/include/create_alter_partition.inc

raw
1 #
2 # This script assumes that the caller has set the following variables:
3 # $TABLESPACE1
4 # $TABLESPACE2
5 # $TABLESPACE3
6 # $TABLESPACE4
7 # $INNODB_FILE_PER_TABLE_FOR_CREATE
8 # $INNODB_FILE_PER_TABLE_FOR_ALTER
9 #
10 --eval SET GLOBAL innodb_file_per_table = $INNODB_FILE_PER_TABLE_FOR_CREATE
11
12 eval CREATE TABLE t1 (a INT NOT NULL, b INT)
13 ENGINE=InnoDB
14 PARTITION BY RANGE(a)
15 (PARTITION p1 VALUES LESS THAN (5) $TABLESPACE1,
16 PARTITION p2 VALUES LESS THAN (10) $TABLESPACE2,
17 PARTITION p3 VALUES LESS THAN (15) $TABLESPACE3);
18
19 SHOW CREATE TABLE t1;
20
21 --disable_query_log
22 --replace_result #P# #p#
23 --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
24 SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as
25 space_name
26 FROM information_schema.innodb_tables A
27 LEFT JOIN
28 information_schema.innodb_tablespaces B
29 ON A.SPACE = B.SPACE
30 WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
31 --enable_query_log
32
33 --eval SET GLOBAL innodb_file_per_table = $INNODB_FILE_PER_TABLE_FOR_ALTER
34
35 eval ALTER TABLE t1 ADD PARTITION
36 (PARTITION p4 VALUES LESS THAN (20) $TABLESPACE4);
37
38 SHOW CREATE TABLE t1;
39
40 --disable_query_log
41 --replace_result #P# #p#
42 --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
43 SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as
44 space_name
45 FROM information_schema.innodb_tables A
46 LEFT JOIN
47 information_schema.innodb_tablespaces B
48 ON A.SPACE = B.SPACE
49 WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME;
50 --enable_query_log
51
52 DROP TABLE t1;
53
54 --eval SET GLOBAL innodb_file_per_table = $INNODB_FILE_PER_TABLE_FOR_CREATE
55
56 eval CREATE TABLE t2 (a INT NOT NULL, b INT)
57 ENGINE=InnoDB
58 PARTITION BY RANGE(a)
59 SUBPARTITION BY KEY(b)
60 (PARTITION p1 VALUES LESS THAN (5)
61 (SUBPARTITION sp1 $TABLESPACE1),
62 PARTITION p2 VALUES LESS THAN (10)
63 (SUBPARTITION sp2 $TABLESPACE2),
64 PARTITION p3 VALUES LESS THAN (15)
65 (SUBPARTITION sp3 $TABLESPACE3));
66
67 SHOW CREATE TABLE t2;
68
69 --disable_query_log
70 --replace_result #P# #p# #SP# #sp#
71 --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
72 SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as
73 space_name
74 FROM information_schema.innodb_tables A
75 LEFT JOIN
76 information_schema.innodb_tablespaces B
77 ON A.SPACE = B.SPACE
78 WHERE A.NAME LIKE '%t2%' ORDER BY A.NAME;
79 --enable_query_log
80
81 --eval SET GLOBAL innodb_file_per_table = $INNODB_FILE_PER_TABLE_FOR_ALTER
82
83 eval ALTER TABLE t2 ADD PARTITION
84 (PARTITION p4 VALUES LESS THAN (20)
85 (SUBPARTITION sp4 $TABLESPACE4));
86
87 SHOW CREATE TABLE t2;
88
89 --disable_query_log
90 --replace_result #P# #p# #SP# #sp#
91 --replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/
92 SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as
93 space_name
94 FROM information_schema.innodb_tables A
95 LEFT JOIN
96 information_schema.innodb_tablespaces B
97 ON A.SPACE = B.SPACE
98 WHERE A.NAME LIKE '%t2%' ORDER BY A.NAME;
99 --enable_query_log
100
101 DROP TABLE t2;