WL#4444: TRUNCATE PARTITION support
Affects: Server-5.5 — Status: Complete — Priority: Medium
Implement support for TRUNCATE PARTITION. See also: BUG#19405 No way to truncate a partition BUG#23603 Partitions: reorganize means delete BUG#41210 cannot truncate a partition individually Questions to solve: 1) Should the auto increment value be reset to 0 for the truncated partitions, or be preserved? 2) Should it be supported for NDB as well? Answers after discussion with Mikael Ronström: 1) TRUNCATE TABLE currently resets the auto_increment, so TRUNCATE PARTITION should also reset the auto_increment on that partition, i.e. if the partition has the highest auto_increment value, the auto_increment value will be dropped to the maximum auto_increment value of the non truncated partitions. 2) There is no need to support TRUNCATE PARTITION for NDB, until it supports all the rest of the partitioning handling commands.
Add syntax: ALTER TABLE tbl_name TRUNCATE PARTITION ALL|partition_name[,partition_name...] This delete all rows in the given partitions and resets the given partitions auto_increment values (if exists) to 0. Only partitioning using the partitioning handler is supported (i.e. no support for engines with native partitioning such as NDB).
Use the same code path as TRUNCATE TABLE, but add ALTER_ADMIN_PARTITION to the Alter_flags variable in the lex structure. This flag is checked in mysql_truncate for verifying that it is only used on a non native partitioned table. And in ha_partition::delete_all_rows the flag is used to only delete from the given partitions (and as a safety procedure, set the pruning accordingly, if a failure leads to row by row deletion). To handle the auto_increment value, ha_reset_auto_increment is also called on the truncated partitions. NOTE: to fully support TRUNCATE PARTITION an engine must support delete_all_rows and either do reset of auto_increment value in delete_all_rows if sql_command == SQLCOM_TRUNCATE or support ::reset_auto_increment. The proposed patch works for InnoDB, Memory and MyISAM. Archive does not support TRUNCATE TABLE (since it does not support HTON_CAN_RECREATE and does not support any delete action) and therefore does not support TRUNCATE PARTITION either.
Copyright (c) 2000, 2017, Oracle Corporation and/or its affiliates. All rights reserved.