--echo #
--echo # InnoDB supports CREATE/ALTER/DROP UNDO TABLESPACE
--echo #

--source include/have_innodb_default_undo_tablespaces.inc

# Do a slow shutdown and restart to clear out the undo logs
SET GLOBAL innodb_fast_shutdown = 0;
--let $shutdown_server_timeout = 300
--source include/restart_mysqld.inc

# Let each undo truncation occur explicitly. This does not need to be
# set back to default since there is another restart below.
SET GLOBAL innodb_undo_log_truncate = OFF;

CREATE UNDO TABLESPACE undo_003 ADD DATAFILE 'undo_003.ibu';
CREATE UNDO TABLESPACE undo_004 ADD DATAFILE 'undo_004.ibu';
CREATE UNDO TABLESPACE undo_005 ADD DATAFILE '5.ibu';

SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
       WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
       WHERE FILE_NAME LIKE '%.ibu' ORDER BY TABLESPACE_NAME;

CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
CREATE TABLE t1 (a int primary key) TABLESPACE ts1;
--echo #
--echo # Populate t1 with separate INSERTs so that all rsegs are used.
--echo #
DELIMITER |;
CREATE PROCEDURE populate_t1(IN BASE INT, IN SIZE INT)
BEGIN
	DECLARE i INT DEFAULT BASE;
	WHILE (i <= SIZE) DO
		INSERT INTO t1 values (i);
		SET i = i + 1;
	END WHILE;
END|
DELIMITER ;|
CALL populate_t1(1, 1000);

--echo #
--echo # Show that the implicit undo tablespaces may be set inactive
--echo # and that a minimum of 2 undo tablespaces must remain active.
--echo #
ALTER UNDO TABLESPACE innodb_undo_001 SET INACTIVE;
let $inactive_undo_space = innodb_undo_001;
source include/wait_until_undo_space_is_empty.inc;

ALTER UNDO TABLESPACE innodb_undo_002 SET INACTIVE;
let $inactive_undo_space = innodb_undo_002;
source include/wait_until_undo_space_is_empty.inc;

ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
let $inactive_undo_space = undo_003;
source include/wait_until_undo_space_is_empty.inc;

--error ER_DISALLOWED_OPERATION
ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
SHOW WARNINGS;
--error ER_DISALLOWED_OPERATION
ALTER UNDO TABLESPACE undo_005 SET INACTIVE;
SHOW WARNINGS;

SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
       WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
       WHERE FILE_NAME LIKE '%.ibu' ORDER BY TABLESPACE_NAME;

ALTER UNDO TABLESPACE innodb_undo_001 SET ACTIVE;
ALTER UNDO TABLESPACE innodb_undo_002 SET ACTIVE;
SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
       WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;

--echo #
--echo # Show that SET ACTIVE and SET INACTIVE are indempotent.
--echo #
ALTER UNDO TABLESPACE undo_003 SET ACTIVE;
ALTER UNDO TABLESPACE undo_003 SET ACTIVE;
ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
let $inactive_undo_space = undo_003;
source include/wait_until_undo_space_is_empty.inc;
ALTER UNDO TABLESPACE undo_003 SET INACTIVE;

--echo #
--echo # SET the explicit tablespaces INACTIVE.
--echo #
ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
let $inactive_undo_space = undo_004;
source include/wait_until_undo_space_is_empty.inc;

ALTER UNDO TABLESPACE undo_005 SET INACTIVE;
let $inactive_undo_space = undo_005;
source include/wait_until_undo_space_is_empty.inc;

SHOW GLOBAL STATUS LIKE '%undo%';
SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
       WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
       WHERE FILE_NAME LIKE '%.ibu' ORDER BY TABLESPACE_NAME;

--echo #
--echo # Drop undo_003
--echo #
DROP UNDO TABLESPACE undo_003;
SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
       WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
       WHERE FILE_NAME LIKE '%.ibu' ORDER BY TABLESPACE_NAME;

ALTER UNDO TABLESPACE undo_005 SET ACTIVE;
SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
       WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;

--echo #
--echo # Try various bad CREATE UNDO TABLESPACE commands
--echo #
# `innodb_undo_001` is an existing implict undo tablespace.
--error ER_WRONG_TABLESPACE_NAME
CREATE UNDO TABLESPACE innodb_undo_001 ADD DATAFILE 'undo_001.ibu';
SHOW WARNINGS;

