--echo #
--echo # This test shows DISCARD/IMPORT of a remote tablespace.
--echo #

# Not supported in embedded
--source include/not_embedded.inc

-- source include/have_innodb.inc

--disable_query_log
# These values can change during the test
LET $innodb_file_per_table_orig=`select @@innodb_file_per_table`;

# This warning is expected in the log
call mtr.add_suppression("tablespace is set as discarded");

# Set up some variables
LET $MYSQL_DATA_DIR = `select @@datadir`;
LET $data_directory_clause = DATA DIRECTORY='$MYSQL_TMP_DIR/alt_dir';
--enable_query_log

SET default_storage_engine=InnoDB;

SET GLOBAL innodb_file_per_table=ON;

--disable_warnings
DROP TABLE IF EXISTS t5980;
--enable_warnings

--echo #
--echo # CREATE TABLE ... DATA DIRECTORY
--echo # combined with  WL#5522 - Transportable Tablespace
--echo # Create the tablespace in MYSQL_TMP_DIR/alt_dir
--echo # InnoDB will create the sub-directories if needed.
--echo # Test that DISCARD and IMPORT work correctly.
--echo #

--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TABLE t5980 (a int KEY, b text) $data_directory_clause;
INSERT INTO t5980 VALUES (1, "Create the tablespace");
SELECT * FROM t5980;
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in 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 t5980;


--echo #
--echo # Backup the cfg and ibd files.
--echo #
FLUSH TABLES t5980 FOR EXPORT;
SELECT * FROM t5980;
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg.bak
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd.bak
UNLOCK TABLES;
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Do some DDL and DML.
--echo #
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;
SELECT * FROM t5980;
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;

--echo #
--echo # Make a second backup of the cfg and ibd files.
--echo #
FLUSH TABLES t5980 FOR EXPORT;
SELECT * FROM t5980;
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg.bak2
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd.bak2
UNLOCK TABLES;
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # DROP the table and make sure all files except the backups are gone.
--echo #
DROP TABLE t5980;
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # CREATE the table again.
--echo #
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TABLE t5980 (a int KEY, b text) $data_directory_clause;
INSERT INTO t5980 VALUES (1, "Create the tablespace a second time");
SELECT * FROM t5980;

--echo #
--echo # DISCARD existing tablespace so backed-up .ibd which can be imported/restored
--echo #
ALTER TABLE t5980 DISCARD TABLESPACE;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980;
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Restore the second backup of cfg and ibd files.
--echo #
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg.bak2 $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd.bak2 $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd
--echo "### files in MYSQL_TMP_DIR/alt_dir/test"
--list_files $MYSQL_TMP_DIR/alt_dir/test/

--echo #
--echo # Try to Import the second backup.  These backups have extra DDL and
--echo # do not match the current frm file.
--echo #
--error ER_TABLE_SCHEMA_MISMATCH
ALTER TABLE t5980 IMPORT TABLESPACE;
CHECK TABLE t5980;
--remove_file $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg
--remove_file $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd 
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Restore the first backup of cfg and ibd files.
--echo #
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg.bak $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd.bak $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Import the tablespace and do some DDL and DML.
--echo #
ALTER TABLE t5980 IMPORT TABLESPACE;
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test
CHECK TABLE t5980;
SELECT COUNT(*) FROM t5980;
SELECT * FROM t5980;
INSERT INTO t5980 VALUES (2,'Inserted record after IMPORT');
SELECT * FROM t5980;
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;
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;

--echo #
--echo # Show that the system tables have this table in them correctly.
--echo #
SELECT name,n_cols,file_format,row_format
       FROM information_schema.innodb_sys_tables
       WHERE name LIKE 'test%' ORDER BY name;
SELECT name,file_format,row_format
       FROM information_schema.innodb_sys_tablespaces
       WHERE name LIKE 'test%' ORDER BY space;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SELECT path FROM information_schema.innodb_sys_datafiles
       WHERE path LIKE '%test%' ORDER BY space;

--echo #
--echo # Drop the imported table and show that the system tables are updated.
--echo #
DROP TABLE t5980;
SELECT name,n_cols,file_format,row_format
       FROM information_schema.innodb_sys_tables
       WHERE name LIKE 'test%' ORDER BY name;
SELECT name,file_format,row_format
       FROM information_schema.innodb_sys_tablespaces
       WHERE name LIKE 'test%' ORDER BY space;
