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