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
-
Activity
The fix makes perfect sense to me. If the DEFAULT partition is dropped, then you lose its data and the ability to store values for which there is no explicitly defined partition. This is correct behavior. Good work.
revision-id: 1c60f292874f3b31580e6da27f0d1c74c3eccc80 (mariadb-10.2.4-122-g1c60f29)
parent(s): 6d417a0bad205a6bacfee10dbc46dd631b093e75
committer: Oleksandr Byelkin
timestamp: 2017-04-04 14:47:58 +0200
message:
MDEV-12395: DROP PARTITION does not work as expected when table has DEFAULT LIST partitionData loss in case of partituon removing is documented => do not try to prevent it
—