# Show that you cannot use an existing file known to the DD.
--error ER_TABLESPACE_DUP_FILENAME
CREATE UNDO TABLESPACE undo_5 ADD DATAFILE '5.ibu';
SHOW WARNINGS;

# Show that you cannot use an existing file unknown to the DD.
let $MYSQLD_DATADIR=`select @@datadir`;
--write_file $MYSQLD_DATADIR/undo_99.ibu
This is a leftover file with an undo tablespace suffix that the DD does not know about
EOF
--error ER_WRONG_FILE_NAME
CREATE UNDO TABLESPACE undo_99 ADD DATAFILE 'undo_99.ibu';
SHOW WARNINGS;
--remove_file $MYSQLD_DATADIR/undo_99.ibu

# Cannot use single quotes for an identifier
--error ER_PARSE_ERROR
CREATE UNDO TABLESPACE 'undo_99' ADD DATAFILE 'undo_001.ibu';

# `undo_99` does not exist.
--error ER_PARSE_ERROR
CREATE UNDO TABLESPACE `undo_99`;

# An explicit undo tablespace datafile name must end with '.ibu'
--error ER_WRONG_FILE_NAME
CREATE UNDO TABLESPACE undo_99 ADD DATAFILE 'undo_99';
SHOW WARNINGS;

--error ER_WRONG_FILE_NAME
CREATE UNDO TABLESPACE undo_99 ADD DATAFILE 'undo_99.ibd';
SHOW WARNINGS;

# The datafile name must be in an existing directory
--error ER_WRONG_FILE_NAME
CREATE UNDO TABLESPACE undo_99 ADD DATAFILE '/dir_does_not_exist/undo_99.ibu';
--replace_result \\ /
SHOW WARNINGS;

# The location must be a known datafile location, see --innodb-directories
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
--error ER_WRONG_FILE_NAME
CREATE UNDO TABLESPACE undo_99 ADD DATAFILE '../undo_99.ibu';
--replace_result \\ /
SHOW WARNINGS;

--echo #
--echo # Try various bad ALTER UNDO TABLESPACE commands
--echo #
# Must ALTER with SET ACTIVE or SET INACTIVE
--error ER_PARSE_ERROR
ALTER UNDO TABLESPACE `undo_99`;

# `undo_99` does not exist.
--error ER_TABLESPACE_MISSING_WITH_NAME
ALTER UNDO TABLESPACE `undo_99` SET INACTIVE;
SHOW WARNINGS;
--error ER_TABLESPACE_MISSING_WITH_NAME
ALTER UNDO TABLESPACE `undo_99` SET ACTIVE;
SHOW WARNINGS;

# Show that ALTER UNDO TABLESPACE with SET ACTIVE or SET INACTIVE
# will not work on a general tablespace.
--error ER_WRONG_TABLESPACE_NAME
ALTER UNDO TABLESPACE `ts1` SET INACTIVE;
SHOW WARNINGS;
--error ER_WRONG_TABLESPACE_NAME
ALTER UNDO TABLESPACE `ts1` SET ACTIVE;
SHOW WARNINGS;

# Show that ALTER TABLESPACE RENAME does not work on an UNDO tablespace.
--error ER_WRONG_TABLESPACE_NAME
ALTER TABLESPACE undo_005 RENAME TO undo_5;
SHOW WARNINGS;

# SET EMPTY is not a valid phrase.
--error ER_PARSE_ERROR
ALTER UNDO TABLESPACE undo_005 SET EMPTY;

--echo #
--echo # Try various bad DROP UNDO TABLESPACE commands
--echo #

# undo_001 is an implict undo tablespace, cannot be dropped.
--error ER_WRONG_TABLESPACE_NAME
DROP UNDO TABLESPACE innodb_undo_001;
SHOW WARNINGS;

# undo_99 does not exist.
--error ER_TABLESPACE_MISSING_WITH_NAME
DROP UNDO TABLESPACE undo_99;
SHOW WARNINGS;

# undo_005 is currently active, cannot be dropped.
--error ER_DROP_FILEGROUP_FAILED
DROP UNDO TABLESPACE undo_005;
SHOW WARNINGS;

# Show that DROP TABLESPACE does not work on undo tablespaces.
--error ER_WRONG_TABLESPACE_NAME
DROP TABLESPACE undo_005;
SHOW WARNINGS;

