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