SELECT path FROM information_schema.innodb_sys_datafiles
       WHERE path LIKE '%test%' ORDER BY space;

--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # CREATE the table a third time.
--echo #
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TABLE t5980 (a int KEY, b text) $data_directory_clause;
INSERT INTO t5980 VALUES (1, "Create the tablespace a third time");
SELECT * FROM t5980;
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Restart the server
--echo # This test makes sure that you can still execute the FLUSH TABLES command
--echo # after restarting the server and the tablespace can still be found.
--echo #
--source include/restart_mysqld.inc
SET GLOBAL innodb_file_per_table=ON;
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test
SELECT * FROM t5980;
FLUSH TABLES t5980 FOR EXPORT;
SELECT * FROM t5980;
UNLOCK TABLES;

--echo #
--echo # Restart the server again.  This test makes sure that you can
--echo # still DISCARD a remote table after restarting the server.
--echo #
--source include/restart_mysqld.inc
SET GLOBAL innodb_file_per_table=ON;
SELECT * FROM t5980;
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test
ALTER TABLE t5980 DISCARD TABLESPACE;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980;
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Restore the backup of *.ibd and *.cfg files
--echo #
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd.bak  $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg.bak  $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Import the tablespace and check it out.
--echo #
ALTER TABLE t5980 IMPORT TABLESPACE;
SELECT * FROM t5980;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t5980;
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # DISCARD the tablespace again
--echo #
ALTER TABLE t5980 DISCARD TABLESPACE;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980;
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Restart the engine while the tablespace is in the discarded state
--echo #
--source include/restart_mysqld.inc
SET GLOBAL innodb_file_per_table=ON;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980;
CHECK TABLE t5980;

--echo #
--echo # Relocate this discarded file to the default directory
--echo # instead of the remote directory it was discarded from.
--echo # Put cfg and idb files into the default directory.
--echo # Delete the isl file and the remote cfg file.
--echo # Restart the engine again.
--echo # The tablespace is still in the discarded state.
--echo #
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd.bak  $MYSQL_DATA_DIR/test/t5980.ibd
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg.bak  $MYSQL_DATA_DIR/test/t5980.cfg
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test
--echo # Restarting ...
--source include/restart_mysqld.inc
SET GLOBAL innodb_file_per_table=ON;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980;
CHECK TABLE t5980;

--echo #
--echo # Try to import the tablespace.  It can only be imported from
--echo # the location it was discarded from.
--echo # The error message for 1810 (IO_READ_ERROR) refers to a local path
--echo # so do not display it.
--echo #
--disable_result_log
--error ER_NO_SUCH_TABLE
ALTER TABLE t5980 IMPORT TABLESPACE;
--enable_result_log
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980;
CHECK TABLE t5980;

--echo #
--echo # Restore the ibd and cfg files to the remote directory.
--echo # Delete the ibd and cfg files from the default directory.
--echo # The isl file is missing, but is no longer needed since the
--echo # remote location is in the data dictionary.
--echo # Import the tablespace and check it out.
--echo #
--copy_file $MYSQL_DATA_DIR/test/t5980.ibd  $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd
--copy_file $MYSQL_DATA_DIR/test/t5980.cfg  $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg
--remove_file $MYSQL_DATA_DIR/test/t5980.ibd
--remove_file $MYSQL_DATA_DIR/test/t5980.cfg
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test
ALTER TABLE t5980 IMPORT TABLESPACE;
INSERT INTO t5980 VALUES (2, "Insert this record after IMPORT");
SELECT * FROM t5980;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t5980;

--echo #
--echo # Show that the system tables have this table in them correctly.
--echo #
SELECT name,n_cols,file_format,row_format
       FROM information_schema.innodb_sys_tables
       WHERE name LIKE 'test%' ORDER BY name;
SELECT name,file_format,row_format
       FROM information_schema.innodb_sys_tablespaces
       WHERE name LIKE 'test%' ORDER BY space;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SELECT path FROM information_schema.innodb_sys_datafiles
       WHERE path LIKE '%test%' ORDER BY space;

DROP TABLE t5980;
--remove_file $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg.bak
--remove_file $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd.bak
--remove_file $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg.bak2
--remove_file $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd.bak2

--echo #
--echo # Create a local and remote tablespaces, discard two and make
--echo # the other two missing upon restart, and try some DDL and DML
--echo # on these discarded and missing tablespaces.
--echo #

