SET @save_frequency=@@GLOBAL.innodb_purge_rseg_truncate_frequency;
SET GLOBAL innodb_purge_rseg_truncate_frequency=1;
SELECT table_id INTO @table_stats_id FROM information_schema.innodb_sys_tables
WHERE name = 'mysql/innodb_table_stats';
SELECT table_id INTO @index_stats_id FROM information_schema.innodb_sys_tables
WHERE name = 'mysql/innodb_index_stats';
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE table_id NOT IN (@table_stats_id, @index_stats_id) ORDER BY table_id;
TABLE_ID	NAME	FLAG	N_COLS	SPACE	ROW_FORMAT	ZIP_PAGE_SIZE	SPACE_TYPE
11	SYS_FOREIGN	0	7	0	Redundant	0	System
12	SYS_FOREIGN_COLS	0	7	0	Redundant	0	System
13	SYS_TABLESPACES	0	6	0	Redundant	0	System
14	SYS_DATAFILES	0	5	0	Redundant	0	System
15	SYS_VIRTUAL	0	6	0	Redundant	0	System
18	mysql/transaction_registry	33	8	3	Dynamic	0	Single
SELECT table_id,pos,mtype,prtype,len,name
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS
WHERE table_id NOT IN (@table_stats_id, @index_stats_id)
ORDER BY table_id, pos;
table_id	pos	mtype	prtype	len	name
11	0	1	524292	0	ID
11	1	1	524292	0	FOR_NAME
11	2	1	524292	0	REF_NAME
11	3	6	0	4	N_COLS
12	0	1	524292	0	ID
12	1	6	0	4	POS
12	2	1	524292	0	FOR_COL_NAME
12	3	1	524292	0	REF_COL_NAME
13	0	6	0	4	SPACE
13	1	1	524292	0	NAME
13	2	6	0	4	FLAGS
14	0	6	0	4	SPACE
14	1	1	524292	0	PATH
15	0	6	0	8	TABLE_ID
15	1	6	0	4	POS
15	2	6	0	4	BASE_POS
18	0	6	1800	8	transaction_id
18	1	6	1800	8	commit_id
18	2	3	526087	7	begin_timestamp
18	3	3	526087	7	commit_timestamp
18	4	6	1022	1	isolation_level
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES
WHERE table_id NOT IN (@table_stats_id, @index_stats_id) ORDER BY index_id;
INDEX_ID	NAME	TABLE_ID	TYPE	N_FIELDS	PAGE_NO	SPACE	MERGE_THRESHOLD
#	ID_IND	#	3	1	#	#	50
#	FOR_IND	#	0	1	#	#	50
#	REF_IND	#	0	1	#	#	50
#	ID_IND	#	3	2	#	#	50
#	SYS_TABLESPACES_SPACE	#	3	1	#	#	50
#	SYS_DATAFILES_SPACE	#	3	1	#	#	50
#	BASE_IDX	#	3	3	#	#	50
#	PRIMARY	#	3	1	#	#	50
#	commit_id	#	2	1	#	#	50
#	begin_timestamp	#	0	1	#	#	50
#	commit_timestamp	#	0	2	#	#	50
SELECT index_id,pos,name FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS
WHERE name NOT IN ('database_name', 'table_name', 'index_name', 'stat_name')
ORDER BY index_id, pos;
index_id	pos	name
11	0	ID
12	0	FOR_NAME
13	0	REF_NAME
14	0	ID
14	1	POS
15	0	SPACE
16	0	SPACE
17	0	TABLE_ID
17	1	POS
17	2	BASE_POS
20	0	transaction_id
21	0	commit_id
22	0	begin_timestamp
23	0	commit_timestamp
23	1	transaction_id
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
ID	FOR_COL_NAME	REF_COL_NAME	POS
CREATE TABLE t_redundant (a INT KEY, b TEXT) ROW_FORMAT=REDUNDANT ENGINE=innodb;
CREATE TABLE t_compact (a INT KEY, b TEXT) ROW_FORMAT=COMPACT ENGINE=innodb;
CREATE TABLE t_compressed (a INT KEY, b TEXT) ROW_FORMAT=COMPRESSED ENGINE=innodb KEY_BLOCK_SIZE=2;
CREATE TABLE t_dynamic (a INT KEY, b TEXT) ROW_FORMAT=DYNAMIC ENGINE=innodb;
=== information_schema.innodb_sys_tables  and innodb_sys_tablespaces ===
Table Name	Tablespace	Table Flags	Columns	Row Format	Zip Size
test/t_compact	test/t_compact	1	5	Compact	0
test/t_compressed	test/t_compressed	37	5	Compressed	2048
test/t_dynamic	test/t_dynamic	33	5	Dynamic	0
test/t_redundant	test/t_redundant	0	5	Redundant	0
=== information_schema.innodb_sys_tablespaces and innodb_sys_datafiles ===
Space_Name	Page_Size	Zip_Size	Path
test/t_redundant	DEFAULT	DEFAULT	MYSQLD_DATADIR/test/t_redundant.ibd
test/t_compact	DEFAULT	DEFAULT	MYSQLD_DATADIR/test/t_compact.ibd
test/t_compressed	DEFAULT	2048	MYSQLD_DATADIR/test/t_compressed.ibd
test/t_dynamic	DEFAULT	DEFAULT	MYSQLD_DATADIR/test/t_dynamic.ibd
DROP TABLE t_redundant, t_compact, t_compressed, t_dynamic;
SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS;
count(*)
8
CREATE TABLE parent (id INT NOT NULL,
PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT,
INDEX par_ind (parent_id),
CONSTRAINT constraint_test
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE) ENGINE=INNODB;
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/constraint_test	test/child	test/parent	1	1
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
ID	FOR_COL_NAME	REF_COL_NAME	POS
test/constraint_test	parent_id	id	0
INSERT INTO parent VALUES(1);
InnoDB		0 transactions not purged
SELECT name, num_rows, ref_count
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
WHERE name LIKE "%parent";
name	num_rows	ref_count
test/parent	1	1
SELECT NAME, FLAG, N_COLS FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE name NOT LIKE 'sys/%';
NAME	FLAG	N_COLS
SYS_DATAFILES	0	5
SYS_FOREIGN	0	7
SYS_FOREIGN_COLS	0	7
SYS_TABLESPACES	0	6
SYS_VIRTUAL	0	6
mysql/innodb_index_stats	33	11
mysql/innodb_table_stats	33	9
mysql/transaction_registry	33	8
test/child	33	5
test/parent	33	4
SELECT name, n_fields
from INFORMATION_SCHEMA.INNODB_SYS_INDEXES
WHERE table_id In (SELECT table_id from
INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE name LIKE "%parent%");
name	n_fields
PRIMARY	1
SELECT name, n_fields
from INFORMATION_SCHEMA.INNODB_SYS_INDEXES
WHERE table_id In (SELECT table_id from
INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE name LIKE "%child%");
name	n_fields
GEN_CLUST_INDEX	0
par_ind	1
SELECT name, pos, mtype, len
from INFORMATION_SCHEMA.INNODB_SYS_COLUMNS
WHERE table_id In (SELECT table_id from
INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE name LIKE "%child%");
name	pos	mtype	len
id	0	6	4
parent_id	1	6	4
DROP TABLE child;
DROP TABLE parent;
CREATE TABLE parent (id INT NOT NULL, newid INT NOT NULL,
PRIMARY KEY (id, newid)) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT,
INDEX par_ind (parent_id),
CONSTRAINT constraint_test
FOREIGN KEY (id, parent_id) REFERENCES parent(id, newid)
ON DELETE CASCADE) ENGINE=INNODB;
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/constraint_test	test/child	test/parent	2	1
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
ID	FOR_COL_NAME	REF_COL_NAME	POS
test/constraint_test	id	id	0
test/constraint_test	parent_id	newid	1
INSERT INTO parent VALUES(1, 9);
SELECT * FROM parent WHERE id IN (SELECT id FROM parent);
id	newid
1	9
InnoDB		0 transactions not purged
SELECT name, num_rows, ref_count
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
WHERE name LIKE "%parent";
name	num_rows	ref_count
test/parent	1	2
DROP TABLE child;
DROP TABLE parent;
SET GLOBAL innodb_purge_rseg_truncate_frequency=@save_frequency;