762 lines
34 KiB
Plaintext
762 lines
34 KiB
Plaintext
SET default_storage_engine=InnoDB;
|
|
#
|
|
# TABLESPACE related tests.
|
|
#
|
|
#
|
|
# CREATE TABLE ... DATA DIRECTORY
|
|
# Innodb does not support INDEX DIRECTORY.
|
|
#
|
|
SET SESSION innodb_strict_mode = ON;
|
|
CREATE TABLE t1 (a int KEY, b text) INDEX DIRECTORY='MYSQL_TMP_DIR/alt_dir';
|
|
ERROR HY000: Table storage engine for 't1' doesn't have this option
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1478 InnoDB: INDEX DIRECTORY is not supported
|
|
Error 1031 Table storage engine for 't1' doesn't have this option
|
|
#
|
|
# Without strict mode, INDEX DIRECTORY is just ignored
|
|
#
|
|
SET SESSION innodb_strict_mode = OFF;
|
|
CREATE TABLE t1 (a int KEY, b text) INDEX DIRECTORY='MYSQL_TMP_DIR/alt_dir';
|
|
Warnings:
|
|
Warning 1618 <INDEX DIRECTORY> option ignored
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1618 <INDEX DIRECTORY> option ignored
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` text,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
DROP TABLE t1;
|
|
#
|
|
# Innodb does not support DATA DIRECTORY without innodb_file_per_table=ON.
|
|
#
|
|
SET SESSION innodb_strict_mode = ON;
|
|
SET GLOBAL innodb_file_per_table=OFF;
|
|
CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
|
|
ERROR HY000: Table storage engine for 't1' doesn't have this option
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1478 InnoDB: DATA DIRECTORY requires innodb_file_per_table.
|
|
Error 1031 Table storage engine for 't1' doesn't have this option
|
|
#
|
|
# Without strict mode, DATA DIRECTORY without innodb_file_per_table=ON is just ignored.
|
|
#
|
|
SET SESSION innodb_strict_mode = OFF;
|
|
CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
|
|
Warnings:
|
|
Warning 1478 InnoDB: DATA DIRECTORY requires innodb_file_per_table.
|
|
Warning 1618 <DATA DIRECTORY> option ignored
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1478 InnoDB: DATA DIRECTORY requires innodb_file_per_table.
|
|
Warning 1618 <DATA DIRECTORY> option ignored
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` text,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
DROP TABLE t1;
|
|
# Now set innodb_file_per_table so that DATA DIRECTORY can be tested.
|
|
SET GLOBAL innodb_file_per_table=ON;
|
|
#
|
|
# Create the tablespace in MYSQL_TMP_DIR/alt_dir
|
|
# InnoDB will create the sub-directories if needed.
|
|
#
|
|
CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
INSERT INTO t1 VALUES (1, "Create the tablespace");
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 Create the tablespace
|
|
#
|
|
# Check if link file exists in MYSQLD_DATADIR
|
|
#
|
|
---- MYSQLD_DATADIR/test
|
|
# Check if tablespace file exists where we specified in DATA DIRECTORY
|
|
---- MYSQL_TMP_DIR/alt_dir/test
|
|
t1.ibd
|
|
#
|
|
# Check that DATA DIRECTORY shows up in the SHOW CREATE TABLE results.
|
|
#
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` text,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
|
|
# Show that the new system tables have this table in them correctly
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/t1 test/t1 97 8 Dynamic 0 Single
|
|
=== information_schema.innodb_tablespaces and innodb_datafiles ===
|
|
Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path
|
|
mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
test/t1 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t1.ibd
|
|
=== information_schema.files ===
|
|
Space_Name File_Type Engine Status Tablespace_Name Path
|
|
mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
test/t1 TABLESPACE InnoDB NORMAL test/t1 MYSQL_TMP_DIR/alt_dir/test/t1.ibd
|
|
#
|
|
# Show that the system tables are updated on drop table
|
|
#
|
|
DROP TABLE t1;
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
=== information_schema.innodb_tablespaces and innodb_datafiles ===
|
|
Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path
|
|
mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
=== information_schema.files ===
|
|
Space_Name File_Type Engine Status Tablespace_Name Path
|
|
mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
#
|
|
# Create the same table a second time in the same place
|
|
#
|
|
CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
|
|
INSERT INTO t1 VALUES (2, "Create the same table a second time in the same place");
|
|
SELECT * FROM t1;
|
|
a b
|
|
2 Create the same table a second time in the same place
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` text,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/t1 test/t1 97 8 Dynamic 0 Single
|
|
=== information_schema.innodb_tablespaces and innodb_datafiles ===
|
|
Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path
|
|
mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
test/t1 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t1.ibd
|
|
=== information_schema.files ===
|
|
Space_Name File_Type Engine Status Tablespace_Name Path
|
|
mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
test/t1 TABLESPACE InnoDB NORMAL test/t1 MYSQL_TMP_DIR/alt_dir/test/t1.ibd
|
|
---- MYSQLD_DATADIR/test
|
|
---- MYSQL_TMP_DIR/alt_dir/test
|
|
t1.ibd
|
|
#
|
|
# Truncate the table, then insert and verify
|
|
#
|
|
TRUNCATE TABLE t1;
|
|
INSERT INTO t1 VALUES (3, "Truncate the table, then insert");
|
|
SELECT * FROM t1;
|
|
a b
|
|
3 Truncate the table, then insert
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/t1 test/t1 97 8 Dynamic 0 Single
|
|
=== information_schema.innodb_tablespaces and innodb_datafiles ===
|
|
Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path
|
|
mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
test/t1 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t1.ibd
|
|
=== information_schema.files ===
|
|
Space_Name File_Type Engine Status Tablespace_Name Path
|
|
mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
test/t1 TABLESPACE InnoDB NORMAL test/t1 MYSQL_TMP_DIR/alt_dir/test/t1.ibd
|
|
SELECT name, server_version, space_version FROM information_schema.innodb_tablespaces where name="mysql";
|
|
name server_version space_version
|
|
mysql X.Y.Z 1
|
|
---- MYSQLD_DATADIR/test
|
|
---- MYSQL_TMP_DIR/alt_dir/test
|
|
t1.ibd
|
|
#
|
|
# Rename the table, then insert and verify
|
|
#
|
|
RENAME TABLE t1 TO t2;
|
|
INSERT INTO t2 VALUES (4, "Rename the table, then insert");
|
|
SELECT * FROM t2;
|
|
a b
|
|
3 Truncate the table, then insert
|
|
4 Rename the table, then insert
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/t2 test/t2 97 8 Dynamic 0 Single
|
|
=== information_schema.innodb_tablespaces and innodb_datafiles ===
|
|
Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path
|
|
mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
test/t2 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t2.ibd
|
|
=== information_schema.files ===
|
|
Space_Name File_Type Engine Status Tablespace_Name Path
|
|
mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
test/t2 TABLESPACE InnoDB NORMAL test/t2 MYSQL_TMP_DIR/alt_dir/test/t2.ibd
|
|
---- MYSQLD_DATADIR/test
|
|
---- MYSQL_TMP_DIR/alt_dir/test
|
|
t2.ibd
|
|
#
|
|
# CREATE TABLE LIKE does not retain DATA DIRECTORY automatically.
|
|
#
|
|
CREATE TABLE t3 LIKE t2;
|
|
INSERT INTO t3 VALUES (5, "CREATE TABLE LIKE");
|
|
SELECT * FROM t3;
|
|
a b
|
|
5 CREATE TABLE LIKE
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/t2 test/t2 97 8 Dynamic 0 Single
|
|
test/t3 test/t3 33 8 Dynamic 0 Single
|
|
=== information_schema.innodb_tablespaces and innodb_datafiles ===
|
|
Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path
|
|
mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
test/t2 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t2.ibd
|
|
test/t3 Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t3.ibd
|
|
=== information_schema.files ===
|
|
Space_Name File_Type Engine Status Tablespace_Name Path
|
|
mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
test/t2 TABLESPACE InnoDB NORMAL test/t2 MYSQL_TMP_DIR/alt_dir/test/t2.ibd
|
|
test/t3 TABLESPACE InnoDB NORMAL test/t3 MYSQLD_DATADIR/test/t3.ibd
|
|
---- MYSQLD_DATADIR/test
|
|
t3.ibd
|
|
#
|
|
# Now make sure the tables can be fully dropped.
|
|
#
|
|
DROP TABLE t2, t3;
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
=== information_schema.innodb_tablespaces and innodb_datafiles ===
|
|
Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path
|
|
mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
=== information_schema.files ===
|
|
Space_Name File_Type Engine Status Tablespace_Name Path
|
|
mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
---- MYSQLD_DATADIR/test
|
|
---- MYSQL_TMP_DIR/alt_dir/test
|
|
#
|
|
# Be sure SQL MODE "NO_DIR_IN_CREATE" prevents the use of DATA DIRECTORY
|
|
#
|
|
SET @org_mode=@@sql_mode;
|
|
SET @@sql_mode='NO_DIR_IN_CREATE';
|
|
SELECT @@sql_mode;
|
|
@@sql_mode
|
|
NO_DIR_IN_CREATE
|
|
CREATE TABLE t1 (a int, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
|
|
Warnings:
|
|
Warning 1618 <DATA DIRECTORY> option ignored
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1618 <DATA DIRECTORY> option ignored
|
|
INSERT INTO t1 VALUES (6, "SQL MODE NO_DIR_IN_CREATE prevents DATA DIRECTORY");
|
|
DROP TABLE t1;
|
|
set @@sql_mode=@org_mode;
|
|
#
|
|
# MySQL engine does not allow DATA DIRECTORY to be
|
|
# within --datadir for any engine, including InnoDB
|
|
#
|
|
CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY 'MYSQLD_DATADIR/test';
|
|
ERROR HY000: Incorrect arguments to DATA DIRECTORY
|
|
#
|
|
# TEMPORARY tables are incompatible with DATA DIRECTORY
|
|
#
|
|
SET SESSION innodb_strict_mode = ON;
|
|
#
|
|
# CREATE TEMPORARY TABLE with DATA DIRECTORY is rejected in strict mode.
|
|
#
|
|
CREATE TEMPORARY TABLE t1 (a int KEY, b text) engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
|
|
ERROR HY000: Table storage engine for 't1' doesn't have this option
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1478 InnoDB: DATA DIRECTORY cannot be used for TEMPORARY tables.
|
|
Error 1031 Table storage engine for 't1' doesn't have this option
|
|
SET SESSION innodb_strict_mode = OFF;
|
|
#
|
|
# DATA DIRECTORY is ignored in CREATE TEMPORARY TABLE in non-strict mode.
|
|
#
|
|
CREATE TEMPORARY TABLE t1 (a int KEY, b text) engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
|
|
Warnings:
|
|
Warning 1478 InnoDB: DATA DIRECTORY cannot be used for TEMPORARY tables.
|
|
Warning 1618 <DATA DIRECTORY> option ignored
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TEMPORARY TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` text,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
DROP TABLE t1;
|
|
#
|
|
# A warning should be issued if DATA DIR and TEMP is used without
|
|
# innodb_file_per_table
|
|
#
|
|
SET GLOBAL innodb_file_per_table=OFF;
|
|
CREATE TEMPORARY TABLE t1 (a int KEY, b text) engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
|
|
Warnings:
|
|
Warning 1478 InnoDB: DATA DIRECTORY requires innodb_file_per_table.
|
|
Warning 1478 InnoDB: DATA DIRECTORY cannot be used for TEMPORARY tables.
|
|
Warning 1618 <DATA DIRECTORY> option ignored
|
|
DROP TABLE t1;
|
|
SET SESSION innodb_strict_mode = ON;
|
|
CREATE TEMPORARY TABLE t1 (a int KEY, b text) engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
|
|
ERROR HY000: Table storage engine for 't1' doesn't have this option
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1478 InnoDB: DATA DIRECTORY requires innodb_file_per_table.
|
|
Warning 1478 InnoDB: DATA DIRECTORY cannot be used for TEMPORARY tables.
|
|
Error 1031 Table storage engine for 't1' doesn't have this option
|
|
SET SESSION innodb_strict_mode = OFF;
|
|
SET GLOBAL innodb_file_per_table=ON;
|
|
---- MYSQLD_DATADIR/test
|
|
---- MYSQL_TMP_DIR/alt_dir/test
|
|
#
|
|
# Create the remote table via static DDL statements in a stored procedure
|
|
#
|
|
CREATE PROCEDURE static_proc() BEGIN CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'; END |
|
|
CALL static_proc;
|
|
=== information_schema.innodb_tablespaces and innodb_datafiles ===
|
|
Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path
|
|
mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
test/t1 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t1.ibd
|
|
=== information_schema.files ===
|
|
Space_Name File_Type Engine Status Tablespace_Name Path
|
|
mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
test/t1 TABLESPACE InnoDB NORMAL test/t1 MYSQL_TMP_DIR/alt_dir/test/t1.ibd
|
|
INSERT INTO t1 VALUES (7, "Create the remote table via static DDL statements");
|
|
SELECT * FROM t1;
|
|
a b
|
|
7 Create the remote table via static DDL statements
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` text,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
|
|
---- MYSQLD_DATADIR/test
|
|
---- MYSQL_TMP_DIR/alt_dir/test
|
|
t1.ibd
|
|
DROP PROCEDURE static_proc;
|
|
DROP TABLE t1;
|
|
#
|
|
# Create the remote table via dynamic DDL statements in a stored procedure
|
|
#
|
|
CREATE PROCEDURE dynamic_proc() BEGIN PREPARE stmt1 FROM "CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'"; EXECUTE stmt1; END |
|
|
CALL dynamic_proc;
|
|
=== information_schema.innodb_tablespaces and innodb_datafiles ===
|
|
Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path
|
|
mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
test/t1 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t1.ibd
|
|
=== information_schema.files ===
|
|
Space_Name File_Type Engine Status Tablespace_Name Path
|
|
mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
test/t1 TABLESPACE InnoDB NORMAL test/t1 MYSQL_TMP_DIR/alt_dir/test/t1.ibd
|
|
INSERT INTO t1 VALUES (8, "Create the remote table via dynamic DDL statements");
|
|
SELECT * FROM t1;
|
|
a b
|
|
8 Create the remote table via dynamic DDL statements
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` text,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
|
|
---- MYSQLD_DATADIR/test
|
|
---- MYSQL_TMP_DIR/alt_dir/test
|
|
t1.ibd
|
|
DROP PROCEDURE dynamic_proc;
|
|
DEALLOCATE PREPARE stmt1;
|
|
DROP TABLE t1;
|
|
#
|
|
# CREATE, DROP, ADD and TRUNCATE PARTITION with DATA DIRECTORY
|
|
#
|
|
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);
|
|
SHOW CREATE TABLE emp;
|
|
Table Create Table
|
|
emp CREATE TABLE `emp` (
|
|
`id` int(11) NOT NULL,
|
|
`store_name` varchar(30) DEFAULT NULL,
|
|
`parts` varchar(30) DEFAULT NULL,
|
|
`store_id` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY LIST (`store_id`)
|
|
(PARTITION east VALUES IN (10,20,30) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east/' ENGINE = InnoDB,
|
|
PARTITION north VALUES IN (40,50,60) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north/' ENGINE = InnoDB,
|
|
PARTITION west VALUES IN (70,80,100) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west/' ENGINE = InnoDB) */
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/emp#p#east test/emp#p#east 97 10 Dynamic 0 Single
|
|
test/emp#p#north test/emp#p#north 97 10 Dynamic 0 Single
|
|
test/emp#p#west test/emp#p#west 97 10 Dynamic 0 Single
|
|
=== information_schema.innodb_tablespaces and innodb_datafiles ===
|
|
Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path
|
|
mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
test/emp#p#east Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
|
|
test/emp#p#north Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
|
|
test/emp#p#west Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west.ibd
|
|
=== information_schema.files ===
|
|
Space_Name File_Type Engine Status Tablespace_Name Path
|
|
mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
test/emp#p#east TABLESPACE InnoDB NORMAL test/emp#p#east MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
|
|
test/emp#p#north TABLESPACE InnoDB NORMAL test/emp#p#north MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
|
|
test/emp#p#west TABLESPACE InnoDB NORMAL test/emp#p#west MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west.ibd
|
|
SELECT * FROM emp;
|
|
id store_name parts store_id
|
|
1 Oracle NUTT 10
|
|
2 HUAWEI BOLT 40
|
|
3 IBM NAIL 70
|
|
---- MYSQLD_DATADIR/test
|
|
---- MYSQL_TMP_DIR/alt_dir_east/test
|
|
emp#p#east.ibd
|
|
---- MYSQL_TMP_DIR/alt_dir_north/test
|
|
emp#p#north.ibd
|
|
---- MYSQL_TMP_DIR/alt_dir_west/test
|
|
emp#p#west.ibd
|
|
#
|
|
# DROP one PARTITION.
|
|
#
|
|
ALTER TABLE emp DROP PARTITION west;
|
|
SHOW CREATE TABLE emp;
|
|
Table Create Table
|
|
emp CREATE TABLE `emp` (
|
|
`id` int(11) NOT NULL,
|
|
`store_name` varchar(30) DEFAULT NULL,
|
|
`parts` varchar(30) DEFAULT NULL,
|
|
`store_id` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY LIST (`store_id`)
|
|
(PARTITION east VALUES IN (10,20,30) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = InnoDB,
|
|
PARTITION north VALUES IN (40,50,60) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = InnoDB) */
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/emp#p#east test/emp#p#east 97 10 Dynamic 0 Single
|
|
test/emp#p#north test/emp#p#north 97 10 Dynamic 0 Single
|
|
=== information_schema.innodb_tablespaces and innodb_datafiles ===
|
|
Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path
|
|
mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
test/emp#p#east Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
|
|
test/emp#p#north Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
|
|
=== information_schema.files ===
|
|
Space_Name File_Type Engine Status Tablespace_Name Path
|
|
mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
test/emp#p#east TABLESPACE InnoDB NORMAL test/emp#p#east MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
|
|
test/emp#p#north TABLESPACE InnoDB NORMAL test/emp#p#north MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
|
|
SELECT * FROM emp;
|
|
id store_name parts store_id
|
|
1 Oracle NUTT 10
|
|
2 HUAWEI BOLT 40
|
|
---- MYSQLD_DATADIR/test
|
|
---- MYSQL_TMP_DIR/alt_dir_east/test
|
|
emp#p#east.ibd
|
|
---- MYSQL_TMP_DIR/alt_dir_north/test
|
|
emp#p#north.ibd
|
|
---- MYSQL_TMP_DIR/alt_dir_west/test
|
|
#
|
|
# ADD the PARTITION back.
|
|
#
|
|
ALTER TABLE emp ADD PARTITION (
|
|
PARTITION west VALUES IN (70,80,100)
|
|
DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west');
|
|
SHOW CREATE TABLE emp;
|
|
Table Create Table
|
|
emp CREATE TABLE `emp` (
|
|
`id` int(11) NOT NULL,
|
|
`store_name` varchar(30) DEFAULT NULL,
|
|
`parts` varchar(30) DEFAULT NULL,
|
|
`store_id` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY LIST (`store_id`)
|
|
(PARTITION east VALUES IN (10,20,30) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = InnoDB,
|
|
PARTITION north VALUES IN (40,50,60) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = InnoDB,
|
|
PARTITION west VALUES IN (70,80,100) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' ENGINE = InnoDB) */
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/emp#p#east test/emp#p#east 97 10 Dynamic 0 Single
|
|
test/emp#p#north test/emp#p#north 97 10 Dynamic 0 Single
|
|
test/emp#p#west test/emp#p#west 97 10 Dynamic 0 Single
|
|
=== information_schema.innodb_tablespaces and innodb_datafiles ===
|
|
Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path
|
|
mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
test/emp#p#east Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
|
|
test/emp#p#north Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
|
|
test/emp#p#west Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west.ibd
|
|
=== information_schema.files ===
|
|
Space_Name File_Type Engine Status Tablespace_Name Path
|
|
mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
test/emp#p#east TABLESPACE InnoDB NORMAL test/emp#p#east MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
|
|
test/emp#p#north TABLESPACE InnoDB NORMAL test/emp#p#north MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
|
|
test/emp#p#west TABLESPACE InnoDB NORMAL test/emp#p#west MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west.ibd
|
|
INSERT INTO emp VALUES(3,'IBM','NAIL',70);
|
|
SELECT * FROM emp;
|
|
id store_name parts store_id
|
|
1 Oracle NUTT 10
|
|
2 HUAWEI BOLT 40
|
|
3 IBM NAIL 70
|
|
---- MYSQLD_DATADIR/test
|
|
---- MYSQL_TMP_DIR/alt_dir_east/test
|
|
emp#p#east.ibd
|
|
---- MYSQL_TMP_DIR/alt_dir_north/test
|
|
emp#p#north.ibd
|
|
---- MYSQL_TMP_DIR/alt_dir_west/test
|
|
emp#p#west.ibd
|
|
#
|
|
# TRUNCATE one PARTITION.
|
|
#
|
|
ALTER TABLE emp TRUNCATE PARTITION west;
|
|
SHOW CREATE TABLE emp;
|
|
Table Create Table
|
|
emp CREATE TABLE `emp` (
|
|
`id` int(11) NOT NULL,
|
|
`store_name` varchar(30) DEFAULT NULL,
|
|
`parts` varchar(30) DEFAULT NULL,
|
|
`store_id` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY LIST (`store_id`)
|
|
(PARTITION east VALUES IN (10,20,30) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = InnoDB,
|
|
PARTITION north VALUES IN (40,50,60) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = InnoDB,
|
|
PARTITION west VALUES IN (70,80,100) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' ENGINE = InnoDB) */
|
|
=== information_schema.innodb_tables and innodb_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
|
|
mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single
|
|
test/emp#p#east test/emp#p#east 97 10 Dynamic 0 Single
|
|
test/emp#p#north test/emp#p#north 97 10 Dynamic 0 Single
|
|
test/emp#p#west test/emp#p#west 97 10 Dynamic 0 Single
|
|
=== information_schema.innodb_tablespaces and innodb_datafiles ===
|
|
Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path
|
|
mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
test/emp#p#east Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
|
|
test/emp#p#north Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
|
|
test/emp#p#west Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west.ibd
|
|
=== information_schema.files ===
|
|
Space_Name File_Type Engine Status Tablespace_Name Path
|
|
mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
|
|
mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
|
|
test/emp#p#east TABLESPACE InnoDB NORMAL test/emp#p#east MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
|
|
test/emp#p#north TABLESPACE InnoDB NORMAL test/emp#p#north MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
|
|
test/emp#p#west TABLESPACE InnoDB NORMAL test/emp#p#west MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west.ibd
|
|
SELECT * FROM emp;
|
|
id store_name parts store_id
|
|
1 Oracle NUTT 10
|
|
2 HUAWEI BOLT 40
|
|
INSERT INTO emp VALUES(3,'IBM','NAIL',70);
|
|
SELECT * FROM emp;
|
|
id store_name parts store_id
|
|
1 Oracle NUTT 10
|
|
2 HUAWEI BOLT 40
|
|
3 IBM NAIL 70
|
|
---- MYSQLD_DATADIR/test
|
|
---- MYSQL_TMP_DIR/alt_dir_east/test
|
|
emp#p#east.ibd
|
|
---- MYSQL_TMP_DIR/alt_dir_north/test
|
|
emp#p#north.ibd
|
|
---- MYSQL_TMP_DIR/alt_dir_west/test
|
|
emp#p#west.ibd
|
|
DROP TABLE emp;
|
|
#
|
|
# Cleanup
|
|
#
|
|
#
|
|
# Bug#22899690 FAILING ASSERT: FIL_SPACE_GET(TABLE->SPACE) !=__NULL
|
|
# IN ROW0QUIESCE.CC LINE 724
|
|
#
|
|
CREATE TABLE t1(c1 CHAR (1));
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
FLUSH TABLES t1 FOR EXPORT;
|
|
ERROR HY000: Tablespace has been discarded for table 't1'
|
|
UNLOCK TABLES;
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#27903881 [MYSQL 8.0 GA RELEASE & DEBUG BUILD]
|
|
# FIL_SPACE_GET(TABLE->SPACE) != __NULL
|
|
#
|
|
CREATE TABLE t1(c1 INT,c2 CHAR,c3 DATE)PARTITION BY HASH(DAYOFWEEK(c3));
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
FLUSH TABLES t1 FOR EXPORT;
|
|
ERROR HY000: Tablespace has been discarded for table 't1'
|
|
UNLOCK TABLES;
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#29492113 TABLE NOT ACCESSIBLE AFTER MOVING THE IBD FILE,
|
|
# WHEN THE DATABASE NAME HAS "/"
|
|
#
|
|
#
|
|
# Scenario-1
|
|
# create database `abc/def`
|
|
# create table `abc/def`.`xyz/123` in MYSQL_TMP_DIR/my_dir1
|
|
# The directory structure after creating the table should be
|
|
# MYSQL_TMP_DIR/my_dir1/abc@002fdef/xyz@002f123.ibd
|
|
#
|
|
CREATE DATABASE `abc/def`;
|
|
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;
|
|
Level Code Message
|
|
#
|
|
# Validate the directory structure
|
|
#
|
|
abc@002fdef
|
|
xyz@002f123#p#p0@002fpart#sp#s0.ibd
|
|
xyz@002f123#p#p0@002fpart#sp#s1@002fsubpart.ibd
|
|
xyz@002f123#p#p1#sp#s2.ibd
|
|
xyz@002f123#p#p1#sp#s3.ibd
|
|
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`;
|
|
COUNT(*)
|
|
16
|
|
SELECT file_name, tablespace_name FROM information_schema.files WHERE file_name LIKE '%123%';
|
|
FILE_NAME TABLESPACE_NAME
|
|
MYSQL_TMP_DIR/my_dir1/abc@002fdef/xyz@002f123#p#p0@002fpart#sp#s0.ibd abc/def/xyz/123#p#p0/part#sp#s0
|
|
MYSQL_TMP_DIR/my_dir1/abc@002fdef/xyz@002f123#p#p0@002fpart#sp#s1@002fsubpart.ibd abc/def/xyz/123#p#p0/part#sp#s1/subpart
|
|
MYSQL_TMP_DIR/my_dir1/abc@002fdef/xyz@002f123#p#p1#sp#s2.ibd abc/def/xyz/123#p#p1#sp#s2
|
|
MYSQL_TMP_DIR/my_dir1/abc@002fdef/xyz@002f123#p#p1#sp#s3.ibd abc/def/xyz/123#p#p1#sp#s3
|
|
SELECT name, space_type FROM information_schema.innodb_tablespaces WHERE name LIKE '%123%';
|
|
name space_type
|
|
abc/def/xyz/123#p#p0/part#sp#s0 Single
|
|
abc/def/xyz/123#p#p0/part#sp#s1/subpart Single
|
|
abc/def/xyz/123#p#p1#sp#s2 Single
|
|
abc/def/xyz/123#p#p1#sp#s3 Single
|
|
#
|
|
# Shutdown the server
|
|
#
|
|
#
|
|
# Scenario-2
|
|
# Move the IBD file from MYSQL_TMP_DIR/my_dir1 to MYSQL_TMP_DIR/my_dir2
|
|
# Restart the server with --innodb_directories MYSQL_TMP_DIR/my_dir2
|
|
# Query the table
|
|
# Restart the server without --innodb_directories option
|
|
#
|
|
#
|
|
# Move IBD file in MYSQL_TMP_DIR/my_dir1 to MYSQL_TMP_DIR/my_dir2
|
|
#
|
|
#
|
|
# Resart server with --innodb_directories MYSQL_TMP_DIR/my_dir2
|
|
#
|
|
# restart: --innodb_directories=MYSQL_TMP_DIR/my_dir2
|
|
SELECT COUNT(*) FROM `abc/def`.`xyz/123`;
|
|
COUNT(*)
|
|
16
|
|
#
|
|
# Restart server without --innodb_directories option
|
|
#
|
|
# restart:
|
|
SELECT COUNT(*) FROM `abc/def`.`xyz/123`;
|
|
COUNT(*)
|
|
16
|
|
SELECT file_name, tablespace_name FROM information_schema.files WHERE file_name LIKE '%123%';
|
|
FILE_NAME TABLESPACE_NAME
|
|
MYSQL_TMP_DIR/my_dir2/abc@002fdef/xyz@002f123#p#p0@002fpart#sp#s0.ibd abc/def/xyz/123#p#p0/part#sp#s0
|
|
MYSQL_TMP_DIR/my_dir2/abc@002fdef/xyz@002f123#p#p0@002fpart#sp#s1@002fsubpart.ibd abc/def/xyz/123#p#p0/part#sp#s1/subpart
|
|
MYSQL_TMP_DIR/my_dir2/abc@002fdef/xyz@002f123#p#p1#sp#s2.ibd abc/def/xyz/123#p#p1#sp#s2
|
|
MYSQL_TMP_DIR/my_dir2/abc@002fdef/xyz@002f123#p#p1#sp#s3.ibd abc/def/xyz/123#p#p1#sp#s3
|
|
SELECT name, space_type FROM information_schema.innodb_tablespaces WHERE name LIKE '%123%';
|
|
name space_type
|
|
abc/def/xyz/123#p#p0/part#sp#s0 Single
|
|
abc/def/xyz/123#p#p0/part#sp#s1/subpart Single
|
|
abc/def/xyz/123#p#p1#sp#s2 Single
|
|
abc/def/xyz/123#p#p1#sp#s3 Single
|
|
#
|
|
# Shutdown server
|
|
#
|
|
#
|
|
# Scenario-3
|
|
# Move the IBD file from MYSQL_TMP_DIR/my_dir2 to MYSQL_TMP_DIR/my_dir1
|
|
# Restart the server with --innodb_directories MYSQL_TMP_DIR/my_dir1
|
|
#
|
|
#
|
|
# Move IBD file from MYSQL_TMP_DIR/my_dir2 to MYSQL_TMP_DIR/my_dir1
|
|
#
|
|
#
|
|
# Restart server with --innodb_directories MYSQL_TMP_DIR/my_dir1
|
|
#
|
|
# restart: --innodb_directories=MYSQL_TMP_DIR/my_dir1
|
|
SELECT COUNT(*) FROM `abc/def`.`xyz/123`;
|
|
COUNT(*)
|
|
16
|
|
SELECT file_name, tablespace_name FROM information_schema.files WHERE file_name LIKE '%123%';
|
|
FILE_NAME TABLESPACE_NAME
|
|
MYSQL_TMP_DIR/my_dir1/abc@002fdef/xyz@002f123#p#p0@002fpart#sp#s0.ibd abc/def/xyz/123#p#p0/part#sp#s0
|
|
MYSQL_TMP_DIR/my_dir1/abc@002fdef/xyz@002f123#p#p0@002fpart#sp#s1@002fsubpart.ibd abc/def/xyz/123#p#p0/part#sp#s1/subpart
|
|
MYSQL_TMP_DIR/my_dir1/abc@002fdef/xyz@002f123#p#p1#sp#s2.ibd abc/def/xyz/123#p#p1#sp#s2
|
|
MYSQL_TMP_DIR/my_dir1/abc@002fdef/xyz@002f123#p#p1#sp#s3.ibd abc/def/xyz/123#p#p1#sp#s3
|
|
SELECT name, space_type FROM information_schema.innodb_tablespaces WHERE name LIKE '%123%';
|
|
name space_type
|
|
abc/def/xyz/123#p#p0/part#sp#s0 Single
|
|
abc/def/xyz/123#p#p0/part#sp#s1/subpart Single
|
|
abc/def/xyz/123#p#p1#sp#s2 Single
|
|
abc/def/xyz/123#p#p1#sp#s3 Single
|
|
#
|
|
# Cleanup
|
|
#
|
|
DROP DATABASE `abc/def`;
|