1# 2# A series of tests to show the correct behavior when using 3# ALTER TABLE ... REORGANIZE PARTITION with TokuDB 4# See also; parts.partition_basic_symlink_tokudb.test for 5# partition related tests with remote tablespaces. 6# See tokudb.tokudb-restart for tablespace migration tests. 7# 8--source include/have_tokudb.inc 9--source include/have_partition.inc 10SET default_storage_engine=TokuDB; 11LET $MYSQLD_DATADIR = `select @@datadir`; 12 13# These values can change during the test 14LET $tokudb_file_format_orig=`select @@tokudb_file_format`; 15LET $tokudb_file_per_table_orig=`select @@tokudb_file_per_table`; 16LET $tokudb_strict_mode_orig=`select @@session.tokudb_strict_mode`; 17 18SET SESSION tokudb_strict_mode = ON; 19SET GLOBAL tokudb_file_per_table=ON; 20SET GLOBAL tokudb_file_format=barracuda; 21 22# Unlike MyISAM, TokuDB creates the subdirectories given to it in the 23# DATA DIRECTORY clauses. Another difference is that TokuDB uses an extra 24# directory under DATA DIRECTORY with the name of the database. 25--echo # 26--echo # CREATE a table with SUBPARTITIONS 27--echo # 28--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR 29eval CREATE TABLE emp ( 30 id INT NOT NULL, 31 store_name VARCHAR(30), 32 parts VARCHAR(30), 33 store_id INT 34) engine TokuDB 35PARTITION BY RANGE(store_id) SUBPARTITION BY HASH(store_id) 36( 37 PARTITION northeast VALUES LESS THAN (50) 38 DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_northeast' 39 (SUBPARTITION ne0, SUBPARTITION ne1), 40 PARTITION southwest VALUES LESS THAN (100) 41 DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_southwest' 42 (SUBPARTITION sw2, SUBPARTITION sw3) 43); 44INSERT INTO emp values(1,'Oracle','NUT',10); 45INSERT INTO emp values(2,'SAP','BOLT',40); 46INSERT INTO emp values(3,'IBM','NAIL',60); 47INSERT INTO emp values(4,'SUN','SCREW',90); 48SELECT * FROM emp; 49 50--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR 51SHOW CREATE TABLE emp; 52--replace_regex /#P#/#p#/ /#SP#/#sp#/ 53SELECT name,n_cols,file_format,row_format 54 FROM information_schema.tokudb_sys_tables 55 WHERE name LIKE 'test%' ORDER BY name; 56--replace_regex /#P#/#p#/ /#SP#/#sp#/ 57SELECT name,file_format,row_format 58 FROM information_schema.tokudb_sys_tablespaces 59 WHERE name LIKE 'test%' ORDER BY name; 60--replace_regex /#P#/#p#/ /#SP#/#sp#/ 61--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR 62SELECT path FROM information_schema.tokudb_sys_datafiles 63 WHERE path LIKE '%test%' ORDER BY space; 64--echo ---- MYSQLD_DATADIR/test 65--replace_regex /#P#/#p#/ /#SP#/#sp#/ 66--list_files $MYSQLD_DATADIR/test 67--echo ---- MYSQL_TMP_DIR/alt_dir_northeast/test 68--replace_regex /#P#/#p#/ /#SP#/#sp#/ 69--list_files $MYSQL_TMP_DIR/alt_dir_northeast/test 70--echo ---- MYSQL_TMP_DIR/alt_dir_southwest/test 71--replace_regex /#P#/#p#/ /#SP#/#sp#/ 72--list_files $MYSQL_TMP_DIR/alt_dir_southwest/test 73 74 75--echo # 76--echo # REORGANIZE the PARTITIONS and SUBPARTITIONS 77--echo # 78--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR 79eval ALTER TABLE emp REORGANIZE PARTITION northeast INTO 80( 81 PARTITION east VALUES LESS THAN (25) 82 DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_east' 83 (SUBPARTITION e0, SUBPARTITION e1), 84 PARTITION north VALUES LESS THAN (50) 85 DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_north' 86 (SUBPARTITION n0, SUBPARTITION n1) 87); 88 89--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR 90eval ALTER TABLE emp REORGANIZE PARTITION southwest INTO 91( 92 PARTITION west VALUES LESS THAN (75) 93 DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_west' 94 (SUBPARTITION w0, SUBPARTITION w1), 95 PARTITION south VALUES LESS THAN (100) 96 DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_south' 97 (SUBPARTITION s0, SUBPARTITION s1) 98); 99SELECT * FROM emp; 100--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR 101SHOW CREATE TABLE emp; 102--replace_regex /#P#/#p#/ /#SP#/#sp#/ 103SELECT name,n_cols,file_format,row_format 104 FROM information_schema.tokudb_sys_tables 105 WHERE name LIKE 'test%' ORDER BY name; 106--replace_regex /#P#/#p#/ /#SP#/#sp#/ 107SELECT name,file_format,row_format 108 FROM information_schema.tokudb_sys_tablespaces 109 WHERE name LIKE 'test%' ORDER BY name; 110--replace_regex /#P#/#p#/ /#SP#/#sp#/ 111--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR 112SELECT path FROM information_schema.tokudb_sys_datafiles 113 WHERE path LIKE '%test%' ORDER BY space; 114--echo ---- MYSQLD_DATADIR/test 115--replace_regex /#P#/#p#/ /#SP#/#sp#/ 116--list_files $MYSQLD_DATADIR/test 117--echo ---- MYSQL_TMP_DIR/alt_dir_northeast/test 118--replace_regex /#P#/#p#/ /#SP#/#sp#/ 119--list_files $MYSQL_TMP_DIR/alt_dir_northeast/test 120--echo ---- MYSQL_TMP_DIR/alt_dir_southwest/test 121--replace_regex /#P#/#p#/ /#SP#/#sp#/ 122--list_files $MYSQL_TMP_DIR/alt_dir_southwest/test 123--echo ---- MYSQL_TMP_DIR/alt_dir_east/test 124--replace_regex /#P#/#p#/ /#SP#/#sp#/ 125--list_files $MYSQL_TMP_DIR/alt_dir_east/test 126--echo ---- MYSQL_TMP_DIR/alt_dir_north/test 127--replace_regex /#P#/#p#/ /#SP#/#sp#/ 128--list_files $MYSQL_TMP_DIR/alt_dir_north/test 129--echo ---- MYSQL_TMP_DIR/alt_dir_west/test 130--replace_regex /#P#/#p#/ /#SP#/#sp#/ 131--list_files $MYSQL_TMP_DIR/alt_dir_west/test 132--echo ---- MYSQL_TMP_DIR/alt_dir_south/test 133--replace_regex /#P#/#p#/ /#SP#/#sp#/ 134--list_files $MYSQL_TMP_DIR/alt_dir_south/test 135 136DROP TABLE emp; 137 138--echo # 139--echo # Cleanup 140--echo # 141 142--rmdir $MYSQL_TMP_DIR/alt_dir_northeast/test 143--rmdir $MYSQL_TMP_DIR/alt_dir_northeast 144--rmdir $MYSQL_TMP_DIR/alt_dir_southwest/test 145--rmdir $MYSQL_TMP_DIR/alt_dir_southwest 146--rmdir $MYSQL_TMP_DIR/alt_dir_east/test 147--rmdir $MYSQL_TMP_DIR/alt_dir_east 148--rmdir $MYSQL_TMP_DIR/alt_dir_north/test 149--rmdir $MYSQL_TMP_DIR/alt_dir_north 150--rmdir $MYSQL_TMP_DIR/alt_dir_west/test 151--rmdir $MYSQL_TMP_DIR/alt_dir_west 152--rmdir $MYSQL_TMP_DIR/alt_dir_south/test 153--rmdir $MYSQL_TMP_DIR/alt_dir_south 154 155--disable_query_log 156EVAL SET GLOBAL tokudb_file_format=$tokudb_file_format_orig; 157EVAL SET GLOBAL tokudb_file_per_table=$tokudb_file_per_table_orig; 158EVAL SET SESSION tokudb_strict_mode=$tokudb_strict_mode_orig; 159--enable_query_log 160 161