570 lines
19 KiB
Plaintext
570 lines
19 KiB
Plaintext
#
|
|
# A series of tests to show the correct tablespace behavior.
|
|
# 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;
|
|
|
|
--echo #
|
|
--echo # TABLESPACE related tests.
|
|
--echo #
|
|
|
|
# Set up some variables
|
|
LET $MYSQLD_DATADIR = `select @@datadir`;
|
|
LET $INNODB_PAGE_SIZE = `select @@innodb_page_size`;
|
|
LET $data_directory_clause = DATA DIRECTORY='$MYSQL_TMP_DIR/alt_dir';
|
|
LET $index_directory_clause = INDEX DIRECTORY='$MYSQL_TMP_DIR/alt_dir';
|
|
|
|
# These values can change during the test
|
|
LET $innodb_file_per_table_orig=`select @@innodb_file_per_table`;
|
|
|
|
--echo #
|
|
--echo # CREATE TABLE ... DATA DIRECTORY
|
|
--echo # Innodb does not support INDEX DIRECTORY.
|
|
--echo #
|
|
SET SESSION innodb_strict_mode = ON;
|
|
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
|
|
--error ER_ILLEGAL_HA
|
|
eval CREATE TABLE t1 (a int KEY, b text) $index_directory_clause;
|
|
SHOW WARNINGS;
|
|
|
|
--echo #
|
|
--echo # Without strict mode, INDEX DIRECTORY is just ignored
|
|
--echo #
|
|
SET SESSION innodb_strict_mode = OFF;
|
|
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
|
|
eval CREATE TABLE t1 (a int KEY, b text) $index_directory_clause;
|
|
SHOW WARNINGS;
|
|
SHOW CREATE TABLE t1;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Innodb does not support DATA DIRECTORY without innodb_file_per_table=ON.
|
|
--echo #
|
|
SET SESSION innodb_strict_mode = ON;
|
|
SET GLOBAL innodb_file_per_table=OFF;
|
|
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
|
|
--error ER_ILLEGAL_HA
|
|
eval CREATE TABLE t1 (a int KEY, b text) $data_directory_clause;
|
|
SHOW WARNINGS;
|
|
|
|
--echo #
|
|
--echo # Without strict mode, DATA DIRECTORY without innodb_file_per_table=ON is just ignored.
|
|
--echo #
|
|
SET SESSION innodb_strict_mode = OFF;
|
|
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
|
|
eval CREATE TABLE t1 (a int KEY, b text) $data_directory_clause;
|
|
SHOW WARNINGS;
|
|
SHOW CREATE TABLE t1;
|
|
DROP TABLE t1;
|
|
|
|
--echo # Now set innodb_file_per_table so that DATA DIRECTORY can be tested.
|
|
SET GLOBAL innodb_file_per_table=ON;
|
|
|
|
--echo #
|
|
--echo # Create the tablespace in MYSQL_TMP_DIR/alt_dir
|
|
--echo # InnoDB will create the sub-directories if needed.
|
|
--echo #
|
|
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
|
|
eval CREATE TABLE t1 (a int KEY, b text) $data_directory_clause;
|
|
SHOW WARNINGS;
|
|
INSERT INTO t1 VALUES (1, "Create the tablespace");
|
|
SELECT * FROM t1;
|
|
|
|
--echo #
|
|
--echo # Check if link file exists in MYSQLD_DATADIR
|
|
--echo #
|
|
--echo ---- MYSQLD_DATADIR/test
|
|
--list_files $MYSQLD_DATADIR/test
|
|
--echo # Check if tablespace file exists where we specified in DATA DIRECTORY
|
|
--echo ---- MYSQL_TMP_DIR/alt_dir/test
|
|
--list_files $MYSQL_TMP_DIR/alt_dir/test
|
|
|
|
--echo #
|
|
--echo # Check that DATA DIRECTORY shows up in the SHOW CREATE TABLE results.
|
|
--echo #
|
|
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
|
|
SHOW CREATE TABLE t1;
|
|
|
|
--echo # Show that the new system tables have this table in them correctly
|
|
--source suite/innodb/include/show_i_s_tables.inc
|
|
--source suite/innodb/include/show_i_s_tablespaces.inc
|
|
|
|
--echo #
|
|
--echo # Show that the system tables are updated on drop table
|
|
--echo #
|
|
DROP TABLE t1;
|
|
--source suite/innodb/include/show_i_s_tables.inc
|
|
--source suite/innodb/include/show_i_s_tablespaces.inc
|
|
|
|
--echo #
|
|
--echo # Create the same table a second time in the same place
|
|
--echo #
|
|
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
|
|
eval CREATE TABLE t1 (a int KEY, b text) $data_directory_clause;
|
|
|
|
INSERT INTO t1 VALUES (2, "Create the same table a second time in the same place");
|
|
SELECT * FROM t1;
|
|
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
|
|
SHOW CREATE TABLE t1;
|
|
--source suite/innodb/include/show_i_s_tables.inc
|
|
--source suite/innodb/include/show_i_s_tablespaces.inc
|
|
--echo ---- MYSQLD_DATADIR/test
|
|
--list_files $MYSQLD_DATADIR/test
|
|
--echo ---- MYSQL_TMP_DIR/alt_dir/test
|
|
--list_files $MYSQL_TMP_DIR/alt_dir/test
|
|
|
|
--echo #
|
|
--echo # Truncate the table, then insert and verify
|
|
--echo #
|
|
TRUNCATE TABLE t1;
|
|
INSERT INTO t1 VALUES (3, "Truncate the table, then insert");
|
|
SELECT * FROM t1;
|
|
|
|
--source suite/innodb/include/show_i_s_tables.inc
|
|
--source suite/innodb/include/show_i_s_tablespaces.inc
|
|
|
|
--replace_regex /[1-9].[0-9].[0-9]+/X.Y.Z/
|
|
SELECT name, server_version, space_version FROM information_schema.innodb_tablespaces where name="mysql";
|
|
|
|
--echo ---- MYSQLD_DATADIR/test
|
|
--list_files $MYSQLD_DATADIR/test
|
|
--echo ---- MYSQL_TMP_DIR/alt_dir/test
|
|
--list_files $MYSQL_TMP_DIR/alt_dir/test
|
|
|
|
--echo #
|
|
--echo # Rename the table, then insert and verify
|
|
--echo #
|
|
RENAME TABLE t1 TO t2;
|
|
INSERT INTO t2 VALUES (4, "Rename the table, then insert");
|
|
SELECT * FROM t2;
|
|
|
|
--source suite/innodb/include/show_i_s_tables.inc
|
|
--source suite/innodb/include/show_i_s_tablespaces.inc
|
|
--echo ---- MYSQLD_DATADIR/test
|
|
--list_files $MYSQLD_DATADIR/test
|
|
--echo ---- MYSQL_TMP_DIR/alt_dir/test
|
|
--list_files $MYSQL_TMP_DIR/alt_dir/test
|
|
|
|
--echo #
|
|
--echo # CREATE TABLE LIKE does not retain DATA DIRECTORY automatically.
|
|
--echo #
|
|
CREATE TABLE t3 LIKE t2;
|
|
INSERT INTO t3 VALUES (5, "CREATE TABLE LIKE");
|
|
SELECT * FROM t3;
|
|
--source suite/innodb/include/show_i_s_tables.inc
|
|
--source suite/innodb/include/show_i_s_tablespaces.inc
|
|
--echo ---- MYSQLD_DATADIR/test
|
|
--list_files $MYSQLD_DATADIR/test
|
|
|
|
--echo #
|
|
--echo # Now make sure the tables can be fully dropped.
|
|
--echo #
|
|
DROP TABLE t2, t3;
|
|
--source suite/innodb/include/show_i_s_tables.inc
|
|
--source suite/innodb/include/show_i_s_tablespaces.inc
|
|
--echo ---- MYSQLD_DATADIR/test
|
|
--list_files $MYSQLD_DATADIR/test
|
|
--echo ---- MYSQL_TMP_DIR/alt_dir/test
|
|
--list_files $MYSQL_TMP_DIR/alt_dir/test
|
|
|
|
--echo #
|
|
--echo # Be sure SQL MODE "NO_DIR_IN_CREATE" prevents the use of DATA DIRECTORY
|
|
--echo #
|
|
SET @org_mode=@@sql_mode;
|
|
SET @@sql_mode='NO_DIR_IN_CREATE';
|
|
SELECT @@sql_mode;
|
|
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
|
|
eval CREATE TABLE t1 (a int, b text) $data_directory_clause;
|
|
SHOW WARNINGS;
|
|
INSERT INTO t1 VALUES (6, "SQL MODE NO_DIR_IN_CREATE prevents DATA DIRECTORY");
|
|
# Checking if tablespace exists in --datadir since DATA DIRECTORY was ignored.
|
|
--file_exists $MYSQLD_DATADIR/test/t1.ibd
|
|
DROP TABLE t1;
|
|
set @@sql_mode=@org_mode;
|
|
|
|
--echo #
|
|
--echo # MySQL engine does not allow DATA DIRECTORY to be
|
|
--echo # within --datadir for any engine, including InnoDB
|
|
--echo #
|
|
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
|
|
--error ER_WRONG_ARGUMENTS
|
|
eval CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY '$MYSQLD_DATADIR/test';
|
|
|
|
--echo #
|
|
--echo # TEMPORARY tables are incompatible with DATA DIRECTORY
|
|
--echo #
|
|
SET SESSION innodb_strict_mode = ON;
|
|
|
|
--echo #
|
|
--echo # CREATE TEMPORARY TABLE with DATA DIRECTORY is rejected in strict mode.
|
|
--echo #
|
|
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
|
|
--error ER_ILLEGAL_HA
|
|
eval CREATE TEMPORARY TABLE t1 (a int KEY, b text) engine=InnoDB $data_directory_clause;
|
|
SHOW WARNINGS;
|
|
|
|
SET SESSION innodb_strict_mode = OFF;
|
|
|
|
--echo #
|
|
--echo # DATA DIRECTORY is ignored in CREATE TEMPORARY TABLE in non-strict mode.
|
|
--echo #
|
|
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
|
|
eval CREATE TEMPORARY TABLE t1 (a int KEY, b text) engine=InnoDB $data_directory_clause;
|
|
SHOW CREATE TABLE t1;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # A warning should be issued if DATA DIR and TEMP is used without
|
|
--echo # innodb_file_per_table
|
|
--echo #
|
|
SET GLOBAL innodb_file_per_table=OFF;
|
|
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
|
|
eval CREATE TEMPORARY TABLE t1 (a int KEY, b text) engine=InnoDB $data_directory_clause;
|
|
DROP TABLE t1;
|
|
SET SESSION innodb_strict_mode = ON;
|
|
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
|
|
--error ER_ILLEGAL_HA
|
|
eval CREATE TEMPORARY TABLE t1 (a int KEY, b text) engine=InnoDB $data_directory_clause;
|
|
SHOW WARNINGS;
|
|
SET SESSION innodb_strict_mode = OFF;
|
|
SET GLOBAL innodb_file_per_table=ON;
|
|
--echo ---- MYSQLD_DATADIR/test
|
|
--list_files $MYSQLD_DATADIR/test
|
|
--echo ---- MYSQL_TMP_DIR/alt_dir/test
|
|
--list_files $MYSQL_TMP_DIR/alt_dir/test
|
|
|
|
--echo #
|
|
--echo # Create the remote table via static DDL statements in a stored procedure
|
|
--echo #
|
|
DELIMITER |;
|
|
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
|
|
eval CREATE PROCEDURE static_proc() BEGIN CREATE TABLE t1 (a int KEY, b text) $data_directory_clause; END |
|
|
DELIMITER ;|
|
|
CALL static_proc;
|
|
--source suite/innodb/include/show_i_s_tablespaces.inc
|
|
INSERT INTO t1 VALUES (7, "Create the remote table via static DDL statements");
|
|
SELECT * FROM t1;
|
|
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
|
|
SHOW CREATE TABLE t1;
|
|
--echo ---- MYSQLD_DATADIR/test
|
|
--list_files $MYSQLD_DATADIR/test
|
|
--echo ---- MYSQL_TMP_DIR/alt_dir/test
|
|
--list_files $MYSQL_TMP_DIR/alt_dir/test
|
|
DROP PROCEDURE static_proc;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Create the remote table via dynamic DDL statements in a stored procedure
|
|
--echo #
|
|
DELIMITER |;
|
|
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
|
|
eval CREATE PROCEDURE dynamic_proc() BEGIN PREPARE stmt1 FROM "CREATE TABLE t1 (a int KEY, b text) $data_directory_clause"; EXECUTE stmt1; END |
|
|
DELIMITER ;|
|
|
CALL dynamic_proc;
|
|
--source suite/innodb/include/show_i_s_tablespaces.inc
|
|
INSERT INTO t1 VALUES (8, "Create the remote table via dynamic DDL statements");
|
|
SELECT * FROM t1;
|
|
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
|
|
SHOW CREATE TABLE t1;
|
|
--echo ---- MYSQLD_DATADIR/test
|
|
--list_files $MYSQLD_DATADIR/test
|
|
--echo ---- MYSQL_TMP_DIR/alt_dir/test
|
|
--list_files $MYSQL_TMP_DIR/alt_dir/test
|
|
DROP PROCEDURE dynamic_proc;
|
|
DEALLOCATE PREPARE stmt1;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # CREATE, DROP, ADD and TRUNCATE PARTITION with DATA DIRECTORY
|
|
--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
|
|
)
|
|
PARTITION BY LIST(store_id) (
|
|
PARTITION east VALUES IN (10,20,30)
|
|
DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_east',
|
|
PARTITION north VALUES IN (40,50,60)
|
|
DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_north',
|
|
PARTITION west VALUES IN (70,80,100)
|
|
DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_west'
|
|
);
|
|
|
|
INSERT INTO emp values(1,'Oracle','NUTT',10);
|
|
INSERT INTO emp values(2,'HUAWEI','BOLT',40);
|
|
INSERT INTO emp values(3,'IBM','NAIL',70);
|
|
|
|
--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
|
|
SELECT * FROM emp;
|
|
--echo ---- MYSQLD_DATADIR/test
|
|
--replace_regex /emp#P#/emp#p#/
|
|
--list_files $MYSQLD_DATADIR/test
|
|
--echo ---- MYSQL_TMP_DIR/alt_dir_east/test
|
|
--replace_regex /emp#P#/emp#p#/
|
|
--list_files $MYSQL_TMP_DIR/alt_dir_east/test
|
|
--echo ---- MYSQL_TMP_DIR/alt_dir_north/test
|
|
--replace_regex /emp#P#/emp#p#/
|
|
--list_files $MYSQL_TMP_DIR/alt_dir_north/test
|
|
--echo ---- MYSQL_TMP_DIR/alt_dir_west/test
|
|
--replace_regex /emp#P#/emp#p#/
|
|
--list_files $MYSQL_TMP_DIR/alt_dir_west/test
|
|
|
|
--echo #
|
|
--echo # DROP one PARTITION.
|
|
--echo #
|
|
ALTER TABLE emp DROP PARTITION west;
|
|
--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
|
|
SELECT * FROM emp;
|
|
--echo ---- MYSQLD_DATADIR/test
|
|
--replace_regex /emp#P#/emp#p#/
|
|
--list_files $MYSQLD_DATADIR/test
|
|
--echo ---- MYSQL_TMP_DIR/alt_dir_east/test
|
|
--replace_regex /emp#P#/emp#p#/
|
|
--list_files $MYSQL_TMP_DIR/alt_dir_east/test
|
|
--echo ---- MYSQL_TMP_DIR/alt_dir_north/test
|
|
--replace_regex /emp#P#/emp#p#/
|
|
--list_files $MYSQL_TMP_DIR/alt_dir_north/test
|
|
--echo ---- MYSQL_TMP_DIR/alt_dir_west/test
|
|
--replace_regex /emp#P#/emp#p#/
|
|
--list_files $MYSQL_TMP_DIR/alt_dir_west/test
|
|
|
|
--echo #
|
|
--echo # ADD the PARTITION back.
|
|
--echo #
|
|
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
|
|
eval ALTER TABLE emp ADD PARTITION (
|
|
PARTITION west VALUES IN (70,80,100)
|
|
DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_west');
|
|
--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
|
|
|
|
INSERT INTO emp VALUES(3,'IBM','NAIL',70);
|
|
SELECT * FROM emp;
|
|
|
|
--echo ---- MYSQLD_DATADIR/test
|
|
--replace_regex /emp#P#/emp#p#/
|
|
--list_files $MYSQLD_DATADIR/test
|
|
--echo ---- MYSQL_TMP_DIR/alt_dir_east/test
|
|
--replace_regex /emp#P#/emp#p#/
|
|
--list_files $MYSQL_TMP_DIR/alt_dir_east/test
|
|
--echo ---- MYSQL_TMP_DIR/alt_dir_north/test
|
|
--replace_regex /emp#P#/emp#p#/
|
|
--list_files $MYSQL_TMP_DIR/alt_dir_north/test
|
|
--echo ---- MYSQL_TMP_DIR/alt_dir_west/test
|
|
--replace_regex /emp#P#/emp#p#/
|
|
--list_files $MYSQL_TMP_DIR/alt_dir_west/test
|
|
|
|
--echo #
|
|
--echo # TRUNCATE one PARTITION.
|
|
--echo #
|
|
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
|
|
ALTER TABLE emp TRUNCATE PARTITION west;
|
|
--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
|
|
|
|
SELECT * FROM emp;
|
|
INSERT INTO emp VALUES(3,'IBM','NAIL',70);
|
|
|
|
SELECT * FROM emp;
|
|
--echo ---- MYSQLD_DATADIR/test
|
|
--replace_regex /emp#P#/emp#p#/
|
|
--list_files $MYSQLD_DATADIR/test
|
|
--echo ---- MYSQL_TMP_DIR/alt_dir_east/test
|
|
--replace_regex /emp#P#/emp#p#/
|
|
--list_files $MYSQL_TMP_DIR/alt_dir_east/test
|
|
--echo ---- MYSQL_TMP_DIR/alt_dir_north/test
|
|
--replace_regex /emp#P#/emp#p#/
|
|
--list_files $MYSQL_TMP_DIR/alt_dir_north/test
|
|
--echo ---- MYSQL_TMP_DIR/alt_dir_west/test
|
|
--replace_regex /emp#P#/emp#p#/
|
|
--list_files $MYSQL_TMP_DIR/alt_dir_west/test
|
|
|
|
DROP TABLE emp;
|
|
|
|
--echo #
|
|
--echo # Cleanup
|
|
--echo #
|
|
|
|
--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/test
|
|
--rmdir $MYSQL_TMP_DIR/alt_dir
|
|
|
|
--disable_query_log
|
|
EVAL SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig;
|
|
--enable_query_log
|
|
|
|
--echo #
|
|
--echo # Bug#22899690 FAILING ASSERT: FIL_SPACE_GET(TABLE->SPACE) !=__NULL
|
|
--echo # IN ROW0QUIESCE.CC LINE 724
|
|
--echo #
|
|
CREATE TABLE t1(c1 CHAR (1));
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--error ER_TABLESPACE_DISCARDED
|
|
FLUSH TABLES t1 FOR EXPORT;
|
|
UNLOCK TABLES;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Bug#27903881 [MYSQL 8.0 GA RELEASE & DEBUG BUILD]
|
|
--echo # FIL_SPACE_GET(TABLE->SPACE) != __NULL
|
|
--echo #
|
|
--disable_query_log
|
|
call mtr.add_suppression("\\[Warning\\] Missing .ibd file for table `test`\.`t1` .* ");
|
|
--enable_query_log
|
|
CREATE TABLE t1(c1 INT,c2 CHAR,c3 DATE)PARTITION BY HASH(DAYOFWEEK(c3));
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--error ER_TABLESPACE_DISCARDED
|
|
FLUSH TABLES t1 FOR EXPORT;
|
|
UNLOCK TABLES;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Bug#29492113 TABLE NOT ACCESSIBLE AFTER MOVING THE IBD FILE,
|
|
--echo # WHEN THE DATABASE NAME HAS "/"
|
|
--echo #
|
|
|
|
--echo #
|
|
--echo # Scenario-1
|
|
--echo # create database `abc/def`
|
|
--echo # create table `abc/def`.`xyz/123` in MYSQL_TMP_DIR/my_dir1
|
|
--echo # The directory structure after creating the table should be
|
|
--echo # MYSQL_TMP_DIR/my_dir1/abc@002fdef/xyz@002f123.ibd
|
|
--echo #
|
|
|
|
CREATE DATABASE `abc/def`;
|
|
|
|
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
|
|
eval CREATE TABLE `abc/def`.`xyz/123` (
|
|
c1 INT AUTO_INCREMENT KEY, c2 CHAR(10))
|
|
PARTITION BY RANGE(c1) SUBPARTITION BY HASH(c1) (
|
|
PARTITION `p0/part` VALUES LESS THAN (10) (
|
|
SUBPARTITION s0 DATA DIRECTORY='$MYSQL_TMP_DIR/my_dir1',
|
|
SUBPARTITION `s1/subpart` DATA DIRECTORY='$MYSQL_TMP_DIR/my_dir1'),
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE (
|
|
SUBPARTITION s2 DATA DIRECTORY='$MYSQL_TMP_DIR/my_dir1',
|
|
SUBPARTITION s3 DATA DIRECTORY='$MYSQL_TMP_DIR/my_dir1'));
|
|
SHOW WARNINGS;
|
|
|
|
--echo #
|
|
--echo # Validate the directory structure
|
|
--echo #
|
|
--list_files $MYSQL_TMP_DIR/my_dir1 *
|
|
--replace_regex /#P#/#p#/ /#SP#/#sp#/
|
|
--list_files $MYSQL_TMP_DIR/my_dir1/abc@002fdef *
|
|
|
|
INSERT INTO `abc/def`.`xyz/123` VALUES (0, 'MySQL');
|
|
INSERT INTO `abc/def`.`xyz/123` (SELECT 0, c2 FROM `abc/def`.`xyz/123`);
|
|
INSERT INTO `abc/def`.`xyz/123` (SELECT 0, c2 FROM `abc/def`.`xyz/123`);
|
|
INSERT INTO `abc/def`.`xyz/123` (SELECT 0, c2 FROM `abc/def`.`xyz/123`);
|
|
INSERT INTO `abc/def`.`xyz/123` (SELECT 0, c2 FROM `abc/def`.`xyz/123`);
|
|
SELECT COUNT(*) FROM `abc/def`.`xyz/123`;
|
|
|
|
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
|
|
--replace_regex /#P#/#p#/ /#SP#/#sp#/
|
|
SELECT file_name, tablespace_name FROM information_schema.files WHERE file_name LIKE '%123%';
|
|
--replace_regex /#P#/#p#/ /#SP#/#sp#/
|
|
SELECT name, space_type FROM information_schema.innodb_tablespaces WHERE name LIKE '%123%';
|
|
|
|
--echo #
|
|
--echo # Shutdown the server
|
|
--echo #
|
|
--source include/shutdown_mysqld.inc
|
|
|
|
--echo #
|
|
--echo # Scenario-2
|
|
--echo # Move the IBD file from MYSQL_TMP_DIR/my_dir1 to MYSQL_TMP_DIR/my_dir2
|
|
--echo # Restart the server with --innodb_directories MYSQL_TMP_DIR/my_dir2
|
|
--echo # Query the table
|
|
--echo # Restart the server without --innodb_directories option
|
|
--echo #
|
|
|
|
--echo #
|
|
--echo # Move IBD file in MYSQL_TMP_DIR/my_dir1 to MYSQL_TMP_DIR/my_dir2
|
|
--echo #
|
|
--mkdir $MYSQL_TMP_DIR/my_dir2
|
|
--mkdir $MYSQL_TMP_DIR/my_dir2/abc@002fdef
|
|
--copy_files_wildcard $MYSQL_TMP_DIR/my_dir1/abc@002fdef/ $MYSQL_TMP_DIR/my_dir2/abc@002fdef/ *
|
|
--remove_files_wildcard $MYSQL_TMP_DIR/my_dir1/abc@002fdef/ *
|
|
|
|
--echo #
|
|
--echo # Resart server with --innodb_directories MYSQL_TMP_DIR/my_dir2
|
|
--echo #
|
|
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
|
|
--let $restart_parameters = "restart: --innodb_directories=$MYSQL_TMP_DIR/my_dir2"
|
|
--source include/start_mysqld.inc
|
|
|
|
SELECT COUNT(*) FROM `abc/def`.`xyz/123`;
|
|
|
|
--echo #
|
|
--echo # Restart server without --innodb_directories option
|
|
--echo #
|
|
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
|
|
--let $restart_parameters = "restart:"
|
|
--source include/restart_mysqld.inc
|
|
|
|
SELECT COUNT(*) FROM `abc/def`.`xyz/123`;
|
|
|
|
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
|
|
--replace_regex /#P#/#p#/ /#SP#/#sp#/
|
|
SELECT file_name, tablespace_name FROM information_schema.files WHERE file_name LIKE '%123%';
|
|
--replace_regex /#P#/#p#/ /#SP#/#sp#/
|
|
SELECT name, space_type FROM information_schema.innodb_tablespaces WHERE name LIKE '%123%';
|
|
|
|
--echo #
|
|
--echo # Shutdown server
|
|
--echo #
|
|
--source include/shutdown_mysqld.inc
|
|
|
|
--echo #
|
|
--echo # Scenario-3
|
|
--echo # Move the IBD file from MYSQL_TMP_DIR/my_dir2 to MYSQL_TMP_DIR/my_dir1
|
|
--echo # Restart the server with --innodb_directories MYSQL_TMP_DIR/my_dir1
|
|
--echo #
|
|
|
|
--echo #
|
|
--echo # Move IBD file from MYSQL_TMP_DIR/my_dir2 to MYSQL_TMP_DIR/my_dir1
|
|
--echo #
|
|
--copy_files_wildcard $MYSQL_TMP_DIR/my_dir2/abc@002fdef/ $MYSQL_TMP_DIR/my_dir1/abc@002fdef/ *
|
|
--force-rmdir $MYSQL_TMP_DIR/my_dir2
|
|
|
|
--echo #
|
|
--echo # Restart server with --innodb_directories MYSQL_TMP_DIR/my_dir1
|
|
--echo #
|
|
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
|
|
--let $restart_parameters="restart: --innodb_directories=$MYSQL_TMP_DIR/my_dir1"
|
|
--source include/start_mysqld.inc
|
|
|
|
SELECT COUNT(*) FROM `abc/def`.`xyz/123`;
|
|
|
|
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
|
|
--replace_regex /#P#/#p#/ /#SP#/#sp#/
|
|
SELECT file_name, tablespace_name FROM information_schema.files WHERE file_name LIKE '%123%';
|
|
--replace_regex /#P#/#p#/ /#SP#/#sp#/
|
|
SELECT name, space_type FROM information_schema.innodb_tablespaces WHERE name LIKE '%123%';
|
|
|
|
--echo #
|
|
--echo # Cleanup
|
|
--echo #
|
|
DROP DATABASE `abc/def`;
|
|
--force-rmdir $MYSQL_TMP_DIR/my_dir1
|