#At file:///Users/mattiasj/clones/bzrroot/b41210-51-main/ based on revid:alik@stripped
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
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
=== modified file 'mysql-test/suite/parts/inc/partition_mgm.inc'
--- a/mysql-test/suite/parts/inc/partition_mgm.inc 2008-11-05 21:26:39 +0000
+++ b/mysql-test/suite/parts/inc/partition_mgm.inc 2009-03-26 08:39:15 +0000
@@ -13,6 +13,7 @@
# part_optA-D Extra partitioning options (E.g. INDEX/DATA DIR) #
# #
# have_bug33158 NDB case insensitive create, but case sensitive rename #
+# no_truncate No support for truncate partition #
#------------------------------------------------------------------------------#
# Original Author: mattiasj #
# Original Date: 2008-06-27 #
@@ -518,6 +519,95 @@ DROP TABLE TableA;
}
# End of $can_only_key
+if ($no_truncate)
+{
+--echo # Verify that TRUNCATE PARTITION gives error
+eval CREATE TABLE t1
+(a BIGINT AUTO_INCREMENT PRIMARY KEY,
+ b VARCHAR(255))
+ENGINE = $engine
+PARTITION BY KEY (a)
+(PARTITION LT1000,
+ PARTITION LT2000,
+ PARTITION MAX);
+INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX");
+--error ER_PARTITION_MGMT_ON_NONPARTITIONED, ER_ILLEGAL_HA
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+}
+if (!$no_truncate)
+{
+--echo # Testing TRUNCATE PARTITION
+eval CREATE TABLE t1
+(a BIGINT AUTO_INCREMENT PRIMARY KEY,
+ b VARCHAR(255))
+ENGINE = $engine
+PARTITION BY RANGE (a)
+(PARTITION LT1000 VALUES LESS THAN (1000),
+ PARTITION LT2000 VALUES LESS THAN (2000),
+ PARTITION MAX VALUES LESS THAN MAXVALUE);
+INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX");
+SHOW CREATE TABLE t1;
+SELECT * FROM t1 ORDER BY a;
+ALTER TABLE t1 ANALYZE PARTITION MAX;
+--echo # Truncate without FLUSH
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (1)");
+SELECT * FROM t1 WHERE a >= 2000;
+--echo # Truncate with FLUSH after
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+FLUSH TABLES;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (2)");
+SELECT * FROM t1 WHERE a >= 2000;
+--echo # Truncate with FLUSH before
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (3)");
+SELECT * FROM t1 WHERE a >= 2000;
+--echo # Truncate with FLUSH after INSERT
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (4)");
+SELECT * FROM t1 WHERE a >= 2000;
+--echo # Truncate without FLUSH
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (1)");
+SELECT * FROM t1 ORDER BY a;
+--echo # Truncate with FLUSH after
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+FLUSH TABLES;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (2)");
+SELECT * FROM t1 ORDER BY a;
+--echo # Truncate with FLUSH before
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (3)");
+SELECT * FROM t1 ORDER BY a;
+--echo # Truncate with FLUSH after INSERT
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (4)");
+SELECT * FROM t1 ORDER BY a;
+--echo # Truncate without FLUSH
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (1)");
+SELECT * FROM t1 ORDER BY a;
+--echo # Truncate with FLUSH after
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+FLUSH TABLES;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (2)");
+SELECT * FROM t1 ORDER BY a;
+--echo # Truncate with FLUSH before
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (3)");
+SELECT * FROM t1 ORDER BY a;
+--echo # Truncate with FLUSH after INSERT
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (4)");
+SELECT * FROM t1 ORDER BY a;
+DROP TABLE t1;
+}
--echo # Cleaning up before exit
eval USE $old_db;
DROP DATABASE MySQL_Test_DB;
=== modified file 'mysql-test/suite/parts/r/partition_auto_increment_memory.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_memory.result 2009-02-05 17:47:24 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_memory.result 2009-03-26 08:39:15 +0000
@@ -381,12 +381,12 @@ Table Create Table
t1 CREATE TABLE `t1` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`c1`)
-) ENGINE=MEMORY AUTO_INCREMENT=28 DEFAULT CHARSET=latin1
+) ENGINE=MEMORY AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (c1)
PARTITIONS 2 */
SELECT * FROM t1 ORDER BY c1;
c1
-27
+1
INSERT INTO t1 VALUES (100);
INSERT INTO t1 VALUES (NULL);
DELETE FROM t1 WHERE c1 >= 100;
=== modified file 'mysql-test/suite/parts/r/partition_auto_increment_myisam.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_myisam.result 2009-02-05 17:47:24 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_myisam.result 2009-03-26 08:39:15 +0000
@@ -381,12 +381,12 @@ Table Create Table
t1 CREATE TABLE `t1` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`c1`)
-) ENGINE=MyISAM AUTO_INCREMENT=28 DEFAULT CHARSET=latin1
+) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (c1)
PARTITIONS 2 */
SELECT * FROM t1 ORDER BY c1;
c1
-27
+1
INSERT INTO t1 VALUES (100);
INSERT INTO t1 VALUES (NULL);
DELETE FROM t1 WHERE c1 >= 100;
=== modified file 'mysql-test/suite/parts/r/partition_mgm_lc0_archive.result'
--- a/mysql-test/suite/parts/r/partition_mgm_lc0_archive.result 2009-02-18 20:22:56 +0000
+++ b/mysql-test/suite/parts/r/partition_mgm_lc0_archive.result 2009-03-26 08:39:15 +0000
@@ -915,6 +915,18 @@ TableA CREATE TABLE `TableA` (
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
# Cleaning up after LIST PARTITIONING test
DROP TABLE TableA;
+# Verify that TRUNCATE PARTITION gives error
+CREATE TABLE t1
+(a BIGINT AUTO_INCREMENT PRIMARY KEY,
+b VARCHAR(255))
+ENGINE = 'Archive'
+PARTITION BY KEY (a)
+(PARTITION LT1000,
+PARTITION LT2000,
+PARTITION MAX);
+INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX");
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+Got one of the listed errors
# Cleaning up before exit
USE test;
DROP DATABASE MySQL_Test_DB;
=== modified file 'mysql-test/suite/parts/r/partition_mgm_lc0_innodb.result'
--- a/mysql-test/suite/parts/r/partition_mgm_lc0_innodb.result 2009-02-18 20:22:56 +0000
+++ b/mysql-test/suite/parts/r/partition_mgm_lc0_innodb.result 2009-03-26 08:39:15 +0000
@@ -915,6 +915,170 @@ TableA CREATE TABLE `TableA` (
) ENGINE=InnoDB DEFAULT CHARSET=latin1
# Cleaning up after LIST PARTITIONING test
DROP TABLE TableA;
+# Testing TRUNCATE PARTITION
+CREATE TABLE t1
+(a BIGINT AUTO_INCREMENT PRIMARY KEY,
+b VARCHAR(255))
+ENGINE = 'InnoDB'
+PARTITION BY RANGE (a)
+(PARTITION LT1000 VALUES LESS THAN (1000),
+PARTITION LT2000 VALUES LESS THAN (2000),
+PARTITION MAX VALUES LESS THAN MAXVALUE);
+INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX");
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` bigint(20) NOT NULL AUTO_INCREMENT,
+ `b` varchar(255) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=2002 DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (a)
+(PARTITION LT1000 VALUES LESS THAN (1000) ENGINE = InnoDB,
+ PARTITION LT2000 VALUES LESS THAN (2000) ENGINE = InnoDB,
+ PARTITION MAX VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
+SELECT * FROM t1 ORDER BY a;
+a b
+1 First
+2 Second
+999 Last in LT1000
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First in MAX
+2001 Second in MAX
+ALTER TABLE t1 ANALYZE PARTITION MAX;
+Table Op Msg_type Msg_text
+MySQL_Test_DB.t1 analyze status OK
+# Truncate without FLUSH
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (1)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (1)
+# Truncate with FLUSH after
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+FLUSH TABLES;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (2)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (2)
+# Truncate with FLUSH before
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (3)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (3)
+# Truncate with FLUSH after INSERT
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (4)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (4)
+# Truncate without FLUSH
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (1)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+# Truncate with FLUSH after
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+FLUSH TABLES;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (2)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+# Truncate with FLUSH before
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (3)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+# Truncate with FLUSH after INSERT
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (4)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+# Truncate without FLUSH
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (1)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+# Truncate with FLUSH after
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+FLUSH TABLES;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (2)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+2006 First after TRUNCATE LT2000 (2)
+# Truncate with FLUSH before
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (3)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+2006 First after TRUNCATE LT2000 (2)
+2007 First after TRUNCATE LT2000 (3)
+# Truncate with FLUSH after INSERT
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (4)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+2006 First after TRUNCATE LT2000 (2)
+2007 First after TRUNCATE LT2000 (3)
+2008 First after TRUNCATE LT2000 (4)
+DROP TABLE t1;
# Cleaning up before exit
USE test;
DROP DATABASE MySQL_Test_DB;
=== modified file 'mysql-test/suite/parts/r/partition_mgm_lc0_memory.result'
--- a/mysql-test/suite/parts/r/partition_mgm_lc0_memory.result 2009-02-18 20:22:56 +0000
+++ b/mysql-test/suite/parts/r/partition_mgm_lc0_memory.result 2009-03-26 08:39:15 +0000
@@ -915,6 +915,170 @@ TableA CREATE TABLE `TableA` (
) ENGINE=MEMORY DEFAULT CHARSET=latin1
# Cleaning up after LIST PARTITIONING test
DROP TABLE TableA;
+# Testing TRUNCATE PARTITION
+CREATE TABLE t1
+(a BIGINT AUTO_INCREMENT PRIMARY KEY,
+b VARCHAR(255))
+ENGINE = 'Memory'
+PARTITION BY RANGE (a)
+(PARTITION LT1000 VALUES LESS THAN (1000),
+PARTITION LT2000 VALUES LESS THAN (2000),
+PARTITION MAX VALUES LESS THAN MAXVALUE);
+INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX");
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` bigint(20) NOT NULL AUTO_INCREMENT,
+ `b` varchar(255) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=MEMORY AUTO_INCREMENT=2002 DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (a)
+(PARTITION LT1000 VALUES LESS THAN (1000) ENGINE = MEMORY,
+ PARTITION LT2000 VALUES LESS THAN (2000) ENGINE = MEMORY,
+ PARTITION MAX VALUES LESS THAN MAXVALUE ENGINE = MEMORY) */
+SELECT * FROM t1 ORDER BY a;
+a b
+1 First
+2 Second
+999 Last in LT1000
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First in MAX
+2001 Second in MAX
+ALTER TABLE t1 ANALYZE PARTITION MAX;
+Table Op Msg_type Msg_text
+MySQL_Test_DB.t1 analyze note The storage engine for the table doesn't support analyze
+# Truncate without FLUSH
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (1)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (1)
+# Truncate with FLUSH after
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+FLUSH TABLES;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (2)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (2)
+# Truncate with FLUSH before
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (3)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (3)
+# Truncate with FLUSH after INSERT
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (4)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (4)
+# Truncate without FLUSH
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (1)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+# Truncate with FLUSH after
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+FLUSH TABLES;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (2)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+# Truncate with FLUSH before
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (3)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+# Truncate with FLUSH after INSERT
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (4)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+# Truncate without FLUSH
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (1)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+# Truncate with FLUSH after
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+FLUSH TABLES;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (2)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+2006 First after TRUNCATE LT2000 (2)
+# Truncate with FLUSH before
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (3)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+2006 First after TRUNCATE LT2000 (2)
+2007 First after TRUNCATE LT2000 (3)
+# Truncate with FLUSH after INSERT
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (4)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+2006 First after TRUNCATE LT2000 (2)
+2007 First after TRUNCATE LT2000 (3)
+2008 First after TRUNCATE LT2000 (4)
+DROP TABLE t1;
# Cleaning up before exit
USE test;
DROP DATABASE MySQL_Test_DB;
=== modified file 'mysql-test/suite/parts/r/partition_mgm_lc0_myisam.result'
--- a/mysql-test/suite/parts/r/partition_mgm_lc0_myisam.result 2009-02-18 20:22:56 +0000
+++ b/mysql-test/suite/parts/r/partition_mgm_lc0_myisam.result 2009-03-26 08:39:15 +0000
@@ -915,6 +915,170 @@ TableA CREATE TABLE `TableA` (
) ENGINE=MyISAM DEFAULT CHARSET=latin1
# Cleaning up after LIST PARTITIONING test
DROP TABLE TableA;
+# Testing TRUNCATE PARTITION
+CREATE TABLE t1
+(a BIGINT AUTO_INCREMENT PRIMARY KEY,
+b VARCHAR(255))
+ENGINE = 'MyISAM'
+PARTITION BY RANGE (a)
+(PARTITION LT1000 VALUES LESS THAN (1000),
+PARTITION LT2000 VALUES LESS THAN (2000),
+PARTITION MAX VALUES LESS THAN MAXVALUE);
+INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX");
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` bigint(20) NOT NULL AUTO_INCREMENT,
+ `b` varchar(255) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=MyISAM AUTO_INCREMENT=2002 DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (a)
+(PARTITION LT1000 VALUES LESS THAN (1000) ENGINE = MyISAM,
+ PARTITION LT2000 VALUES LESS THAN (2000) ENGINE = MyISAM,
+ PARTITION MAX VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
+SELECT * FROM t1 ORDER BY a;
+a b
+1 First
+2 Second
+999 Last in LT1000
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First in MAX
+2001 Second in MAX
+ALTER TABLE t1 ANALYZE PARTITION MAX;
+Table Op Msg_type Msg_text
+MySQL_Test_DB.t1 analyze status OK
+# Truncate without FLUSH
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (1)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (1)
+# Truncate with FLUSH after
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+FLUSH TABLES;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (2)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (2)
+# Truncate with FLUSH before
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (3)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (3)
+# Truncate with FLUSH after INSERT
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (4)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (4)
+# Truncate without FLUSH
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (1)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+# Truncate with FLUSH after
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+FLUSH TABLES;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (2)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+# Truncate with FLUSH before
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (3)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+# Truncate with FLUSH after INSERT
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (4)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+# Truncate without FLUSH
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (1)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+# Truncate with FLUSH after
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+FLUSH TABLES;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (2)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+2006 First after TRUNCATE LT2000 (2)
+# Truncate with FLUSH before
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (3)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+2006 First after TRUNCATE LT2000 (2)
+2007 First after TRUNCATE LT2000 (3)
+# Truncate with FLUSH after INSERT
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (4)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+2006 First after TRUNCATE LT2000 (2)
+2007 First after TRUNCATE LT2000 (3)
+2008 First after TRUNCATE LT2000 (4)
+DROP TABLE t1;
# Cleaning up before exit
USE test;
DROP DATABASE MySQL_Test_DB;
=== modified file 'mysql-test/suite/parts/r/partition_mgm_lc0_ndb.result'
--- a/mysql-test/suite/parts/r/partition_mgm_lc0_ndb.result 2009-02-18 20:22:56 +0000
+++ b/mysql-test/suite/parts/r/partition_mgm_lc0_ndb.result 2009-03-26 08:39:15 +0000
@@ -181,6 +181,18 @@ TableA CREATE TABLE `TableA` (
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
# Cleaning up after KEY PARTITIONING test
DROP TABLE TableA;
+# Verify that TRUNCATE PARTITION gives error
+CREATE TABLE t1
+(a BIGINT AUTO_INCREMENT PRIMARY KEY,
+b VARCHAR(255))
+ENGINE = 'NDBCluster'
+PARTITION BY KEY (a)
+(PARTITION LT1000,
+PARTITION LT2000,
+PARTITION MAX);
+INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX");
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+Got one of the listed errors
# Cleaning up before exit
USE test;
DROP DATABASE MySQL_Test_DB;
=== modified file 'mysql-test/suite/parts/r/partition_mgm_lc1_archive.result'
--- a/mysql-test/suite/parts/r/partition_mgm_lc1_archive.result 2009-02-18 20:22:56 +0000
+++ b/mysql-test/suite/parts/r/partition_mgm_lc1_archive.result 2009-03-26 08:39:15 +0000
@@ -882,6 +882,18 @@ TableA CREATE TABLE `tablea` (
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
# Cleaning up after LIST PARTITIONING test
DROP TABLE TableA;
+# Verify that TRUNCATE PARTITION gives error
+CREATE TABLE t1
+(a BIGINT AUTO_INCREMENT PRIMARY KEY,
+b VARCHAR(255))
+ENGINE = 'Archive'
+PARTITION BY KEY (a)
+(PARTITION LT1000,
+PARTITION LT2000,
+PARTITION MAX);
+INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX");
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+Got one of the listed errors
# Cleaning up before exit
USE test;
DROP DATABASE MySQL_Test_DB;
=== modified file 'mysql-test/suite/parts/r/partition_mgm_lc1_innodb.result'
--- a/mysql-test/suite/parts/r/partition_mgm_lc1_innodb.result 2009-02-18 20:22:56 +0000
+++ b/mysql-test/suite/parts/r/partition_mgm_lc1_innodb.result 2009-03-26 08:39:15 +0000
@@ -882,6 +882,170 @@ TableA CREATE TABLE `tablea` (
) ENGINE=InnoDB DEFAULT CHARSET=latin1
# Cleaning up after LIST PARTITIONING test
DROP TABLE TableA;
+# Testing TRUNCATE PARTITION
+CREATE TABLE t1
+(a BIGINT AUTO_INCREMENT PRIMARY KEY,
+b VARCHAR(255))
+ENGINE = 'InnoDB'
+PARTITION BY RANGE (a)
+(PARTITION LT1000 VALUES LESS THAN (1000),
+PARTITION LT2000 VALUES LESS THAN (2000),
+PARTITION MAX VALUES LESS THAN MAXVALUE);
+INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX");
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` bigint(20) NOT NULL AUTO_INCREMENT,
+ `b` varchar(255) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=2002 DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (a)
+(PARTITION LT1000 VALUES LESS THAN (1000) ENGINE = InnoDB,
+ PARTITION LT2000 VALUES LESS THAN (2000) ENGINE = InnoDB,
+ PARTITION MAX VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
+SELECT * FROM t1 ORDER BY a;
+a b
+1 First
+2 Second
+999 Last in LT1000
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First in MAX
+2001 Second in MAX
+ALTER TABLE t1 ANALYZE PARTITION MAX;
+Table Op Msg_type Msg_text
+mysql_test_db.t1 analyze status OK
+# Truncate without FLUSH
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (1)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (1)
+# Truncate with FLUSH after
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+FLUSH TABLES;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (2)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (2)
+# Truncate with FLUSH before
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (3)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (3)
+# Truncate with FLUSH after INSERT
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (4)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (4)
+# Truncate without FLUSH
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (1)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+# Truncate with FLUSH after
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+FLUSH TABLES;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (2)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+# Truncate with FLUSH before
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (3)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+# Truncate with FLUSH after INSERT
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (4)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+# Truncate without FLUSH
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (1)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+# Truncate with FLUSH after
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+FLUSH TABLES;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (2)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+2006 First after TRUNCATE LT2000 (2)
+# Truncate with FLUSH before
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (3)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+2006 First after TRUNCATE LT2000 (2)
+2007 First after TRUNCATE LT2000 (3)
+# Truncate with FLUSH after INSERT
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (4)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+2006 First after TRUNCATE LT2000 (2)
+2007 First after TRUNCATE LT2000 (3)
+2008 First after TRUNCATE LT2000 (4)
+DROP TABLE t1;
# Cleaning up before exit
USE test;
DROP DATABASE MySQL_Test_DB;
=== modified file 'mysql-test/suite/parts/r/partition_mgm_lc1_memory.result'
--- a/mysql-test/suite/parts/r/partition_mgm_lc1_memory.result 2009-02-18 20:22:56 +0000
+++ b/mysql-test/suite/parts/r/partition_mgm_lc1_memory.result 2009-03-26 08:39:15 +0000
@@ -882,6 +882,170 @@ TableA CREATE TABLE `tablea` (
) ENGINE=MEMORY DEFAULT CHARSET=latin1
# Cleaning up after LIST PARTITIONING test
DROP TABLE TableA;
+# Testing TRUNCATE PARTITION
+CREATE TABLE t1
+(a BIGINT AUTO_INCREMENT PRIMARY KEY,
+b VARCHAR(255))
+ENGINE = 'Memory'
+PARTITION BY RANGE (a)
+(PARTITION LT1000 VALUES LESS THAN (1000),
+PARTITION LT2000 VALUES LESS THAN (2000),
+PARTITION MAX VALUES LESS THAN MAXVALUE);
+INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX");
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` bigint(20) NOT NULL AUTO_INCREMENT,
+ `b` varchar(255) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=MEMORY AUTO_INCREMENT=2002 DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (a)
+(PARTITION LT1000 VALUES LESS THAN (1000) ENGINE = MEMORY,
+ PARTITION LT2000 VALUES LESS THAN (2000) ENGINE = MEMORY,
+ PARTITION MAX VALUES LESS THAN MAXVALUE ENGINE = MEMORY) */
+SELECT * FROM t1 ORDER BY a;
+a b
+1 First
+2 Second
+999 Last in LT1000
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First in MAX
+2001 Second in MAX
+ALTER TABLE t1 ANALYZE PARTITION MAX;
+Table Op Msg_type Msg_text
+mysql_test_db.t1 analyze note The storage engine for the table doesn't support analyze
+# Truncate without FLUSH
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (1)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (1)
+# Truncate with FLUSH after
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+FLUSH TABLES;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (2)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (2)
+# Truncate with FLUSH before
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (3)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (3)
+# Truncate with FLUSH after INSERT
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (4)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (4)
+# Truncate without FLUSH
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (1)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+# Truncate with FLUSH after
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+FLUSH TABLES;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (2)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+# Truncate with FLUSH before
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (3)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+# Truncate with FLUSH after INSERT
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (4)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+# Truncate without FLUSH
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (1)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+# Truncate with FLUSH after
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+FLUSH TABLES;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (2)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+2006 First after TRUNCATE LT2000 (2)
+# Truncate with FLUSH before
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (3)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+2006 First after TRUNCATE LT2000 (2)
+2007 First after TRUNCATE LT2000 (3)
+# Truncate with FLUSH after INSERT
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (4)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+2006 First after TRUNCATE LT2000 (2)
+2007 First after TRUNCATE LT2000 (3)
+2008 First after TRUNCATE LT2000 (4)
+DROP TABLE t1;
# Cleaning up before exit
USE test;
DROP DATABASE MySQL_Test_DB;
=== modified file 'mysql-test/suite/parts/r/partition_mgm_lc1_myisam.result'
--- a/mysql-test/suite/parts/r/partition_mgm_lc1_myisam.result 2009-02-18 20:22:56 +0000
+++ b/mysql-test/suite/parts/r/partition_mgm_lc1_myisam.result 2009-03-26 08:39:15 +0000
@@ -882,6 +882,170 @@ TableA CREATE TABLE `tablea` (
) ENGINE=MyISAM DEFAULT CHARSET=latin1
# Cleaning up after LIST PARTITIONING test
DROP TABLE TableA;
+# Testing TRUNCATE PARTITION
+CREATE TABLE t1
+(a BIGINT AUTO_INCREMENT PRIMARY KEY,
+b VARCHAR(255))
+ENGINE = 'MyISAM'
+PARTITION BY RANGE (a)
+(PARTITION LT1000 VALUES LESS THAN (1000),
+PARTITION LT2000 VALUES LESS THAN (2000),
+PARTITION MAX VALUES LESS THAN MAXVALUE);
+INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX");
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` bigint(20) NOT NULL AUTO_INCREMENT,
+ `b` varchar(255) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=MyISAM AUTO_INCREMENT=2002 DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (a)
+(PARTITION LT1000 VALUES LESS THAN (1000) ENGINE = MyISAM,
+ PARTITION LT2000 VALUES LESS THAN (2000) ENGINE = MyISAM,
+ PARTITION MAX VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
+SELECT * FROM t1 ORDER BY a;
+a b
+1 First
+2 Second
+999 Last in LT1000
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First in MAX
+2001 Second in MAX
+ALTER TABLE t1 ANALYZE PARTITION MAX;
+Table Op Msg_type Msg_text
+mysql_test_db.t1 analyze status OK
+# Truncate without FLUSH
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (1)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (1)
+# Truncate with FLUSH after
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+FLUSH TABLES;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (2)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (2)
+# Truncate with FLUSH before
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (3)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (3)
+# Truncate with FLUSH after INSERT
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (4)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (4)
+# Truncate without FLUSH
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (1)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+# Truncate with FLUSH after
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+FLUSH TABLES;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (2)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+# Truncate with FLUSH before
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (3)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+# Truncate with FLUSH after INSERT
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (4)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+# Truncate without FLUSH
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (1)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+# Truncate with FLUSH after
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+FLUSH TABLES;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (2)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+2006 First after TRUNCATE LT2000 (2)
+# Truncate with FLUSH before
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (3)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+2006 First after TRUNCATE LT2000 (2)
+2007 First after TRUNCATE LT2000 (3)
+# Truncate with FLUSH after INSERT
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (4)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+2006 First after TRUNCATE LT2000 (2)
+2007 First after TRUNCATE LT2000 (3)
+2008 First after TRUNCATE LT2000 (4)
+DROP TABLE t1;
# Cleaning up before exit
USE test;
DROP DATABASE MySQL_Test_DB;
=== modified file 'mysql-test/suite/parts/r/partition_mgm_lc1_ndb.result'
--- a/mysql-test/suite/parts/r/partition_mgm_lc1_ndb.result 2009-02-18 20:22:56 +0000
+++ b/mysql-test/suite/parts/r/partition_mgm_lc1_ndb.result 2009-03-26 08:39:15 +0000
@@ -219,6 +219,18 @@ TableA CREATE TABLE `tablea` (
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
# Cleaning up after KEY PARTITIONING test
DROP TABLE TableA;
+# Verify that TRUNCATE PARTITION gives error
+CREATE TABLE t1
+(a BIGINT AUTO_INCREMENT PRIMARY KEY,
+b VARCHAR(255))
+ENGINE = 'NDBCluster'
+PARTITION BY KEY (a)
+(PARTITION LT1000,
+PARTITION LT2000,
+PARTITION MAX);
+INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX");
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+Got one of the listed errors
# Cleaning up before exit
USE test;
DROP DATABASE MySQL_Test_DB;
=== modified file 'mysql-test/suite/parts/r/partition_mgm_lc2_archive.result'
--- a/mysql-test/suite/parts/r/partition_mgm_lc2_archive.result 2009-02-18 20:22:56 +0000
+++ b/mysql-test/suite/parts/r/partition_mgm_lc2_archive.result 2009-03-26 08:39:15 +0000
@@ -882,6 +882,18 @@ TableA CREATE TABLE `TableA` (
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
# Cleaning up after LIST PARTITIONING test
DROP TABLE TableA;
+# Verify that TRUNCATE PARTITION gives error
+CREATE TABLE t1
+(a BIGINT AUTO_INCREMENT PRIMARY KEY,
+b VARCHAR(255))
+ENGINE = 'Archive'
+PARTITION BY KEY (a)
+(PARTITION LT1000,
+PARTITION LT2000,
+PARTITION MAX);
+INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX");
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+Got one of the listed errors
# Cleaning up before exit
USE test;
DROP DATABASE MySQL_Test_DB;
=== modified file 'mysql-test/suite/parts/r/partition_mgm_lc2_innodb.result'
--- a/mysql-test/suite/parts/r/partition_mgm_lc2_innodb.result 2009-02-18 20:22:56 +0000
+++ b/mysql-test/suite/parts/r/partition_mgm_lc2_innodb.result 2009-03-26 08:39:15 +0000
@@ -882,6 +882,170 @@ TableA CREATE TABLE `TableA` (
) ENGINE=InnoDB DEFAULT CHARSET=latin1
# Cleaning up after LIST PARTITIONING test
DROP TABLE TableA;
+# Testing TRUNCATE PARTITION
+CREATE TABLE t1
+(a BIGINT AUTO_INCREMENT PRIMARY KEY,
+b VARCHAR(255))
+ENGINE = 'InnoDB'
+PARTITION BY RANGE (a)
+(PARTITION LT1000 VALUES LESS THAN (1000),
+PARTITION LT2000 VALUES LESS THAN (2000),
+PARTITION MAX VALUES LESS THAN MAXVALUE);
+INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX");
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` bigint(20) NOT NULL AUTO_INCREMENT,
+ `b` varchar(255) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=2002 DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (a)
+(PARTITION LT1000 VALUES LESS THAN (1000) ENGINE = InnoDB,
+ PARTITION LT2000 VALUES LESS THAN (2000) ENGINE = InnoDB,
+ PARTITION MAX VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
+SELECT * FROM t1 ORDER BY a;
+a b
+1 First
+2 Second
+999 Last in LT1000
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First in MAX
+2001 Second in MAX
+ALTER TABLE t1 ANALYZE PARTITION MAX;
+Table Op Msg_type Msg_text
+mysql_test_db.t1 analyze status OK
+# Truncate without FLUSH
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (1)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (1)
+# Truncate with FLUSH after
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+FLUSH TABLES;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (2)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (2)
+# Truncate with FLUSH before
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (3)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (3)
+# Truncate with FLUSH after INSERT
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (4)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (4)
+# Truncate without FLUSH
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (1)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+# Truncate with FLUSH after
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+FLUSH TABLES;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (2)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+# Truncate with FLUSH before
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (3)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+# Truncate with FLUSH after INSERT
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (4)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+# Truncate without FLUSH
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (1)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+# Truncate with FLUSH after
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+FLUSH TABLES;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (2)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+2006 First after TRUNCATE LT2000 (2)
+# Truncate with FLUSH before
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (3)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+2006 First after TRUNCATE LT2000 (2)
+2007 First after TRUNCATE LT2000 (3)
+# Truncate with FLUSH after INSERT
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (4)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+2006 First after TRUNCATE LT2000 (2)
+2007 First after TRUNCATE LT2000 (3)
+2008 First after TRUNCATE LT2000 (4)
+DROP TABLE t1;
# Cleaning up before exit
USE test;
DROP DATABASE MySQL_Test_DB;
=== modified file 'mysql-test/suite/parts/r/partition_mgm_lc2_memory.result'
--- a/mysql-test/suite/parts/r/partition_mgm_lc2_memory.result 2009-02-18 20:22:56 +0000
+++ b/mysql-test/suite/parts/r/partition_mgm_lc2_memory.result 2009-03-26 08:39:15 +0000
@@ -882,6 +882,170 @@ TableA CREATE TABLE `TableA` (
) ENGINE=MEMORY DEFAULT CHARSET=latin1
# Cleaning up after LIST PARTITIONING test
DROP TABLE TableA;
+# Testing TRUNCATE PARTITION
+CREATE TABLE t1
+(a BIGINT AUTO_INCREMENT PRIMARY KEY,
+b VARCHAR(255))
+ENGINE = 'Memory'
+PARTITION BY RANGE (a)
+(PARTITION LT1000 VALUES LESS THAN (1000),
+PARTITION LT2000 VALUES LESS THAN (2000),
+PARTITION MAX VALUES LESS THAN MAXVALUE);
+INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX");
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` bigint(20) NOT NULL AUTO_INCREMENT,
+ `b` varchar(255) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=MEMORY AUTO_INCREMENT=2002 DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (a)
+(PARTITION LT1000 VALUES LESS THAN (1000) ENGINE = MEMORY,
+ PARTITION LT2000 VALUES LESS THAN (2000) ENGINE = MEMORY,
+ PARTITION MAX VALUES LESS THAN MAXVALUE ENGINE = MEMORY) */
+SELECT * FROM t1 ORDER BY a;
+a b
+1 First
+2 Second
+999 Last in LT1000
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First in MAX
+2001 Second in MAX
+ALTER TABLE t1 ANALYZE PARTITION MAX;
+Table Op Msg_type Msg_text
+mysql_test_db.t1 analyze note The storage engine for the table doesn't support analyze
+# Truncate without FLUSH
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (1)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (1)
+# Truncate with FLUSH after
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+FLUSH TABLES;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (2)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (2)
+# Truncate with FLUSH before
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (3)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (3)
+# Truncate with FLUSH after INSERT
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (4)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (4)
+# Truncate without FLUSH
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (1)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+# Truncate with FLUSH after
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+FLUSH TABLES;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (2)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+# Truncate with FLUSH before
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (3)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+# Truncate with FLUSH after INSERT
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (4)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+# Truncate without FLUSH
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (1)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+# Truncate with FLUSH after
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+FLUSH TABLES;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (2)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+2006 First after TRUNCATE LT2000 (2)
+# Truncate with FLUSH before
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (3)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+2006 First after TRUNCATE LT2000 (2)
+2007 First after TRUNCATE LT2000 (3)
+# Truncate with FLUSH after INSERT
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (4)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+2006 First after TRUNCATE LT2000 (2)
+2007 First after TRUNCATE LT2000 (3)
+2008 First after TRUNCATE LT2000 (4)
+DROP TABLE t1;
# Cleaning up before exit
USE test;
DROP DATABASE MySQL_Test_DB;
=== modified file 'mysql-test/suite/parts/r/partition_mgm_lc2_myisam.result'
--- a/mysql-test/suite/parts/r/partition_mgm_lc2_myisam.result 2009-02-18 20:22:56 +0000
+++ b/mysql-test/suite/parts/r/partition_mgm_lc2_myisam.result 2009-03-26 08:39:15 +0000
@@ -882,6 +882,170 @@ TableA CREATE TABLE `TableA` (
) ENGINE=MyISAM DEFAULT CHARSET=latin1
# Cleaning up after LIST PARTITIONING test
DROP TABLE TableA;
+# Testing TRUNCATE PARTITION
+CREATE TABLE t1
+(a BIGINT AUTO_INCREMENT PRIMARY KEY,
+b VARCHAR(255))
+ENGINE = 'MyISAM'
+PARTITION BY RANGE (a)
+(PARTITION LT1000 VALUES LESS THAN (1000),
+PARTITION LT2000 VALUES LESS THAN (2000),
+PARTITION MAX VALUES LESS THAN MAXVALUE);
+INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX");
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` bigint(20) NOT NULL AUTO_INCREMENT,
+ `b` varchar(255) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=MyISAM AUTO_INCREMENT=2002 DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (a)
+(PARTITION LT1000 VALUES LESS THAN (1000) ENGINE = MyISAM,
+ PARTITION LT2000 VALUES LESS THAN (2000) ENGINE = MyISAM,
+ PARTITION MAX VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
+SELECT * FROM t1 ORDER BY a;
+a b
+1 First
+2 Second
+999 Last in LT1000
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First in MAX
+2001 Second in MAX
+ALTER TABLE t1 ANALYZE PARTITION MAX;
+Table Op Msg_type Msg_text
+mysql_test_db.t1 analyze status OK
+# Truncate without FLUSH
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (1)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (1)
+# Truncate with FLUSH after
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+FLUSH TABLES;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (2)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (2)
+# Truncate with FLUSH before
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (3)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (3)
+# Truncate with FLUSH after INSERT
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (4)");
+SELECT * FROM t1 WHERE a >= 2000;
+a b
+2000 First after TRUNCATE MAX (4)
+# Truncate without FLUSH
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (1)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+# Truncate with FLUSH after
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+FLUSH TABLES;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (2)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+# Truncate with FLUSH before
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (3)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+# Truncate with FLUSH after INSERT
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT1000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (4)");
+SELECT * FROM t1 ORDER BY a;
+a b
+1000 First in LT2000
+1001 Second in LT2000
+1999 Last in LT2000
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+# Truncate without FLUSH
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (1)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+# Truncate with FLUSH after
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+FLUSH TABLES;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (2)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+2006 First after TRUNCATE LT2000 (2)
+# Truncate with FLUSH before
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (3)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+2006 First after TRUNCATE LT2000 (2)
+2007 First after TRUNCATE LT2000 (3)
+# Truncate with FLUSH after INSERT
+FLUSH TABLES;
+ALTER TABLE t1 TRUNCATE PARTITION LT2000;
+INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (4)");
+SELECT * FROM t1 ORDER BY a;
+a b
+2000 First after TRUNCATE MAX (4)
+2001 First after TRUNCATE LT1000 (1)
+2002 First after TRUNCATE LT1000 (2)
+2003 First after TRUNCATE LT1000 (3)
+2004 First after TRUNCATE LT1000 (4)
+2005 First after TRUNCATE LT2000 (1)
+2006 First after TRUNCATE LT2000 (2)
+2007 First after TRUNCATE LT2000 (3)
+2008 First after TRUNCATE LT2000 (4)
+DROP TABLE t1;
# Cleaning up before exit
USE test;
DROP DATABASE MySQL_Test_DB;
=== modified file 'mysql-test/suite/parts/r/partition_mgm_lc2_ndb.result'
--- a/mysql-test/suite/parts/r/partition_mgm_lc2_ndb.result 2009-02-18 20:22:56 +0000
+++ b/mysql-test/suite/parts/r/partition_mgm_lc2_ndb.result 2009-03-26 08:39:15 +0000
@@ -219,6 +219,18 @@ TableA CREATE TABLE `TableA` (
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
# Cleaning up after KEY PARTITIONING test
DROP TABLE TableA;
+# Verify that TRUNCATE PARTITION gives error
+CREATE TABLE t1
+(a BIGINT AUTO_INCREMENT PRIMARY KEY,
+b VARCHAR(255))
+ENGINE = 'NDBCluster'
+PARTITION BY KEY (a)
+(PARTITION LT1000,
+PARTITION LT2000,
+PARTITION MAX);
+INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX");
+ALTER TABLE t1 TRUNCATE PARTITION MAX;
+Got one of the listed errors
# Cleaning up before exit
USE test;
DROP DATABASE MySQL_Test_DB;
=== modified file 'mysql-test/suite/parts/t/partition_mgm_lc0_archive.test'
--- a/mysql-test/suite/parts/t/partition_mgm_lc0_archive.test 2008-11-05 21:26:39 +0000
+++ b/mysql-test/suite/parts/t/partition_mgm_lc0_archive.test 2009-03-26 08:39:15 +0000
@@ -35,6 +35,7 @@
##### Storage engine to be tested
--source include/have_archive.inc
let $engine= 'Archive';
+let $no_truncate= 1;
#------------------------------------------------------------------------------#
# Execute the tests to be applied to all storage engines
=== modified file 'mysql-test/suite/parts/t/partition_mgm_lc0_ndb.test'
--- a/mysql-test/suite/parts/t/partition_mgm_lc0_ndb.test 2008-07-10 23:14:13 +0000
+++ b/mysql-test/suite/parts/t/partition_mgm_lc0_ndb.test 2009-03-26 08:39:15 +0000
@@ -41,6 +41,8 @@ let $can_only_key= 1;
# Allow hash/list/range partitioning with ndb
#SET new=on;
let $engine= 'NDBCluster';
+# NDB does not yet support TRUNCATE PARTITION
+let $no_truncate= 1;
#------------------------------------------------------------------------------#
# Execute the tests to be applied to all storage engines
=== modified file 'mysql-test/suite/parts/t/partition_mgm_lc1_archive.test'
--- a/mysql-test/suite/parts/t/partition_mgm_lc1_archive.test 2008-07-10 23:14:13 +0000
+++ b/mysql-test/suite/parts/t/partition_mgm_lc1_archive.test 2009-03-26 08:39:15 +0000
@@ -32,6 +32,7 @@
##### Storage engine to be tested
--source include/have_archive.inc
let $engine= 'Archive';
+let $no_truncate= 1;
#------------------------------------------------------------------------------#
# Execute the tests to be applied to all storage engines
=== modified file 'mysql-test/suite/parts/t/partition_mgm_lc1_ndb.test'
--- a/mysql-test/suite/parts/t/partition_mgm_lc1_ndb.test 2008-07-10 23:14:13 +0000
+++ b/mysql-test/suite/parts/t/partition_mgm_lc1_ndb.test 2009-03-26 08:39:15 +0000
@@ -38,6 +38,8 @@ let $can_only_key= 1;
# Allow hash/list/range partitioning with ndb
#SET new=on;
let $engine= 'NDBCluster';
+# NDB does not yet support TRUNCATE PARTITION
+let $no_truncate= 1;
#------------------------------------------------------------------------------#
# Execute the tests to be applied to all storage engines
=== modified file 'mysql-test/suite/parts/t/partition_mgm_lc2_archive.test'
--- a/mysql-test/suite/parts/t/partition_mgm_lc2_archive.test 2008-07-10 23:14:13 +0000
+++ b/mysql-test/suite/parts/t/partition_mgm_lc2_archive.test 2009-03-26 08:39:15 +0000
@@ -32,6 +32,7 @@
##### Storage engine to be tested
--source include/have_archive.inc
let $engine= 'Archive';
+let $no_truncate= 1;
#------------------------------------------------------------------------------#
# Execute the tests to be applied to all storage engines
=== modified file 'mysql-test/suite/parts/t/partition_mgm_lc2_ndb.test'
--- a/mysql-test/suite/parts/t/partition_mgm_lc2_ndb.test 2008-07-10 23:14:13 +0000
+++ b/mysql-test/suite/parts/t/partition_mgm_lc2_ndb.test 2009-03-26 08:39:15 +0000
@@ -37,6 +37,8 @@ let $can_only_key= 1;
# Allow hash/list/range partitioning with ndb
#SET new=on;
let $engine= 'NDBCluster';
+# NDB does not yet support TRUNCATE PARTITION
+let $no_truncate= 1;
#------------------------------------------------------------------------------#
# Execute the tests to be applied to all storage engines
=== modified file 'sql/ha_partition.cc'
--- a/sql/ha_partition.cc 2009-02-20 15:56:32 +0000
+++ b/sql/ha_partition.cc 2009-03-26 08:39:15 +0000
@@ -1068,7 +1068,7 @@ int ha_partition::handle_opt_partitions(
it should only do named partitions, otherwise all partitions
*/
if (!(thd->lex->alter_info.flags & ALTER_ADMIN_PARTITION) ||
- part_elem->part_state == PART_CHANGED)
+ part_elem->part_state == PART_ADMIN)
{
if (m_is_sub_partitioned)
{
@@ -1129,6 +1129,7 @@ int ha_partition::handle_opt_partitions(
DBUG_RETURN(error);
}
}
+ part_elem->part_state= PART_NORMAL;
}
} while (++i < no_parts);
DBUG_RETURN(FALSE);
@@ -3174,28 +3175,110 @@ int ha_partition::delete_row(const uchar
Called from sql_delete.cc by mysql_delete().
Called from sql_select.cc by JOIN::reinit().
Called from sql_union.cc by st_select_lex_unit::exec().
+
+ Also used for handle ALTER TABLE t TRUNCATE PARTITION ...
+ NOTE: auto increment value will be truncated in that partition as well!
*/
int ha_partition::delete_all_rows()
{
int error;
handler **file;
+ bool truncate= FALSE;
THD *thd= ha_thd();
DBUG_ENTER("ha_partition::delete_all_rows");
if (thd->lex->sql_command == SQLCOM_TRUNCATE)
{
+ Alter_info *alter_info= &thd->lex->alter_info;
HA_DATA_PARTITION *ha_data= (HA_DATA_PARTITION*) table_share->ha_data;
+ /* TRUNCATE also means resetting auto_increment */
lock_auto_increment();
ha_data->next_auto_inc_val= 0;
ha_data->auto_inc_initialized= FALSE;
unlock_auto_increment();
+ if (alter_info->flags & ALTER_ADMIN_PARTITION)
+ {
+ /* ALTER TABLE t TRUNCATE PARTITION ... */
+ List_iterator<partition_element> part_it(m_part_info->partitions);
+ int saved_error= 0;
+ uint no_parts= m_part_info->no_parts;
+ uint no_subparts= m_part_info->no_subparts;
+ uint i= 0;
+ uint no_parts_set= alter_info->partition_names.elements;
+ uint no_parts_found= set_part_state(alter_info, m_part_info,
+ PART_ADMIN);
+ if (no_parts_set != no_parts_found &&
+ (!(alter_info->flags & ALTER_ALL_PARTITION)))
+ DBUG_RETURN(HA_ERR_NO_PARTITION_FOUND);
+
+ /*
+ Cannot return HA_ERR_WRONG_COMMAND here without correct pruning
+ since that whould delete the whole table row by row in sql_delete.cc
+ */
+ bitmap_clear_all(&m_part_info->used_partitions);
+ do
+ {
+ partition_element *part_elem= part_it++;
+ if (part_elem->part_state == PART_ADMIN)
+ {
+ if (m_is_sub_partitioned)
+ {
+ List_iterator<partition_element>
+ subpart_it(part_elem->subpartitions);
+ partition_element *sub_elem;
+ uint j= 0, part;
+ do
+ {
+ sub_elem= subpart_it++;
+ part= i * no_subparts + j;
+ bitmap_set_bit(&m_part_info->used_partitions, part);
+ if (!saved_error)
+ {
+ DBUG_PRINT("info", ("truncate subpartition %u (%s)",
+ part, sub_elem->partition_name));
+ if ((error= m_file[part]->ha_delete_all_rows()))
+ saved_error= error;
+ /* If not reset_auto_increment is supported, just accept it */
+ if (!saved_error &&
+ (error= m_file[part]->ha_reset_auto_increment(0)) &&
+ error != HA_ERR_WRONG_COMMAND)
+ saved_error= error;
+ }
+ } while (++j < no_subparts);
+ }
+ else
+ {
+ DBUG_PRINT("info", ("truncate partition %u (%s)", i,
+ part_elem->partition_name));
+ bitmap_set_bit(&m_part_info->used_partitions, i);
+ if (!saved_error)
+ {
+ if ((error= m_file[i]->ha_delete_all_rows()) && !saved_error)
+ saved_error= error;
+ /* If not reset_auto_increment is supported, just accept it */
+ if (!saved_error &&
+ (error= m_file[i]->ha_reset_auto_increment(0)) &&
+ error != HA_ERR_WRONG_COMMAND)
+ saved_error= error;
+ }
+ }
+ part_elem->part_state= PART_NORMAL;
+ }
+ } while (++i < no_parts);
+ DBUG_RETURN(saved_error);
+ }
+ truncate= TRUE;
}
+
file= m_file;
do
{
if ((error= (*file)->ha_delete_all_rows()))
DBUG_RETURN(error);
+ /* Ingnore the error */
+ if (truncate)
+ (void) (*file)->ha_reset_auto_increment(0);
} while (*(++file));
DBUG_RETURN(0);
}
=== modified file 'sql/partition_element.h'
--- a/sql/partition_element.h 2007-04-13 17:23:02 +0000
+++ b/sql/partition_element.h 2009-03-26 08:39:15 +0000
@@ -32,7 +32,8 @@ enum partition_state {
PART_REORGED_DROPPED= 5,
PART_CHANGED= 6,
PART_IS_CHANGED= 7,
- PART_IS_ADDED= 8
+ PART_IS_ADDED= 8,
+ PART_ADMIN= 9
};
/*
=== modified file 'sql/sql_delete.cc'
--- a/sql/sql_delete.cc 2009-02-09 22:51:59 +0000
+++ b/sql/sql_delete.cc 2009-03-26 08:39:15 +0000
@@ -1015,6 +1015,7 @@ bool mysql_truncate(THD *thd, TABLE_LIST
{
handlerton *table_type= table->s->db_type();
TABLE_SHARE *share= table->s;
+ /* Note that a temporary table cannot be partitioned */
if (!ha_check_storage_engine_flag(table_type, HTON_CAN_RECREATE))
goto trunc_by_del;
@@ -1053,8 +1054,22 @@ bool mysql_truncate(THD *thd, TABLE_LIST
table_list->db, table_list->table_name);
DBUG_RETURN(TRUE);
}
- if (!ha_check_storage_engine_flag(ha_resolve_by_legacy_type(thd, table_type),
- HTON_CAN_RECREATE))
+#ifdef WITH_PARTITION_STORAGE_ENGINE
+ /*
+ TODO: add support for TRUNCATE PARTITION for NDB and other engines
+ supporting native partitioning
+ */
+ if (table_type != DB_TYPE_PARTITION_DB &&
+ thd->lex->alter_info.flags & ALTER_ADMIN_PARTITION)
+ {
+ my_error(ER_PARTITION_MGMT_ON_NONPARTITIONED, MYF(0));
+ DBUG_RETURN(TRUE);
+ }
+#endif
+ if (!ha_check_storage_engine_flag(ha_resolve_by_legacy_type(thd,
+ table_type),
+ HTON_CAN_RECREATE) ||
+ thd->lex->alter_info.flags & ALTER_ADMIN_PARTITION)
goto trunc_by_del;
if (lock_and_wait_for_table_name(thd, table_list))
=== modified file 'sql/sql_partition.cc'
--- a/sql/sql_partition.cc 2009-01-09 13:18:08 +0000
+++ b/sql/sql_partition.cc 2009-03-26 08:39:15 +0000
@@ -4142,6 +4142,8 @@ uint set_part_state(Alter_info *alter_in
DBUG_PRINT("info", ("Setting part_state to %u for partition %s",
part_state, part_elem->partition_name));
}
+ else
+ part_elem->part_state= PART_NORMAL;
} while (++part_count < tab_part_info->no_parts);
return no_parts_found;
}
=== modified file 'sql/sql_table.cc'
--- a/sql/sql_table.cc 2009-03-05 08:41:00 +0000
+++ b/sql/sql_table.cc 2009-03-26 08:39:15 +0000
@@ -4231,7 +4231,7 @@ static bool mysql_admin_table(THD* thd,
uint no_parts_found;
uint no_parts_opt= alter_info->partition_names.elements;
no_parts_found= set_part_state(alter_info, table->table->part_info,
- PART_CHANGED);
+ PART_ADMIN);
if (no_parts_found != no_parts_opt &&
(!(alter_info->flags & ALTER_ALL_PARTITION)))
{
=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy 2009-03-05 14:22:33 +0000
+++ b/sql/sql_yacc.yy 2009-03-26 08:39:15 +0000
@@ -5657,7 +5657,7 @@ alter_commands:
all_or_alt_part_name_list
{
LEX *lex= Lex;
- lex->sql_command = SQLCOM_OPTIMIZE;
+ lex->sql_command= SQLCOM_OPTIMIZE;
lex->alter_info.flags|= ALTER_ADMIN_PARTITION;
lex->no_write_to_binlog= $3;
lex->check_opt.init();
@@ -5667,7 +5667,7 @@ alter_commands:
all_or_alt_part_name_list
{
LEX *lex= Lex;
- lex->sql_command = SQLCOM_ANALYZE;
+ lex->sql_command= SQLCOM_ANALYZE;
lex->alter_info.flags|= ALTER_ADMIN_PARTITION;
lex->no_write_to_binlog= $3;
lex->check_opt.init();
@@ -5675,7 +5675,7 @@ alter_commands:
| CHECK_SYM PARTITION_SYM all_or_alt_part_name_list
{
LEX *lex= Lex;
- lex->sql_command = SQLCOM_CHECK;
+ lex->sql_command= SQLCOM_CHECK;
lex->alter_info.flags|= ALTER_ADMIN_PARTITION;
lex->check_opt.init();
}
@@ -5684,7 +5684,7 @@ alter_commands:
all_or_alt_part_name_list
{
LEX *lex= Lex;
- lex->sql_command = SQLCOM_REPAIR;
+ lex->sql_command= SQLCOM_REPAIR;
lex->alter_info.flags|= ALTER_ADMIN_PARTITION;
lex->no_write_to_binlog= $3;
lex->check_opt.init();
@@ -5697,6 +5697,13 @@ alter_commands:
lex->no_write_to_binlog= $3;
lex->alter_info.no_parts= $4;
}
+ | TRUNCATE_SYM PARTITION_SYM all_or_alt_part_name_list
+ {
+ LEX *lex= Lex;
+ lex->sql_command= SQLCOM_TRUNCATE;
+ lex->alter_info.flags|= ALTER_ADMIN_PARTITION;
+ lex->check_opt.init();
+ }
| reorg_partition_rule
;
@@ -9741,6 +9748,7 @@ truncate:
{
LEX* lex= Lex;
lex->sql_command= SQLCOM_TRUNCATE;
+ lex->alter_info.reset();
lex->select_lex.options= 0;
lex->select_lex.sql_cache= SELECT_LEX::SQL_CACHE_UNSPECIFIED;
lex->select_lex.init_order();
=== modified file 'storage/heap/ha_heap.cc'
--- a/storage/heap/ha_heap.cc 2008-12-16 12:12:22 +0000
+++ b/storage/heap/ha_heap.cc 2009-03-26 08:39:15 +0000
@@ -419,6 +419,14 @@ int ha_heap::delete_all_rows()
return 0;
}
+
+int ha_heap::reset_auto_increment(ulonglong value)
+{
+ file->s->auto_increment= value;
+ return 0;
+}
+
+
int ha_heap::external_lock(THD *thd, int lock_type)
{
return 0; // No external locking
=== modified file 'storage/heap/ha_heap.h'
--- a/storage/heap/ha_heap.h 2007-08-13 13:11:25 +0000
+++ b/storage/heap/ha_heap.h 2009-03-26 08:39:15 +0000
@@ -98,6 +98,7 @@ public:
int reset();
int external_lock(THD *thd, int lock_type);
int delete_all_rows(void);
+ int reset_auto_increment(ulonglong value);
int disable_indexes(uint mode);
int enable_indexes(uint mode);
int indexes_are_disabled(void);
=== modified file 'storage/myisam/ha_myisam.cc'
--- a/storage/myisam/ha_myisam.cc 2009-02-24 11:17:50 +0000
+++ b/storage/myisam/ha_myisam.cc 2009-03-26 08:39:15 +0000
@@ -1813,6 +1813,12 @@ int ha_myisam::delete_all_rows()
return mi_delete_all_rows(file);
}
+int ha_myisam::reset_auto_increment(ulonglong value)
+{
+ file->s->state.auto_increment= value;
+ return 0;
+}
+
int ha_myisam::delete_table(const char *name)
{
return mi_delete_table(name);
=== modified file 'storage/myisam/ha_myisam.h'
--- a/storage/myisam/ha_myisam.h 2007-11-15 19:25:43 +0000
+++ b/storage/myisam/ha_myisam.h 2009-03-26 08:39:15 +0000
@@ -101,6 +101,7 @@ class ha_myisam: public handler
int reset(void);
int external_lock(THD *thd, int lock_type);
int delete_all_rows(void);
+ int reset_auto_increment(ulonglong value);
int disable_indexes(uint mode);
int enable_indexes(uint mode);
int indexes_are_disabled(void);
Attachment: [text/bzr-bundle] bzr/mattias.jonsson@sun.com-20090326083915-ovtb770t4izxy0wj.bundle
| Thread |
|---|
| • bzr commit into mysql-5.1 branch (mattias.jonsson:2773) Bug#19405WL#4444 | Mattias Jonsson | 26 Mar |
