MySQL 5.6.14 Source Code Document
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
partition_mgm.inc
1 ################################################################################
2 # inc/partition_mgm.inc #
3 # #
4 # Purpose: #
5 # Test of partition management functions including different Upper/Lower #
6 # case names of databases, tables and partitions #
7 # #
8 # #
9 # Uses following variables: #
10 # engine Use specified storage engine #
11 # can_only_key Storage engine only able to use HASH/KEY (not range/list) #
12 # (E.g. not ndbcluster) #
13 # part_optA-D Extra partitioning options (E.g. INDEX/DATA DIR) #
14 # #
15 # have_bug33158 NDB case insensitive create, but case sensitive rename #
16 # no_truncate No support for truncate partition #
17 #------------------------------------------------------------------------------#
18 # Original Author: mattiasj #
19 # Original Date: 2008-06-27 #
20 ################################################################################
21 --enable_abort_on_error
22 
23 let $MYSQLD_DATADIR = `SELECT @@datadir`;
24 let $old_db= `SELECT DATABASE()`;
25 --echo # Creating database MySQL_TEST_DB
26 CREATE DATABASE MySQL_Test_DB;
27 USE MySQL_Test_DB;
28 let $MYSQL_TEST_DB_NAME = `SELECT DATABASE()`;
29 --echo # 1.0 KEY partitioning mgm
30 --echo # Creating KEY partitioned table
31 eval CREATE TABLE TableA (a INT)
32 ENGINE = $engine
33 PARTITION BY KEY (a)
34 (PARTITION parta $part_optA,
35  PARTITION partB $part_optB,
36  PARTITION Partc $part_optC,
37  PARTITION PartD $part_optD);
38 INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10);
39 INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12);
40 --sorted_result
41 SELECT * FROM TableA;
42 
43 --echo # Test of ADD/COALESCE PARTITIONS
44 --echo # expecting duplicate partition name
45 --error ER_SAME_NAME_PARTITION
46 ALTER TABLE TableA ADD PARTITION
47 (PARTITION partA,
48  PARTITION Parta,
49  PARTITION PartA);
50 ALTER TABLE TableA ADD PARTITION
51 (PARTITION partE,
52  PARTITION Partf,
53  PARTITION PartG);
54 --sorted_result
55 SELECT * FROM TableA;
56 SHOW CREATE TABLE TableA;
57 ALTER TABLE TableA COALESCE PARTITION 4;
58 --sorted_result
59 SELECT * FROM TableA;
60 SHOW CREATE TABLE TableA;
61 
62 -- disable_query_log
63 -- disable_result_log
64 ANALYZE TABLE TableA;
65 -- enable_result_log
66 -- enable_query_log
67 
68 --echo # Test of EXCHANGE PARTITION WITH TABLE
69 if (!$native_partitioning)
70 {
71 SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA ='MySQL_Test_DB' AND TABLE_NAME = 'TableA';
72 CREATE TABLE TableB LIKE TableA;
73 ALTER TABLE TableB REMOVE PARTITIONING;
74 ALTER TABLE TableA EXCHANGE PARTITION parta WITH TABLE TableB;
75 --sorted_result
76 SELECT * FROM TableA;
77 SHOW CREATE TABLE TableA;
78 --sorted_result
79 SELECT * FROM TableB;
80 SHOW CREATE TABLE TableB;
81 SELECT PARTITION_NAME, IF(TABLE_ROWS, 'YES', 'NO') AS HAVE_TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA ='MySQL_Test_DB' AND TABLE_NAME = 'TableA';
82 ALTER TABLE TableA EXCHANGE PARTITION parta WITH TABLE TableB;
83 INSERT INTO TableB VALUES (11);
84 --error ER_ROW_DOES_NOT_MATCH_PARTITION
85 ALTER TABLE TableA EXCHANGE PARTITION Partc WITH TABLE TableB;
86 DROP TABLE TableB;
87 --sorted_result
88 SELECT * FROM TableA;
89 SHOW CREATE TABLE TableA;
90 }
91 
92 --echo # Test of REORGANIZE PARTITIONS
93 --echo # Should not work on HASH/KEY
94 --error ER_REORG_HASH_ONLY_ON_SAME_NO
95 eval ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO
96 (PARTITION PARTA $part_optA,
97  PARTITION partc $part_optC);
98 --error ER_CONSECUTIVE_REORG_PARTITIONS
99 eval ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO
100 (PARTITION partB $part_optA,
101  PARTITION parta $part_optC);
102 eval ALTER TABLE TableA REORGANIZE PARTITION parta,partB INTO
103 (PARTITION partB $part_optA COMMENT="Previusly named parta",
104  PARTITION parta $part_optB COMMENT="Previusly named partB");
105 if ($fixed_bug20129)
106 {
107 ALTER TABLE TableA ANALYZE PARTITION parta, partB, Partc;
108 ALTER TABLE TableA CHECK PARTITION parta, partB, Partc;
109 ALTER TABLE TableA OPTIMIZE PARTITION parta, partB, Partc;
110 ALTER TABLE TableA REPAIR PARTITION parta, partB, Partc;
111 }
112 --sorted_result
113 SELECT * FROM TableA;
114 SHOW CREATE TABLE TableA;
115 
116 --echo # Test of RENAME TABLE
117 RENAME TABLE TableA to TableB;
118 --sorted_result
119 SELECT * FROM TableB;
120 RENAME TABLE TableB to TableA;
121 --sorted_result
122 SELECT * FROM TableA;
123 
124 --echo # Checking name comparision Upper vs Lower case
125 --echo # Error if lower_case_table_names != 0
126 let $lower_case_table_names= `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'lower_case_table_names'`;
127 --echo # lower_case_table_names: $lower_case_table_names
128 if ($lower_case_table_names)
129 {
130 --error ER_TABLE_EXISTS_ERROR
131 eval CREATE TABLE tablea (a INT)
132 ENGINE = $engine
133 PARTITION BY KEY (a)
134 (PARTITION parta $part_optA,
135  PARTITION partB $part_optB,
136  PARTITION Partc $part_optC,
137  PARTITION PartD $part_optD);
138 SHOW TABLES;
139 --error ER_TABLE_EXISTS_ERROR
140 RENAME TABLE TableA to tablea;
141 --error ER_TABLE_EXISTS_ERROR
142 RENAME TABLE tablea to TableA;
143 --sorted_result
144 SELECT * FROM tablea;
145 SHOW CREATE TABLE tablea;
146 }
147 if (!$lower_case_table_names)
148 {
149 if (!$have_bug33158)
150 {
151 eval CREATE TABLE tablea (a INT)
152 ENGINE = $engine
153 PARTITION BY KEY (a)
154 (PARTITION parta $part_optA,
155  PARTITION partB $part_optB,
156  PARTITION Partc $part_optC,
157  PARTITION PartD $part_optD);
158 INSERT INTO tablea VALUES (1), (2), (7), (8), (9), (10);
159 SHOW TABLES;
160 RENAME TABLE TableA to tableA;
161 --sorted_result
162 SELECT * FROM tablea;
163 --sorted_result
164 SELECT * FROM tableA;
165 RENAME TABLE tableA to TableA;
166 SHOW CREATE TABLE tablea;
167 DROP TABLE tablea;
168 }
169 }
170 
171 --echo # Test of REMOVE PARTITIONING
172 ALTER TABLE TableA REMOVE PARTITIONING;
173 --sorted_result
174 SELECT * FROM TableA;
175 SHOW CREATE TABLE TableA;
176 
177 --echo # Cleaning up after KEY PARTITIONING test
178 DROP TABLE TableA;
179 
180 if (!$can_only_key)
181 {
182 --echo # 2.0 HASH partitioning mgm
183 --echo # expecting duplicate partition name
184 --error ER_SAME_NAME_PARTITION
185 eval CREATE TABLE TableA (a INT)
186 ENGINE = $engine
187 PARTITION BY HASH (a)
188 (PARTITION parta $part_optA,
189  PARTITION partA $part_optB,
190  PARTITION Parta $part_optC,
191  PARTITION PartA $part_optD);
192 
193 --echo # Creating Hash partitioned table
194 eval CREATE TABLE TableA (a INT)
195 ENGINE = $engine
196 PARTITION BY HASH (a)
197 (PARTITION parta $part_optA,
198  PARTITION partB $part_optB,
199  PARTITION Partc $part_optC,
200  PARTITION PartD $part_optD);
201 INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10);
202 INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12);
203 --sorted_result
204 SELECT * FROM TableA;
205 
206 --echo # Test of ADD/COALESCE PARTITIONS
207 --echo # expecting duplicate partition name
208 --error ER_SAME_NAME_PARTITION
209 ALTER TABLE TableA ADD PARTITION
210 (PARTITION partA,
211  PARTITION Parta,
212  PARTITION PartA);
213 ALTER TABLE TableA ADD PARTITION
214 (PARTITION partE,
215  PARTITION Partf,
216  PARTITION PartG);
217 --sorted_result
218 SELECT * FROM TableA;
219 SHOW CREATE TABLE TableA;
220 ALTER TABLE TableA COALESCE PARTITION 4;
221 --sorted_result
222 SELECT * FROM TableA;
223 SHOW CREATE TABLE TableA;
224 
225 --echo # Test of REORGANIZE PARTITIONS
226 --echo # Should not work on HASH/KEY
227 --error ER_REORG_HASH_ONLY_ON_SAME_NO
228 eval ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO
229 (PARTITION PARTA $part_optA,
230  PARTITION partc $part_optC);
231 --error ER_CONSECUTIVE_REORG_PARTITIONS
232 eval ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO
233 (PARTITION partB $part_optA,
234  PARTITION parta $part_optC);
235 eval ALTER TABLE TableA REORGANIZE PARTITION parta,partB INTO
236 (PARTITION partB $part_optA COMMENT="Previusly named parta",
237  PARTITION parta $part_optB COMMENT="Previusly named partB");
238 if ($fixed_bug20129)
239 {
240 ALTER TABLE TableA ANALYZE PARTITION parta, partB, Partc;
241 ALTER TABLE TableA CHECK PARTITION parta, partB, Partc;
242 ALTER TABLE TableA OPTIMIZE PARTITION parta, partB, Partc;
243 ALTER TABLE TableA REPAIR PARTITION parta, partB, Partc;
244 }
245 --sorted_result
246 SELECT * FROM TableA;
247 SHOW CREATE TABLE TableA;
248 
249 --echo # Test of RENAME TABLE
250 RENAME TABLE TableA to TableB;
251 --sorted_result
252 SELECT * FROM TableB;
253 RENAME TABLE TableB to TableA;
254 --sorted_result
255 SELECT * FROM TableA;
256 
257 --echo # Checking name comparision Upper vs Lower case
258 --echo # Error if lower_case_table_names != 0
259 let $lower_case_table_names= `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'lower_case_table_names'`;
260 --echo # lower_case_table_names: $lower_case_table_names
261 if ($lower_case_table_names)
262 {
263 --error ER_TABLE_EXISTS_ERROR
264 eval CREATE TABLE tablea (a INT)
265 ENGINE = $engine
266 PARTITION BY HASH (a)
267 (PARTITION parta $part_optA,
268  PARTITION partB $part_optB,
269  PARTITION Partc $part_optC,
270  PARTITION PartD $part_optD);
271 SHOW TABLES;
272 --error ER_TABLE_EXISTS_ERROR
273 RENAME TABLE TableA to tablea;
274 --error ER_TABLE_EXISTS_ERROR
275 RENAME TABLE tablea to TableA;
276 --sorted_result
277 SELECT * FROM tablea;
278 SHOW CREATE TABLE tablea;
279 }
280 if (!$lower_case_table_names)
281 {
282 eval CREATE TABLE tablea (a INT)
283 ENGINE = $engine
284 PARTITION BY HASH (a)
285 (PARTITION parta $part_optA,
286  PARTITION partB $part_optB,
287  PARTITION Partc $part_optC,
288  PARTITION PartD $part_optD);
289 INSERT INTO tablea VALUES (1), (2), (7), (8), (9), (10);
290 SHOW TABLES;
291 RENAME TABLE TableA to tableA;
292 --sorted_result
293 SELECT * FROM tablea;
294 --sorted_result
295 SELECT * FROM tableA;
296 RENAME TABLE tableA to TableA;
297 SHOW CREATE TABLE tablea;
298 DROP TABLE tablea;
299 }
300 
301 --echo # Test of REMOVE PARTITIONING
302 ALTER TABLE TableA REMOVE PARTITIONING;
303 --sorted_result
304 SELECT * FROM TableA;
305 SHOW CREATE TABLE TableA;
306 
307 --echo # Cleaning up after HASH PARTITIONING test
308 DROP TABLE TableA;
309 
310 
311 --echo # 3.0 RANGE partitioning mgm
312 --echo # Creating RANGE partitioned table
313 eval CREATE TABLE TableA (a INT)
314 ENGINE = $engine
315 PARTITION BY RANGE (a)
316 (PARTITION parta VALUES LESS THAN (4) $part_optA,
317  PARTITION partB VALUES LESS THAN (7) $part_optB,
318  PARTITION Partc VALUES LESS THAN (10) $part_optC,
319  PARTITION PartD VALUES LESS THAN (13) $part_optD);
320 INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10);
321 INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12);
322 --sorted_result
323 SELECT * FROM TableA;
324 
325 --echo # Test of ADD/DROP PARTITIONS
326 --echo # expecting duplicate partition name
327 --error ER_SAME_NAME_PARTITION
328 ALTER TABLE TableA ADD PARTITION
329 (PARTITION partA VALUES LESS THAN (MAXVALUE));
330 ALTER TABLE TableA ADD PARTITION
331 (PARTITION partE VALUES LESS THAN (16),
332  PARTITION Partf VALUES LESS THAN (19),
333  PARTITION PartG VALUES LESS THAN (22));
334 --sorted_result
335 SELECT * FROM TableA;
336 SHOW CREATE TABLE TableA;
337 ALTER TABLE TableA DROP PARTITION partE, PartG;
338 ALTER TABLE TableA DROP PARTITION Partf;
339 ALTER TABLE TableA ADD PARTITION
340 (PARTITION PartE VALUES LESS THAN (MAXVALUE));
341 --sorted_result
342 SELECT * FROM TableA;
343 SHOW CREATE TABLE TableA;
344 
345 --echo # Test of REORGANIZE PARTITIONS
346 --echo # Error since it must reorganize a consecutive range
347 --error ER_CONSECUTIVE_REORG_PARTITIONS
348 eval ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO
349 (PARTITION partB VALUES LESS THAN (3) $part_optA,
350  PARTITION parta VALUES LESS THAN (11) $part_optC);
351 eval ALTER TABLE TableA REORGANIZE PARTITION partB,Partc,PartD,PartE INTO
352 (PARTITION partD VALUES LESS THAN (8) $part_optB
353  COMMENT="Previously partB and partly Partc",
354  PARTITION partB VALUES LESS THAN (11) $part_optC
355  COMMENT="Previously partly Partc and partly PartD",
356  PARTITION partC VALUES LESS THAN (MAXVALUE) $part_optD
357  COMMENT="Previously partly PartD");
358 if ($fixed_bug20129)
359 {
360 ALTER TABLE TableA ANALYZE PARTITION parta, partB, Partc;
361 ALTER TABLE TableA CHECK PARTITION parta, partB, Partc;
362 ALTER TABLE TableA OPTIMIZE PARTITION parta, partB, Partc;
363 ALTER TABLE TableA REPAIR PARTITION parta, partB, Partc;
364 }
365 --sorted_result
366 SELECT * FROM TableA;
367 SHOW CREATE TABLE TableA;
368 
369 --echo # Test of RENAME TABLE
370 RENAME TABLE TableA to TableB;
371 --sorted_result
372 SELECT * FROM TableB;
373 RENAME TABLE TableB to TableA;
374 --sorted_result
375 SELECT * FROM TableA;
376 
377 --echo # Checking name comparision Upper vs Lower case
378 --echo # Error if lower_case_table_names != 0
379 let $lower_case_table_names= `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'lower_case_table_names'`;
380 --echo # lower_case_table_names: $lower_case_table_names
381 if ($lower_case_table_names)
382 {
383 --error ER_TABLE_EXISTS_ERROR
384 eval CREATE TABLE tablea (a INT)
385 ENGINE = $engine
386 PARTITION BY RANGE (a)
387 (PARTITION parta VALUES LESS THAN (4) $part_optA,
388  PARTITION partB VALUES LESS THAN (7) $part_optB,
389  PARTITION Partc VALUES LESS THAN (10) $part_optC,
390  PARTITION PartD VALUES LESS THAN (13) $part_optD);
391 SHOW TABLES;
392 --error ER_TABLE_EXISTS_ERROR
393 RENAME TABLE TableA to tablea;
394 --error ER_TABLE_EXISTS_ERROR
395 RENAME TABLE tablea to TableA;
396 --sorted_result
397 SELECT * FROM tablea;
398 SHOW CREATE TABLE tablea;
399 }
400 if (!$lower_case_table_names)
401 {
402 eval CREATE TABLE tablea (a INT)
403 ENGINE = $engine
404 PARTITION BY RANGE (a)
405 (PARTITION parta VALUES LESS THAN (4) $part_optA,
406  PARTITION partB VALUES LESS THAN (7) $part_optB,
407  PARTITION Partc VALUES LESS THAN (10) $part_optC,
408  PARTITION PartD VALUES LESS THAN (13) $part_optD);
409 INSERT INTO tablea VALUES (1), (2), (7), (8), (9), (10);
410 SHOW TABLES;
411 RENAME TABLE TableA to tableA;
412 --sorted_result
413 SELECT * FROM tablea;
414 --sorted_result
415 SELECT * FROM tableA;
416 RENAME TABLE tableA to TableA;
417 SHOW CREATE TABLE tablea;
418 DROP TABLE tablea;
419 }
420 
421 --echo # Test of REMOVE PARTITIONING
422 ALTER TABLE TableA REMOVE PARTITIONING;
423 --sorted_result
424 SELECT * FROM TableA;
425 SHOW CREATE TABLE TableA;
426 
427 --echo # Cleaning up after RANGE PARTITIONING test
428 DROP TABLE TableA;
429 
430 --echo # 4.0 LIST partitioning mgm
431 --echo # Creating LIST partitioned table
432 eval CREATE TABLE TableA (a INT)
433 ENGINE = $engine
434 PARTITION BY LIST (a)
435 (PARTITION parta VALUES IN (1,8,9) $part_optA,
436  PARTITION partB VALUES IN (2,10,11) $part_optB,
437  PARTITION Partc VALUES IN (3,4,7) $part_optC,
438  PARTITION PartD VALUES IN (5,6,12) $part_optD);
439 INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10);
440 INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12);
441 --sorted_result
442 SELECT * FROM TableA;
443 
444 --echo # Test of ADD/DROP PARTITIONS
445 --echo # expecting duplicate partition name
446 --error ER_SAME_NAME_PARTITION
447 ALTER TABLE TableA ADD PARTITION
448 (PARTITION partA VALUES IN (0));
449 ALTER TABLE TableA ADD PARTITION
450 (PARTITION partE VALUES IN (16),
451  PARTITION Partf VALUES IN (19),
452  PARTITION PartG VALUES IN (22));
453 --sorted_result
454 SELECT * FROM TableA;
455 SHOW CREATE TABLE TableA;
456 ALTER TABLE TableA DROP PARTITION partE, PartG;
457 ALTER TABLE TableA DROP PARTITION Partf;
458 ALTER TABLE TableA ADD PARTITION
459 (PARTITION PartE VALUES IN (13));
460 --sorted_result
461 SELECT * FROM TableA;
462 SHOW CREATE TABLE TableA;
463 
464 --echo # Test of REORGANIZE PARTITIONS
465 --error ER_CONSECUTIVE_REORG_PARTITIONS
466 eval ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO
467 (PARTITION Partc VALUES IN (1,7) $part_optA
468  COMMENT = "Mix 1 of old parta and Partc",
469  PARTITION partF VALUES IN (3,9) $part_optC
470  COMMENT = "Mix 2 of old parta and Partc",
471  PARTITION parta VALUES IN (4,8) $part_optC
472  COMMENT = "Mix 3 of old parta and Partc");
473 eval ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO
474 (PARTITION Partc VALUES IN (1,7) $part_optA
475  COMMENT = "Mix 1 of old parta and Partc",
476  PARTITION parta VALUES IN (3,9) $part_optC
477  COMMENT = "Mix 2 of old parta and Partc",
478  PARTITION partB VALUES IN (4,8) $part_optC
479  COMMENT = "Mix 3 of old parta and Partc");
480 if ($fixed_bug20129)
481 {
482 ALTER TABLE TableA ANALYZE PARTITION parta, partB, Partc;
483 ALTER TABLE TableA CHECK PARTITION parta, partB, Partc;
484 ALTER TABLE TableA OPTIMIZE PARTITION parta, partB, Partc;
485 ALTER TABLE TableA REPAIR PARTITION parta, partB, Partc;
486 }
487 --sorted_result
488 SELECT * FROM TableA;
489 SHOW CREATE TABLE TableA;
490 
491 --echo # Test of RENAME TABLE
492 RENAME TABLE TableA to TableB;
493 --sorted_result
494 SELECT * FROM TableB;
495 RENAME TABLE TableB to TableA;
496 --sorted_result
497 SELECT * FROM TableA;
498 
499 --echo # Checking name comparision Upper vs Lower case
500 --echo # Error if lower_case_table_names != 0
501 let $lower_case_table_names= `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'lower_case_table_names'`;
502 --echo # lower_case_table_names: $lower_case_table_names
503 if ($lower_case_table_names)
504 {
505 --error ER_TABLE_EXISTS_ERROR
506 eval CREATE TABLE tablea (a INT)
507 ENGINE = $engine
508 PARTITION BY LIST (a)
509 (PARTITION parta VALUES IN (1,8,9) $part_optA,
510  PARTITION partB VALUES IN (2,10,11) $part_optB,
511  PARTITION Partc VALUES IN (3,4,7) $part_optC,
512  PARTITION PartD VALUES IN (5,6,12) $part_optD);
513 SHOW TABLES;
514 --error ER_TABLE_EXISTS_ERROR
515 RENAME TABLE TableA to tablea;
516 --error ER_TABLE_EXISTS_ERROR
517 RENAME TABLE tablea to TableA;
518 --sorted_result
519 SELECT * FROM tablea;
520 SHOW CREATE TABLE tablea;
521 }
522 if (!$lower_case_table_names)
523 {
524 eval CREATE TABLE tablea (a INT)
525 ENGINE = $engine
526 PARTITION BY LIST (a)
527 (PARTITION parta VALUES IN (1,8,9) $part_optA,
528  PARTITION partB VALUES IN (2,10,11) $part_optB,
529  PARTITION Partc VALUES IN (3,4,7) $part_optC,
530  PARTITION PartD VALUES IN (5,6,12) $part_optD);
531 INSERT INTO tablea VALUES (1), (2), (7), (8), (9), (10);
532 SHOW TABLES;
533 RENAME TABLE TableA to tableA;
534 --sorted_result
535 SELECT * FROM tablea;
536 --sorted_result
537 SELECT * FROM tableA;
538 RENAME TABLE tableA to TableA;
539 SHOW CREATE TABLE tablea;
540 DROP TABLE tablea;
541 }
542 
543 --echo # Test of REMOVE PARTITIONING
544 ALTER TABLE TableA REMOVE PARTITIONING;
545 --sorted_result
546 SELECT * FROM TableA;
547 SHOW CREATE TABLE TableA;
548 
549 --echo # Cleaning up after LIST PARTITIONING test
550 DROP TABLE TableA;
551 }
552 # End of $can_only_key
553 
554 if ($no_truncate)
555 {
556 --echo # Verify that TRUNCATE PARTITION gives error
557 eval CREATE TABLE t1
558 (a BIGINT AUTO_INCREMENT PRIMARY KEY,
559  b VARCHAR(255))
560 ENGINE = $engine
561 PARTITION BY KEY (a)
562 (PARTITION LT1000,
563  PARTITION LT2000,
564  PARTITION MAX);
565 INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX");
566 --error ER_PARTITION_MGMT_ON_NONPARTITIONED, ER_ILLEGAL_HA
567 ALTER TABLE t1 TRUNCATE PARTITION MAX;
568 --sorted_result
569 SELECT * FROM t1;
570 }
571 if (!$no_truncate)
572 {
573 --echo # Testing TRUNCATE PARTITION
574 eval CREATE TABLE t1
575 (a BIGINT AUTO_INCREMENT PRIMARY KEY,
576  b VARCHAR(255))
577 ENGINE = $engine
578 PARTITION BY RANGE (a)
579 (PARTITION LT1000 VALUES LESS THAN (1000),
580  PARTITION LT2000 VALUES LESS THAN (2000),
581  PARTITION MAX VALUES LESS THAN MAXVALUE);
582 INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX");
583 SHOW CREATE TABLE t1;
584 SELECT * FROM t1 ORDER BY a;
585 ALTER TABLE t1 ANALYZE PARTITION MAX;
586 --echo # Truncate without FLUSH
587 ALTER TABLE t1 TRUNCATE PARTITION MAX;
588 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (1)");
589 SELECT * FROM t1 WHERE a >= 2000;
590 --echo # Truncate with FLUSH after
591 ALTER TABLE t1 TRUNCATE PARTITION MAX;
592 FLUSH TABLES;
593 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (2)");
594 SELECT * FROM t1 WHERE a >= 2000;
595 --echo # Truncate with FLUSH before
596 FLUSH TABLES;
597 ALTER TABLE t1 TRUNCATE PARTITION MAX;
598 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (3)");
599 SELECT * FROM t1 WHERE a >= 2000;
600 --echo # Truncate with FLUSH after INSERT
601 FLUSH TABLES;
602 ALTER TABLE t1 TRUNCATE PARTITION MAX;
603 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (4)");
604 SELECT * FROM t1 WHERE a >= 2000;
605 --echo # Truncate without FLUSH
606 ALTER TABLE t1 TRUNCATE PARTITION LT1000;
607 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (1)");
608 SELECT * FROM t1 ORDER BY a;
609 --echo # Truncate with FLUSH after
610 ALTER TABLE t1 TRUNCATE PARTITION LT1000;
611 FLUSH TABLES;
612 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (2)");
613 SELECT * FROM t1 ORDER BY a;
614 --echo # Truncate with FLUSH before
615 FLUSH TABLES;
616 ALTER TABLE t1 TRUNCATE PARTITION LT1000;
617 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (3)");
618 SELECT * FROM t1 ORDER BY a;
619 --echo # Truncate with FLUSH after INSERT
620 FLUSH TABLES;
621 ALTER TABLE t1 TRUNCATE PARTITION LT1000;
622 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (4)");
623 SELECT * FROM t1 ORDER BY a;
624 --echo # Truncate without FLUSH
625 ALTER TABLE t1 TRUNCATE PARTITION LT2000;
626 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (1)");
627 SELECT * FROM t1 ORDER BY a;
628 --echo # Truncate with FLUSH after
629 ALTER TABLE t1 TRUNCATE PARTITION LT2000;
630 FLUSH TABLES;
631 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (2)");
632 SELECT * FROM t1 ORDER BY a;
633 --echo # Truncate with FLUSH before
634 FLUSH TABLES;
635 ALTER TABLE t1 TRUNCATE PARTITION LT2000;
636 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (3)");
637 SELECT * FROM t1 ORDER BY a;
638 --echo # Truncate with FLUSH after INSERT
639 FLUSH TABLES;
640 ALTER TABLE t1 TRUNCATE PARTITION LT2000;
641 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (4)");
642 SELECT * FROM t1 ORDER BY a;
643 DROP TABLE t1;
644 }
645 --echo # Cleaning up before exit
646 eval USE $old_db;
647 DROP DATABASE MySQL_Test_DB;