Bug #41210 cannot truncate a partition individually
Submitted: 3 Dec 2008 18:43 Modified: 14 Dec 2008 23:15
Reporter: Duc Le Email Updates:
Status: Duplicate Impact on me:
Category:MySQL Server: Partitions Severity:S4 (Feature request)
Version:5.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: partitioning truncate

[3 Dec 2008 18:43] Duc Le
Currently not able to truncate an individual partition of a table (as supported by Oracle db).  Truncate for table is allowed however.  This function is useful because dropping and re-adding of partitions is non-trivial.  For example re-adding a partition to a range partitioned table has rule that can only add to the upper range only.  So ideally, would like to leave the partitions in place and just truncate/wipe-out the data similar to how truncate of a table works.  This seems like a very simple update/feature to implement, but is extremely, extremely useful.

How to repeat:
cannot run this command currently:

alter table statuses truncate partition p01,p02;

Suggested fix:
allow truncate of partition similar to:

alter table statuses truncate partition p01,p02;
[3 Dec 2008 19:00] Duc Le
i tried a workaround of using reorganize partition to split the partition into 2 pieces:  for example if the range for partition p001 was previously 70022, then I would split into partition p001temp (70021) and p001 (70022), then I would drop the partition.  However, I'm finding that the reorganize of partition is becoming a performance bottleneck and is not going to meet the requirement for fast deletion of millions of records.
[14 Dec 2008 23:15] MySQL Verification Team
Duplicate of bug: http://bugs.mysql.com/bug.php?id=19405.