# Show that DROP UNDO TABLESPACE does not work on general tablespaces.
--error ER_WRONG_TABLESPACE_NAME
DROP UNDO TABLESPACE ts1;
SHOW WARNINGS;

--echo #
--echo # Show that tables cannot be added to an undo tablespace.
--echo #

--error ER_WRONG_TABLESPACE_NAME
CREATE TABLE t2 (a int primary key) TABLESPACE undo_004;
SHOW WARNINGS;

--error ER_WRONG_TABLESPACE_NAME
ALTER TABLE t1 TABLESPACE undo_004;
SHOW WARNINGS;

--echo #
--echo # Show that a missing undo tablespace can be dropped
--echo #
let $MYSQLD_DATADIR=`select @@datadir`;
--source include/shutdown_mysqld.inc
--remove_file $MYSQLD_DATADIR/undo_004.ibu
--source include/start_mysqld.inc
SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
       WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
       WHERE FILE_NAME LIKE '%.ibu' ORDER BY TABLESPACE_NAME;
DROP UNDO TABLESPACE undo_004;

--echo #
--echo # Show that the setting innodb_validate_tablespace_paths does not affect undo tablespaces.
--echo #
CREATE UNDO TABLESPACE undo_006 ADD DATAFILE 'undo_006.ibu';
SHOW GLOBAL STATUS LIKE '%undo%';
SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
       WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
       WHERE FILE_NAME LIKE '%.ibu' ORDER BY TABLESPACE_NAME;

--echo # Restart with validation turned OFF
let $restart_parameters = restart: --innodb_validate_tablespace_paths=0;
--source include/restart_mysqld.inc
SHOW GLOBAL STATUS LIKE '%undo%';
SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
       WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
       WHERE FILE_NAME LIKE '%.ibu' ORDER BY TABLESPACE_NAME;

--echo # Kill and restart mysqld with validation turned OFF
let $restart_parameters = restart: --innodb_validate_tablespace_paths=0;
--source include/kill_and_restart_mysqld.inc
SHOW GLOBAL STATUS LIKE '%undo%';
SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
       WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
       WHERE FILE_NAME LIKE '%.ibu' ORDER BY TABLESPACE_NAME;

--echo # Restart mysqld with validation turned ON
let $restart_parameters = restart:;
--source include/restart_mysqld.inc
SHOW GLOBAL STATUS LIKE '%undo%';
SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
       WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
SELECT TABLESPACE_NAME, FILE_TYPE, FILE_NAME FROM INFORMATION_SCHEMA.FILES
       WHERE FILE_NAME LIKE '%.ibu' ORDER BY TABLESPACE_NAME;

--echo #
--echo # Cleanup
--echo #
DROP TABLE t1;
DROP TABLESPACE ts1;
DROP PROCEDURE populate_t1;

ALTER UNDO TABLESPACE undo_005 SET INACTIVE;
let $inactive_undo_space = undo_005;
source include/wait_until_undo_space_is_empty.inc;
DROP UNDO TABLESPACE undo_005;

ALTER UNDO TABLESPACE undo_006 SET INACTIVE;
let $inactive_undo_space = undo_006;
source include/wait_until_undo_space_is_empty.inc;
DROP UNDO TABLESPACE undo_006;

--disable_query_log
call mtr.add_suppression("\\[Warning\\] .* Log writer is waiting for checkpointer to to catch up lag");
call mtr.add_suppression("\\[Warning\\] .* Tablespace .*, name 'undo_004', file 'undo_004.ibu' is missing");
call mtr.add_suppression("\\[Warning\\] .* Trying to access missing tablespace");
call mtr.add_suppression("\\[ERROR\\] .* The directory for tablespace .* does not exist or is incorrect.");
call mtr.add_suppression("\\[ERROR\\] .* Cannot drop undo tablespace 'undo_005' because it is active. Please do: ALTER UNDO TABLESPACE undo_005 SET INACTIVE");
call mtr.add_suppression("\\[ERROR\\] .* Cannot create tablespace undo_99 because the directory is not a valid location. The UNDO DATAFILE location must be in a known directory");
call mtr.add_suppression("\\[ERROR\\] .* Cannot find undo tablespace undo_004 with filename '.*' as indicated by the Data Dictionary. Did you move or delete this tablespace. Any undo logs in it cannot be used");
--enable_query_log