| Bug #19405 | No way to truncate a partition | ||
|---|---|---|---|
| Submitted: | 27 Apr 2006 17:33 | Modified: | 15 Nov 2009 13:49 |
| Reporter: | Partha Dutta | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S4 (Feature request) |
| Version: | 5.1.X | OS: | Any |
| Assigned to: | Mattias Jonsson | CPU Architecture: | Any |
[27 Apr 2006 17:33]
Partha Dutta
[28 Apr 2006 17:05]
Valeriy Kravchuk
Looks like a reasonable feature request for me. Do you agree?
[21 Feb 2008 22:50]
Chris Daniel
I also would like to see ALTER TABLE TRUNCATE PARTITION. How complex is this? Can we expect to see it before the 5.1 release?
[27 Aug 2008 8:56]
Mikael Ronström
This is in the plans, see WL#4444 for progress notes
[14 Dec 2008 23:17]
MySQL Verification Team
Bug: http://bugs.mysql.com/bug.php?id=41210 marked as duplicate of this one.
[31 Dec 2008 15:22]
Aner Mazursky
the plan link seems to be broken. Is there a working plan? when is the ticket due?
[26 Mar 2009 8:40]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/70482 2773 Mattias Jonsson 2009-03-26 WL#4444: TRUNCATE PARTITION support Bug#19405: No way to truncate a partition Added support for ALTER TABLE t TRUNCATE PARTITION (p0) The code path is the same as for TRUNCATE TABLE, i.e. using mysql_truncate. This also uses the fact that a partitioned table does not support HA_CAN_RECREATE, i.e. a truncate will not just recreate the table, but try to execute delete_all_rows for the handler, and if that is not supported it falls back to row by row deletion, but it is currently supported by the main engines (MyISAM, Memory, InnoDB). To be able to use delete_all_rows for Memory and MyISAM reset_auto_increment was also added to their handlers (see bug-35111). Since NDB does native partitioning, it does not support TRUNCATE PARTITION, this is done by only allowing TRUNCATE PARTITION if it uses the partitioning handler. @ mysql-test/suite/parts/inc/partition_mgm.inc WL#4444: TRUNCATE PARTITION support Added tests for TRUNCATE PARTITION @ mysql-test/suite/parts/r/partition_auto_increment_memory.result WL#4444: TRUNCATE PARTITION support Bug-35111: Truncate a MyISAM partitioned table does not reset the auto_increment value Updated with correct result @ mysql-test/suite/parts/r/partition_auto_increment_myisam.result WL#4444: TRUNCATE PARTITION support Bug-35111: Truncate a MyISAM partitioned table does not reset the auto_increment value Updated with correct result @ mysql-test/suite/parts/r/partition_mgm_lc0_archive.result WL#4444: TRUNCATE PARTITION support Added result for TRUNCATE PARTITION @ mysql-test/suite/parts/r/partition_mgm_lc0_innodb.result WL#4444: TRUNCATE PARTITION support Added result for TRUNCATE PARTITION @ mysql-test/suite/parts/r/partition_mgm_lc0_memory.result WL#4444: TRUNCATE PARTITION support Added result for TRUNCATE PARTITION @ mysql-test/suite/parts/r/partition_mgm_lc0_myisam.result WL#4444: TRUNCATE PARTITION support Added result for TRUNCATE PARTITION @ mysql-test/suite/parts/r/partition_mgm_lc0_ndb.result WL#4444: TRUNCATE PARTITION support Added result for TRUNCATE PARTITION @ mysql-test/suite/parts/r/partition_mgm_lc1_archive.result WL#4444: TRUNCATE PARTITION support Added result for TRUNCATE PARTITION @ mysql-test/suite/parts/r/partition_mgm_lc1_innodb.result WL#4444: TRUNCATE PARTITION support Added result for TRUNCATE PARTITION @ mysql-test/suite/parts/r/partition_mgm_lc1_memory.result WL#4444: TRUNCATE PARTITION support Added result for TRUNCATE PARTITION @ mysql-test/suite/parts/r/partition_mgm_lc1_myisam.result WL#4444: TRUNCATE PARTITION support Added result for TRUNCATE PARTITION @ mysql-test/suite/parts/r/partition_mgm_lc1_ndb.result WL#4444: TRUNCATE PARTITION support Added result for TRUNCATE PARTITION @ mysql-test/suite/parts/r/partition_mgm_lc2_archive.result WL#4444: TRUNCATE PARTITION support Added result for TRUNCATE PARTITION @ mysql-test/suite/parts/r/partition_mgm_lc2_innodb.result WL#4444: TRUNCATE PARTITION support Added result for TRUNCATE PARTITION @ mysql-test/suite/parts/r/partition_mgm_lc2_memory.result WL#4444: TRUNCATE PARTITION support Added result for TRUNCATE PARTITION @ mysql-test/suite/parts/r/partition_mgm_lc2_myisam.result WL#4444: TRUNCATE PARTITION support Added result for TRUNCATE PARTITION @ mysql-test/suite/parts/r/partition_mgm_lc2_ndb.result WL#4444: TRUNCATE PARTITION support Added result for TRUNCATE PARTITION @ mysql-test/suite/parts/t/partition_mgm_lc0_archive.test WL#4444: TRUNCATE PARTITION support Added variable for not supporting TRUNCATE PARTITION @ mysql-test/suite/parts/t/partition_mgm_lc0_ndb.test WL#4444: TRUNCATE PARTITION support Added variable for not supporting TRUNCATE PARTITION @ mysql-test/suite/parts/t/partition_mgm_lc1_archive.test WL#4444: TRUNCATE PARTITION support Added variable for not supporting TRUNCATE PARTITION @ mysql-test/suite/parts/t/partition_mgm_lc1_ndb.test WL#4444: TRUNCATE PARTITION support Added variable for not supporting TRUNCATE PARTITION @ mysql-test/suite/parts/t/partition_mgm_lc2_archive.test WL#4444: TRUNCATE PARTITION support Added variable for not supporting TRUNCATE PARTITION @ mysql-test/suite/parts/t/partition_mgm_lc2_ndb.test WL#4444: TRUNCATE PARTITION support Added variable for not supporting TRUNCATE PARTITION @ sql/ha_partition.cc WL#4444: TRUNCATE PARTITION support Using delete_all_rows for TRUNCATE PARTITION. also does pruning as a safety in case of errors, so that it is correctly pruned when falling back on row-by-row deletion. Also added reset_auto_increment in case the partitions engine does not do that in delete_all_rows when called through TRUNCATE. @ sql/partition_element.h WL#4444: TRUNCATE PARTITION support Added enum PART_ADMIN for used togather with ALTER_ADMIN_PARTITION Alter_info flag. (ANALYZE/CHECK/OPTIMIZE/REPAIR/TRUNCATE PARTITION) @ sql/sql_delete.cc WL#4444: TRUNCATE PARTITION support Ensuring that TRUNCATE PARTITION only works with the partitioning handler (not even for engines with native partitioning) @ sql/sql_partition.cc WL#4444: TRUNCATE PARTITION support Minor fix to reset the state of the partition to avoid possible bug. @ sql/sql_table.cc WL#4444: TRUNCATE PARTITION support Using PART_ADMIN instead of PART_CHANGED for ALTER_ADMIN_PARTITION @ sql/sql_yacc.yy WL#4444: TRUNCATE PARTITION support Added syntax for ALTER TABLE t TRUNCATE PARTITION minor typos fixes to following the code guidelines better. @ storage/heap/ha_heap.cc WL#4444: TRUNCATE PARTITION support Added reset_auto_increment to support TRUNCATE through the partitioning handler @ storage/heap/ha_heap.h WL#4444: TRUNCATE PARTITION support Added reset_auto_increment to support TRUNCATE through the partitioning handler @ storage/myisam/ha_myisam.cc WL#4444: TRUNCATE PARTITION support Added reset_auto_increment to support TRUNCATE through the partitioning handler @ storage/myisam/ha_myisam.h WL#4444: TRUNCATE PARTITION support Added reset_auto_increment to support TRUNCATE through the partitioning handler
[11 May 2009 11:43]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/73749 2847 Mikael Ronstrom 2009-05-08 WL#4444 Added TRUNCATE partition support, fixes bug#19405 and bug#35111 modified: mysql-test/suite/parts/inc/partition_mgm.inc mysql-test/suite/parts/r/partition_auto_increment_memory.result mysql-test/suite/parts/r/partition_auto_increment_myisam.result mysql-test/suite/parts/r/partition_mgm_lc0_archive.result mysql-test/suite/parts/r/partition_mgm_lc0_innodb.result mysql-test/suite/parts/r/partition_mgm_lc0_memory.result mysql-test/suite/parts/r/partition_mgm_lc0_myisam.result mysql-test/suite/parts/r/partition_mgm_lc0_ndb.result mysql-test/suite/parts/r/partition_mgm_lc1_archive.result mysql-test/suite/parts/r/partition_mgm_lc1_innodb.result mysql-test/suite/parts/r/partition_mgm_lc1_memory.result mysql-test/suite/parts/r/partition_mgm_lc1_myisam.result mysql-test/suite/parts/r/partition_mgm_lc1_ndb.result mysql-test/suite/parts/r/partition_mgm_lc2_archive.result mysql-test/suite/parts/r/partition_mgm_lc2_innodb.result mysql-test/suite/parts/r/partition_mgm_lc2_memory.result mysql-test/suite/parts/r/partition_mgm_lc2_myisam.result mysql-test/suite/parts/r/partition_mgm_lc2_ndb.result mysql-test/suite/parts/t/partition_mgm_lc0_archive.test mysql-test/suite/parts/t/partition_mgm_lc0_ndb.test mysql-test/suite/parts/t/partition_mgm_lc1_archive.test mysql-test/suite/parts/t/partition_mgm_lc1_ndb.test mysql-test/suite/parts/t/partition_mgm_lc2_archive.test mysql-test/suite/parts/t/partition_mgm_lc2_ndb.test sql/ha_partition.cc sql/partition_element.h sql/sql_delete.cc sql/sql_partition.cc sql/sql_table.cc sql/sql_yacc.yy storage/heap/ha_heap.cc storage/heap/ha_heap.h storage/myisam/ha_myisam.cc storage/myisam/ha_myisam.h
[21 Jul 2009 15:42]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/79100 2826 Mikael Ronstrom 2009-07-21 WL#4444 Added TRUNCATE partition support, fixes bug#19405 and bug#35111 modified: mysql-test/suite/parts/inc/partition_mgm.inc mysql-test/suite/parts/r/partition_mgm_lc0_archive.result mysql-test/suite/parts/r/partition_mgm_lc0_innodb.result mysql-test/suite/parts/r/partition_mgm_lc0_memory.result mysql-test/suite/parts/r/partition_mgm_lc0_myisam.result mysql-test/suite/parts/r/partition_mgm_lc0_ndb.result mysql-test/suite/parts/r/partition_mgm_lc1_archive.result mysql-test/suite/parts/r/partition_mgm_lc1_innodb.result mysql-test/suite/parts/r/partition_mgm_lc1_memory.result mysql-test/suite/parts/r/partition_mgm_lc1_myisam.result mysql-test/suite/parts/r/partition_mgm_lc1_ndb.result mysql-test/suite/parts/r/partition_mgm_lc2_archive.result mysql-test/suite/parts/r/partition_mgm_lc2_innodb.result mysql-test/suite/parts/r/partition_mgm_lc2_memory.result mysql-test/suite/parts/r/partition_mgm_lc2_myisam.result mysql-test/suite/parts/r/partition_mgm_lc2_ndb.result mysql-test/suite/parts/t/partition_mgm_lc0_archive.test mysql-test/suite/parts/t/partition_mgm_lc0_ndb.test mysql-test/suite/parts/t/partition_mgm_lc1_archive.test mysql-test/suite/parts/t/partition_mgm_lc1_ndb.test mysql-test/suite/parts/t/partition_mgm_lc2_archive.test mysql-test/suite/parts/t/partition_mgm_lc2_ndb.test sql/ha_partition.cc sql/partition_element.h sql/sql_delete.cc sql/sql_partition.cc sql/sql_table.cc sql/sql_yacc.yy
[18 Aug 2009 20:03]
Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090818200211-fx1sq0284n261fb3) (version source revid:mikael@mysql.com-20090721154436-z3jtxoycg9a68h58) (merge vers: 5.4.4-alpha) (pib:11)
[10 Sep 2009 9:09]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/82901 2877 Mikael Ronstrom 2009-09-10 WL#4444 Added TRUNCATE partition support, fixes bug#19405 and bug #35111 added: mysql-test/r/partition_truncate.result mysql-test/t/partition_truncate.test modified: BUILD/build_mccge.sh mysql-test/suite/parts/inc/partition_mgm.inc mysql-test/suite/parts/r/partition_mgm_lc0_archive.result mysql-test/suite/parts/r/partition_mgm_lc0_innodb.result mysql-test/suite/parts/r/partition_mgm_lc0_memory.result mysql-test/suite/parts/r/partition_mgm_lc0_myisam.result mysql-test/suite/parts/r/partition_mgm_lc0_ndb.result mysql-test/suite/parts/r/partition_mgm_lc1_archive.result mysql-test/suite/parts/r/partition_mgm_lc1_innodb.result mysql-test/suite/parts/r/partition_mgm_lc1_memory.result mysql-test/suite/parts/r/partition_mgm_lc1_myisam.result mysql-test/suite/parts/r/partition_mgm_lc1_ndb.result mysql-test/suite/parts/r/partition_mgm_lc2_archive.result mysql-test/suite/parts/r/partition_mgm_lc2_innodb.result mysql-test/suite/parts/r/partition_mgm_lc2_memory.result mysql-test/suite/parts/r/partition_mgm_lc2_myisam.result mysql-test/suite/parts/r/partition_mgm_lc2_ndb.result mysql-test/suite/parts/t/partition_mgm_lc0_archive.test mysql-test/suite/parts/t/partition_mgm_lc0_ndb.test mysql-test/suite/parts/t/partition_mgm_lc1_archive.test mysql-test/suite/parts/t/partition_mgm_lc1_ndb.test mysql-test/suite/parts/t/partition_mgm_lc2_archive.test mysql-test/suite/parts/t/partition_mgm_lc2_ndb.test sql/ha_partition.cc sql/handler.cc sql/partition_element.h sql/sql_delete.cc sql/sql_partition.cc sql/sql_table.cc sql/sql_yacc.yy
[30 Sep 2009 8:17]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20090929093622-1mooerbh12e97zux) (version source revid:alik@sun.com-20090929093622-1mooerbh12e97zux) (merge vers: 6.0.14-alpha) (pib:11)
[13 Oct 2009 14:53]
Eyal Sorek
Hi, Is this patch planned to be pushed to any 5.1.x version ? Thanks, Eyal Sorek
[13 Oct 2009 15:51]
Mattias Jonsson
This is implemented by WL#4444, and as it seems right now it will be in 5.4
[12 Nov 2009 8:16]
Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:alik@sun.com-20091002121459-pvg0cs6lt298xo83) (merge vers: 5.4.5-beta) (pib:13)
[12 Nov 2009 14:28]
Mattias Jonsson
Pushed into 5.5 as WL#4444.
[15 Nov 2009 13:49]
Jon Stephens
(a) The feature change this fix imparts appears for the first time in 5.5.0, I consider this in the "Doesn't appear in any release category"; (b) Docs work associated with the change is already covered under WL#4444 (which is my task to document); Therefore, I am closing this bug without adding a changelog entry.
[29 Nov 2009 12:07]
Jon Stephens
ALTER TABLE ... TRUNCATE has been pushed into 5.5.0, and documentation sources have been updated with info relating to this. The syntax is: ALTER TABLE table TRUNCATE PARTITION partition-list partition-list: partition-name[,partition-name[,...]] You can see that the rows were dropped by querying the INFORMATION_SCHEMA.PARTITIONS table, e.g.: SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'mytable'; Documentation will appear online in http://dev.mysql.com/doc/refman/5.5/en/alter-table.html and http://dev.mysql.com/doc/refman/5.5/en/partitioning-maintenance.html when the MySQL 5.5 Manual is published in conjunction with the MySQL 5.5.0 release.
