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
2825 Mikael Ronstrom 2009-07-21
WL#4571, Enable Key cache defined for a partition to enable more scalability on partitioned MyISQM tables among other things
modified:
sql/ha_partition.cc
sql/ha_partition.h
sql/sql_partition.cc
sql/sql_table.cc
sql/sql_yacc.yy
2824 Alexander Nozdrin 2009-07-12
Update disabled tests aux files
modified:
mysql-test/extra/scripts/monitor_disabled_tests/comments.txt
mysql-test/extra/scripts/monitor_disabled_tests/disabled_tags.txt
=== 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-07-21 15:44:36 +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_mgm_lc0_archive.result'
--- a/mysql-test/suite/parts/r/partition_mgm_lc0_archive.result 2009-02-20 12:37:37 +0000
+++ b/mysql-test/suite/parts/r/partition_mgm_lc0_archive.result 2009-07-21 15:44:36 +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-20 12:37:37 +0000
+++ b/mysql-test/suite/parts/r/partition_mgm_lc0_innodb.result 2009-07-21 15:44:36 +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-20 12:37:37 +0000
+++ b/mysql-test/suite/parts/r/partition_mgm_lc0_memory.result 2009-07-21 15:44:36 +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-20 12:37:37 +0000
+++ b/mysql-test/suite/parts/r/partition_mgm_lc0_myisam.result 2009-07-21 15:44:36 +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-20 12:37:37 +0000
+++ b/mysql-test/suite/parts/r/partition_mgm_lc0_ndb.result 2009-07-21 15:44:36 +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-20 12:37:37 +0000
+++ b/mysql-test/suite/parts/r/partition_mgm_lc1_archive.result 2009-07-21 15:44:36 +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-20 12:37:37 +0000
+++ b/mysql-test/suite/parts/r/partition_mgm_lc1_innodb.result 2009-07-21 15:44:36 +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-20 12:37:37 +0000
+++ b/mysql-test/suite/parts/r/partition_mgm_lc1_memory.result 2009-07-21 15:44:36 +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-20 12:37:37 +0000
+++ b/mysql-test/suite/parts/r/partition_mgm_lc1_myisam.result 2009-07-21 15:44:36 +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-20 12:37:37 +0000
+++ b/mysql-test/suite/parts/r/partition_mgm_lc1_ndb.result 2009-07-21 15:44:36 +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-20 12:37:37 +0000
+++ b/mysql-test/suite/parts/r/partition_mgm_lc2_archive.result 2009-07-21 15:44:36 +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-20 12:37:37 +0000
+++ b/mysql-test/suite/parts/r/partition_mgm_lc2_innodb.result 2009-07-21 15:44:36 +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-20 12:37:37 +0000
+++ b/mysql-test/suite/parts/r/partition_mgm_lc2_memory.result 2009-07-21 15:44:36 +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-20 12:37:37 +0000
+++ b/mysql-test/suite/parts/r/partition_mgm_lc2_myisam.result 2009-07-21 15:44:36 +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-20 12:37:37 +0000
+++ b/mysql-test/suite/parts/r/partition_mgm_lc2_ndb.result 2009-07-21 15:44:36 +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-07-21 15:44:36 +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-07-21 15:44:36 +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-07-21 15:44:36 +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-07-21 15:44:36 +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-07-21 15:44:36 +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-07-21 15:44:36 +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-07-08 12:46:36 +0000
+++ b/sql/ha_partition.cc 2009-07-21 15:44:36 +0000
@@ -868,9 +868,12 @@ int ha_partition::rename_partitions(THD
#define ANALYZE_PARTS 2
#define CHECK_PARTS 3
#define REPAIR_PARTS 4
+#define ASSIGN_KEYCACHE_PARTS 5
+#define PRELOAD_KEYS_PARTS 6
static const char *opt_op_name[]= {NULL,
- "optimize", "analyze", "check", "repair" };
+ "optimize", "analyze", "check", "repair",
+ "assign_to_keycache", "preload_keys"};
/*
Optimize table
@@ -955,7 +958,44 @@ int ha_partition::repair(THD *thd, HA_CH
DBUG_RETURN(handle_opt_partitions(thd, check_opt, REPAIR_PARTS));
}
+/**
+ Assign to keycache
+
+ @param thd Thread object
+ @param check_opt Check/analyze/repair/optimize options
+
+ @return
+ @retval >0 Error
+ @retval 0 Success
+*/
+
+int ha_partition::assign_to_keycache(THD *thd, HA_CHECK_OPT *check_opt)
+{
+ DBUG_ENTER("ha_partition::assign_to_keycache");
+
+ DBUG_RETURN(handle_opt_partitions(thd, check_opt, ASSIGN_KEYCACHE_PARTS));
+}
+
+
+/**
+ Preload to keycache
+
+ @param thd Thread object
+ @param check_opt Check/analyze/repair/optimize options
+
+ @return
+ @retval >0 Error
+ @retval 0 Success
+*/
+
+int ha_partition::preload_keys(THD *thd, HA_CHECK_OPT *check_opt)
+{
+ DBUG_ENTER("ha_partition::preload_keys");
+
+ DBUG_RETURN(handle_opt_partitions(thd, check_opt, PRELOAD_KEYS_PARTS));
+}
+
/*
Handle optimize/analyze/check/repair of one partition
@@ -986,6 +1026,10 @@ static int handle_opt_part(THD *thd, HA_
error= file->ha_check(thd, check_opt);
else if (flag == REPAIR_PARTS)
error= file->ha_repair(thd, check_opt);
+ else if (flag == ASSIGN_KEYCACHE_PARTS)
+ error= file->assign_to_keycache(thd, check_opt);
+ else if (flag == PRELOAD_KEYS_PARTS)
+ error= file->preload_keys(thd, check_opt);
else
{
DBUG_ASSERT(FALSE);
@@ -1083,7 +1127,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)
{
@@ -1115,6 +1159,12 @@ int ha_partition::handle_opt_partitions(
"Subpartition %s returned error",
sub_elem->partition_name);
}
+ /* reset part_state for the remaining partitions */
+ do
+ {
+ if (part_elem->part_state == PART_ADMIN)
+ part_elem->part_state= PART_NORMAL;
+ } while (part_elem= part_it++);
DBUG_RETURN(error);
}
} while (++j < no_subparts);
@@ -1141,9 +1191,16 @@ int ha_partition::handle_opt_partitions(
opt_op_name[flag], "Partition %s returned error",
part_elem->partition_name);
}
+ /* reset part_state for the remaining partitions */
+ do
+ {
+ if (part_elem->part_state == PART_ADMIN)
+ part_elem->part_state= PART_NORMAL;
+ } while (part_elem= part_it++);
DBUG_RETURN(error);
}
}
+ part_elem->part_state= PART_NORMAL;
}
} while (++i < no_parts);
DBUG_RETURN(FALSE);
@@ -3222,6 +3279,9 @@ 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()
@@ -3234,11 +3294,84 @@ int 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;
=== modified file 'sql/ha_partition.h'
--- a/sql/ha_partition.h 2009-02-19 13:06:05 +0000
+++ b/sql/ha_partition.h 2009-07-21 15:10:54 +0000
@@ -1060,12 +1060,13 @@ public:
virtual int backup(TD* thd, HA_CHECK_OPT *check_opt);
virtual int restore(THD* thd, HA_CHECK_OPT *check_opt);
- virtual int assign_to_keycache(THD* thd, HA_CHECK_OPT *check_opt);
- virtual int preload_keys(THD *thd, HA_CHECK_OPT *check_opt);
virtual int dump(THD* thd, int fd = -1);
virtual int net_read_dump(NET* net);
virtual uint checksum() const;
*/
+ /* Enabled keycache for performance reasons, WL#4571 */
+ virtual int assign_to_keycache(THD* thd, HA_CHECK_OPT *check_opt);
+ virtual int preload_keys(THD* thd, HA_CHECK_OPT* check_opt);
/*
-------------------------------------------------------------------------
=== modified file 'sql/partition_element.h'
--- a/sql/partition_element.h 2007-04-13 17:23:02 +0000
+++ b/sql/partition_element.h 2009-07-21 15:44:36 +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-06-19 09:28:44 +0000
+++ b/sql/sql_delete.cc 2009-07-21 15:44:36 +0000
@@ -1095,7 +1095,7 @@ bool mysql_truncate(THD *thd, TABLE_LIST
handlerton *table_type= table->s->db_type();
TABLE_SHARE *share= table->s;
bool frm_only= (share->tmp_table == TMP_TABLE_FRM_FILE_ONLY);
-
+ /* Note that a temporary table cannot be partitioned */
if (!ha_check_storage_engine_flag(table_type, HTON_CAN_RECREATE))
goto trunc_by_del;
@@ -1136,9 +1136,22 @@ bool mysql_truncate(THD *thd, TABLE_LIST
table_list->db, table_list->table_name);
DBUG_RETURN(TRUE);
}
+#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))
+ HTON_CAN_RECREATE) ||
+ thd->lex->alter_info.flags & ALTER_ADMIN_PARTITION)
goto trunc_by_del;
if (table_type == DB_TYPE_NDBCLUSTER)
=== modified file 'sql/sql_partition.cc'
--- a/sql/sql_partition.cc 2009-07-08 12:17:27 +0000
+++ b/sql/sql_partition.cc 2009-07-21 15:44:36 +0000
@@ -4135,13 +4135,15 @@ uint set_part_state(Alter_info *alter_in
/*
Mark the partition.
I.e mark the partition as a partition to be "changed" by
- analyzing/optimizing/rebuilding/checking/repairing
+ analyzing/optimizing/rebuilding/checking/repairing/...
*/
no_parts_found++;
part_elem->part_state= part_state;
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-07-10 12:31:32 +0000
+++ b/sql/sql_table.cc 2009-07-21 15:44:36 +0000
@@ -4524,6 +4524,7 @@ static bool mysql_admin_table(THD* thd,
/*
Set up which partitions that should be processed
if ALTER TABLE t ANALYZE/CHECK/OPTIMIZE/REPAIR PARTITION ..
+ CACHE INDEX/LOAD INDEX for specified partitions
*/
Alter_info *alter_info= &lex->alter_info;
@@ -4537,7 +4538,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-07-10 12:31:32 +0000
+++ b/sql/sql_yacc.yy 2009-07-21 15:44:36 +0000
@@ -1403,7 +1403,9 @@ bool my_yyoverflow(short **a, YYSTYPE **
slave master_def master_defs master_file_def slave_until_opts
repair restore backup analyze check start checksum
field_list field_list_item field_spec kill column_def key_def
- keycache_list assign_to_keycache preload_list preload_keys
+ keycache_list keycache_list_or_parts assign_to_keycache
+ assign_to_keycache_parts
+ preload_list preload_list_or_parts preload_keys preload_keys_parts
select_item_list select_item values_list no_braces
opt_limit_clause delete_limit_clause fields opt_values values
procedure_list procedure_list2 procedure_item
@@ -4113,17 +4115,9 @@ opt_partitioning:
;
partitioning:
- PARTITION_SYM
+ PARTITION_SYM have_partitioning
{
-#ifdef WITH_PARTITION_STORAGE_ENGINE
LEX *lex= Lex;
- LEX_STRING partition_name={C_STRING_WITH_LEN("partition")};
- if (!plugin_is_ready(&partition_name, MYSQL_STORAGE_ENGINE_PLUGIN))
- {
- my_error(ER_FEATURE_DISABLED, MYF(0),
- "partitioning", "--with-partition");
- MYSQL_YYABORT;
- }
lex->part_info= new partition_info();
if (!lex->part_info)
{
@@ -4134,14 +4128,27 @@ partitioning:
{
lex->alter_info.flags|= ALTER_PARTITION;
}
+ }
+ partition
+ ;
+
+have_partitioning:
+ /* empty */
+ {
+#ifdef WITH_PARTITION_STORAGE_ENGINE
+ LEX_STRING partition_name={C_STRING_WITH_LEN("partition")};
+ if (!plugin_is_ready(&partition_name, MYSQL_STORAGE_ENGINE_PLUGIN))
+ {
+ my_error(ER_FEATURE_DISABLED, MYF(0),
+ "partitioning", "--with-partition");
+ MYSQL_YYABORT;
+ }
#else
my_error(ER_FEATURE_DISABLED, MYF(0),
"partitioning", "--with-partition");
MYSQL_YYABORT;
#endif
-
}
- partition
;
partition_entry:
@@ -5973,7 +5980,6 @@ alter:
if (!lex->select_lex.add_table_to_list(thd, $5, NULL,
TL_OPTION_UPDATING))
MYSQL_YYABORT;
- lex->alter_info.reset();
lex->col_list.empty();
lex->select_lex.init_order();
lex->select_lex.db=
@@ -6211,7 +6217,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();
@@ -6221,7 +6227,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();
@@ -6229,7 +6235,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();
}
@@ -6238,7 +6244,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();
@@ -6251,6 +6257,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
;
@@ -6964,14 +6977,23 @@ table_to_table:
;
keycache:
- CACHE_SYM INDEX_SYM keycache_list IN_SYM key_cache_name
+ CACHE_SYM INDEX_SYM
+ {
+ Lex->alter_info.reset();
+ }
+ keycache_list_or_parts IN_SYM key_cache_name
{
LEX *lex=Lex;
lex->sql_command= SQLCOM_ASSIGN_TO_KEYCACHE;
- lex->ident= $5;
+ lex->ident= $6;
}
;
+keycache_list_or_parts:
+ keycache_list
+ | assign_to_keycache_parts
+ ;
+
keycache_list:
assign_to_keycache
| keycache_list ',' assign_to_keycache
@@ -6986,6 +7008,15 @@ assign_to_keycache:
}
;
+assign_to_keycache_parts:
+ table_ident adm_partition cache_keys_spec
+ {
+ if (!Select->add_table_to_list(YYTHD, $1, NULL, 0, TL_READ,
+ Select->pop_index_hints()))
+ MYSQL_YYABORT;
+ }
+ ;
+
key_cache_name:
ident { $$= $1; }
| DEFAULT { $$ = default_key_cache_base; }
@@ -6996,11 +7027,17 @@ preload:
{
LEX *lex=Lex;
lex->sql_command=SQLCOM_PRELOAD_KEYS;
+ lex->alter_info.reset();
}
- preload_list
+ preload_list_or_parts
{}
;
+preload_list_or_parts:
+ preload_keys_parts
+ | preload_list
+ ;
+
preload_list:
preload_keys
| preload_list ',' preload_keys
@@ -7015,6 +7052,23 @@ preload_keys:
}
;
+preload_keys_parts:
+ table_ident adm_partition cache_keys_spec opt_ignore_leaves
+ {
+ if (!Select->add_table_to_list(YYTHD, $1, NULL, $4, TL_READ,
+ Select->pop_index_hints()))
+ MYSQL_YYABORT;
+ }
+ ;
+
+adm_partition:
+ PARTITION_SYM have_partitioning
+ {
+ Lex->alter_info.flags|= ALTER_ADMIN_PARTITION;
+ }
+ '(' all_or_alt_part_name_list ')'
+ ;
+
cache_keys_spec:
{
Lex->select_lex.alloc_index_hints(YYTHD);
@@ -10573,6 +10627,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();
| Thread |
|---|
| • bzr push into mysql-5.4 branch (mikael:2824 to 2826) Bug#19405 Bug#35111WL#4444 | Mikael Ronstrom | 21 Jul |
