222 lines
6.6 KiB
Plaintext
222 lines
6.6 KiB
Plaintext
#
|
|
# 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;
|