MySQL Bugs: #31585: ALTER TABLE ... DROP PARTITION loses data. No ability to archive data.

Bug #31585	ALTER TABLE ... DROP PARTITION loses data. No ability to archive data.
Submitted:	14 Oct 2007 4:54	Modified:	9 Aug 2010 10:47
Reporter:	Kevin Burton	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	partitioning

Description:
Partitioning has no way to take a partition offline while preserving the underlying data.  ALTER TABLE DROP PARTITION will delete the underlying file and table.

The data is stored in an internal INNODB or MyISAM table not visible to the MySQL process except through the partitioned table.

While mysqldump can take a backup of this partition it's an expensive operation.

MySQL should provide a mechanism to take a partition offline from the production table yet still preserve the underlying.

Ideally it would make the table available via another name for direct access.

A good example for this functionality would be storing log files in MySQL.

If one partitions on a DATETIME column with a new partition for each day then the DB server will eventually fill up and/or SELECTs will slow down.

Taking data offline this way would allow for historical/archive queries but still allow the table to keep a good amount of data online at any give point.

How to repeat:
Create a data with four partitions, insert data linearly across the partitions, drop one of the partitions, watch 25% of your data vanish.

Suggested fix:
Add a new command to MySQL named

ALTER TABLE ARCHIVE PARTITION;

which takes the underlying partition, removes it from the table definition, and makes it available as a table within MySQL.

This table can then be kept out of production, backed up, use used for selected maintenance.

Thank you for the reasonable feature request.

Now you can use --where switch for mysqldump command to archive data from specified partition.

I think this would be solved by implementing WL#4445, 'ALTER TABLE t1 EXCHANGE PARTITION p WITH TABLE t2'. That way one can exchange a partition with a table, drop the partition, and archive table t2.

Closing the bug since WL#4444 has been pushed to mysql-trunk. I.e. adding support for archiving data by 'ALTER TABLE tp EXCHANGE PARTITION p WITH TABLE t'.