Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2(EOL)
Description
DROP PARTITION x is supposed to delete all data stored in the partition x. It is documented, for example, in MySQL manual, and probably hidden or implied here in the KB (for a note, there could be a better documentation for partition maintenance).
Normally, it works this way – with RANGE partitioning, whether or not the MAXVALUE partition is involved.
MariaDB [test]> CREATE TABLE trange (i INT) PARTITION BY RANGE (i) (PARTITION p VALUES LESS THAN (5), PARTITION pmax VALUES LESS THAN (MAXVALUE)); |
Query OK, 0 rows affected (0.45 sec) |
|
|
MariaDB [test]> INSERT INTO trange VALUES (1),(10); |
Query OK, 2 rows affected (0.04 sec) |
Records: 2 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> ALTER TABLE trange DROP PARTITION p; |
Query OK, 0 rows affected (0.31 sec) |
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> SELECT * FROM trange; |
+------+ |
| i |
|
+------+ |
| 10 |
|
+------+ |
1 row in set (0.00 sec) |
It also works with LIST partitioning when the table only has "normal" list partitions:
MariaDB [test]> CREATE TABLE tlist (i INT) PARTITION BY LIST (i) (PARTITION p VALUES IN (1,2,3,4), PARTITION p2 VALUES IN (10)); |
Query OK, 0 rows affected (0.48 sec) |
|
|
MariaDB [test]> INSERT INTO tlist VALUES (1),(10); |
Query OK, 2 rows affected (0.06 sec) |
Records: 2 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> ALTER TABLE tlist DROP PARTITION p; |
Query OK, 0 rows affected (0.35 sec) |
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> SELECT * FROM tlist; |
+------+ |
| i |
|
+------+ |
| 10 |
|
+------+ |
1 row in set (0.00 sec) |
But it does not work when a LIST-partitioned table has the DEFAULT-partition.
In this case, when a "normal" partition is dropped, values are transferred to the default partition:
MariaDB [test]> CREATE TABLE tlist (i INT) PARTITION BY LIST (i) (PARTITION p VALUES IN (1,2,3,4), PARTITION pdef DEFAULT); |
Query OK, 0 rows affected (0.42 sec) |
|
|
MariaDB [test]> INSERT INTO tlist VALUES (1),(10); |
Query OK, 2 rows affected (0.05 sec) |
Records: 2 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> ALTER TABLE tlist DROP PARTITION p; |
Query OK, 2 rows affected (1.24 sec) |
Records: 2 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> SELECT * FROM tlist; |
+------+ |
| i |
|
+------+ |
| 1 |
|
| 10 |
|
+------+ |
2 rows in set (0.00 sec) |
And if the default partition is being dropped, it causes an error:
MariaDB [test]> CREATE TABLE tlist (i INT) PARTITION BY LIST (i) (PARTITION p VALUES IN (1,2,3,4), PARTITION pdef DEFAULT); |
Query OK, 0 rows affected (0.40 sec) |
|
|
MariaDB [test]> INSERT INTO tlist VALUES (1),(10); |
Query OK, 2 rows affected (0.05 sec) |
Records: 2 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> ALTER TABLE tlist DROP PARTITION pdef; |
ERROR 1526 (HY000): Table has no partition for value 10 |
Attachments
Issue Links
- relates to
-
MDEV-8348 Add catchall to all table partitioning for list partitions
-
- Closed
-