#
# WL#6965: Truncate explicitly created UNDO logs.
#
# Create 2 explicit undo tablespaces.
CREATE UNDO TABLESPACE undo_003 ADD DATAFILE 'undo_003.ibu';
CREATE UNDO TABLESPACE undo_004 ADD DATAFILE 'undo_004.ibu';
# Make the 2 implicit undo tablespaces inactive.
ALTER UNDO TABLESPACE innodb_undo_001 SET INACTIVE;
ALTER UNDO TABLESPACE innodb_undo_002 SET INACTIVE;
SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
NAME	SPACE_TYPE	STATE
innodb_undo_001	Undo	empty
innodb_undo_002	Undo	empty
undo_003	Undo	active
undo_004	Undo	active
SET GLOBAL innodb_purge_stop_now=ON;
create table t1(
keyc int,
c1 char(255),
c2 char(255),
c3 char(255),
c4 char(255),
c5 char(255),
c6 char(255),
primary key(keyc)) engine = innodb;
CREATE PROCEDURE populate_t1()
BEGIN
DECLARE i INT DEFAULT 1;
while (i <= 20000) DO
insert into t1 values (i, 'a', 'b', 'c', 'd', 'e', 'f' );
SET i = i + 1;
END WHILE;
END |
call populate_t1();
delete from t1 where keyc < 10000;
update t1 set c1 = 'mysql' where  keyc > 10000;
update t1 set c2 = 'mysql' where  keyc > 10000;
update t1 set c3= 'mysql' where keyc > 10000;
update t1 set c4= 'mysql' where keyc > 10000;
update t1 set c5= 'mysql' where keyc > 10000;
update t1 set c6= 'mysql' where keyc > 10000;
create table t2 (
keyc int,
c1 char(255),
c2 char(255),
c3 char(255),
c4 char(255),
c5 char(255),
c6 char(255),
primary key(keyc)) engine = innodb;
CREATE PROCEDURE populate_t2()
BEGIN
DECLARE i INT DEFAULT 1;
while (i <= 20000) DO
insert into t2 values (i, 'a', 'b', 'c', 'd', 'e', 'f' );
SET i = i + 1;
END WHILE;
END |
call populate_t2();
delete from t2 where keyc < 10000;
update t2 set c1 = 'mysql' where  keyc > 10000;
update t2 set c2 = 'mysql' where  keyc > 10000;
update t2 set c3= 'mysql' where keyc > 10000;
update t2 set c4= 'mysql' where keyc > 10000;
update t2 set c5= 'mysql' where keyc > 10000;
update t2 set c6= 'mysql' where keyc > 10000;
drop PROCEDURE populate_t1;
drop PROCEDURE populate_t2;
drop table t1, t2;
SET GLOBAL innodb_purge_run_now=ON;
SET GLOBAL innodb_fast_shutdown=0;
#
# Cleanup
#
# restart
SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
NAME	SPACE_TYPE	STATE
innodb_undo_001	Undo	empty
innodb_undo_002	Undo	empty
undo_003	Undo	active
undo_004	Undo	active
ALTER UNDO TABLESPACE innodb_undo_001 SET ACTIVE;
ALTER UNDO TABLESPACE innodb_undo_002 SET ACTIVE;
ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
NAME	SPACE_TYPE	STATE
innodb_undo_001	Undo	active
innodb_undo_002	Undo	active
undo_003	Undo	empty
undo_004	Undo	empty
DROP UNDO TABLESPACE undo_003;
DROP UNDO TABLESPACE undo_004;