1# 2# WL7943 Implement INFORMATION_SCHEMA.FILES 3# 4SET default_storage_engine=InnoDB; 5CREATE TABLE t1 (a serial, b text, c text); 6==I_S.FILES== ==I_S.FILES== 7FILE_ID 0 8TABLESPACE_NAME innodb_system 9FILE_NAME MYSQLD_DATADIR/ibdata1 10FILE_TYPE TABLESPACE 11STATUS NORMAL 12ENGINE InnoDB 13EXTENT_SIZE 1048576 14MAXIMUM_SIZE NULL 15AUTOEXTEND_SIZE 67108864 16==I_S.FILES== ==I_S.FILES== 17FILE_ID 1 18TABLESPACE_NAME innodb_undo001 19FILE_NAME MYSQLD_DATADIR//undo001 20FILE_TYPE UNDO LOG 21STATUS NORMAL 22ENGINE InnoDB 23EXTENT_SIZE 1048576 24MAXIMUM_SIZE NULL 25==I_S.FILES== ==I_S.FILES== 26FILE_ID 2 27TABLESPACE_NAME innodb_undo002 28FILE_NAME MYSQLD_DATADIR//undo002 29FILE_TYPE UNDO LOG 30STATUS NORMAL 31ENGINE InnoDB 32EXTENT_SIZE 1048576 33MAXIMUM_SIZE NULL 34==I_S.FILES== ==I_S.FILES== 35TABLESPACE_NAME innodb_temporary 36FILE_NAME MYSQLD_DATADIR/ibtmp1 37FILE_TYPE TEMPORARY 38STATUS NORMAL 39ENGINE InnoDB 40EXTENT_SIZE 1048576 41INITIAL_SIZE 12582912 42MAXIMUM_SIZE NULL 43AUTOEXTEND_SIZE 67108864 44==I_S.FILES== ==I_S.FILES== 45TABLESPACE_NAME innodb_file_per_table_## 46FILE_NAME MYSQLD_DATADIR/test/t1.ibd 47FILE_TYPE TABLESPACE 48STATUS NORMAL 49ENGINE InnoDB 50DATA_FREE 0 51FREE_EXTENTS 0 52TOTAL_EXTENTS 0 53EXTENT_SIZE 1048576 54INITIAL_SIZE 16384 55MAXIMUM_SIZE NULL 56AUTOEXTEND_SIZE 1048576 57# 58# Populate t1 with about 3MB of data 59# 60CREATE PROCEDURE populate_t1() 61BEGIN 62DECLARE i INT DEFAULT 1; 63while (i <= 6000) DO 64INSERT INTO t1 (b,c) VALUES (repeat('b', 250), repeat('c', 250)); 65SET i = i + 1; 66END WHILE; 67END | 68BEGIN; 69CALL populate_t1(); 70FLUSH TABLE t1; 71==I_S.FILES== ==I_S.FILES== 72TABLESPACE_NAME innodb_file_per_table_## 73FILE_NAME MYSQLD_DATADIR/test/t1.ibd 74FILE_TYPE TABLESPACE 75STATUS NORMAL 76ENGINE InnoDB 77DATA_FREE 4194304 78FREE_EXTENTS 1 79TOTAL_EXTENTS 12 80EXTENT_SIZE 1048576 81INITIAL_SIZE 16384 82MAXIMUM_SIZE NULL 83AUTOEXTEND_SIZE 1048576 84# 85# Populate t1 with another 3MB of data 86# 87CALL populate_t1(); 88FLUSH TABLE t1; 89==I_S.FILES== ==I_S.FILES== 90TABLESPACE_NAME innodb_file_per_table_## 91FILE_NAME MYSQLD_DATADIR/test/t1.ibd 92FILE_TYPE TABLESPACE 93STATUS NORMAL 94ENGINE InnoDB 95DATA_FREE 4194304 96FREE_EXTENTS 1 97TOTAL_EXTENTS 16 98EXTENT_SIZE 1048576 99INITIAL_SIZE 16384 100MAXIMUM_SIZE NULL 101AUTOEXTEND_SIZE 4194304 102# 103# Delete the half of the records. 104# 105DELETE FROM t1 WHERE a % 2 = 1; 106FLUSH TABLE t1; 107==I_S.FILES== ==I_S.FILES== 108TABLESPACE_NAME innodb_file_per_table_## 109FILE_NAME MYSQLD_DATADIR/test/t1.ibd 110FILE_TYPE TABLESPACE 111STATUS NORMAL 112ENGINE InnoDB 113DATA_FREE 4194304 114FREE_EXTENTS 1 115TOTAL_EXTENTS 16 116EXTENT_SIZE 1048576 117INITIAL_SIZE 16384 118MAXIMUM_SIZE NULL 119AUTOEXTEND_SIZE 4194304 120# 121# Cleanup 122# 123DROP TABLE t1; 124DROP PROCEDURE populate_t1; 125