SET GLOBAL innodb_file_per_table=ON;
CREATE TABLE t5980a (a int, b text) engine=InnoDB;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TABLE t5980b (a int, b text) engine=InnoDB $data_directory_clause;
CREATE TABLE t5980c (a int, b text) engine=InnoDB;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TABLE t5980d (a int, b text) engine=InnoDB $data_directory_clause;

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;
SELECT * FROM t5980b;
SELECT * FROM t5980c;
SELECT * FROM t5980d;

SHOW CREATE TABLE t5980a;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t5980b;
SHOW CREATE TABLE t5980c;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t5980d;

SELECT name,n_cols,file_format,row_format FROM information_schema.innodb_sys_tables
       WHERE name LIKE 'test%' ORDER BY name;
SELECT name,file_format,row_format FROM information_schema.innodb_sys_tablespaces
       WHERE name LIKE 'test%' ORDER BY space;
--replace_result ./ MYSQL_DATA_DIR/  $MYSQL_DATA_DIR MYSQL_DATA_DIR  $MYSQL_TMP_DIR MYSQL_TMP_DIR
SELECT path FROM information_schema.innodb_sys_datafiles
       WHERE path LIKE '%test%' ORDER BY space;

--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Shutdown the server, remove two tablespaces, restart server.
--echo #
--source include/shutdown_mysqld.inc
--remove_file $MYSQL_DATA_DIR/test/t5980c.ibd
--remove_file $MYSQL_DATA_DIR/test/t5980d.isl
--remove_file $MYSQL_TMP_DIR/alt_dir/test/t5980d.ibd
--source include/start_mysqld.inc

FLUSH TABLES t5980a, t5980b FOR EXPORT;
UNLOCK TABLES;

ALTER TABLE t5980a DISCARD TABLESPACE;
ALTER TABLE t5980b DISCARD TABLESPACE;

--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980a;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980b;
--error ER_NO_SUCH_TABLE
SELECT * FROM t5980c;
--error ER_NO_SUCH_TABLE
SELECT * FROM t5980d;

SHOW CREATE TABLE t5980a;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t5980b;
--error ER_NO_SUCH_TABLE
SHOW CREATE TABLE t5980c;
--error ER_NO_SUCH_TABLE
SHOW CREATE TABLE t5980d;

SELECT name,n_cols,file_format,row_format FROM information_schema.innodb_sys_tables
       WHERE name LIKE 'test%' ORDER BY name;
SELECT name,file_format,row_format FROM information_schema.innodb_sys_tablespaces
       WHERE name LIKE 'test%' ORDER BY space;
--replace_result ./ MYSQL_DATA_DIR/  $MYSQL_DATA_DIR MYSQL_DATA_DIR  $MYSQL_TMP_DIR MYSQL_TMP_DIR
SELECT path FROM information_schema.innodb_sys_datafiles
       WHERE path LIKE '%test%' ORDER BY space;

--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Discarded and missing tablespaces cannot be TRUNCATED
--echo #

--error ER_TABLESPACE_DISCARDED
TRUNCATE TABLE t5980a;
--error ER_TABLESPACE_DISCARDED
TRUNCATE TABLE t5980b;
--error ER_NO_SUCH_TABLE
TRUNCATE TABLE t5980c;
--error ER_NO_SUCH_TABLE
TRUNCATE TABLE t5980d;

--echo #
--echo # Discarded tablespaces can be RENAMED but they remain discarded
--echo #

RENAME TABLE t5980a TO t5980aa;
RENAME TABLE t5980b TO t5980bb;

--echo #
--echo # Missing tablespaces cannot be RENAMED
--echo #

--error ER_ERROR_ON_RENAME
RENAME TABLE t5980c TO t5980cc;
--error ER_ERROR_ON_RENAME
RENAME TABLE t5980d TO t5980dd;

--error ER_NO_SUCH_TABLE
SELECT * FROM t5980a;
--error ER_NO_SUCH_TABLE
SELECT * FROM t5980b;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980aa;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980bb;
--error ER_NO_SUCH_TABLE
SELECT * FROM t5980c;
--error ER_NO_SUCH_TABLE
SELECT * FROM t5980d;

SHOW CREATE TABLE t5980aa;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t5980bb;
--error ER_NO_SUCH_TABLE
SHOW CREATE TABLE t5980c;
--error ER_NO_SUCH_TABLE
SHOW CREATE TABLE t5980d;

