#
# WL7943 Implement INFORMATION_SCHEMA.FILES
#
# Mysqld is initialized in this test with with:
# --innodb_rollback_segments=1
# --bootstrap --innodb_page_size=16k
# 2 implicit and 30 explicit undo tablespaces
CREATE UNDO TABLESPACE undo_3 ADD DATAFILE 'undo_3.ibu';
CREATE UNDO TABLESPACE undo_4 ADD DATAFILE 'undo_4.ibu';
CREATE UNDO TABLESPACE undo_5 ADD DATAFILE 'undo_5.ibu';
CREATE UNDO TABLESPACE undo_6 ADD DATAFILE 'undo_6.ibu';
CREATE UNDO TABLESPACE undo_7 ADD DATAFILE 'undo_7.ibu';
CREATE UNDO TABLESPACE undo_8 ADD DATAFILE 'undo_8.ibu';
CREATE UNDO TABLESPACE undo_9 ADD DATAFILE 'undo_9.ibu';
CREATE UNDO TABLESPACE undo_10 ADD DATAFILE 'undo_10.ibu';
CREATE UNDO TABLESPACE undo_11 ADD DATAFILE 'undo_11.ibu';
CREATE UNDO TABLESPACE undo_12 ADD DATAFILE 'undo_12.ibu';
CREATE UNDO TABLESPACE undo_13 ADD DATAFILE 'undo_13.ibu';
CREATE UNDO TABLESPACE undo_14 ADD DATAFILE 'undo_14.ibu';
CREATE UNDO TABLESPACE undo_15 ADD DATAFILE 'undo_15.ibu';
CREATE UNDO TABLESPACE undo_16 ADD DATAFILE 'undo_16.ibu';
CREATE UNDO TABLESPACE undo_17 ADD DATAFILE 'undo_17.ibu';
CREATE UNDO TABLESPACE undo_18 ADD DATAFILE 'undo_18.ibu';
CREATE UNDO TABLESPACE undo_19 ADD DATAFILE 'undo_19.ibu';
CREATE UNDO TABLESPACE undo_20 ADD DATAFILE 'undo_20.ibu';
CREATE UNDO TABLESPACE undo_21 ADD DATAFILE 'undo_21.ibu';
CREATE UNDO TABLESPACE undo_22 ADD DATAFILE 'undo_22.ibu';
CREATE UNDO TABLESPACE undo_23 ADD DATAFILE 'undo_23.ibu';
CREATE UNDO TABLESPACE undo_24 ADD DATAFILE 'undo_24.ibu';
CREATE UNDO TABLESPACE undo_25 ADD DATAFILE 'undo_25.ibu';
CREATE UNDO TABLESPACE undo_26 ADD DATAFILE 'undo_26.ibu';
CREATE UNDO TABLESPACE undo_27 ADD DATAFILE 'undo_27.ibu';
CREATE UNDO TABLESPACE undo_28 ADD DATAFILE 'undo_28.ibu';
CREATE UNDO TABLESPACE undo_29 ADD DATAFILE 'undo_29.ibu';
CREATE UNDO TABLESPACE undo_30 ADD DATAFILE 'undo_30.ibu';
CREATE UNDO TABLESPACE undo_31 ADD DATAFILE 'undo_31.ibu';
CREATE UNDO TABLESPACE undo_32 ADD DATAFILE 'undo_32.ibu';
SET default_storage_engine=InnoDB;
CREATE TABLE t1 (a serial, b text, c text);
==I_S.FILES==	==I_S.FILES==
FILE_ID	0
TABLESPACE_NAME	innodb_system
FILE_NAME	MYSQLD_DATADIR/ibdata1
FILE_TYPE	TABLESPACE
STATUS	NORMAL
ENGINE	InnoDB
EXTENT_SIZE	1048576
MAXIMUM_SIZE	NULL
AUTOEXTEND_SIZE	67108864
==I_S.FILES==	==I_S.FILES==
FILE_ID	4294967278
TABLESPACE_NAME	innodb_undo_002
FILE_NAME	MYSQLD_DATADIR/undo_002
FILE_TYPE	UNDO LOG
STATUS	NORMAL
ENGINE	InnoDB
EXTENT_SIZE	1048576
MAXIMUM_SIZE	NULL
==I_S.FILES==	==I_S.FILES==
FILE_ID	4294967279
TABLESPACE_NAME	innodb_undo_001
FILE_NAME	MYSQLD_DATADIR/undo_001
FILE_TYPE	UNDO LOG
STATUS	NORMAL
ENGINE	InnoDB
EXTENT_SIZE	1048576
MAXIMUM_SIZE	NULL
==I_S.FILES==	==I_S.FILES==
TABLESPACE_NAME	innodb_temporary
FILE_NAME	MYSQLD_DATADIR/ibtmp1
FILE_TYPE	TEMPORARY
STATUS	NORMAL
ENGINE	InnoDB
EXTENT_SIZE	1048576
INITIAL_SIZE	12582912
MAXIMUM_SIZE	NULL
AUTOEXTEND_SIZE	67108864
==I_S.FILES==	==I_S.FILES==
TABLESPACE_NAME	test/t1
FILE_NAME	MYSQLD_DATADIR/test/t1.ibd
FILE_TYPE	TABLESPACE
STATUS	NORMAL
ENGINE	InnoDB
DATA_FREE	0
FREE_EXTENTS	0
TOTAL_EXTENTS	0
EXTENT_SIZE	1048576
INITIAL_SIZE	114688
MAXIMUM_SIZE	NULL
AUTOEXTEND_SIZE	1048576
#
# Populate t1 with about 3MB of data
#
CREATE PROCEDURE populate_t1()
BEGIN
DECLARE i INT DEFAULT 1;
while (i <= 6000) DO
INSERT INTO t1 (b,c) VALUES (repeat('b', 250), repeat('c', 250));
SET i = i + 1;
END WHILE;
END |
BEGIN;
CALL populate_t1();
FLUSH TABLE t1;
==I_S.FILES==	==I_S.FILES==
TABLESPACE_NAME	test/t1
FILE_NAME	MYSQLD_DATADIR/test/t1.ibd
FILE_TYPE	TABLESPACE
STATUS	NORMAL
ENGINE	InnoDB
DATA_FREE	4194304
FREE_EXTENTS	2
TOTAL_EXTENTS	11
EXTENT_SIZE	1048576
INITIAL_SIZE	114688
MAXIMUM_SIZE	NULL
AUTOEXTEND_SIZE	1048576
#
# Populate t1 with another 3MB of data
#
CALL populate_t1();
FLUSH TABLE t1;
==I_S.FILES==	==I_S.FILES==
TABLESPACE_NAME	test/t1
FILE_NAME	MYSQLD_DATADIR/test/t1.ibd
FILE_TYPE	TABLESPACE
STATUS	NORMAL
ENGINE	InnoDB
DATA_FREE	4194304
FREE_EXTENTS	2
TOTAL_EXTENTS	15
EXTENT_SIZE	1048576
INITIAL_SIZE	114688
MAXIMUM_SIZE	NULL
AUTOEXTEND_SIZE	1048576
#
# Delete the half of the records.
#
DELETE FROM t1 WHERE a % 2 = 1;
FLUSH TABLE t1;
==I_S.FILES==	==I_S.FILES==
TABLESPACE_NAME	test/t1
FILE_NAME	MYSQLD_DATADIR/test/t1.ibd
FILE_TYPE	TABLESPACE
STATUS	NORMAL
ENGINE	InnoDB
DATA_FREE	4194304
FREE_EXTENTS	2
TOTAL_EXTENTS	15
EXTENT_SIZE	1048576
INITIAL_SIZE	114688
MAXIMUM_SIZE	NULL
AUTOEXTEND_SIZE	1048576
#
# Cleanup
#
DROP TABLE t1;
DROP PROCEDURE populate_t1;
# Set all explicit undo tablespaces to inacitve.
ALTER UNDO TABLESPACE undo_3 SET INACTIVE;
ALTER UNDO TABLESPACE undo_4 SET INACTIVE;
ALTER UNDO TABLESPACE undo_5 SET INACTIVE;
ALTER UNDO TABLESPACE undo_6 SET INACTIVE;
ALTER UNDO TABLESPACE undo_7 SET INACTIVE;
ALTER UNDO TABLESPACE undo_8 SET INACTIVE;
ALTER UNDO TABLESPACE undo_9 SET INACTIVE;
ALTER UNDO TABLESPACE undo_10 SET INACTIVE;
ALTER UNDO TABLESPACE undo_11 SET INACTIVE;
ALTER UNDO TABLESPACE undo_12 SET INACTIVE;
ALTER UNDO TABLESPACE undo_13 SET INACTIVE;
ALTER UNDO TABLESPACE undo_14 SET INACTIVE;
ALTER UNDO TABLESPACE undo_15 SET INACTIVE;
ALTER UNDO TABLESPACE undo_16 SET INACTIVE;
ALTER UNDO TABLESPACE undo_17 SET INACTIVE;
ALTER UNDO TABLESPACE undo_18 SET INACTIVE;
ALTER UNDO TABLESPACE undo_19 SET INACTIVE;
ALTER UNDO TABLESPACE undo_20 SET INACTIVE;
ALTER UNDO TABLESPACE undo_21 SET INACTIVE;
ALTER UNDO TABLESPACE undo_22 SET INACTIVE;
ALTER UNDO TABLESPACE undo_23 SET INACTIVE;
ALTER UNDO TABLESPACE undo_24 SET INACTIVE;
ALTER UNDO TABLESPACE undo_25 SET INACTIVE;
ALTER UNDO TABLESPACE undo_26 SET INACTIVE;
ALTER UNDO TABLESPACE undo_27 SET INACTIVE;
ALTER UNDO TABLESPACE undo_28 SET INACTIVE;
ALTER UNDO TABLESPACE undo_29 SET INACTIVE;
ALTER UNDO TABLESPACE undo_30 SET INACTIVE;
ALTER UNDO TABLESPACE undo_31 SET INACTIVE;
ALTER UNDO TABLESPACE undo_32 SET INACTIVE;
# Wait for all explicit undo tablespaces to become empty.
# Drop all explicit undo tablespaces.
DROP UNDO TABLESPACE undo_3;
DROP UNDO TABLESPACE undo_4;
DROP UNDO TABLESPACE undo_5;
DROP UNDO TABLESPACE undo_6;
DROP UNDO TABLESPACE undo_7;
DROP UNDO TABLESPACE undo_8;
DROP UNDO TABLESPACE undo_9;
DROP UNDO TABLESPACE undo_10;
DROP UNDO TABLESPACE undo_11;
DROP UNDO TABLESPACE undo_12;
DROP UNDO TABLESPACE undo_13;
DROP UNDO TABLESPACE undo_14;
DROP UNDO TABLESPACE undo_15;
DROP UNDO TABLESPACE undo_16;
DROP UNDO TABLESPACE undo_17;
DROP UNDO TABLESPACE undo_18;
DROP UNDO TABLESPACE undo_19;
DROP UNDO TABLESPACE undo_20;
DROP UNDO TABLESPACE undo_21;
DROP UNDO TABLESPACE undo_22;
DROP UNDO TABLESPACE undo_23;
DROP UNDO TABLESPACE undo_24;
DROP UNDO TABLESPACE undo_25;
DROP UNDO TABLESPACE undo_26;
DROP UNDO TABLESPACE undo_27;
DROP UNDO TABLESPACE undo_28;
DROP UNDO TABLESPACE undo_29;
DROP UNDO TABLESPACE undo_30;
DROP UNDO TABLESPACE undo_31;
DROP UNDO TABLESPACE undo_32;