Debian Codegraph @

mysql-test/suite/parts/inc/partition_mgm.inc

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