diff options
Diffstat (limited to 'mysql-test/suite/storage_engine/parts')
18 files changed, 1388 insertions, 0 deletions
diff --git a/mysql-test/suite/storage_engine/parts/alter_table.result b/mysql-test/suite/storage_engine/parts/alter_table.result new file mode 100644 index 00000000..d94fd3d5 --- /dev/null +++ b/mysql-test/suite/storage_engine/parts/alter_table.result @@ -0,0 +1,42 @@ +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (a <INT_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY HASH(a) PARTITIONS 2; +INSERT INTO t1 (a) VALUES (1),(2),(2),(3),(4); +ALTER TABLE t1 ADD PARTITION PARTITIONS 2; +EXPLAIN PARTiTIONS SELECT a FROM t1 WHERE a = 3; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p3 # # # # # # # +ALTER TABLE t1 COALESCE PARTITION 1; +EXPLAIN PARTiTIONS SELECT a FROM t1 WHERE a = 3; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0 # # # # # # # +ALTER TABLE t1 REORGANIZE PARTITION; +EXPLAIN PARTiTIONS SELECT a FROM t1 WHERE a = 2; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0 # # # # # # # +ALTER TABLE t1 REBUILD PARTITION p0; +EXPLAIN PARTiTIONS SELECT a FROM t1; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0 # # # # # # # +ALTER TABLE t1 REMOVE PARTITIONING; +EXPLAIN PARTiTIONS SELECT a FROM t1; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL # # # # # # # +ALTER TABLE t1 PARTITION BY LIST(a) (PARTITION p0 VALUES IN (1,2,3), PARTITION p1 VALUES IN (101,102)); +ERROR HY000: Table has no partition for value 4 +ALTER TABLE t1 PARTITION BY LIST(a) (PARTITION p0 VALUES IN (1,2,3,4), PARTITION p1 VALUES IN (101,102)); +INSERT INTO t1 (a) VALUES (50); +ERROR HY000: Table has no partition for value 50 +ALTER TABLE t1 ADD PARTITION (PARTITION p2 VALUES IN (50,51)); +INSERT INTO t1 (a) VALUES (50); +ALTER TABLE t1 DROP PARTITION p1; +ALTER TABLE t1 REORGANIZE PARTITION p0, p2 INTO (PARTITION p0 VALUES IN (1,2,3), PARTITION p1 VALUES IN (4), PARTITION p2 VALUES IN (50,51), PARTITION p3 VALUES IN (101,102)); +EXPLAIN PARTiTIONS SELECT a FROM t1 WHERE a = 2; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0 # # # # # # # +DROP TABLE t1; +CREATE TABLE t1 (a <INT_COLUMN>, b <INT_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY LIST(a) SUBPARTITION by HASH(b) ( +PARTITION abc VALUES IN (1,2,3), +PARTITION def VALUES IN (100,101,102) +); +ALTER TABLE t1 DROP PARTITION abc; +DROP TABLE t1; diff --git a/mysql-test/suite/storage_engine/parts/alter_table.test b/mysql-test/suite/storage_engine/parts/alter_table.test new file mode 100644 index 00000000..48f1f3f7 --- /dev/null +++ b/mysql-test/suite/storage_engine/parts/alter_table.test @@ -0,0 +1,107 @@ +# +# Basic ALTER TABLE statements related to partitioning +# + +--source include/have_partition.inc +--source ../have_engine.inc + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +--let $create_definition = a $int_col +--let $partition_options = PARTITION BY HASH(a) PARTITIONS 2 +--source ../create_table.inc +if ($mysql_errname) +{ + --let $my_last_stmt = $create_statement + --let $functionality = Partitions + --source ../unexpected_result.inc +} +if (!$mysql_errname) +{ + INSERT INTO t1 (a) VALUES (1),(2),(2),(3),(4); + + --let $alter_definition = ADD PARTITION PARTITIONS 2 + --source ../alter_table.inc + if ($mysql_errname) + { + --let $my_last_stmt = $alter_statement + --let $functionality = ALTER TABLE + --source ../unexpected_result.inc + } + --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 # + EXPLAIN PARTiTIONS SELECT a FROM t1 WHERE a = 3; + + --let $alter_definition = COALESCE PARTITION 1 + --source ../alter_table.inc + --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 # + EXPLAIN PARTiTIONS SELECT a FROM t1 WHERE a = 3; + + --let $alter_definition = REORGANIZE PARTITION + --source ../alter_table.inc + --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 # + EXPLAIN PARTiTIONS SELECT a FROM t1 WHERE a = 2; + + --let $alter_definition = REBUILD PARTITION p0 + --source ../alter_table.inc + --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 # + EXPLAIN PARTiTIONS SELECT a FROM t1; + + --let $alter_definition = REMOVE PARTITIONING + --source ../alter_table.inc + --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 # + EXPLAIN PARTiTIONS SELECT a FROM t1; + + --let $error_codes = ER_NO_PARTITION_FOR_GIVEN_VALUE + --let $alter_definition = PARTITION BY LIST(a) ( PARTITION p0 VALUES IN (1,2,3), PARTITION p1 VALUES IN (101,102) ) + --source ../alter_table.inc + if ($mysql_errname != ER_NO_PARTITION_FOR_GIVEN_VALUE) + { + --let $my_last_stmt = $alter_statement + --let $my_functionality = ALTER TABLE or INSERT + --source ../unexpected_result.inc + } + + --let $alter_definition = PARTITION BY LIST(a) ( PARTITION p0 VALUES IN (1,2,3,4), PARTITION p1 VALUES IN (101,102) ) + --source ../alter_table.inc + + --let $error_codes = ER_NO_PARTITION_FOR_GIVEN_VALUE + INSERT INTO t1 (a) VALUES (50); + --source ../check_errors.inc + --let $alter_definition = ADD PARTITION ( PARTITION p2 VALUES IN (50,51) ) + --source ../alter_table.inc + INSERT INTO t1 (a) VALUES (50); + + --let $alter_definition = DROP PARTITION p1 + --source ../alter_table.inc + --let $alter_definition = REORGANIZE PARTITION p0, p2 INTO ( PARTITION p0 VALUES IN (1,2,3), PARTITION p1 VALUES IN (4), PARTITION p2 VALUES IN (50,51), PARTITION p3 VALUES IN (101,102) ) + --source ../alter_table.inc + --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 # + EXPLAIN PARTiTIONS SELECT a FROM t1 WHERE a = 2; + DROP TABLE t1; +} + + +--let $create_definition = a $int_col, b $int_col +let $partition_options = PARTITION BY LIST(a) SUBPARTITION by HASH(b) ( + PARTITION abc VALUES IN (1,2,3), + PARTITION def VALUES IN (100,101,102) +); +--source ../create_table.inc +if ($mysql_errname) +{ + --let $my_last_stmt = $create_statement + --let $functionality = Partitions or subpartitions + --source ../unexpected_result.inc +} +if (!$mysql_errname) +{ + --let $alter_definition = DROP PARTITION abc + --source ../alter_table.inc + + DROP TABLE t1; +} + +--source ../cleanup_engine.inc + diff --git a/mysql-test/suite/storage_engine/parts/analyze_table.result b/mysql-test/suite/storage_engine/parts/analyze_table.result new file mode 100644 index 00000000..8cd210e4 --- /dev/null +++ b/mysql-test/suite/storage_engine/parts/analyze_table.result @@ -0,0 +1,47 @@ +DROP TABLE IF EXISTS t1,t2; +CREATE TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY HASH(a) PARTITIONS 2; +INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'),(3,'c'),(2,'d'),(4,'e'),(100,'f'),(101,'g'); +CREATE TABLE t2 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>; +INSERT INTO t2 (a,b) SELECT a,b FROM t1; +INSERT INTO t1 (a,b) VALUES (3,'c'); +ALTER TABLE t1 ANALYZE PARTITION p0; +Table Op Msg_type Msg_text +test.t1 analyze status OK +INSERT INTO t2 (a,b) VALUES (4,'d'), (1000,'e'); +ALTER TABLE t1 ANALYZE PARTITION LOCAL ALL; +Table Op Msg_type Msg_text +test.t1 analyze status OK +INSERT INTO t1 (a,b) VALUES (5,'f'),(50,'g'); +ALTER TABLE t1 ANALYZE PARTITION NO_WRITE_TO_BINLOG p1,p0; +Table Op Msg_type Msg_text +test.t1 analyze status OK +DROP TABLE t1, t2; +DROP TABLE IF EXISTS t1,t2; +CREATE TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY HASH(a) PARTITIONS 2; +INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'); +CREATE TABLE t2 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY HASH(a) PARTITIONS 2; +INSERT INTO t1 (a,b) VALUES (3,'c'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +INSERT INTO t2 (a,b) VALUES (4,'d'); +ANALYZE NO_WRITE_TO_BINLOG TABLE t2; +Table Op Msg_type Msg_text +test.t2 analyze status OK +INSERT INTO t1 (a,b) VALUES (5,'e'); +INSERT INTO t2 (a,b) VALUES (6,'f'); +ANALYZE LOCAL TABLE t1, t2; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +DROP TABLE t1, t2; +CREATE TABLE t1 (a <INT_COLUMN>, <CUSTOM_INDEX>(a)) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY HASH(a) PARTITIONS 2; +INSERT INTO t1 (a) VALUES (1),(2),(4),(7); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +INSERT INTO t1 (a) VALUES (8),(10),(11),(12); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +DROP TABLE t1; diff --git a/mysql-test/suite/storage_engine/parts/analyze_table.test b/mysql-test/suite/storage_engine/parts/analyze_table.test new file mode 100644 index 00000000..2ff8fff1 --- /dev/null +++ b/mysql-test/suite/storage_engine/parts/analyze_table.test @@ -0,0 +1,61 @@ +# +# ANALYZE TABLE for partitioned tables and ANALYZE PARTITION +# +--source include/have_partition.inc +--source ../have_engine.inc + + +--disable_warnings +DROP TABLE IF EXISTS t1,t2; +--enable_warnings + +--let $partition_options = PARTITION BY HASH(a) PARTITIONS 2 +--source ../create_table.inc +if ($mysql_errname) +{ + --let $my_last_stmt = $create_statement + --let $functionality = Partitions + --source ../unexpected_result.inc +} +if (!$mysql_errname) +{ + INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'),(3,'c'),(2,'d'),(4,'e'),(100,'f'),(101,'g'); + let $partiton_options = PARTITION BY RANGE(a) ( + PARTITION p0 VALUES LESS THAN (100), + PARTITION p1 VALUES LESS THAN MAXVALUE + ); + --let $table_name = t2 + --source ../create_table.inc + + INSERT INTO t2 (a,b) SELECT a,b FROM t1; + + INSERT INTO t1 (a,b) VALUES (3,'c'); + + --let $alter_definition = ANALYZE PARTITION p0 + --source ../alter_table.inc + if ($mysql_errname) + { + --let $my_last_stmt = $alter_statement + --let $functionality = ALTER TABLE .. ANALYZE PARTITION + --source unexpected_result.inc + } + + INSERT INTO t2 (a,b) VALUES (4,'d'), (1000,'e'); + + --let $alter_definition = ANALYZE PARTITION LOCAL ALL + --source ../alter_table.inc + + INSERT INTO t1 (a,b) VALUES (5,'f'),(50,'g'); + + --let $alter_definition = ANALYZE PARTITION NO_WRITE_TO_BINLOG p1,p0 + --source ../alter_table.inc + + DROP TABLE t1, t2; +} + +let $extra_tbl_opts = PARTITION BY HASH(a) PARTITIONS 2; +--source ../analyze_table.inc +let $extra_tbl_opts =; + + +--source ../cleanup_engine.inc diff --git a/mysql-test/suite/storage_engine/parts/check_table.result b/mysql-test/suite/storage_engine/parts/check_table.result new file mode 100644 index 00000000..6bd8d68e --- /dev/null +++ b/mysql-test/suite/storage_engine/parts/check_table.result @@ -0,0 +1,104 @@ +DROP TABLE IF EXISTS t1, t2; +CREATE TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY HASH(a) PARTITIONS 2; +INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'),(3,'c'),(2,'d'),(4,'e'),(100,'f'),(101,'g'); +CREATE TABLE t2 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY RANGE(a) ( +PARTITION p0 VALUES LESS THAN (100), +PARTITION p1 VALUES LESS THAN MAXVALUE +); +INSERT INTO t2 (a,b) SELECT a, b FROM t1; +ALTER TABLE t1 CHECK PARTITION p0; +Table Op Msg_type Msg_text +test.t1 check status OK +INSERT INTO t1 (a,b) VALUES (3,'c'); +ALTER TABLE t1 CHECK PARTITION p0, p1 FOR UPGRADE; +Table Op Msg_type Msg_text +test.t1 check status OK +INSERT INTO t2 (a,b) VALUES (10000,'e'); +ALTER TABLE t2 CHECK PARTITION p0 QUICK; +Table Op Msg_type Msg_text +test.t2 check status OK +INSERT INTO t1 (a,b) VALUES (6,'f'); +ALTER TABLE t1 CHECK PARTITION p1 FAST; +Table Op Msg_type Msg_text +test.t1 check status OK +INSERT INTO t2 (a,b) VALUES (8,'h'); +ALTER TABLE t2 CHECK PARTITION p1 MEDIUM; +Table Op Msg_type Msg_text +test.t2 check status OK +INSERT INTO t1 (a,b) VALUES (9,'i'); +ALTER TABLE t1 CHECK PARTITION ALL EXTENDED; +Table Op Msg_type Msg_text +test.t1 check status OK +INSERT INTO t1 (a,b) VALUES (11,'k'); +ALTER TABLE t1 CHECK PARTITION p0 CHANGED; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1, t2; +DROP TABLE IF EXISTS t1,t2; +CREATE TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY HASH(a) PARTITIONS 2; +INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'); +CREATE TABLE t2 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY HASH(a) PARTITIONS 2; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +INSERT INTO t1 (a,b) VALUES (3,'c'); +INSERT INTO t2 (a,b) VALUES (4,'d'); +CHECK TABLE t1, t2 FOR UPGRADE; +Table Op Msg_type Msg_text +test.t1 check status OK +test.t2 check status OK +INSERT INTO t2 (a,b) VALUES (5,'e'); +CHECK TABLE t2 QUICK; +Table Op Msg_type Msg_text +test.t2 check status OK +INSERT INTO t1 (a,b) VALUES (6,'f'); +CHECK TABLE t1 FAST; +Table Op Msg_type Msg_text +test.t1 check status OK +INSERT INTO t1 (a,b) VALUES (7,'g'); +INSERT INTO t2 (a,b) VALUES (8,'h'); +CHECK TABLE t2, t1 MEDIUM; +Table Op Msg_type Msg_text +test.t2 check status OK +test.t1 check status OK +INSERT INTO t1 (a,b) VALUES (9,'i'); +INSERT INTO t2 (a,b) VALUES (10,'j'); +CHECK TABLE t1, t2 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check status OK +test.t2 check status OK +INSERT INTO t1 (a,b) VALUES (11,'k'); +CHECK TABLE t1 CHANGED; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1, t2; +CREATE TABLE t1 (a <INT_COLUMN>, <CUSTOM_INDEX>(a)) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY HASH(a) PARTITIONS 2; +INSERT INTO t1 (a) VALUES (1),(2),(5); +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +INSERT INTO t1 (a) VALUES (6),(8),(12); +CHECK TABLE t1 FOR UPGRADE; +Table Op Msg_type Msg_text +test.t1 check status OK +INSERT INTO t1 (a) VALUES (13),(15),(16); +CHECK TABLE t1 QUICK; +Table Op Msg_type Msg_text +test.t1 check status OK +INSERT INTO t1 (a) VALUES (17),(120),(132); +CHECK TABLE t1 FAST; +Table Op Msg_type Msg_text +test.t1 check status OK +INSERT INTO t1 (a) VALUES (801),(900),(7714); +CHECK TABLE t1 MEDIUM; +Table Op Msg_type Msg_text +test.t1 check status OK +INSERT INTO t1 (a) VALUES (8760),(10023),(12000); +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check status OK +INSERT INTO t1 (a) VALUES (13345),(24456),(78302),(143028); +CHECK TABLE t1 CHANGED; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; diff --git a/mysql-test/suite/storage_engine/parts/check_table.test b/mysql-test/suite/storage_engine/parts/check_table.test new file mode 100644 index 00000000..9a426203 --- /dev/null +++ b/mysql-test/suite/storage_engine/parts/check_table.test @@ -0,0 +1,82 @@ +# +# CHECK TABLE for partitioned tables and ANALYZE PARTITION +# + +--source include/have_partition.inc +--source ../have_engine.inc + + +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +--enable_warnings + +--let $partition_options = PARTITION BY HASH(a) PARTITIONS 2 +--source ../create_table.inc +if ($mysql_errname) +{ + --let $my_last_stmt = $create_statement + --let $functionality = Partitions + --source ../unexpected_result.inc +} +if (!$mysql_errname) +{ + INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'),(3,'c'),(2,'d'),(4,'e'),(100,'f'),(101,'g'); + + let $partition_options = PARTITION BY RANGE(a) ( + PARTITION p0 VALUES LESS THAN (100), + PARTITION p1 VALUES LESS THAN MAXVALUE + ); + --let $table_name = t2 + --source ../create_table.inc + + INSERT INTO t2 (a,b) SELECT a, b FROM t1; + + let $alter_definition = CHECK PARTITION p0; + --source ../alter_table.inc + if ($mysql_errname) + { + --let $my_last_stmt = $alter_statement + --let $functionality = ALTER TABLE .. CHECK PARTITION + --source ../unexpected_result.inc + } + + INSERT INTO t1 (a,b) VALUES (3,'c'); + + let $alter_definition = CHECK PARTITION p0, p1 FOR UPGRADE; + --source ../alter_table.inc + + INSERT INTO t2 (a,b) VALUES (10000,'e'); + + let $alter_definition = CHECK PARTITION p0 QUICK; + let $table_name = t2; + --source ../alter_table.inc + + INSERT INTO t1 (a,b) VALUES (6,'f'); + + let $alter_definition = CHECK PARTITION p1 FAST; + --source ../alter_table.inc + + INSERT INTO t2 (a,b) VALUES (8,'h'); + + let $alter_definition = CHECK PARTITION p1 MEDIUM; + let $table_name = t2; + --source ../alter_table.inc + + INSERT INTO t1 (a,b) VALUES (9,'i'); + + let $alter_definition = CHECK PARTITION ALL EXTENDED; + --source ../alter_table.inc + + INSERT INTO t1 (a,b) VALUES (11,'k'); + + let $alter_definition = CHECK PARTITION p0 CHANGED; + --source ../alter_table.inc + + DROP TABLE t1, t2; +} + +let $extra_tbl_opts = PARTITION BY HASH(a) PARTITIONS 2; +--source ../check_table.inc + +--source ../cleanup_engine.inc + diff --git a/mysql-test/suite/storage_engine/parts/checksum_table.result b/mysql-test/suite/storage_engine/parts/checksum_table.result new file mode 100644 index 00000000..f084c426 --- /dev/null +++ b/mysql-test/suite/storage_engine/parts/checksum_table.result @@ -0,0 +1,40 @@ +DROP TABLE IF EXISTS t1,t2; +CREATE TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> CHECKSUM=0 PARTITION BY HASH(a) PARTITIONS 2; +INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'); +CREATE TABLE t2 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> CHECKSUM=0 PARTITION BY HASH(a) PARTITIONS 2; +CHECKSUM TABLE t1; +Table Checksum +test.t1 4272806499 +CHECKSUM TABLE t2, t1; +Table Checksum +test.t2 0 +test.t1 4272806499 +CHECKSUM TABLE t1, t2 QUICK; +Table Checksum +test.t1 NULL +test.t2 NULL +CHECKSUM TABLE t1, t2 EXTENDED; +Table Checksum +test.t1 4272806499 +test.t2 0 +DROP TABLE t1, t2; +DROP TABLE IF EXISTS t1,t2; +CREATE TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> CHECKSUM=1 PARTITION BY HASH(a) PARTITIONS 2; +INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'); +CREATE TABLE t2 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> CHECKSUM=1 PARTITION BY HASH(a) PARTITIONS 2; +CHECKSUM TABLE t1; +Table Checksum +test.t1 4272806499 +CHECKSUM TABLE t2, t1; +Table Checksum +test.t2 0 +test.t1 4272806499 +CHECKSUM TABLE t1, t2 QUICK; +Table Checksum +test.t1 4272806499 +test.t2 0 +CHECKSUM TABLE t1, t2 EXTENDED; +Table Checksum +test.t1 4272806499 +test.t2 0 +DROP TABLE t1, t2; diff --git a/mysql-test/suite/storage_engine/parts/checksum_table.test b/mysql-test/suite/storage_engine/parts/checksum_table.test new file mode 100644 index 00000000..eff70e3c --- /dev/null +++ b/mysql-test/suite/storage_engine/parts/checksum_table.test @@ -0,0 +1,13 @@ +# +# CHECKSUM TABLE for partitioned tables +# + +--source include/have_partition.inc +--source ../have_engine.inc + +let $extra_tbl_opts = PARTITION BY HASH(a) PARTITIONS 2; +--source ../checksum_table.inc +--source ../checksum_table_live.inc + +--source ../cleanup_engine.inc + diff --git a/mysql-test/suite/storage_engine/parts/create_table.result b/mysql-test/suite/storage_engine/parts/create_table.result new file mode 100644 index 00000000..945ed231 --- /dev/null +++ b/mysql-test/suite/storage_engine/parts/create_table.result @@ -0,0 +1,91 @@ +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (a <INT_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY HASH(a) PARTITIONS 2; +INSERT INTO t1 (a) VALUES (1),(2),(3),(2); +EXPLAIN PARTITIONS SELECT a FROM t1; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0,p1 # # # # # # # +EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a=2; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0 # # # # # # # +DROP TABLE t1; +CREATE TABLE t1 (a <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY KEY(a) PARTITIONS 2; +INSERT INTO t1 (a) VALUES ('a'),('b'),('c'); +EXPLAIN PARTITIONS SELECT a FROM t1; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0,p1 # # # # # # # +EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a = 'b'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p1 # # # # # # # +DROP TABLE t1; +CREATE TABLE t1 (a <INT_COLUMN>, <CUSTOM_INDEX> (a)) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY KEY(a) PARTITIONS 2; +SHOW INDEX IN t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +t1 1 a 1 a # # NULL NULL # # +INSERT INTO t1 (a) VALUES (1),(2),(3),(5); +EXPLAIN PARTITIONS SELECT a FROM t1; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0,p1 # # # # # # # +EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a IN (1,3); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0 # # # # # # # +DROP TABLE t1; +CREATE TABLE t1 (a <INT_COLUMN> PRIMARY KEY) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY KEY() PARTITIONS 2; +SHOW INDEX IN t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +t1 0 PRIMARY 1 a # # NULL NULL # # +INSERT INTO t1 (a) VALUES (1),(200),(3),(2); +EXPLAIN PARTITIONS SELECT a FROM t1; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0,p1 # # # # # # # +EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a=2; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p1 # # # # # # # +DROP TABLE t1; +CREATE TABLE t1 (a <INT_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY RANGE(a) ( +PARTITION p0 VALUES LESS THAN (10), +PARTITION p1 VALUES LESS THAN (1000) +); +INSERT INTO t1 (a) VALUES (1),(2),(400); +EXPLAIN PARTITIONS SELECT a FROM t1; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0,p1 # # # # # # # +EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a = 2; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0 # # # # # # # +INSERT INTO t1 (a) VALUES (10000); +ERROR HY000: Table has no partition for value 10000 +DROP TABLE t1; +CREATE TABLE t1 (a <INT_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY LIST(a) ( +PARTITION abc VALUES IN (1,2,3), +PARTITION def VALUES IN (100,101,102) +); +INSERT INTO t1 (a) VALUES (1),(101),(1); +EXPLAIN PARTITIONS SELECT a FROM t1; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 abc,def # # # # # # # +EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a = 100; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL # # # # # # # +INSERT INTO t1 (a) VALUES (50); +ERROR HY000: Table has no partition for value 50 +DROP TABLE t1; +CREATE TABLE t1 (a <INT_COLUMN>, b <INT_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY LIST(a) SUBPARTITION by HASH(b) ( +PARTITION abc VALUES IN (1,2,3), +PARTITION def VALUES IN (100,101,102) +); +SHOW INDEX IN t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +INSERT INTO t1 (a,b) VALUES (1,1),(101,2),(1,3); +EXPLAIN PARTITIONS SELECT a FROM t1; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 abc_abcsp0,def_defsp0 # # # # # # # +EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a = 100; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL # # # # # # # +SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, PARTITION_METHOD, SUBPARTITION_METHOD +FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 't1'; +TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_METHOD SUBPARTITION_METHOD +test t1 abc abcsp0 LIST HASH +test t1 def defsp0 LIST HASH +SELECT * FROM INFORMATION_SCHEMA.PARTITIONS; +DROP TABLE t1; diff --git a/mysql-test/suite/storage_engine/parts/create_table.test b/mysql-test/suite/storage_engine/parts/create_table.test new file mode 100644 index 00000000..69ab51f7 --- /dev/null +++ b/mysql-test/suite/storage_engine/parts/create_table.test @@ -0,0 +1,179 @@ +# +# Basic CREATE TABLE .. PARTITION statements +# + +--source include/have_partition.inc +--source ../have_engine.inc + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +let $create_definition = a $int_col; +let $partition_options = PARTITION BY HASH(a) PARTITIONS 2; +--source ../create_table.inc +if ($mysql_errname) +{ + --let $my_last_stmt = $create_statement + --let $functionality = Partitions + --source ../unexpected_result.inc +} +if (!$mysql_errname) +{ + INSERT INTO t1 (a) VALUES (1),(2),(3),(2); + # We are only interested in partition information + --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 # + EXPLAIN PARTITIONS SELECT a FROM t1; + --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 # + EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a=2; + + DROP TABLE t1; +} + +let $create_definition = a $char_col; +let $partition_options = PARTITION BY KEY(a) PARTITIONS 2; +--source ../create_table.inc +if ($mysql_errname) +{ + --let $my_last_stmt = $create_statement + --let $functionality = Partitions or CHAR types + --source ../unexpected_result.inc +} +if (!$mysql_errname) +{ + INSERT INTO t1 (a) VALUES ('a'),('b'),('c'); + --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 # + EXPLAIN PARTITIONS SELECT a FROM t1; + --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 # + EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a = 'b'; + DROP TABLE t1; +} + +--let $continue = 1 +--source ../have_default_index.inc +if ($have_default_index) +{ + let $create_definition = a $int_indexed_col, $default_index (a); + let $partition_options = PARTITION BY KEY(a) PARTITIONS 2; + --source ../create_table.inc + if ($mysql_errname) + { + --let $my_last_stmt = $create_statement + --let $functionality = Partitions or indexes + --source ../unexpected_result.inc + } + if (!$mysql_errname) + { + --source ../mask_engine.inc + --replace_column 6 # 7 # 10 # 11 # + SHOW INDEX IN t1; + INSERT INTO t1 (a) VALUES (1),(2),(3),(5); + --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 # + EXPLAIN PARTITIONS SELECT a FROM t1; + --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 # + EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a IN (1,3); + DROP TABLE t1; + } +} + +let $create_definition = a $int_indexed_col PRIMARY KEY; +let $partition_options = PARTITION BY KEY() PARTITIONS 2; +--source ../create_table.inc +if ($mysql_errname) +{ + --let $my_last_stmt = $create_statement + --let $functionality = PK + --source ../unexpected_result.inc +} +if (!$mysql_errname) +{ + --replace_column 6 # 7 # 10 # 11 # + SHOW INDEX IN t1; + + INSERT INTO t1 (a) VALUES (1),(200),(3),(2); + --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 # + EXPLAIN PARTITIONS SELECT a FROM t1; + --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 # + EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a=2; + DROP TABLE t1; +} + + +let $create_definition = a $int_col; +let $partition_options = PARTITION BY RANGE(a) ( + PARTITION p0 VALUES LESS THAN (10), + PARTITION p1 VALUES LESS THAN (1000) +); +--source ../create_table.inc +if ($mysql_errname) +{ + --let $my_last_stmt = $create_statement + --let $functionality = Partitions + --source ../unexpected_result.inc +} +if (!$mysql_errname) +{ + INSERT INTO t1 (a) VALUES (1),(2),(400); + --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 # + EXPLAIN PARTITIONS SELECT a FROM t1; + --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 # + EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a = 2; + + --let $error_codes = ER_NO_PARTITION_FOR_GIVEN_VALUE + INSERT INTO t1 (a) VALUES (10000); + --source ../check_errors.inc + DROP TABLE t1; + + let $create_definition = a $int_col; + let $partition_options = PARTITION BY LIST(a) ( + PARTITION abc VALUES IN (1,2,3), + PARTITION def VALUES IN (100,101,102) + ); + --source ../create_table.inc + + INSERT INTO t1 (a) VALUES (1),(101),(1); + --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 # + EXPLAIN PARTITIONS SELECT a FROM t1; + --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 # + EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a = 100; + --let $error_codes = ER_NO_PARTITION_FOR_GIVEN_VALUE + INSERT INTO t1 (a) VALUES (50); + --source ../check_errors.inc + DROP TABLE t1; +} + +let $create_definition = a $int_col, b $int_col; +let partition_options = PARTITION BY LIST(a) SUBPARTITION by HASH(b) ( + PARTITION abc VALUES IN (1,2,3), + PARTITION def VALUES IN (100,101,102) +); +--source ../create_table.inc +if ($mysql_errname) +{ + --let $my_last_stmt = $create_statement + --let $functionality = Partitions or subpartitions + --source ../unexpected_result.inc +} +if (!$mysql_errname) +{ + --replace_column 6 # 7 # 10 # 11 # + SHOW INDEX IN t1; + + INSERT INTO t1 (a,b) VALUES (1,1),(101,2),(1,3); + --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 # + EXPLAIN PARTITIONS SELECT a FROM t1; + --replace_column 5 # 6 # 7 # 8 # 9 # 10 # 11 # + EXPLAIN PARTITIONS SELECT a FROM t1 WHERE a = 100; + + --sorted_result + SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, PARTITION_METHOD, SUBPARTITION_METHOD + FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 't1'; + # This is just to hit some more handler functions while we are here + --disable_result_log + SELECT * FROM INFORMATION_SCHEMA.PARTITIONS; + --enable_result_log + + DROP TABLE t1; +} +--source ../cleanup_engine.inc + diff --git a/mysql-test/suite/storage_engine/parts/my.cnf b/mysql-test/suite/storage_engine/parts/my.cnf new file mode 100644 index 00000000..d6166310 --- /dev/null +++ b/mysql-test/suite/storage_engine/parts/my.cnf @@ -0,0 +1,9 @@ +!include include/default_my.cnf + +[server] +sql-mode=NO_ENGINE_SUBSTITUTION +binlog-format=row +log-bin=master-bin +partition + + diff --git a/mysql-test/suite/storage_engine/parts/optimize_table.result b/mysql-test/suite/storage_engine/parts/optimize_table.result new file mode 100644 index 00000000..da165bc1 --- /dev/null +++ b/mysql-test/suite/storage_engine/parts/optimize_table.result @@ -0,0 +1,54 @@ +DROP TABLE IF EXISTS t1,t2; +CREATE TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY HASH(a) PARTITIONS 2; +INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'),(3,'c'),(2,'d'),(4,'e'),(100,'f'),(101,'g'); +CREATE TABLE t2 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY RANGE(a) ( +PARTITION p0 VALUES LESS THAN (100), +PARTITION p1 VALUES LESS THAN MAXVALUE +); +INSERT INTO t2 (a,b) SELECT a, b FROM t1; +INSERT INTO t1 (a,b) VALUES (3,'c'),(4,'d'); +ALTER TABLE t1 OPTIMIZE PARTITION p1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +INSERT INTO t2 (a,b) VALUES (4,'d'); +ALTER TABLE t2 OPTIMIZE PARTITION p0 NO_WRITE_TO_BINLOG; +Table Op Msg_type Msg_text +test.t2 optimize status OK +INSERT INTO t1 (a,b) VALUES (6,'f'); +ALTER TABLE t1 OPTIMIZE PARTITION ALL LOCAL; +Table Op Msg_type Msg_text +test.t1 optimize status OK +INSERT INTO t2 (a,b) VALUES (5,'e'); +ALTER TABLE t2 OPTIMIZE PARTITION p1,p0; +Table Op Msg_type Msg_text +test.t2 optimize status OK +DROP TABLE t1, t2; +DROP TABLE IF EXISTS t1,t2; +CREATE TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY HASH(a) PARTITIONS 2; +INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'); +CREATE TABLE t2 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY HASH(a) PARTITIONS 2; +INSERT INTO t1 (a,b) VALUES (3,'c'),(4,'d'); +OPTIMIZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +INSERT INTO t2 (a,b) VALUES (4,'d'); +OPTIMIZE NO_WRITE_TO_BINLOG TABLE t2; +Table Op Msg_type Msg_text +test.t2 optimize status OK +INSERT INTO t2 (a,b) VALUES (5,'e'); +INSERT INTO t1 (a,b) VALUES (6,'f'); +OPTIMIZE LOCAL TABLE t1, t2; +Table Op Msg_type Msg_text +test.t1 optimize status OK +test.t2 optimize status OK +OPTIMIZE TABLE t1, t2; +Table Op Msg_type Msg_text +test.t1 optimize status OK +test.t2 optimize status OK +DROP TABLE t1, t2; +CREATE TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>, <CUSTOM_INDEX> (a)) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY HASH(a) PARTITIONS 2; +INSERT INTO t1 (a,b) VALUES (1,'a'),(100,'b'),(2,'c'),(3,'d'); +OPTIMIZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +DROP TABLE t1; diff --git a/mysql-test/suite/storage_engine/parts/optimize_table.test b/mysql-test/suite/storage_engine/parts/optimize_table.test new file mode 100644 index 00000000..8c136933 --- /dev/null +++ b/mysql-test/suite/storage_engine/parts/optimize_table.test @@ -0,0 +1,69 @@ +# +# OPTIMIZE TABLE with partitions and OPTIMIZE PARTITIONS +# + +--source include/have_partition.inc +--source ../have_engine.inc + +--disable_warnings +DROP TABLE IF EXISTS t1,t2; +--enable_warnings + +let $partition_options = PARTITION BY HASH(a) PARTITIONS 2; +--source ../create_table.inc +if ($mysql_errname) +{ + --let $my_last_stmt = $create_statement + --let $functionality = Partitions + --source ../unexpected_result.inc +} +if (!$mysql_errname) +{ + INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'),(3,'c'),(2,'d'),(4,'e'),(100,'f'),(101,'g'); + + let $table_name = t2; + let $partition_options = PARTITION BY RANGE(a) ( + PARTITION p0 VALUES LESS THAN (100), + PARTITION p1 VALUES LESS THAN MAXVALUE + ); + --source ../create_table.inc + + INSERT INTO t2 (a,b) SELECT a, b FROM t1; + + INSERT INTO t1 (a,b) VALUES (3,'c'),(4,'d'); + + let $alter_definition = OPTIMIZE PARTITION p1; + --source ../alter_table.inc + if ($mysql_errname) + { + --let $my_last_stmt = $alter_statement + --let $functionality = ALTER TABLE .. OPTIMIZE PARTITION + --source ../unexpected_result.inc + } + + INSERT INTO t2 (a,b) VALUES (4,'d'); + + let $table_name = t2; + let $alter_definition = OPTIMIZE PARTITION p0 NO_WRITE_TO_BINLOG; + --source ../alter_table.inc + + INSERT INTO t1 (a,b) VALUES (6,'f'); + + let $alter_definition = OPTIMIZE PARTITION ALL LOCAL; + --source ../alter_table.inc + + INSERT INTO t2 (a,b) VALUES (5,'e'); + + let $table_name = t2; + let $alter_definition = OPTIMIZE PARTITION p1,p0; + --source ../alter_table.inc + + DROP TABLE t1, t2; +} + +let $extra_tbl_opts = PARTITION BY HASH(a) PARTITIONS 2; +--source ../optimize_table.inc + + +--source ../cleanup_engine.inc + diff --git a/mysql-test/suite/storage_engine/parts/repair_table.opt b/mysql-test/suite/storage_engine/parts/repair_table.opt new file mode 100644 index 00000000..759bf0b6 --- /dev/null +++ b/mysql-test/suite/storage_engine/parts/repair_table.opt @@ -0,0 +1 @@ +--myisam-recover-options=DEFAULT diff --git a/mysql-test/suite/storage_engine/parts/repair_table.result b/mysql-test/suite/storage_engine/parts/repair_table.result new file mode 100644 index 00000000..63131250 --- /dev/null +++ b/mysql-test/suite/storage_engine/parts/repair_table.result @@ -0,0 +1,232 @@ +call mtr.add_suppression("Table '.*t1.*' is marked as crashed and should be repaired"); +DROP TABLE IF EXISTS t1, t2; +CREATE TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY HASH(a) PARTITIONS 2; +INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'),(3,'c'),(2,'d'),(4,'e'),(100,'f'),(101,'g'); +CREATE TABLE t2 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY RANGE(a) ( +PARTITION p0 VALUES LESS THAN (100), +PARTITION p1 VALUES LESS THAN MAXVALUE +); +INSERT INTO t2 (a,b) SELECT a, b FROM t1; +ALTER TABLE t1 REPAIR PARTITION p0; +Table Op Msg_type Msg_text +test.t1 repair status OK +INSERT INTO t1 (a,b) VALUES (3,'c'); +ALTER TABLE t1 REPAIR PARTITION NO_WRITE_TO_BINLOG p0, p1; +Table Op Msg_type Msg_text +test.t1 repair status OK +INSERT INTO t2 (a,b) VALUES (5,'e'),(6,'f'); +ALTER TABLE t2 REPAIR PARTITION LOCAL p1; +Table Op Msg_type Msg_text +test.t2 repair status OK +INSERT INTO t1 (a,b) VALUES (7,'g'),(8,'h'); +ALTER TABLE t1 REPAIR PARTITION LOCAL ALL EXTENDED; +Table Op Msg_type Msg_text +test.t1 repair status OK +INSERT INTO t1 (a,b) VALUES (10,'j'); +ALTER TABLE t1 REPAIR PARTITION p1 QUICK USE_FRM; +Table Op Msg_type Msg_text +test.t1 repair status OK +INSERT INTO t2 (a,b) VALUES (12,'l'); +ALTER TABLE t2 REPAIR PARTITION NO_WRITE_TO_BINLOG ALL QUICK EXTENDED USE_FRM; +Table Op Msg_type Msg_text +test.t2 repair status OK +DROP TABLE t1, t2; +DROP TABLE IF EXISTS t1,t2; +CREATE TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY HASH(a) PARTITIONS 2; +INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'); +CREATE TABLE t2 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY HASH(a) PARTITIONS 2; +REPAIR TABLE t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +INSERT INTO t1 (a,b) VALUES (3,'c'); +INSERT INTO t2 (a,b) VALUES (4,'d'); +REPAIR NO_WRITE_TO_BINLOG TABLE t1, t2; +Table Op Msg_type Msg_text +test.t1 repair status OK +test.t2 repair status OK +INSERT INTO t2 (a,b) VALUES (5,'e'),(6,'f'); +REPAIR LOCAL TABLE t2; +Table Op Msg_type Msg_text +test.t2 repair status OK +INSERT INTO t1 (a,b) VALUES (7,'g'),(8,'h'); +INSERT INTO t2 (a,b) VALUES (9,'i'); +REPAIR LOCAL TABLE t2, t1 EXTENDED; +Table Op Msg_type Msg_text +test.t2 repair status OK +test.t1 repair status OK +INSERT INTO t1 (a,b) VALUES (10,'j'); +INSERT INTO t2 (a,b) VALUES (11,'k'); +REPAIR TABLE t1, t2 QUICK USE_FRM; +Table Op Msg_type Msg_text +test.t1 repair status OK +test.t2 repair status OK +INSERT INTO t1 (a,b) VALUES (12,'l'); +INSERT INTO t2 (a,b) VALUES (13,'m'); +REPAIR NO_WRITE_TO_BINLOG TABLE t1, t2 QUICK EXTENDED USE_FRM; +Table Op Msg_type Msg_text +test.t1 repair status OK +test.t2 repair status OK +FLUSH TABLE t1; +INSERT INTO t1 (a,b) VALUES (14,'n'); +ERROR HY000: Failed to read from the .par file +# Statement ended with one of expected results (0,130,ER_FAILED_READ_FROM_PAR_FILE,ER_OPEN_AS_READONLY). +# If you got a difference in error message, just add it to rdiff file +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check Error Failed to read from the .par file +test.t1 check error Corrupt +SELECT a,b FROM t1; +ERROR HY000: Failed to read from the .par file +# Statement ended with one of expected results (0,130,ER_FAILED_READ_FROM_PAR_FILE,ER_OPEN_AS_READONLY). +# If you got a difference in error message, just add it to rdiff file +REPAIR TABLE t1; +Table Op Msg_type Msg_text +test.t1 repair Error Failed to read from the .par file +test.t1 repair error Corrupt +DROP TABLE t1, t2; +call mtr.add_suppression("Got an error from thread_id=.*"); +call mtr.add_suppression("MySQL thread id .*, query id .* localhost.*root Checking table"); +call mtr.add_suppression(" '\..test.t1'"); +call mtr.add_suppression("Couldn't repair table: test.t1"); +call mtr.add_suppression("Table 't1' is marked as crashed.*"); +CREATE TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>, <CUSTOM_INDEX> (a)) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY HASH(a) PARTITIONS 2; +REPAIR TABLE t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +INSERT INTO t1 (a,b) VALUES (7,'g'),(8,'h'); +REPAIR TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 repair status OK +INSERT INTO t1 (a,b) VALUES (10,'j'); +REPAIR TABLE t1 USE_FRM; +Table Op Msg_type Msg_text +test.t1 repair status OK +db.opt +t1#P#p0.MYD +t1#P#p0.MYI +t1#P#p1.MYD +t1#P#p1.MYI +t1.frm +t1.par +INSERT INTO t1 (a,b) VALUES (14,'n'),(15,'o'); +# Statement ended with one of expected results (0,144). +# If you got a difference in error message, just add it to rdiff file +FLUSH TABLE t1; +Restoring <DATADIR>/test/t1#P#p0.MYD +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check error Size of datafile is: 26 Should be: 39 +test.t1 check error Partition p0 returned error +test.t1 check error Corrupt +SELECT a,b FROM t1; +a b +8 h +10 j +7 g +15 o +Warnings: +Error 145 Table './test/t1#P#p0' is marked as crashed and should be repaired +Error 1034 Number of rows changed from 3 to 2 +# Statement ended with one of expected results (0,ER_NOT_KEYFILE,144). +# If you got a difference in error message, just add it to rdiff file +INSERT INTO t1 (a,b) VALUES (14,'n'),(15,'o'); +# Statement ended with one of expected results (0,144). +# If you got a difference in error message, just add it to rdiff file +FLUSH TABLE t1; +Restoring <DATADIR>/test/t1#P#p0.MYI +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check warning Size of datafile is: 39 Should be: 26 +test.t1 check error Record-count is not ok; is 3 Should be: 2 +test.t1 check warning Found 3 key parts. Should be: 2 +test.t1 check error Partition p0 returned error +test.t1 check error Corrupt +SELECT a,b FROM t1; +a b +8 h +10 j +14 n +7 g +15 o +15 o +Warnings: +Error 145 Table './test/t1#P#p0' is marked as crashed and should be repaired +Error 1034 Number of rows changed from 2 to 3 +# Statement ended with one of expected results (0,ER_NOT_KEYFILE,144). +# If you got a difference in error message, just add it to rdiff file +INSERT INTO t1 (a,b) VALUES (14,'n'),(15,'o'); +# Statement ended with one of expected results (0,144). +# If you got a difference in error message, just add it to rdiff file +FLUSH TABLE t1; +Restoring <DATADIR>/test/t1#P#p1.MYD +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check error Size of datafile is: 39 Should be: 52 +test.t1 check error Partition p1 returned error +test.t1 check error Corrupt +SELECT a,b FROM t1; +a b +8 h +10 j +14 n +14 n +7 g +15 o +15 o +Warnings: +Error 145 Table './test/t1#P#p1' is marked as crashed and should be repaired +Error 1034 Number of rows changed from 4 to 3 +# Statement ended with one of expected results (0,ER_NOT_KEYFILE,144). +# If you got a difference in error message, just add it to rdiff file +INSERT INTO t1 (a,b) VALUES (14,'n'),(15,'o'); +# Statement ended with one of expected results (0,144). +# If you got a difference in error message, just add it to rdiff file +FLUSH TABLE t1; +Restoring <DATADIR>/test/t1#P#p1.MYI +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check warning Size of datafile is: 52 Should be: 39 +test.t1 check error Record-count is not ok; is 4 Should be: 3 +test.t1 check warning Found 4 key parts. Should be: 3 +test.t1 check error Partition p1 returned error +test.t1 check error Corrupt +SELECT a,b FROM t1; +a b +8 h +10 j +14 n +14 n +14 n +7 g +15 o +15 o +15 o +Warnings: +Error 145 Table './test/t1#P#p1' is marked as crashed and should be repaired +Error 1034 Number of rows changed from 3 to 4 +# Statement ended with one of expected results (0,ER_NOT_KEYFILE,144). +# If you got a difference in error message, just add it to rdiff file +INSERT INTO t1 (a,b) VALUES (14,'n'),(15,'o'); +# Statement ended with one of expected results (0,144). +# If you got a difference in error message, just add it to rdiff file +FLUSH TABLE t1; +Restoring <DATADIR>/test/t1.par +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT a,b FROM t1; +a b +8 h +10 j +14 n +14 n +14 n +14 n +7 g +15 o +15 o +15 o +15 o +# Statement ended with one of expected results (0,ER_NOT_KEYFILE,144). +# If you got a difference in error message, just add it to rdiff file +DROP TABLE t1; diff --git a/mysql-test/suite/storage_engine/parts/repair_table.test b/mysql-test/suite/storage_engine/parts/repair_table.test new file mode 100644 index 00000000..06be8de1 --- /dev/null +++ b/mysql-test/suite/storage_engine/parts/repair_table.test @@ -0,0 +1,78 @@ +# +# REPAIR TABLE for partitioned tables and REPAIR PARTITION +# + +--source include/have_partition.inc +--source ../have_engine.inc + +call mtr.add_suppression("Table '.*t1.*' is marked as crashed and should be repaired"); + +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +--enable_warnings + +let $partition_options = PARTITION BY HASH(a) PARTITIONS 2; +--source ../create_table.inc +if ($mysql_errname) +{ + --let $my_last_stmt = $create_statement + --let $functionality = Partitions + --source ../unexpected_result.inc +} +if (!$mysql_errname) +{ + INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'),(3,'c'),(2,'d'),(4,'e'),(100,'f'),(101,'g'); + + let $table_name = t2; + let $partition_options = PARTITION BY RANGE(a) ( + PARTITION p0 VALUES LESS THAN (100), + PARTITION p1 VALUES LESS THAN MAXVALUE + ); + --source ../create_table.inc + + INSERT INTO t2 (a,b) SELECT a, b FROM t1; + + let $alter_definition = REPAIR PARTITION p0; + --source ../alter_table.inc + + INSERT INTO t1 (a,b) VALUES (3,'c'); + + let $alter_definition = REPAIR PARTITION NO_WRITE_TO_BINLOG p0, p1; + --source ../alter_table.inc + if ($mysql_errname) + { + --let $my_last_stmt = $alter_statement + --let $functionality = ALTER TABLE + --source ../unexpected_result.inc + } + + INSERT INTO t2 (a,b) VALUES (5,'e'),(6,'f'); + + let $table_name = t2; + let $alter_definition = REPAIR PARTITION LOCAL p1; + --source ../alter_table.inc + + INSERT INTO t1 (a,b) VALUES (7,'g'),(8,'h'); + + let $alter_definition = REPAIR PARTITION LOCAL ALL EXTENDED; + --source ../alter_table.inc + + INSERT INTO t1 (a,b) VALUES (10,'j'); + + let $alter_definition = REPAIR PARTITION p1 QUICK USE_FRM; + --source ../alter_table.inc + + INSERT INTO t2 (a,b) VALUES (12,'l'); + + let $table_name = t2; + let $alter_definition = REPAIR PARTITION NO_WRITE_TO_BINLOG ALL QUICK EXTENDED USE_FRM; + --source ../alter_table.inc + + DROP TABLE t1, t2; +} + +let $extra_tbl_opts = PARTITION BY HASH(a) PARTITIONS 2; +--source ../repair_table.inc + +--source ../cleanup_engine.inc + diff --git a/mysql-test/suite/storage_engine/parts/truncate_table.result b/mysql-test/suite/storage_engine/parts/truncate_table.result new file mode 100644 index 00000000..bfbca529 --- /dev/null +++ b/mysql-test/suite/storage_engine/parts/truncate_table.result @@ -0,0 +1,68 @@ +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY HASH(a) PARTITIONS 2; +TRUNCATE TABLE t1; +INSERT INTO t1 (a,b) VALUES (1,'a'), (2,'b'), (3,'c'); +TRUNCATE TABLE t1; +SELECT a,b FROM t1; +a b +DROP TABLE t1; +CREATE TABLE t1 (a <INT_COLUMN> KEY AUTO_INCREMENT, c <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY HASH(a) PARTITIONS 2; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL AUTO_INCREMENT, + `c` char(8) DEFAULT NULL, + PRIMARY KEY (`a`) +) ENGINE=<STORAGE_ENGINE> DEFAULT CHARSET=latin1 + PARTITION BY HASH (`a`) +PARTITIONS 2 +INSERT INTO t1 (c) VALUES ('a'),('b'),('c'); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL AUTO_INCREMENT, + `c` char(8) DEFAULT NULL, + PRIMARY KEY (`a`) +) ENGINE=<STORAGE_ENGINE> AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 + PARTITION BY HASH (`a`) +PARTITIONS 2 +TRUNCATE TABLE t1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL AUTO_INCREMENT, + `c` char(8) DEFAULT NULL, + PRIMARY KEY (`a`) +) ENGINE=<STORAGE_ENGINE> DEFAULT CHARSET=latin1 + PARTITION BY HASH (`a`) +PARTITIONS 2 +INSERT INTO t1 (c) VALUES ('d'); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL AUTO_INCREMENT, + `c` char(8) DEFAULT NULL, + PRIMARY KEY (`a`) +) ENGINE=<STORAGE_ENGINE> AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 + PARTITION BY HASH (`a`) +PARTITIONS 2 +SELECT a,c FROM t1; +a c +1 d +DROP TABLE t1; +CREATE TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> PARTITION BY HASH(a) PARTITIONS 2; +INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'),(3,'c'),(2,'d'),(4,'e'),(100,'f'),(101,'g'); +ALTER TABLE t1 TRUNCATE PARTITION p0; +SELECT a,b FROM t1; +a b +1 a +101 g +3 c +EXPLAIN PARTITIONS SELECT a,b FROM t1; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p0,p1 # # # # # # +INSERT INTO t1 (a,b) VALUES (1,'a'), (2,'b'), (3,'c'); +ALTER TABLE t1 TRUNCATE PARTITION ALL; +SELECT a,b FROM t1; +a b +DROP TABLE t1; diff --git a/mysql-test/suite/storage_engine/parts/truncate_table.test b/mysql-test/suite/storage_engine/parts/truncate_table.test new file mode 100644 index 00000000..9d921f01 --- /dev/null +++ b/mysql-test/suite/storage_engine/parts/truncate_table.test @@ -0,0 +1,111 @@ +# +# TRUNCATE TABLE with partitions and TRUNCATE PARTITION +# + +--source include/have_partition.inc +--source ../have_engine.inc + +# A part of the standard TRUNCATE test + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + + +# Truncate partitioned table + +let $partition_options = PARTITION BY HASH(a) PARTITIONS 2; +--source ../create_table.inc +if ($mysql_errname) +{ + --let $my_last_stmt = $create_statement + --let $functionality = Partitions + --source ../unexpected_result.inc +} +if (!$mysql_errname) +{ + TRUNCATE TABLE t1; + if ($mysql_errname) + { + --let $functionality = TRUNCATE TABLE + --source ../unexpected_result.inc + } + + INSERT INTO t1 (a,b) VALUES (1,'a'), (2,'b'), (3,'c'); + TRUNCATE TABLE t1; + SELECT a,b FROM t1; + + DROP TABLE t1; +} + + +# Truncate resets auto-increment value on the table + +let $create_definition = a $int_indexed_col KEY AUTO_INCREMENT, c $char_col; +let $partition_options = PARTITION BY HASH(a) PARTITIONS 2; +--source ../create_table.inc + +if ($mysql_errname) +{ + --let $my_last_stmt = $create_statement + --let $functionality = Partitions or PK or auto-increment + --source ../unexpected_result.inc +} +if (!$mysql_errname) +{ + --source ../mask_engine.inc + SHOW CREATE TABLE t1; + INSERT INTO t1 (c) VALUES ('a'),('b'),('c'); + --source ../mask_engine.inc + SHOW CREATE TABLE t1; + TRUNCATE TABLE t1; + --source ../mask_engine.inc + SHOW CREATE TABLE t1; + INSERT INTO t1 (c) VALUES ('d'); + --source ../mask_engine.inc + SHOW CREATE TABLE t1; + SELECT a,c FROM t1; + DROP TABLE t1; +} + + +# Truncate partitions + +let $partition_options = PARTITION BY HASH(a) PARTITIONS 2; +--source ../create_table.inc +if ($mysql_errname) +{ + --let $my_last_stmt = $create_statement + --let $functionality = Partitions + --source ../unexpected_result.inc +} +if (!$mysql_errname) +{ + INSERT INTO t1 (a,b) VALUES (1,'a'),(2,'b'),(3,'c'),(2,'d'),(4,'e'),(100,'f'),(101,'g'); + + let $alter_definition = TRUNCATE PARTITION p0; + --source ../alter_table.inc + if ($mysql_errname) + { + --let $my_last_stmt = $alter_statement + --let $functionality = ALTER TABLE + --source ../unexpected_result.inc + } + + --sorted_result + SELECT a,b FROM t1; + --replace_column 5 # 6 # 7 # 8 # 9 # 10 # + EXPLAIN PARTITIONS SELECT a,b FROM t1; + + INSERT INTO t1 (a,b) VALUES (1,'a'), (2,'b'), (3,'c'); + + let $alter_definition = TRUNCATE PARTITION ALL; + --source ../alter_table.inc + + SELECT a,b FROM t1; + + DROP TABLE t1; +} + +--source ../cleanup_engine.inc + |