# *************************************************************
# 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