SELECT name,n_cols,file_format,row_format FROM information_schema.innodb_sys_tables
       WHERE name LIKE 'test%' ORDER BY name;
SELECT name,file_format,row_format FROM information_schema.innodb_sys_tablespaces
       WHERE name LIKE 'test%' ORDER BY space;
--replace_result ./ MYSQL_DATA_DIR/  $MYSQL_DATA_DIR MYSQL_DATA_DIR  $MYSQL_TMP_DIR MYSQL_TMP_DIR
SELECT path FROM information_schema.innodb_sys_datafiles
       WHERE path LIKE '%test%' ORDER BY space;

--echo #
--echo # Discarded tablespaces cannot be ALTERED with ALGORITHM=COPY.
--echo #

--error ER_TABLESPACE_DISCARDED
ALTER TABLE t5980aa ADD PRIMARY KEY(a), ALGORITHM=COPY;
--error ER_TABLESPACE_DISCARDED
ALTER TABLE t5980bb ADD PRIMARY KEY(a), ALGORITHM=COPY;

--echo #
--echo # Discarded tablespaces can be ALTERED with ALGORITHM=INPLACE.
--echo #

# NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on.
# And adding a PRIMARY KEY will also add NOT NULL implicitly!
SET @old_sql_mode = @@sql_mode;
SET @@sql_mode = 'STRICT_TRANS_TABLES';
ALTER TABLE t5980aa ADD PRIMARY KEY(a), ALGORITHM=INPLACE;
ALTER TABLE t5980bb ADD PRIMARY KEY(a), ALGORITHM=INPLACE;
SET @@sql_mode = @old_sql_mode;

--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Missing tablespaces cannot be ALTERED.
--echo #

--error ER_NO_SUCH_TABLE
ALTER TABLE t5980c ADD PRIMARY KEY(a);
--error ER_NO_SUCH_TABLE
ALTER TABLE t5980d ADD PRIMARY KEY(a);

--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980aa;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980bb;

SHOW CREATE TABLE t5980aa;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t5980bb;

SELECT name,n_cols,file_format,row_format FROM information_schema.innodb_sys_tables
       WHERE name LIKE 'test%' ORDER BY name;
SELECT name,file_format,row_format FROM information_schema.innodb_sys_tablespaces
       WHERE name LIKE 'test%' ORDER BY space;
--replace_result ./ MYSQL_DATA_DIR/  $MYSQL_DATA_DIR MYSQL_DATA_DIR  $MYSQL_TMP_DIR MYSQL_TMP_DIR
SELECT path FROM information_schema.innodb_sys_datafiles
       WHERE path LIKE '%test%' ORDER BY space;

--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Restart the server to check if the discarded flag is persistent
--echo #
--source include/shutdown_mysqld.inc
--source include/start_mysqld.inc

--echo #
--echo # Discarded tablespaces that were ALTERED IN_PLACE are still discarded.
--echo #

--error ER_TABLESPACE_DISCARDED
INSERT INTO t5980aa VALUES (1, "Inserted into Discarded Local tablespace after ALTER ADD PRIMARY KEY, ALGORITHM=INPLACE");
--error ER_TABLESPACE_DISCARDED
INSERT INTO t5980bb VALUES (1, "Inserted into Discarded Local tablespace after ALTER ADD PRIMARY KEY, ALGORITHM=INPLACE");

--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980aa;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980bb;

RENAME TABLE t5980aa TO t5980a;
RENAME TABLE t5980bb TO t5980b;

SHOW CREATE TABLE t5980a;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t5980b;

SELECT name,n_cols,file_format,row_format FROM information_schema.innodb_sys_tables
       WHERE name LIKE 'test%' ORDER BY name;
SELECT name,file_format,row_format FROM information_schema.innodb_sys_tablespaces
       WHERE name LIKE 'test%' ORDER BY space;
--replace_result ./ MYSQL_DATA_DIR/  $MYSQL_DATA_DIR MYSQL_DATA_DIR  $MYSQL_TMP_DIR MYSQL_TMP_DIR
SELECT path FROM information_schema.innodb_sys_datafiles
       WHERE path LIKE '%test%' ORDER BY space;

--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Discard tablespaces again and try another ALTER TABLE ROW_FORMAT.
--echo #

