MySQL 5.6.14 Source Code Document
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
partition_alter3.inc
1 ################################################################################
2 # inc/partition_alter3.inc #
3 # #
4 # Purpose: #
5 # Tests for partition management commands for HASH and KEY partitioning #
6 # #
7 #------------------------------------------------------------------------------#
8 # Original Author: mleich #
9 # Original Date: 2006-04-11 #
10 # Change Author: #
11 # Change Date: #
12 # Change: #
13 ################################################################################
14 
15 --echo
16 --echo #========================================================================
17 --echo # 1. Partition management commands on HASH partitioned table
18 --echo # column in partitioning function is of type DATE
19 --echo #========================================================================
20 # 1. Create the table
21 --disable_warnings
22 DROP TABLE IF EXISTS t1;
23 --enable_warnings
24 eval CREATE TABLE t1 (f_date DATE, f_varchar VARCHAR(30));
25 # 2. Fill the table t1 with records
26 INSERT INTO t1 (f_date, f_varchar)
27 SELECT CONCAT(CAST((f_int1 + 999) AS CHAR),'-02-10'), CAST(f_char1 AS CHAR)
28 FROM t0_template
29 WHERE f_int1 + 999 BETWEEN 1000 AND 9999;
30 # 3. Calculate the number of inserted records.
31 SELECT IF(9999 - 1000 + 1 > @max_row, @max_row , 9999 - 1000 + 1)
32  INTO @exp_row_count;
33 # DEBUG SELECT @exp_row_count;
34 # 4. Print the layout, check Readability
35 --source suite/parts/inc/partition_layout.inc
36 --source suite/parts/inc/partition_check_read1.inc
37 #
38 --echo #------------------------------------------------------------------------
39 --echo # 1.1 Increase number of PARTITIONS
40 --echo #------------------------------------------------------------------------
41 --echo # 1.1.1 ADD PARTITION to not partitioned table --> must fail
42 --error ER_PARTITION_MGMT_ON_NONPARTITIONED
43 ALTER TABLE t1 ADD PARTITION (PARTITION part2);
44 #
45 --echo # 1.1.2 Assign HASH partitioning
46 ALTER TABLE t1 PARTITION BY HASH(YEAR(f_date));
47 --source suite/parts/inc/partition_layout.inc
48 --source suite/parts/inc/partition_check_read1.inc
49 #
50 --echo # 1.1.3 Assign other HASH partitioning to already partitioned table
51 --echo # + test and switch back + test
52 ALTER TABLE t1 PARTITION BY HASH(DAYOFYEAR(f_date));
53 --source suite/parts/inc/partition_layout.inc
54 --source suite/parts/inc/partition_check_read1.inc
55 ALTER TABLE t1 PARTITION BY HASH(YEAR(f_date));
56 --source suite/parts/inc/partition_layout.inc
57 --source suite/parts/inc/partition_check_read1.inc
58 #
59 --echo # 1.1.4 Add PARTITIONS not fitting to HASH --> must fail
60 --error ER_PARTITION_WRONG_VALUES_ERROR
61 ALTER TABLE t1 ADD PARTITION (PARTITION part1 VALUES IN (0));
62 --error ER_PARTITION_WRONG_VALUES_ERROR
63 ALTER TABLE t1 ADD PARTITION (PARTITION part2 VALUES LESS THAN (0));
64 #
65 --echo # 1.1.5 Add two named partitions + test
66 ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
67 --source suite/parts/inc/partition_layout.inc
68 --source suite/parts/inc/partition_check_read1.inc
69 #
70 --echo # 1.1.6 Add two named partitions, name clash --> must fail
71 --error ER_SAME_NAME_PARTITION
72 ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
73 #
74 --echo # 1.1.7 Add one named partition + test
75 ALTER TABLE t1 ADD PARTITION (PARTITION part2);
76 --source suite/parts/inc/partition_layout.inc
77 --source suite/parts/inc/partition_check_read1.inc
78 #
79 --echo # 1.1.8 Add four not named partitions + test
80 ALTER TABLE t1 ADD PARTITION PARTITIONS 4;
81 --source suite/parts/inc/partition_layout.inc
82 --source suite/parts/inc/partition_check_read1.inc
83 
84 --echo #------------------------------------------------------------------------
85 --echo # 1.2 Decrease number of PARTITIONS
86 --echo #------------------------------------------------------------------------
87 --echo # 1.2.1 DROP PARTITION is not supported for HASH --> must fail
88 --error ER_ONLY_ON_RANGE_LIST_PARTITION
89 ALTER TABLE t1 DROP PARTITION part1;
90 #
91 --echo # 1.2.2 COALESCE PARTITION partitionname is not supported
92 --error ER_PARSE_ERROR
93 ALTER TABLE t1 COALESCE PARTITION part1;
94 #
95 --echo # 1.2.3 Decrease by 0 is non sense --> must fail
96 --error ER_COALESCE_PARTITION_NO_PARTITION
97 ALTER TABLE t1 COALESCE PARTITION 0;
98 #
99 --echo # 1.2.4 COALESCE one partition + test loop
100 let $loop= 7;
101 while ($loop)
102 {
103  ALTER TABLE t1 COALESCE PARTITION 1;
104  --source suite/parts/inc/partition_layout.inc
105  --source suite/parts/inc/partition_check_read1.inc
106  dec $loop;
107 }
108 --echo # 1.2.5 COALESCE of last partition --> must fail
109 --error ER_DROP_LAST_PARTITION
110 ALTER TABLE t1 COALESCE PARTITION 1;
111 #
112 --echo # 1.2.6 Remove partitioning
113 ALTER TABLE t1 REMOVE PARTITIONING;
114 --source suite/parts/inc/partition_layout.inc
115 --source suite/parts/inc/partition_check_read1.inc
116 #
117 --echo # 1.2.7 Remove partitioning from not partitioned table --> ????
118 --error ER_PARTITION_MGMT_ON_NONPARTITIONED
119 ALTER TABLE t1 REMOVE PARTITIONING;
120 DROP TABLE t1;
121 --source suite/parts/inc/partition_check_drop.inc
122 
123 --echo
124 --echo #========================================================================
125 --echo # 2. Partition management commands on KEY partitioned table
126 --echo #========================================================================
127 # 1. Create the table
128 --disable_warnings
129 DROP TABLE IF EXISTS t1;
130 --enable_warnings
131 eval CREATE TABLE t1 (
132 $column_list
133 );
134 # 2. Fill the table t1 with some records
135 eval $insert_all;
136 # 4. Print the layout, check Readability
137 --source suite/parts/inc/partition_layout.inc
138 --source suite/parts/inc/partition_check_read2.inc
139 #
140 --echo #------------------------------------------------------------------------
141 --echo # 2.1 Increase number of PARTITIONS
142 --echo # Some negative testcases are omitted (already checked with HASH).
143 --echo #------------------------------------------------------------------------
144 --echo # 2.1.1 Assign KEY partitioning
145 ALTER TABLE t1 PARTITION BY KEY(f_int1);
146 --source suite/parts/inc/partition_layout.inc
147 --source suite/parts/inc/partition_check_read2.inc
148 #
149 --echo # 2.1.2 Add PARTITIONS not fitting to KEY --> must fail
150 --error ER_PARTITION_WRONG_VALUES_ERROR
151 ALTER TABLE t1 ADD PARTITION (PARTITION part1 VALUES IN (0));
152 --error ER_PARTITION_WRONG_VALUES_ERROR
153 ALTER TABLE t1 ADD PARTITION (PARTITION part2 VALUES LESS THAN (0));
154 #
155 --echo # 2.1.3 Add two named partitions + test
156 ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
157 --source suite/parts/inc/partition_layout.inc
158 --source suite/parts/inc/partition_check_read2.inc
159 #
160 --echo # 2.1.4 Add one named partition + test
161 ALTER TABLE t1 ADD PARTITION (PARTITION part2);
162 --source suite/parts/inc/partition_layout.inc
163 --source suite/parts/inc/partition_check_read2.inc
164 #
165 --echo # 2.1.5 Add four not named partitions + test
166 ALTER TABLE t1 ADD PARTITION PARTITIONS 4;
167 --source suite/parts/inc/partition_layout.inc
168 --source suite/parts/inc/partition_check_read2.inc
169 
170 --echo #------------------------------------------------------------------------
171 --echo # 2.2 Decrease number of PARTITIONS
172 --echo # Some negative testcases are omitted (already checked with HASH).
173 --echo #------------------------------------------------------------------------
174 --echo # 2.2.1 DROP PARTITION is not supported for KEY --> must fail
175 --error ER_ONLY_ON_RANGE_LIST_PARTITION
176 ALTER TABLE t1 DROP PARTITION part1;
177 #
178 --echo # 2.2.4 COALESCE one partition + test loop
179 let $loop= 7;
180 while ($loop)
181 {
182  ALTER TABLE t1 COALESCE PARTITION 1;
183  --source suite/parts/inc/partition_layout.inc
184  --source suite/parts/inc/partition_check_read2.inc
185  dec $loop;
186 }
187 --echo # 2.2.5 COALESCE of last partition --> must fail
188 --error ER_DROP_LAST_PARTITION
189 ALTER TABLE t1 COALESCE PARTITION 1;
190 #
191 --echo # 2.2.6 Remove partitioning
192 ALTER TABLE t1 REMOVE PARTITIONING;
193 --source suite/parts/inc/partition_layout.inc
194 --source suite/parts/inc/partition_check_read2.inc
195 #
196 --echo # 2.2.7 Remove partitioning from not partitioned table --> ????
197 --error ER_PARTITION_MGMT_ON_NONPARTITIONED
198 ALTER TABLE t1 REMOVE PARTITIONING;
199 DROP TABLE t1;
200 --source suite/parts/inc/partition_check_drop.inc
201