219 lines
6.5 KiB
Plaintext
219 lines
6.5 KiB
Plaintext
# *************************************************************
|
|
# wl#9509: Test the functionality of explicit undo tablespaces.
|
|
# Show that CREATE/ALTER/DROP UNDO TABLESPACE do not replicate.
|
|
# *************************************************************
|
|
--source include/master-slave.inc
|
|
|
|
--disable_query_log
|
|
call mtr.add_suppression("Cannot drop undo tablespace \\'undo_005\\' because it is active. Please do: ALTER UNDO TABLESPACE undo_005 SET INACTIVE");
|
|
call mtr.add_suppression("\\[InnoDB\\] Log writer is waiting for checkpointer to to catch up lag: 9433600 bytes.*");
|
|
--enable_query_log
|
|
|
|
--echo [Connection Master]
|
|
--connection master
|
|
|
|
CREATE UNDO TABLESPACE undo_005 ADD DATAFILE 'undo_005.ibu';
|
|
CREATE UNDO TABLESPACE undo_006 ADD DATAFILE 'undo_006.ibu';
|
|
|
|
# Check metadata info on master
|
|
SELECT name,space_type,state
|
|
FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE space_type = 'Undo' ORDER BY name;
|
|
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--echo [Connection Slave]
|
|
|
|
# Check metadata info on slave
|
|
SELECT name,space_type,state
|
|
FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE space_type = 'Undo' ORDER BY name;
|
|
|
|
--echo [Connection Master]
|
|
--connection master
|
|
|
|
# Check currently active undo tablespace, cannot be dropped
|
|
--error ER_DROP_FILEGROUP_FAILED
|
|
DROP UNDO TABLESPACE undo_005;
|
|
SHOW WARNINGS;
|
|
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--echo [Connection Slave]
|
|
|
|
# Check currently active undo tablespace, cannot be dropped
|
|
--error ER_TABLESPACE_MISSING_WITH_NAME
|
|
DROP UNDO TABLESPACE undo_005;
|
|
SHOW WARNINGS;
|
|
|
|
--echo [Connection Master]
|
|
--connection master
|
|
|
|
# Check implicit undo tablespace inactive
|
|
ALTER UNDO TABLESPACE innodb_undo_005 SET INACTIVE;
|
|
ALTER UNDO TABLESPACE innodb_undo_006 SET INACTIVE;
|
|
|
|
# Wait until implicit undo tablespace become empty
|
|
let $inactive_undo_space = innodb_undo_005;
|
|
source include/wait_until_undo_space_is_empty.inc;
|
|
let $inactive_undo_space = innodb_undo_006;
|
|
source include/wait_until_undo_space_is_empty.inc;
|
|
|
|
# Check metadata info on master
|
|
SELECT name,space_type,state
|
|
FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE space_type = 'Undo' ORDER BY name;
|
|
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--echo [Connection Slave]
|
|
|
|
# Show that the slave has only 2 implicit undo tablespaces.
|
|
SELECT name,space_type,state
|
|
FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE space_type = 'Undo' ORDER BY name;
|
|
|
|
# Show that they cannot be ade inactive because there always needs to be
|
|
# at least 2 active undo tablespaces so that they can be implicitly
|
|
# truncated one at a time..
|
|
--error ER_DISALLOWED_OPERATION
|
|
ALTER UNDO TABLESPACE innodb_undo_005 SET INACTIVE;
|
|
--error ER_DISALLOWED_OPERATION
|
|
ALTER UNDO TABLESPACE innodb_undo_006 SET INACTIVE;
|
|
|
|
# Check metadata info on slave
|
|
SELECT name,space_type,state
|
|
FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE space_type = 'Undo' ORDER BY name;
|
|
|
|
--echo [Connection Master]
|
|
--connection master
|
|
|
|
# Create tables while implicit undo tablespaces are inactive
|
|
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
|
|
CREATE TABLE tab1(c1 int, c2 varchar(10)) TABLESPACE=ts1;
|
|
INSERT INTO tab1 VALUES(1, 'VISH');
|
|
CREATE TABLE tab2(c1 int , c2 varchar(10)) Engine=InnoDB;
|
|
INSERT INTO tab2 VALUES(2, 'VISH');
|
|
CREATE INDEX ix1 ON tab1(c2) USING BTREE;
|
|
CREATE INDEX ix2 ON tab2(c2) ;
|
|
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--echo [Connection Slave]
|
|
|
|
# Check the tables on slave
|
|
SHOW CREATE TABLE tab1;
|
|
SHOW CREATE TABLE tab2;
|
|
|
|
SELECT * FROM tab1;
|
|
SELECT * FROM tab2;
|
|
|
|
--echo [Connection Master]
|
|
--connection master
|
|
|
|
# Check that there are always 2 undo tablespaces in the active state.
|
|
--error ER_DISALLOWED_OPERATION
|
|
ALTER UNDO TABLESPACE undo_005 SET INACTIVE;
|
|
SHOW WARNINGS;
|
|
--error ER_DISALLOWED_OPERATION
|
|
ALTER UNDO TABLESPACE undo_006 SET INACTIVE;
|
|
SHOW WARNINGS;
|
|
|
|
# Check that implicit undo tablespaces can not be dropped
|
|
--error ER_WRONG_TABLESPACE_NAME
|
|
DROP UNDO TABLESPACE innodb_undo_005;
|
|
|
|
# Check metadata info on master
|
|
SELECT name,space_type,state
|
|
FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE space_type = 'Undo' ORDER BY name;
|
|
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--echo [Connection Slave]
|
|
|
|
--error ER_TABLESPACE_MISSING_WITH_NAME
|
|
ALTER UNDO TABLESPACE undo_006 SET INACTIVE;
|
|
SHOW WARNINGS;
|
|
|
|
# Check metadata info on slave
|
|
SELECT name,space_type,state
|
|
FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE space_type = 'Undo' ORDER BY name;
|
|
|
|
# Check implicit undo tablespaces can not be dropped
|
|
--error ER_WRONG_TABLESPACE_NAME
|
|
DROP UNDO TABLESPACE innodb_undo_001;
|
|
|
|
--echo [Connection Master]
|
|
--connection master
|
|
|
|
# Check implicit undo tablespace active
|
|
ALTER UNDO TABLESPACE innodb_undo_005 SET ACTIVE;
|
|
ALTER UNDO TABLESPACE innodb_undo_006 SET ACTIVE;
|
|
|
|
# Check metadata info on master
|
|
SELECT name,space_type,state
|
|
FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE space_type = 'Undo' ORDER BY name;
|
|
|
|
# Clean up
|
|
DROP TABLE tab1;
|
|
DROP table tab2;
|
|
DROP TABLESPACE ts1;
|
|
|
|
# Check explicit undo tablespace inactive
|
|
ALTER UNDO TABLESPACE undo_005 SET INACTIVE;
|
|
ALTER UNDO TABLESPACE undo_006 SET INACTIVE;
|
|
|
|
# Wait until the implicit undo tablespaces become empty
|
|
let $inactive_undo_space = undo_005;
|
|
source include/wait_until_undo_space_is_empty.inc;
|
|
let $inactive_undo_space = undo_006;
|
|
source include/wait_until_undo_space_is_empty.inc;
|
|
|
|
# Drop one of the two explicit undo tablespaces
|
|
DROP UNDO TABLESPACE undo_005;
|
|
|
|
# Check metadata info on master
|
|
SELECT name,space_type,state
|
|
FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE space_type = 'Undo';
|
|
|
|
# Check relative paths disallow on master
|
|
--error ER_WRONG_FILE_NAME
|
|
CREATE UNDO TABLESPACE undo_005 ADD DATAFILE '../undo_005.ibu';
|
|
--replace_result \\ /
|
|
SHOW WARNINGS;
|
|
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--echo [Connection Slave]
|
|
|
|
# Check metadata info on master
|
|
SELECT name,space_type,state
|
|
FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE space_type = 'Undo';
|
|
|
|
# Check relative paths disallow on slave
|
|
--error ER_WRONG_FILE_NAME
|
|
CREATE UNDO TABLESPACE undo_005 ADD DATAFILE '../undo_005.ibu';
|
|
--replace_result \\ /
|
|
SHOW WARNINGS;
|
|
|
|
--echo [Connection Master]
|
|
--connection master
|
|
|
|
# Drop the second explicit undo tablespace
|
|
DROP UNDO TABLESPACE undo_006;
|
|
|
|
# Check metadata info on master
|
|
SELECT name,space_type,state
|
|
FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE space_type = 'Undo';
|
|
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--echo [Connection Slave]
|
|
|
|
# Check metadata info on slave
|
|
SELECT name,space_type,state
|
|
FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE space_type = 'Undo';
|
|
|
|
--source include/rpl_end.inc
|