219 lines
6.9 KiB
Plaintext
219 lines
6.9 KiB
Plaintext
#
|
|
# A series of tests to show the correct behavior when using
|
|
# ALTER TABLE ... REORGANIZE PARTITION with InnoDB
|
|
# See also; parts.partition_basic_symlink_innodb.test for
|
|
# partition related tests with remote tablespaces.
|
|
# See innodb.innodb-restart for tablespace migration tests.
|
|
#
|
|
SET default_storage_engine=InnoDB;
|
|
LET $MYSQLD_DATADIR = `select @@datadir`;
|
|
LET $INNODB_PAGE_SIZE = `select @@innodb_page_size`;
|
|
|
|
# These values can change during the test
|
|
LET $innodb_file_per_table_orig=`select @@innodb_file_per_table`;
|
|
LET $innodb_strict_mode_orig=`select @@session.innodb_strict_mode`;
|
|
|
|
SET SESSION innodb_strict_mode = ON;
|
|
SET GLOBAL innodb_file_per_table=ON;
|
|
|
|
# Unlike MyISAM, InnoDB creates the subdirectories given to it in the
|
|
# DATA DIRECTORY clauses. Another difference is that InnoDB uses an extra
|
|
# directory under DATA DIRECTORY with the name of the database.
|
|
--echo #
|
|
--echo # CREATE a table with SUBPARTITIONS
|
|
--echo #
|
|
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
|
|
eval CREATE TABLE emp (
|
|
id INT NOT NULL,
|
|
store_name VARCHAR(30),
|
|
parts VARCHAR(30),
|
|
store_id INT
|
|
) engine InnoDB
|
|
PARTITION BY RANGE(store_id) SUBPARTITION BY HASH(store_id)
|
|
(
|
|
PARTITION northeast VALUES LESS THAN (50)
|
|
DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_northeast'
|
|
(SUBPARTITION ne0, SUBPARTITION ne1),
|
|
PARTITION southwest VALUES LESS THAN (100)
|
|
DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_southwest'
|
|
(SUBPARTITION sw2, SUBPARTITION sw3)
|
|
);
|
|
INSERT INTO emp values(1,'Oracle','NUT',10);
|
|
INSERT INTO emp values(2,'SAP','BOLT',40);
|
|
INSERT INTO emp values(3,'IBM','NAIL',60);
|
|
INSERT INTO emp values(4,'SUN','SCREW',90);
|
|
SELECT * FROM emp;
|
|
|
|
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
|
|
SHOW CREATE TABLE emp;
|
|
--source suite/innodb/include/show_i_s_tables.inc
|
|
--source suite/innodb/include/show_i_s_tablespaces.inc
|
|
--echo ---- MYSQLD_DATADIR/test
|
|
--replace_regex /#P#/#p#/ /#SP#/#sp#/
|
|
--list_files $MYSQLD_DATADIR/test
|
|
--echo ---- MYSQL_TMP_DIR/alt_dir_northeast/test
|
|
--replace_regex /#P#/#p#/ /#SP#/#sp#/
|
|
--list_files $MYSQL_TMP_DIR/alt_dir_northeast/test
|
|
--echo ---- MYSQL_TMP_DIR/alt_dir_southwest/test
|
|
--replace_regex /#P#/#p#/ /#SP#/#sp#/
|
|
--list_files $MYSQL_TMP_DIR/alt_dir_southwest/test
|
|
|
|
|
|
--echo #
|
|
--echo # REORGANIZE the PARTITIONS and SUBPARTITIONS
|
|
--echo #
|
|
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
|
|
eval ALTER TABLE emp REORGANIZE PARTITION northeast INTO
|
|
(
|
|
PARTITION east VALUES LESS THAN (25)
|
|
DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_east'
|
|
(SUBPARTITION e0, SUBPARTITION e1),
|
|
PARTITION north VALUES LESS THAN (50)
|
|
DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_north'
|
|
(SUBPARTITION n0, SUBPARTITION n1)
|
|
);
|
|
|
|
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
|
|
eval ALTER TABLE emp REORGANIZE PARTITION southwest INTO
|
|
(
|
|
PARTITION west VALUES LESS THAN (75)
|
|
DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_west'
|
|
(SUBPARTITION w0, SUBPARTITION w1),
|
|
PARTITION south VALUES LESS THAN (100)
|
|
DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_south'
|
|
(SUBPARTITION s0, SUBPARTITION s1)
|
|
);
|
|
SELECT * FROM emp;
|
|
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
|
|
SHOW CREATE TABLE emp;
|
|
--source suite/innodb/include/show_i_s_tables.inc
|
|
--source suite/innodb/include/show_i_s_tablespaces.inc
|
|
--echo ---- MYSQLD_DATADIR/test
|
|
--replace_regex /#P#/#p#/ /#SP#/#sp#/
|
|
--list_files $MYSQLD_DATADIR/test
|
|
--echo ---- MYSQL_TMP_DIR/alt_dir_northeast/test
|
|
--replace_regex /#P#/#p#/ /#SP#/#sp#/
|
|
--list_files $MYSQL_TMP_DIR/alt_dir_northeast/test
|
|
--echo ---- MYSQL_TMP_DIR/alt_dir_southwest/test
|
|
--replace_regex /#P#/#p#/ /#SP#/#sp#/
|
|
--list_files $MYSQL_TMP_DIR/alt_dir_southwest/test
|
|
--echo ---- MYSQL_TMP_DIR/alt_dir_east/test
|
|
--replace_regex /#P#/#p#/ /#SP#/#sp#/
|
|
--list_files $MYSQL_TMP_DIR/alt_dir_east/test
|
|
--echo ---- MYSQL_TMP_DIR/alt_dir_north/test
|
|
--replace_regex /#P#/#p#/ /#SP#/#sp#/
|
|
--list_files $MYSQL_TMP_DIR/alt_dir_north/test
|
|
--echo ---- MYSQL_TMP_DIR/alt_dir_west/test
|
|
--replace_regex /#P#/#p#/ /#SP#/#sp#/
|
|
--list_files $MYSQL_TMP_DIR/alt_dir_west/test
|
|
--echo ---- MYSQL_TMP_DIR/alt_dir_south/test
|
|
--replace_regex /#P#/#p#/ /#SP#/#sp#/
|
|
--list_files $MYSQL_TMP_DIR/alt_dir_south/test
|
|
|
|
DROP TABLE emp;
|
|
|
|
--echo #
|
|
--echo # Cleanup
|
|
--echo #
|
|
|
|
--rmdir $MYSQL_TMP_DIR/alt_dir_northeast/test
|
|
--rmdir $MYSQL_TMP_DIR/alt_dir_northeast
|
|
--rmdir $MYSQL_TMP_DIR/alt_dir_southwest/test
|
|
--rmdir $MYSQL_TMP_DIR/alt_dir_southwest
|
|
--rmdir $MYSQL_TMP_DIR/alt_dir_east/test
|
|
--rmdir $MYSQL_TMP_DIR/alt_dir_east
|
|
--rmdir $MYSQL_TMP_DIR/alt_dir_north/test
|
|
--rmdir $MYSQL_TMP_DIR/alt_dir_north
|
|
--rmdir $MYSQL_TMP_DIR/alt_dir_west/test
|
|
--rmdir $MYSQL_TMP_DIR/alt_dir_west
|
|
--rmdir $MYSQL_TMP_DIR/alt_dir_south/test
|
|
--rmdir $MYSQL_TMP_DIR/alt_dir_south
|
|
|
|
|
|
# To test if reorganize partition into same partition/subpartition name
|
|
# would work correctly
|
|
|
|
# Scenario one, a partitioned table
|
|
CREATE TABLE t1 (a INT) ENGINE = InnoDB
|
|
PARTITION BY RANGE (a)
|
|
(partition p0 values less than MAXVALUE);
|
|
|
|
INSERT INTO t1 VALUES (1), (11), (21), (33);
|
|
SELECT * FROM t1;
|
|
|
|
SHOW CREATE TABLE t1;
|
|
|
|
--echo ---- MYSQLD_DATADIR/test
|
|
--replace_regex /#P#/#p#/ /#SP#/#sp#/
|
|
--list_files $MYSQLD_DATADIR/test
|
|
|
|
ALTER TABLE t1 REORGANIZE PARTITION p0 INTO
|
|
(PARTITION p0 VALUES LESS THAN (10),
|
|
PARTITION p10 VALUES LESS THAN MAXVALUE);
|
|
|
|
SHOW CREATE TABLE t1;
|
|
|
|
SELECT * FROM t1;
|
|
|
|
--echo ---- MYSQLD_DATADIR/test
|
|
--replace_regex /#P#/#p#/ /#SP#/#sp#/
|
|
--list_files $MYSQLD_DATADIR/test
|
|
|
|
DROP TABLE t1;
|
|
|
|
# Scenario two, a subpartitioned table
|
|
CREATE TABLE tsp (
|
|
a INT,
|
|
b VARCHAR(55),
|
|
PRIMARY KEY (a))
|
|
ENGINE = InnoDB
|
|
PARTITION BY RANGE (a)
|
|
SUBPARTITION BY HASH(a)
|
|
(PARTITION p0 VALUES LESS THAN (10)
|
|
(SUBPARTITION sp00,
|
|
SUBPARTITION sp01,
|
|
SUBPARTITION sp02,
|
|
SUBPARTITION sp03,
|
|
SUBPARTITION sp04),
|
|
PARTITION p1 VALUES LESS THAN (20)
|
|
(SUBPARTITION sp10,
|
|
SUBPARTITION sp11,
|
|
SUBPARTITION sp12,
|
|
SUBPARTITION sp13,
|
|
SUBPARTITION sp14),
|
|
PARTITION p2 VALUES LESS THAN (100)
|
|
(SUBPARTITION sp20,
|
|
SUBPARTITION sp21,
|
|
SUBPARTITION sp22,
|
|
SUBPARTITION sp23,
|
|
SUBPARTITION sp24));
|
|
|
|
INSERT INTO tsp VALUES(1, 'one'), (5, 'five'), (15, 'fifteen'), (30, 'thirty'), (40, 'forty'), (61, 'sixty one'), (75, 'seventy five'), (79, 'seventy nine'), (81, 'eighty one'), (83, 'eighty three'), (99, 'ninety nine');
|
|
|
|
SELECT * FROM tsp PARTITION(p2);
|
|
|
|
--echo ---- MYSQLD_DATADIR/test
|
|
--replace_regex /#P#/#p#/ /#SP#/#sp#/
|
|
--list_files $MYSQLD_DATADIR/test
|
|
|
|
ALTER TABLE tsp REORGANIZE PARTITION p2 INTO(
|
|
PARTITION p20 VALUES LESS THAN(60) (SUBPARTITION sp200, SUBPARTITION sp201, SUBPARTITION sp202, SUBPARTITION sp203, SUBPARTITION sp204),
|
|
PARTITION p2 VALUES LESS THAN(100) (SUBPARTITION sp20, SUBPARTITION sp211, SUBPARTITION sp22, SUBPARTITION sp213, SUBPARTITION sp24));
|
|
|
|
SHOW CREATE TABLE tsp;
|
|
|
|
--echo ---- MYSQLD_DATADIR/test
|
|
--replace_regex /#P#/#p#/ /#SP#/#sp#/
|
|
--list_files $MYSQLD_DATADIR/test
|
|
|
|
SELECT * FROM tsp PARTITION(p2);
|
|
SELECT * FROM tsp PARTITION(p20);
|
|
|
|
DROP TABLE tsp;
|
|
|
|
--disable_query_log
|
|
EVAL SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig;
|
|
EVAL SET SESSION innodb_strict_mode=$innodb_strict_mode_orig;
|
|
--enable_query_log
|
|
|