267 lines
8.4 KiB
Plaintext
267 lines
8.4 KiB
Plaintext
--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.
|
|
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 '%undo%' 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;
|
|
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;
|
|
|
|
SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE SPACE_TYPE = 'Undo' ORDER BY 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;
|
|
|
|
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 '%undo%' ORDER BY TABLESPACE_NAME;
|
|
DROP UNDO TABLESPACE undo_004;
|
|
|
|
--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;
|
|
|
|
--disable_query_log
|
|
call mtr.add_suppression("\\[ERROR\\] .* The directory '.*' does not exist.");
|
|
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("\\[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");
|
|
--enable_query_log
|
|
|