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
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
|
SET @file_per_table=@@global.innodb_file_per_table;
CREATE TABLESPACE table_level ADD DATAFILE 'table.ibd';
CREATE TABLESPACE partition_level ADD DATAFILE 'partition.ibd';
CREATE TABLESPACE subpartition_level ADD DATAFILE 'subpartition.ibd';
--mkdir $MYSQL_TMP_DIR/table
--mkdir $MYSQL_TMP_DIR/partition
--mkdir $MYSQL_TMP_DIR/subpartition
CREATE TABLE subpartitioned(a int auto_increment primary key)
TABLESPACE table_level
PARTITION BY RANGE (a)
SUBPARTITION BY HASH (a) SUBPARTITIONS 2
(PARTITION p0 VALUES LESS THAN (0) TABLESPACE partition_level
(SUBPARTITION sp0 TABLESPACE subpartition_level,
SUBPARTITION sp1),
PARTITION p1 VALUES LESS THAN (16)
(SUBPARTITION sp2,
SUBPARTITION sp3 TABLESPACE innodb_file_per_table),
PARTITION p2 VALUES LESS THAN (256) TABLESPACE innodb_file_per_table
(SUBPARTITION sp4 TABLESPACE subpartition_level,
SUBPARTITION sp5));
--echo # Subpartitions show the inherited TABLESPACE.
--echo # Bug#23219564 FIXME: the TABLESPACE of PARTITION should be shown!
SHOW CREATE TABLE subpartitioned;
--echo # The TABLESPACE of PARTITION/TABLE should be inherited for subpartitions
ALTER TABLE subpartitioned ADD PARTITION (PARTITION p3 VALUES LESS THAN (65536) (SUBPARTITION sp6 TABLESPACE innodb_file_per_table, SUBPARTITION sp7 TABLESPACE innodb_system));
SHOW CREATE TABLE subpartitioned;
ALTER TABLE subpartitioned REORGANIZE PARTITION p3 INTO (PARTITION p3 VALUES LESS THAN(65536) (SUBPARTITION sp6, SUBPARTITION sp7 TABLESPACE subpartition_level));
SHOW CREATE TABLE subpartitioned;
ALTER TABLE subpartitioned DROP PARTITION p3;
SHOW CREATE TABLE subpartitioned;
DROP TABLE subpartitioned;
CREATE TABLE partitioned(a int auto_increment primary key)
TABLESPACE table_level
PARTITION BY RANGE (a)
(PARTITION p0 VALUES LESS THAN (0) TABLESPACE partition_level,
PARTITION p1 VALUES LESS THAN (16),
PARTITION p2 VALUES LESS THAN (256) TABLESPACE innodb_file_per_table);
--echo # Bug#23219685 FIXME: PARTITION should show inherited TABLESPACE!
SHOW CREATE TABLE partitioned;
DROP TABLE partitioned;
SET GLOBAL innodb_file_per_table=ON;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TABLE subpartitioned(a int auto_increment primary key)
DATA DIRECTORY '$MYSQL_TMP_DIR/table'
PARTITION BY RANGE (a)
SUBPARTITION BY HASH (a) SUBPARTITIONS 2
(PARTITION p0 VALUES LESS THAN (0) DATA DIRECTORY '$MYSQL_TMP_DIR/partition'
(SUBPARTITION sp0 DATA DIRECTORY '$MYSQL_TMP_DIR/subpartition',
SUBPARTITION sp1),
PARTITION p1 VALUES LESS THAN (16)
TABLESPACE innodb_file_per_table DATA DIRECTORY '$MYSQL_TMP_DIR/partition'
(SUBPARTITION sp2,
SUBPARTITION sp3 DATA DIRECTORY '$MYSQL_TMP_DIR/subpartition'),
PARTITION p2 VALUES LESS THAN (256)
(SUBPARTITION sp4 DATA DIRECTORY '$MYSQL_TMP_DIR/subpartition',
SUBPARTITION sp5));
--echo # Subpartitions show the inherited DATA DIRECTORY.
--echo # Bug#23219564 FIXME: the DATA DIRECTORY of PARTITION should be shown!
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE subpartitioned;
DROP TABLE subpartitioned;
SET GLOBAL innodb_file_per_table=OFF;
SET innodb_strict_mode=OFF;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TABLE subpartitioned(a int auto_increment primary key)
DATA DIRECTORY '$MYSQL_TMP_DIR/table'
PARTITION BY RANGE (a)
SUBPARTITION BY HASH (a) SUBPARTITIONS 2
(PARTITION p0 VALUES LESS THAN (0) DATA DIRECTORY '$MYSQL_TMP_DIR/partition'
(SUBPARTITION sp0 DATA DIRECTORY '$MYSQL_TMP_DIR/subpartition',
SUBPARTITION sp1),
PARTITION p1 VALUES LESS THAN (16)
TABLESPACE innodb_file_per_table DATA DIRECTORY '$MYSQL_TMP_DIR/partition'
(SUBPARTITION sp2,
SUBPARTITION sp3 DATA DIRECTORY '$MYSQL_TMP_DIR/subpartition'),
PARTITION p2 VALUES LESS THAN (256)
(SUBPARTITION sp4 DATA DIRECTORY '$MYSQL_TMP_DIR/subpartition',
SUBPARTITION sp5));
SET innodb_strict_mode=ON;
--echo # Subpartitions show the inherited DATA DIRECTORY.
--echo # Bug#23219564 FIXME: the DATA DIRECTORY of PARTITION should be shown!
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE subpartitioned;
--echo # The DATA DIRECTORY of PARTITION/TABLE should be inherited for subpartitions
SET GLOBAL innodb_file_per_table=ON;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval ALTER TABLE subpartitioned ADD PARTITION (PARTITION p3 VALUES LESS THAN (65536) TABLESPACE innodb_file_per_table DATA DIRECTORY '$MYSQL_TMP_DIR/partition' (SUBPARTITION sp6, SUBPARTITION sp7));
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE subpartitioned;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval ALTER TABLE subpartitioned REORGANIZE PARTITION p3 INTO (PARTITION p3 VALUES LESS THAN(65536) (SUBPARTITION sp6, SUBPARTITION sp7 DATA DIRECTORY '$MYSQL_TMP_DIR/subpartition'));
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE subpartitioned;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
ALTER TABLE subpartitioned DROP PARTITION p3;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE subpartitioned;
DROP TABLE subpartitioned;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TABLE partitioned(a int auto_increment primary key)
DATA DIRECTORY '$MYSQL_TMP_DIR/table'
PARTITION BY RANGE (a)
(PARTITION p0 VALUES LESS THAN (0) TABLESPACE innodb_file_per_table,
PARTITION p1 VALUES LESS THAN (16)
TABLESPACE innodb_file_per_table DATA DIRECTORY '$MYSQL_TMP_DIR/partition',
PARTITION p2 VALUES LESS THAN (256));
--echo # Bug#23219685 FIXME: PARTITION should show inherited DATA DIRECTORY!
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE partitioned;
DROP TABLE partitioned;
SET GLOBAL innodb_file_per_table=OFF;
SET innodb_strict_mode=OFF;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TABLE partitioned(a int auto_increment primary key)
DATA DIRECTORY '$MYSQL_TMP_DIR/table'
PARTITION BY RANGE (a)
(PARTITION p0 VALUES LESS THAN (0) TABLESPACE innodb_file_per_table,
PARTITION p1 VALUES LESS THAN (16)
TABLESPACE innodb_file_per_table DATA DIRECTORY '$MYSQL_TMP_DIR/partition',
PARTITION p2 VALUES LESS THAN (256));
SET innodb_strict_mode=ON;
--echo # Bug#23219685 FIXME: PARTITION should show inherited DATA DIRECTORY!
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE partitioned;
DROP TABLE partitioned;
--rmdir $MYSQL_TMP_DIR/table/test
--rmdir $MYSQL_TMP_DIR/table
--rmdir $MYSQL_TMP_DIR/partition/test
--rmdir $MYSQL_TMP_DIR/partition
--rmdir $MYSQL_TMP_DIR/subpartition/test
--rmdir $MYSQL_TMP_DIR/subpartition
DROP TABLESPACE table_level;
DROP TABLESPACE partition_level;
DROP TABLESPACE subpartition_level;
SET GLOBAL innodb_file_per_table=@file_per_table;
|