ALTER TABLE t5980a DISCARD TABLESPACE;
ALTER TABLE t5980b DISCARD TABLESPACE;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980a;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980b;

--echo #
--echo # ALTER TABLE ALGORITHM=COPY cannot use a discarded tablespace.
--echo #

--error ER_TABLESPACE_DISCARDED
ALTER TABLE t5980a ROW_FORMAT=REDUNDANT, ALGORITHM=COPY;
--error ER_TABLESPACE_DISCARDED
ALTER TABLE t5980b ROW_FORMAT=REDUNDANT, ALGORITHM=COPY;

--echo #
--echo # ALTER TABLE ALGORITHM=INPLACE can use a discarded tablespace.
--echo #

ALTER TABLE t5980a ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE t5980b ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE;

--echo #
--echo # Discarded tablespaces that were ALTERED IN_PLACE are still discarded.
--echo #

--error ER_TABLESPACE_DISCARDED
INSERT INTO t5980a VALUES (1, "Inserted into discarded local tablespace after ALTER ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE");
--error ER_TABLESPACE_DISCARDED
INSERT INTO t5980b VALUES (1, "Inserted into discarded local tablespace after ALTER ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE");
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980a;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980b;
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Discard tablespaces again and try ALTER TABLE ADD COLUMN.
--echo #

ALTER TABLE t5980a DISCARD TABLESPACE;
ALTER TABLE t5980b DISCARD TABLESPACE;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980a;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980b;

--echo #
--echo # ALTER TABLE ALGORITHM=COPY cannot use a discarded tablespace.
--echo #

--error ER_TABLESPACE_DISCARDED
ALTER TABLE t5980a ADD COLUMN c CHAR(20), ALGORITHM=COPY;
--error ER_TABLESPACE_DISCARDED
ALTER TABLE t5980b ADD COLUMN c CHAR(20), ALGORITHM=COPY;

--echo #
--echo # ALTER TABLE ALGORITHM=INPLACE can use a discarded tablespace.
--echo #

ALTER TABLE t5980a ADD COLUMN c CHAR(20), ALGORITHM=INPLACE;
ALTER TABLE t5980b ADD COLUMN c CHAR(20), ALGORITHM=INPLACE;

--echo #
--echo # Discarded tablespaces that were ALTERED IN_PLACE are still discarded.
--echo #

--error ER_TABLESPACE_DISCARDED
DELETE FROM t5980a;
--error ER_TABLESPACE_DISCARDED
UPDATE t5980a SET c="Tablespace is DISCARDED";
--error ER_TABLESPACE_DISCARDED
INSERT INTO t5980a VALUES (1, "Inserted into discarded local tablespace after ALTER ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE", "new column");
--error ER_TABLESPACE_DISCARDED
INSERT INTO t5980b VALUES (1, "Inserted into discarded local tablespace after ALTER ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE", "new column");
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980a;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980b;
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

DROP TABLE t5980a;
DROP TABLE t5980b;
DROP TABLE t5980c;
DROP TABLE t5980d;

SELECT name,n_cols,file_format,row_format FROM information_schema.innodb_sys_tables
       WHERE name LIKE 'test%' ORDER BY name;
SELECT name,file_format,row_format FROM information_schema.innodb_sys_tablespaces
       WHERE name LIKE 'test%' ORDER BY space;
--replace_result ./ MYSQL_DATA_DIR/  $MYSQL_DATA_DIR MYSQL_DATA_DIR  $MYSQL_TMP_DIR MYSQL_TMP_DIR
SELECT path FROM information_schema.innodb_sys_datafiles
       WHERE path LIKE '%test%' ORDER BY space;

--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Cleanup
--echo #

--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;
call mtr.add_suppression("InnoDB: Could not find a valid tablespace file for");
call mtr.add_suppression("InnoDB: Tablespace open failed for");
call mtr.add_suppression("InnoDB: Failed to find tablespace for table ");
call mtr.add_suppression("InnoDB: Table test/t5980.* does not have an .ibd file in the database directory.");
call mtr.add_suppression("Trying to import a tablespace, but could not open the tablespace file");
call mtr.add_suppression("Cannot delete tablespace .+ because it is not found in the tablespace memory cache");
call mtr.add_suppression("Cannot delete tablespace .+ in DISCARD TABLESPACE. Tablespace not found");
call mtr.add_suppression("doesn't have a corresponding tablespace, it was discarded.");
-- enable_query_log

