150 lines
3.6 KiB
Plaintext
150 lines
3.6 KiB
Plaintext
#
|
|
# WL7943 Implement INFORMATION_SCHEMA.FILES
|
|
#
|
|
# Mysqld is initialized in this test with with:
|
|
# --innodb_rollback_segments=1
|
|
# --bootstrap --innodb_page_size=32k
|
|
# 2 implicit and 6 explicit undo tablespaces
|
|
CREATE UNDO TABLESPACE undo_003 ADD DATAFILE 'undo_003.ibu';
|
|
CREATE UNDO TABLESPACE undo_004 ADD DATAFILE 'undo_004.ibu';
|
|
CREATE UNDO TABLESPACE undo_005 ADD DATAFILE 'undo_005.ibu';
|
|
CREATE UNDO TABLESPACE undo_006 ADD DATAFILE 'undo_006.ibu';
|
|
CREATE UNDO TABLESPACE undo_007 ADD DATAFILE 'undo_007.ibu';
|
|
CREATE UNDO TABLESPACE undo_008 ADD DATAFILE 'undo_008.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 2097152
|
|
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 2097152
|
|
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 2097152
|
|
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 2097152
|
|
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 2097152
|
|
INITIAL_SIZE 229376
|
|
MAXIMUM_SIZE NULL
|
|
AUTOEXTEND_SIZE 2097152
|
|
#
|
|
# 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 8388608
|
|
FREE_EXTENTS 3
|
|
TOTAL_EXTENTS 10
|
|
EXTENT_SIZE 2097152
|
|
INITIAL_SIZE 229376
|
|
MAXIMUM_SIZE NULL
|
|
AUTOEXTEND_SIZE 2097152
|
|
#
|
|
# 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 8388608
|
|
FREE_EXTENTS 2
|
|
TOTAL_EXTENTS 11
|
|
EXTENT_SIZE 2097152
|
|
INITIAL_SIZE 229376
|
|
MAXIMUM_SIZE NULL
|
|
AUTOEXTEND_SIZE 2097152
|
|
#
|
|
# 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 8388608
|
|
FREE_EXTENTS 2
|
|
TOTAL_EXTENTS 11
|
|
EXTENT_SIZE 2097152
|
|
INITIAL_SIZE 229376
|
|
MAXIMUM_SIZE NULL
|
|
AUTOEXTEND_SIZE 2097152
|
|
#
|
|
# Cleanup
|
|
#
|
|
DROP TABLE t1;
|
|
DROP PROCEDURE populate_t1;
|
|
# Set all explicit undo tablespaces to inacitve.
|
|
ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
|
|
ALTER UNDO TABLESPACE undo_004 SET INACTIVE;
|
|
ALTER UNDO TABLESPACE undo_005 SET INACTIVE;
|
|
ALTER UNDO TABLESPACE undo_006 SET INACTIVE;
|
|
ALTER UNDO TABLESPACE undo_007 SET INACTIVE;
|
|
ALTER UNDO TABLESPACE undo_008 SET INACTIVE;
|
|
# Wait for all explicit undo tablespaces to become empty.
|
|
# Drop all explicit undo tablespaces.
|
|
DROP UNDO TABLESPACE undo_003;
|
|
DROP UNDO TABLESPACE undo_004;
|
|
DROP UNDO TABLESPACE undo_005;
|
|
DROP UNDO TABLESPACE undo_006;
|
|
DROP UNDO TABLESPACE undo_007;
|
|
DROP UNDO TABLESPACE undo_008;
|