909 lines
35 KiB
Plaintext
909 lines
35 KiB
Plaintext
#
|
|
# This test shows DISCARD/IMPORT of a remote tablespace.
|
|
#
|
|
SET default_storage_engine=InnoDB;
|
|
SET GLOBAL innodb_file_per_table=ON;
|
|
DROP TABLE IF EXISTS t5980;
|
|
#
|
|
# CREATE TABLE ... DATA DIRECTORY
|
|
# combined with WL#5522 - Transportable Tablespace
|
|
# Create the tablespace in MYSQL_TMP_DIR/alt_dir
|
|
# InnoDB will create the sub-directories if needed.
|
|
# Test that DISCARD and IMPORT work correctly.
|
|
#
|
|
CREATE TABLE t5980 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
|
|
INSERT INTO t5980 VALUES (1, "Create the tablespace");
|
|
SELECT * FROM t5980;
|
|
a b
|
|
1 Create the tablespace
|
|
### files in MYSQLD_DATADIR/test
|
|
### files in MYSQL_TMP_DIR/alt_dir/test
|
|
t5980.ibd
|
|
#
|
|
# Check that DATA DIRECTORY shows up in the SHOW CREATE TABLE results.
|
|
#
|
|
SHOW CREATE TABLE t5980;
|
|
Table Create Table
|
|
t5980 CREATE TABLE `t5980` (
|
|
`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/'
|
|
#
|
|
# Backup the cfg and ibd files.
|
|
#
|
|
FLUSH TABLES t5980 FOR EXPORT;
|
|
SELECT * FROM t5980;
|
|
a b
|
|
1 Create the tablespace
|
|
UNLOCK TABLES;
|
|
### files in MYSQLD_DATADIR/test
|
|
### files in MYSQL_TMP_DIR/alt_dir/test
|
|
t5980.cfg.bak
|
|
t5980.ibd
|
|
t5980.ibd.bak
|
|
#
|
|
# Do some DDL and DML.
|
|
#
|
|
INSERT INTO t5980 VALUES (2,'Remote table has been FLUSHed and UNLOCKed');
|
|
START TRANSACTION;
|
|
INSERT INTO t5980 VALUES (12,'Transactional record inserted');
|
|
COMMIT;
|
|
START TRANSACTION;
|
|
INSERT INTO t5980 VALUES (13,'Rollback this transactional record');
|
|
ROLLBACK;
|
|
SELECT COUNT(*) FROM t5980;
|
|
COUNT(*)
|
|
3
|
|
SELECT * FROM t5980;
|
|
a b
|
|
1 Create the tablespace
|
|
2 Remote table has been FLUSHed and UNLOCKed
|
|
12 Transactional record inserted
|
|
ALTER TABLE t5980 DROP PRIMARY KEY;
|
|
ALTER TABLE t5980 ADD COLUMN c VARCHAR(50) DEFAULT NULL;
|
|
INSERT INTO t5980(a,b,c) VALUES (2,'Duplicate value since primary key has been dropped','third column added');
|
|
SELECT * FROM t5980;
|
|
a b c
|
|
1 Create the tablespace NULL
|
|
2 Remote table has been FLUSHed and UNLOCKed NULL
|
|
12 Transactional record inserted NULL
|
|
2 Duplicate value since primary key has been dropped third column added
|
|
#
|
|
# Make a second backup of the cfg and ibd files.
|
|
#
|
|
FLUSH TABLES t5980 FOR EXPORT;
|
|
SELECT * FROM t5980;
|
|
a b c
|
|
1 Create the tablespace NULL
|
|
2 Remote table has been FLUSHed and UNLOCKed NULL
|
|
12 Transactional record inserted NULL
|
|
2 Duplicate value since primary key has been dropped third column added
|
|
UNLOCK TABLES;
|
|
### files in MYSQLD_DATADIR/test
|
|
### files in MYSQL_TMP_DIR/alt_dir/test
|
|
t5980.cfg.bak
|
|
t5980.cfg.bak2
|
|
t5980.ibd
|
|
t5980.ibd.bak
|
|
t5980.ibd.bak2
|
|
#
|
|
# DROP the table and make sure all files except the backups are gone.
|
|
#
|
|
DROP TABLE t5980;
|
|
### files in MYSQLD_DATADIR/test
|
|
### files in MYSQL_TMP_DIR/alt_dir/test
|
|
t5980.cfg.bak
|
|
t5980.cfg.bak2
|
|
t5980.ibd.bak
|
|
t5980.ibd.bak2
|
|
#
|
|
# CREATE the table again.
|
|
#
|
|
CREATE TABLE t5980 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
|
|
INSERT INTO t5980 VALUES (1, "Create the tablespace a second time");
|
|
SELECT * FROM t5980;
|
|
a b
|
|
1 Create the tablespace a second time
|
|
#
|
|
# DISCARD existing tablespace so backed-up .ibd which can be imported/restored
|
|
#
|
|
ALTER TABLE t5980 DISCARD TABLESPACE;
|
|
SELECT * FROM t5980;
|
|
ERROR HY000: Tablespace has been discarded for table 't5980'
|
|
### files in MYSQLD_DATADIR/test
|
|
### files in MYSQL_TMP_DIR/alt_dir/test
|
|
t5980.cfg.bak
|
|
t5980.cfg.bak2
|
|
t5980.ibd.bak
|
|
t5980.ibd.bak2
|
|
#
|
|
# Restore the second backup of cfg and ibd files.
|
|
#
|
|
"### files in MYSQL_TMP_DIR/alt_dir/test"
|
|
t5980.cfg
|
|
t5980.cfg.bak
|
|
t5980.cfg.bak2
|
|
t5980.ibd
|
|
t5980.ibd.bak
|
|
t5980.ibd.bak2
|
|
#
|
|
# Try to Import the second backup. These backups have extra DDL and
|
|
# do not match the current frm file.
|
|
#
|
|
ALTER TABLE t5980 IMPORT TABLESPACE;
|
|
ERROR HY000: Schema mismatch (Number of columns don't match, table has 8 columns but the tablespace meta-data file has 9 columns)
|
|
CHECK TABLE t5980;
|
|
Table Op Msg_type Msg_text
|
|
test.t5980 check Error Tablespace has been discarded for table 't5980'
|
|
test.t5980 check error Corrupt
|
|
### files in MYSQL_TMP_DIR/alt_dir/test
|
|
t5980.cfg.bak
|
|
t5980.cfg.bak2
|
|
t5980.ibd.bak
|
|
t5980.ibd.bak2
|
|
#
|
|
# Restore the first backup of cfg and ibd files.
|
|
#
|
|
### files in MYSQL_TMP_DIR/alt_dir/test
|
|
t5980.cfg
|
|
t5980.cfg.bak
|
|
t5980.cfg.bak2
|
|
t5980.ibd
|
|
t5980.ibd.bak
|
|
t5980.ibd.bak2
|
|
#
|
|
# Import the tablespace and do some DDL and DML.
|
|
#
|
|
ALTER TABLE t5980 IMPORT TABLESPACE;
|
|
Warnings:
|
|
Warning 1814 InnoDB: Tablespace has been discarded for table 't5980'
|
|
### files in MYSQLD_DATADIR/test
|
|
### files in MYSQL_TMP_DIR/alt_dir/test
|
|
t5980.cfg
|
|
t5980.cfg.bak
|
|
t5980.cfg.bak2
|
|
t5980.ibd
|
|
t5980.ibd.bak
|
|
t5980.ibd.bak2
|
|
CHECK TABLE t5980;
|
|
Table Op Msg_type Msg_text
|
|
test.t5980 check status OK
|
|
SELECT COUNT(*) FROM t5980;
|
|
COUNT(*)
|
|
1
|
|
SELECT * FROM t5980;
|
|
a b
|
|
1 Create the tablespace
|
|
INSERT INTO t5980 VALUES (2,'Inserted record after IMPORT');
|
|
SELECT * FROM t5980;
|
|
a b
|
|
1 Create the tablespace
|
|
2 Inserted record after IMPORT
|
|
START TRANSACTION;
|
|
INSERT INTO t5980 VALUES (12,'Transactional record inserted');
|
|
COMMIT;
|
|
START TRANSACTION;
|
|
INSERT INTO t5980 VALUES (13,'Rollback this transactional record');
|
|
ROLLBACK;
|
|
SELECT * FROM t5980;
|
|
a b
|
|
1 Create the tablespace
|
|
2 Inserted record after IMPORT
|
|
12 Transactional record inserted
|
|
ALTER TABLE t5980 DROP PRIMARY KEY;
|
|
ALTER TABLE t5980 ADD COLUMN c VARCHAR(50) DEFAULT NULL;
|
|
INSERT INTO t5980(a,b,c) VALUES (2,'Duplicate value since primary key has been dropped','third column added');
|
|
SELECT * FROM t5980;
|
|
a b c
|
|
1 Create the tablespace NULL
|
|
2 Inserted record after IMPORT NULL
|
|
12 Transactional record inserted NULL
|
|
2 Duplicate value since primary key has been dropped third column added
|
|
#
|
|
# Show that the 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/t5980 test/t5980 97 9 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/t5980 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980.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/t5980 TABLESPACE InnoDB NORMAL test/t5980 MYSQL_TMP_DIR/alt_dir/test/t5980.ibd
|
|
#
|
|
# Drop the imported table and show that the system tables are updated.
|
|
#
|
|
DROP TABLE t5980;
|
|
=== 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
|
|
### files in MYSQLD_DATADIR/test
|
|
### files in MYSQL_TMP_DIR/alt_dir/test
|
|
t5980.cfg.bak
|
|
t5980.cfg.bak2
|
|
t5980.ibd.bak
|
|
t5980.ibd.bak2
|
|
#
|
|
# CREATE the table a third time.
|
|
#
|
|
CREATE TABLE t5980 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
|
|
INSERT INTO t5980 VALUES (1, "Create the tablespace a third time");
|
|
SELECT * FROM t5980;
|
|
a b
|
|
1 Create the tablespace a third time
|
|
### files in MYSQLD_DATADIR/test
|
|
### files in MYSQL_TMP_DIR/alt_dir/test
|
|
t5980.cfg.bak
|
|
t5980.cfg.bak2
|
|
t5980.ibd
|
|
t5980.ibd.bak
|
|
t5980.ibd.bak2
|
|
#
|
|
# Restart the server
|
|
# This test makes sure that you can still execute the FLUSH TABLES command
|
|
# after restarting the server and the tablespace can still be found.
|
|
#
|
|
# restart
|
|
SET GLOBAL innodb_file_per_table=ON;
|
|
### files in MYSQLD_DATADIR/test
|
|
### files in MYSQL_TMP_DIR/alt_dir/test
|
|
t5980.cfg.bak
|
|
t5980.cfg.bak2
|
|
t5980.ibd
|
|
t5980.ibd.bak
|
|
t5980.ibd.bak2
|
|
SELECT * FROM t5980;
|
|
a b
|
|
1 Create the tablespace a third time
|
|
FLUSH TABLES t5980 FOR EXPORT;
|
|
SELECT * FROM t5980;
|
|
a b
|
|
1 Create the tablespace a third time
|
|
UNLOCK TABLES;
|
|
#
|
|
# Restart the server again. This test makes sure that you can
|
|
# still DISCARD a remote table after restarting the server.
|
|
#
|
|
# restart
|
|
SET GLOBAL innodb_file_per_table=ON;
|
|
SELECT * FROM t5980;
|
|
a b
|
|
1 Create the tablespace a third time
|
|
### files in MYSQLD_DATADIR/test
|
|
### files in MYSQL_TMP_DIR/alt_dir/test
|
|
t5980.cfg.bak
|
|
t5980.cfg.bak2
|
|
t5980.ibd
|
|
t5980.ibd.bak
|
|
t5980.ibd.bak2
|
|
ALTER TABLE t5980 DISCARD TABLESPACE;
|
|
SELECT * FROM t5980;
|
|
ERROR HY000: Tablespace has been discarded for table 't5980'
|
|
### files in MYSQLD_DATADIR/test
|
|
### files in MYSQL_TMP_DIR/alt_dir/test
|
|
t5980.cfg.bak
|
|
t5980.cfg.bak2
|
|
t5980.ibd.bak
|
|
t5980.ibd.bak2
|
|
#
|
|
# Restore the backup of *.ibd and *.cfg files
|
|
#
|
|
### files in MYSQLD_DATADIR/test
|
|
### files in MYSQL_TMP_DIR/alt_dir/test
|
|
t5980.cfg
|
|
t5980.cfg.bak
|
|
t5980.cfg.bak2
|
|
t5980.ibd
|
|
t5980.ibd.bak
|
|
t5980.ibd.bak2
|
|
#
|
|
# Import the tablespace and check it out.
|
|
#
|
|
ALTER TABLE t5980 IMPORT TABLESPACE;
|
|
SELECT * FROM t5980;
|
|
a b
|
|
1 Create the tablespace
|
|
SHOW CREATE TABLE t5980;
|
|
Table Create Table
|
|
t5980 CREATE TABLE `t5980` (
|
|
`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/'
|
|
### files in MYSQLD_DATADIR/test
|
|
### files in MYSQL_TMP_DIR/alt_dir/test
|
|
t5980.cfg
|
|
t5980.cfg.bak
|
|
t5980.cfg.bak2
|
|
t5980.ibd
|
|
t5980.ibd.bak
|
|
t5980.ibd.bak2
|
|
#
|
|
# DISCARD the tablespace again
|
|
#
|
|
ALTER TABLE t5980 DISCARD TABLESPACE;
|
|
SELECT * FROM t5980;
|
|
ERROR HY000: Tablespace has been discarded for table 't5980'
|
|
### files in MYSQLD_DATADIR/test
|
|
### files in MYSQL_TMP_DIR/alt_dir/test
|
|
t5980.cfg.bak
|
|
t5980.cfg.bak2
|
|
t5980.ibd.bak
|
|
t5980.ibd.bak2
|
|
#
|
|
# Restart the engine while the tablespace is in the discarded state
|
|
#
|
|
# restart
|
|
SET GLOBAL innodb_file_per_table=ON;
|
|
SELECT * FROM t5980;
|
|
ERROR HY000: Tablespace has been discarded for table 't5980'
|
|
CHECK TABLE t5980;
|
|
Table Op Msg_type Msg_text
|
|
test.t5980 check Error Tablespace has been discarded for table 't5980'
|
|
test.t5980 check error Corrupt
|
|
#
|
|
# Relocate this discarded file to the default directory
|
|
# instead of the remote directory it was discarded from.
|
|
# Put cfg and idb files into the default directory.
|
|
# Restart the engine again.
|
|
# The tablespace is still in the discarded state.
|
|
#
|
|
### files in MYSQLD_DATADIR/test
|
|
t5980.cfg
|
|
t5980.ibd
|
|
### files in MYSQL_TMP_DIR/alt_dir/test
|
|
t5980.cfg.bak
|
|
t5980.cfg.bak2
|
|
t5980.ibd.bak
|
|
t5980.ibd.bak2
|
|
# Restarting ...
|
|
# restart
|
|
SET GLOBAL innodb_file_per_table=ON;
|
|
SELECT * FROM t5980;
|
|
ERROR HY000: Tablespace has been discarded for table 't5980'
|
|
CHECK TABLE t5980;
|
|
Table Op Msg_type Msg_text
|
|
test.t5980 check Error Tablespace has been discarded for table 't5980'
|
|
test.t5980 check error Corrupt
|
|
#
|
|
# Try to import the tablespace. It can only be imported from
|
|
# the location it was discarded from.
|
|
# The error message for 1810 (IO_READ_ERROR) refers to a local path
|
|
# so do not display it.
|
|
#
|
|
ALTER TABLE t5980 IMPORT TABLESPACE;
|
|
SELECT * FROM t5980;
|
|
ERROR HY000: Tablespace has been discarded for table 't5980'
|
|
CHECK TABLE t5980;
|
|
Table Op Msg_type Msg_text
|
|
test.t5980 check Error Tablespace has been discarded for table 't5980'
|
|
test.t5980 check error Corrupt
|
|
#
|
|
# Restore the ibd and cfg files to the remote directory.
|
|
# Delete the ibd and cfg files from the default directory.
|
|
# Import the tablespace and check it out.
|
|
#
|
|
### files in MYSQLD_DATADIR/test
|
|
### files in MYSQL_TMP_DIR/alt_dir/test
|
|
t5980.cfg
|
|
t5980.cfg.bak
|
|
t5980.cfg.bak2
|
|
t5980.ibd
|
|
t5980.ibd.bak
|
|
t5980.ibd.bak2
|
|
ALTER TABLE t5980 IMPORT TABLESPACE;
|
|
Warnings:
|
|
Warning 1814 InnoDB: Tablespace has been discarded for table 't5980'
|
|
INSERT INTO t5980 VALUES (2, "Insert this record after IMPORT");
|
|
SELECT * FROM t5980;
|
|
a b
|
|
1 Create the tablespace
|
|
2 Insert this record after IMPORT
|
|
SHOW CREATE TABLE t5980;
|
|
Table Create Table
|
|
t5980 CREATE TABLE `t5980` (
|
|
`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 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/t5980 test/t5980 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/t5980 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980.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/t5980 TABLESPACE InnoDB NORMAL test/t5980 MYSQL_TMP_DIR/alt_dir/test/t5980.ibd
|
|
DROP TABLE t5980;
|
|
#
|
|
# Create a local and remote tablespaces, discard two and make
|
|
# the other two missing upon restart, and try some DDL and DML
|
|
# on these discarded and missing tablespaces.
|
|
#
|
|
SET GLOBAL innodb_file_per_table=ON;
|
|
CREATE TABLE t5980a (a int, b text) engine=InnoDB;
|
|
CREATE TABLE t5980b (a int, b text) engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
|
|
CREATE TABLE t5980c (a int, b text) engine=InnoDB;
|
|
CREATE TABLE t5980d (a int, b text) engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
|
|
INSERT INTO t5980a VALUES (1, "Default location, discarded.");
|
|
INSERT INTO t5980b VALUES (1, "Remote location, discarded");
|
|
INSERT INTO t5980c VALUES (1, "Default location, missing");
|
|
INSERT INTO t5980d VALUES (1, "Remote location, missing");
|
|
SELECT * FROM t5980a;
|
|
a b
|
|
1 Default location, discarded.
|
|
SELECT * FROM t5980b;
|
|
a b
|
|
1 Remote location, discarded
|
|
SELECT * FROM t5980c;
|
|
a b
|
|
1 Default location, missing
|
|
SELECT * FROM t5980d;
|
|
a b
|
|
1 Remote location, missing
|
|
SHOW CREATE TABLE t5980a;
|
|
Table Create Table
|
|
t5980a CREATE TABLE `t5980a` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` text
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SHOW CREATE TABLE t5980b;
|
|
Table Create Table
|
|
t5980b CREATE TABLE `t5980b` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` text
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
|
|
SHOW CREATE TABLE t5980c;
|
|
Table Create Table
|
|
t5980c CREATE TABLE `t5980c` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` text
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SHOW CREATE TABLE t5980d;
|
|
Table Create Table
|
|
t5980d CREATE TABLE `t5980d` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` text
|
|
) 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/t5980a test/t5980a 33 8 Dynamic 0 Single
|
|
test/t5980b test/t5980b 97 8 Dynamic 0 Single
|
|
test/t5980c test/t5980c 33 8 Dynamic 0 Single
|
|
test/t5980d test/t5980d 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/t5980a Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t5980a.ibd
|
|
test/t5980b Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980b.ibd
|
|
test/t5980c Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t5980c.ibd
|
|
test/t5980d Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980d.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/t5980a TABLESPACE InnoDB NORMAL test/t5980a MYSQLD_DATADIR/test/t5980a.ibd
|
|
test/t5980b TABLESPACE InnoDB NORMAL test/t5980b MYSQL_TMP_DIR/alt_dir/test/t5980b.ibd
|
|
test/t5980c TABLESPACE InnoDB NORMAL test/t5980c MYSQLD_DATADIR/test/t5980c.ibd
|
|
test/t5980d TABLESPACE InnoDB NORMAL test/t5980d MYSQL_TMP_DIR/alt_dir/test/t5980d.ibd
|
|
### files in MYSQLD_DATADIR/test
|
|
t5980a.ibd
|
|
t5980c.ibd
|
|
### files in MYSQL_TMP_DIR/alt_dir/test
|
|
t5980b.ibd
|
|
t5980d.ibd
|
|
#
|
|
# Shutdown the server, remove two tablespaces, restart server.
|
|
#
|
|
# restart
|
|
FLUSH TABLES t5980a, t5980b FOR EXPORT;
|
|
UNLOCK TABLES;
|
|
ALTER TABLE t5980a DISCARD TABLESPACE;
|
|
ALTER TABLE t5980b DISCARD TABLESPACE;
|
|
### files in MYSQLD_DATADIR/test
|
|
### files in MYSQL_TMP_DIR/alt_dir/test
|
|
SELECT * FROM t5980a;
|
|
ERROR HY000: Tablespace has been discarded for table 't5980a'
|
|
SELECT * FROM t5980b;
|
|
ERROR HY000: Tablespace has been discarded for table 't5980b'
|
|
SELECT * FROM t5980c;
|
|
ERROR HY000: Tablespace is missing for table `test`.`t5980c`.
|
|
SELECT * FROM t5980d;
|
|
ERROR HY000: Tablespace is missing for table `test`.`t5980d`.
|
|
SHOW CREATE TABLE t5980a;
|
|
Table Create Table
|
|
t5980a CREATE TABLE `t5980a` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` text
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SHOW CREATE TABLE t5980b;
|
|
Table Create Table
|
|
t5980b CREATE TABLE `t5980b` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` text
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
|
|
SHOW CREATE TABLE t5980c;
|
|
ERROR HY000: Tablespace is missing for table `test`.`t5980c`.
|
|
SHOW CREATE TABLE t5980d;
|
|
ERROR HY000: Tablespace is missing for table `test`.`t5980d`.
|
|
=== 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/t5980a test/t5980a 33 8 Dynamic 0 Single
|
|
test/t5980b test/t5980b 97 8 Dynamic 0 Single
|
|
test/t5980c test/t5980c 33 8 Dynamic 0 Single
|
|
test/t5980d test/t5980d 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/t5980a Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t5980a.ibd
|
|
test/t5980b Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980b.ibd
|
|
test/t5980c Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t5980c.ibd
|
|
test/t5980d Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980d.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
|
|
Warnings:
|
|
Warning 1812 Tablespace is missing for table test/t5980#.
|
|
Warning 1812 Tablespace is missing for table test/t5980#.
|
|
Warning 1812 Tablespace is missing for table test/t5980#.
|
|
Warning 1812 Tablespace is missing for table test/t5980#.
|
|
### files in MYSQLD_DATADIR/test
|
|
### files in MYSQL_TMP_DIR/alt_dir/test
|
|
#
|
|
# Discarded and missing tablespaces cannot be TRUNCATED
|
|
#
|
|
TRUNCATE TABLE t5980a;
|
|
ERROR HY000: Tablespace has been discarded for table 'test/t5980a'
|
|
call mtr.add_suppression("Table .* does not exist .* though MySQL .* rename");
|
|
TRUNCATE TABLE t5980b;
|
|
ERROR HY000: Tablespace has been discarded for table 'test/t5980b'
|
|
TRUNCATE TABLE t5980c;
|
|
ERROR HY000: Tablespace is missing for table `test`.`t5980c`.
|
|
TRUNCATE TABLE t5980d;
|
|
ERROR HY000: Tablespace is missing for table `test`.`t5980d`.
|
|
#
|
|
# Discarded tablespaces can be RENAMED but they remain discarded
|
|
#
|
|
RENAME TABLE t5980a TO t5980aa;
|
|
RENAME TABLE t5980b TO t5980bb;
|
|
#
|
|
# Missing tablespaces cannot be RENAMED
|
|
#
|
|
RENAME TABLE t5980c TO t5980cc;
|
|
ERROR HY000: Error on rename of './test/t5980c' to './test/t5980cc' (errno: 155 - The table does not exist in engine)
|
|
RENAME TABLE t5980d TO t5980dd;
|
|
ERROR HY000: Error on rename of './test/t5980d' to './test/t5980dd' (errno: 155 - The table does not exist in engine)
|
|
SELECT * FROM t5980a;
|
|
ERROR 42S02: Table 'test.t5980a' doesn't exist
|
|
SELECT * FROM t5980b;
|
|
ERROR 42S02: Table 'test.t5980b' doesn't exist
|
|
SELECT * FROM t5980aa;
|
|
ERROR HY000: Tablespace has been discarded for table 't5980aa'
|
|
SELECT * FROM t5980bb;
|
|
ERROR HY000: Tablespace has been discarded for table 't5980bb'
|
|
SELECT * FROM t5980c;
|
|
ERROR HY000: Tablespace is missing for table `test`.`t5980c`.
|
|
SELECT * FROM t5980d;
|
|
ERROR HY000: Tablespace is missing for table `test`.`t5980d`.
|
|
SHOW CREATE TABLE t5980aa;
|
|
Table Create Table
|
|
t5980aa CREATE TABLE `t5980aa` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` text
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SHOW CREATE TABLE t5980bb;
|
|
Table Create Table
|
|
t5980bb CREATE TABLE `t5980bb` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` text
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
|
|
SHOW CREATE TABLE t5980c;
|
|
ERROR HY000: Tablespace is missing for table `test`.`t5980c`.
|
|
SHOW CREATE TABLE t5980d;
|
|
ERROR HY000: Tablespace is missing for table `test`.`t5980d`.
|
|
=== 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/t5980aa test/t5980aa 33 8 Dynamic 0 Single
|
|
test/t5980bb test/t5980bb 97 8 Dynamic 0 Single
|
|
test/t5980c test/t5980c 33 8 Dynamic 0 Single
|
|
test/t5980d test/t5980d 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/t5980aa Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t5980aa.ibd
|
|
test/t5980bb Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980bb.ibd
|
|
test/t5980c Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t5980c.ibd
|
|
test/t5980d Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980d.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
|
|
Warnings:
|
|
Warning 1812 Tablespace is missing for table test/t5980#.
|
|
Warning 1812 Tablespace is missing for table test/t5980#.
|
|
Warning 1812 Tablespace is missing for table test/t5980#.
|
|
Warning 1812 Tablespace is missing for table test/t5980#.
|
|
#
|
|
# Discarded tablespaces cannot be ALTERED with ALGORITHM=COPY.
|
|
#
|
|
ALTER TABLE t5980aa ADD PRIMARY KEY(a), ALGORITHM=COPY;
|
|
ERROR HY000: Tablespace has been discarded for table 't5980aa'
|
|
ALTER TABLE t5980bb ADD PRIMARY KEY(a), ALGORITHM=COPY;
|
|
ERROR HY000: Tablespace has been discarded for table 't5980bb'
|
|
ALTER TABLE t5980aa CHANGE a c INT, ALGORITHM=COPY;
|
|
ERROR HY000: Tablespace has been discarded for table 't5980aa'
|
|
ALTER TABLE t5980bb CHANGE a c INT, ALGORITHM=COPY;
|
|
ERROR HY000: Tablespace has been discarded for table 't5980bb'
|
|
#
|
|
# Discarded tablespaces can be ALTERED with ALGORITHM=INPLACE
|
|
# only if a rebuild is not required.
|
|
#
|
|
ALTER TABLE t5980aa ADD PRIMARY KEY(a), ALGORITHM=INPLACE;
|
|
ERROR HY000: Tablespace has been discarded for table 'test/t5980aa'
|
|
ALTER TABLE t5980bb ADD PRIMARY KEY(a), ALGORITHM=INPLACE;
|
|
ERROR HY000: Tablespace has been discarded for table 'test/t5980bb'
|
|
ALTER TABLE t5980aa RENAME t5980aaa, ALGORITHM=INPLACE;
|
|
ALTER TABLE t5980aaa RENAME t5980aa, ALGORITHM=INPLACE;
|
|
Warnings:
|
|
Warning 1814 InnoDB: Tablespace has been discarded for table 't5980aaa'
|
|
ALTER TABLE t5980bb RENAME t5980bbb, ALGORITHM=INPLACE;
|
|
ALTER TABLE t5980bbb RENAME t5980bb, ALGORITHM=INPLACE;
|
|
Warnings:
|
|
Warning 1814 InnoDB: Tablespace has been discarded for table 't5980bbb'
|
|
### files in MYSQLD_DATADIR/test
|
|
### files in MYSQL_TMP_DIR/alt_dir/test
|
|
#
|
|
# Missing tablespaces cannot be ALTERED.
|
|
#
|
|
ALTER TABLE t5980c ADD PRIMARY KEY(a);
|
|
ERROR HY000: Tablespace is missing for table `test`.`t5980c`.
|
|
ALTER TABLE t5980d ADD PRIMARY KEY(a);
|
|
ERROR HY000: Tablespace is missing for table `test`.`t5980d`.
|
|
SELECT * FROM t5980aa;
|
|
ERROR HY000: Tablespace has been discarded for table 't5980aa'
|
|
SELECT * FROM t5980bb;
|
|
ERROR HY000: Tablespace has been discarded for table 't5980bb'
|
|
SHOW CREATE TABLE t5980aa;
|
|
Table Create Table
|
|
t5980aa CREATE TABLE `t5980aa` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` text
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SHOW CREATE TABLE t5980bb;
|
|
Table Create Table
|
|
t5980bb CREATE TABLE `t5980bb` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` text
|
|
) 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/t5980aa test/t5980aa 33 8 Dynamic 0 Single
|
|
test/t5980bb test/t5980bb 97 8 Dynamic 0 Single
|
|
test/t5980c test/t5980c 33 8 Dynamic 0 Single
|
|
test/t5980d test/t5980d 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/t5980aa Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t5980aa.ibd
|
|
test/t5980bb Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980bb.ibd
|
|
test/t5980c Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t5980c.ibd
|
|
test/t5980d Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980d.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
|
|
Warnings:
|
|
Warning 1812 Tablespace is missing for table test/t5980#.
|
|
Warning 1812 Tablespace is missing for table test/t5980#.
|
|
Warning 1812 Tablespace is missing for table test/t5980#.
|
|
Warning 1812 Tablespace is missing for table test/t5980#.
|
|
### files in MYSQLD_DATADIR/test
|
|
### files in MYSQL_TMP_DIR/alt_dir/test
|
|
#
|
|
# Restart the server to check if the discarded flag is persistent
|
|
#
|
|
# restart
|
|
#
|
|
# Discarded tablespaces that were ALTERED IN_PLACE are still discarded.
|
|
#
|
|
INSERT INTO t5980aa VALUES (1, "Inserted into Discarded Local tablespace after ALTER ADD PRIMARY KEY, ALGORITHM=INPLACE");
|
|
ERROR HY000: Tablespace has been discarded for table 't5980aa'
|
|
INSERT INTO t5980bb VALUES (1, "Inserted into Discarded Local tablespace after ALTER ADD PRIMARY KEY, ALGORITHM=INPLACE");
|
|
ERROR HY000: Tablespace has been discarded for table 't5980bb'
|
|
SELECT * FROM t5980aa;
|
|
ERROR HY000: Tablespace has been discarded for table 't5980aa'
|
|
SELECT * FROM t5980bb;
|
|
ERROR HY000: Tablespace has been discarded for table 't5980bb'
|
|
RENAME TABLE t5980aa TO t5980a;
|
|
RENAME TABLE t5980bb TO t5980b;
|
|
SHOW CREATE TABLE t5980a;
|
|
Table Create Table
|
|
t5980a CREATE TABLE `t5980a` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` text
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
Warnings:
|
|
Warning 1814 InnoDB: Tablespace has been discarded for table 't5980a'
|
|
SHOW CREATE TABLE t5980b;
|
|
Table Create Table
|
|
t5980b CREATE TABLE `t5980b` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` text
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
|
|
Warnings:
|
|
Warning 1814 InnoDB: Tablespace has been discarded for table 't5980b'
|
|
=== 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/t5980a test/t5980a 33 8 Dynamic 0 Single
|
|
test/t5980b test/t5980b 97 8 Dynamic 0 Single
|
|
test/t5980c test/t5980c 33 8 Dynamic 0 Single
|
|
test/t5980d test/t5980d 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/t5980a Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t5980a.ibd
|
|
test/t5980b Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980b.ibd
|
|
test/t5980c Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t5980c.ibd
|
|
test/t5980d Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980d.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
|
|
Warnings:
|
|
Warning 1812 Tablespace is missing for table test/t5980#.
|
|
Warning 1812 Tablespace is missing for table test/t5980#.
|
|
Warning 1812 Tablespace is missing for table test/t5980#.
|
|
Warning 1812 Tablespace is missing for table test/t5980#.
|
|
### files in MYSQLD_DATADIR/test
|
|
### files in MYSQL_TMP_DIR/alt_dir/test
|
|
#
|
|
# Discard tablespaces again and try another ALTER TABLE ROW_FORMAT.
|
|
#
|
|
ALTER TABLE t5980a DISCARD TABLESPACE;
|
|
Warnings:
|
|
Warning 1812 InnoDB: Tablespace is missing for table test/t5980a.
|
|
ALTER TABLE t5980b DISCARD TABLESPACE;
|
|
Warnings:
|
|
Warning 1812 InnoDB: Tablespace is missing for table test/t5980b.
|
|
SELECT * FROM t5980a;
|
|
ERROR HY000: Tablespace has been discarded for table 't5980a'
|
|
SELECT * FROM t5980b;
|
|
ERROR HY000: Tablespace has been discarded for table 't5980b'
|
|
#
|
|
# ALTER TABLE ALGORITHM=COPY cannot use a discarded tablespace.
|
|
#
|
|
ALTER TABLE t5980a ROW_FORMAT=REDUNDANT, ALGORITHM=COPY;
|
|
ERROR HY000: Tablespace has been discarded for table 't5980a'
|
|
ALTER TABLE t5980b ROW_FORMAT=REDUNDANT, ALGORITHM=COPY;
|
|
ERROR HY000: Tablespace has been discarded for table 't5980b'
|
|
#
|
|
# ALTER TABLE ALGORITHM=INPLACE can use a discarded tablespace.
|
|
# only if a rebuild is not required.
|
|
#
|
|
ALTER TABLE t5980a ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE;
|
|
ERROR HY000: Tablespace has been discarded for table 'test/t5980a'
|
|
ALTER TABLE t5980b ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE;
|
|
ERROR HY000: Tablespace has been discarded for table 'test/t5980b'
|
|
#
|
|
# Discarded tablespaces that were ALTERED IN_PLACE are still discarded.
|
|
#
|
|
INSERT INTO t5980a VALUES (1, "Inserted into discarded local tablespace after ALTER ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE");
|
|
ERROR HY000: Tablespace has been discarded for table 't5980a'
|
|
INSERT INTO t5980b VALUES (1, "Inserted into discarded local tablespace after ALTER ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE");
|
|
ERROR HY000: Tablespace has been discarded for table 't5980b'
|
|
SELECT * FROM t5980a;
|
|
ERROR HY000: Tablespace has been discarded for table 't5980a'
|
|
SELECT * FROM t5980b;
|
|
ERROR HY000: Tablespace has been discarded for table 't5980b'
|
|
### files in MYSQLD_DATADIR/test
|
|
### files in MYSQL_TMP_DIR/alt_dir/test
|
|
#
|
|
# Discard tablespaces again and try ALTER TABLE ADD COLUMN.
|
|
#
|
|
ALTER TABLE t5980a DISCARD TABLESPACE;
|
|
Warnings:
|
|
Warning 1812 InnoDB: Tablespace is missing for table test/t5980a.
|
|
ALTER TABLE t5980b DISCARD TABLESPACE;
|
|
Warnings:
|
|
Warning 1812 InnoDB: Tablespace is missing for table test/t5980b.
|
|
SELECT * FROM t5980a;
|
|
ERROR HY000: Tablespace has been discarded for table 't5980a'
|
|
SELECT * FROM t5980b;
|
|
ERROR HY000: Tablespace has been discarded for table 't5980b'
|
|
#
|
|
# ALTER TABLE ALGORITHM=COPY cannot use a discarded tablespace.
|
|
#
|
|
ALTER TABLE t5980a ADD COLUMN c CHAR(20), ALGORITHM=COPY;
|
|
ERROR HY000: Tablespace has been discarded for table 't5980a'
|
|
ALTER TABLE t5980b ADD COLUMN c CHAR(20), ALGORITHM=COPY;
|
|
ERROR HY000: Tablespace has been discarded for table 't5980b'
|
|
#
|
|
# ALTER TABLE ALGORITHM=INPLACE can use a discarded tablespace.
|
|
# only if a rebuild is not required.
|
|
#
|
|
ALTER TABLE t5980a ADD COLUMN c CHAR(20), ALGORITHM=INPLACE;
|
|
ERROR HY000: Tablespace has been discarded for table 'test/t5980a'
|
|
ALTER TABLE t5980b ADD COLUMN c CHAR(20), ALGORITHM=INPLACE;
|
|
ERROR HY000: Tablespace has been discarded for table 'test/t5980b'
|
|
#
|
|
# Discarded tablespaces that were ALTERED IN_PLACE are still discarded.
|
|
#
|
|
DELETE FROM t5980a;
|
|
ERROR HY000: Tablespace has been discarded for table 't5980a'
|
|
UPDATE t5980a SET b="Tablespace is DISCARDED";
|
|
ERROR HY000: Tablespace has been discarded for table 't5980a'
|
|
INSERT INTO t5980a VALUES (1, "Inserted into discarded local tablespace after ALTER ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE");
|
|
ERROR HY000: Tablespace has been discarded for table 't5980a'
|
|
INSERT INTO t5980b VALUES (1, "Inserted into discarded local tablespace after ALTER ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE");
|
|
ERROR HY000: Tablespace has been discarded for table 't5980b'
|
|
SELECT * FROM t5980a;
|
|
ERROR HY000: Tablespace has been discarded for table 't5980a'
|
|
SELECT * FROM t5980b;
|
|
ERROR HY000: Tablespace has been discarded for table 't5980b'
|
|
### files in MYSQLD_DATADIR/test
|
|
### files in MYSQL_TMP_DIR/alt_dir/test
|
|
DROP TABLE t5980a;
|
|
DROP TABLE t5980b;
|
|
DROP TABLE t5980c;
|
|
DROP TABLE t5980d;
|
|
=== 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
|
|
### files in MYSQLD_DATADIR/test
|
|
### files in MYSQL_TMP_DIR/alt_dir/test
|
|
#
|
|
# Cleanup
|
|
#
|