| 1 |
SET @save_stats_persistent = @@GLOBAL.innodb_stats_persistent; |
| 2 |
SET GLOBAL innodb_stats_persistent = 0; |
| 3 |
SELECT table_id INTO @table_stats_id FROM information_schema.innodb_sys_tables |
| 4 |
WHERE name = 'mysql/innodb_table_stats'; |
| 5 |
SELECT table_id INTO @index_stats_id FROM information_schema.innodb_sys_tables |
| 6 |
WHERE name = 'mysql/innodb_index_stats'; |
| 7 |
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES |
| 8 |
WHERE table_id NOT IN (@table_stats_id, @index_stats_id) ORDER BY table_id; |
| 9 |
TABLE_ID NAME FLAG N_COLS SPACE ROW_FORMAT ZIP_PAGE_SIZE SPACE_TYPE |
| 10 |
11 SYS_FOREIGN 0 7 0 Redundant 0 System |
| 11 |
12 SYS_FOREIGN_COLS 0 7 0 Redundant 0 System |
| 12 |
13 SYS_VIRTUAL 0 6 0 Redundant 0 System |
| 13 |
16 mysql/transaction_registry 33 8 6 Dynamic 0 Single |
| 14 |
SELECT table_id,pos,mtype,prtype,len,name |
| 15 |
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS |
| 16 |
WHERE table_id NOT IN (@table_stats_id, @index_stats_id) |
| 17 |
ORDER BY table_id, pos; |
| 18 |
table_id pos mtype prtype len name |
| 19 |
11 0 1 524292 0 ID |
| 20 |
11 1 1 524292 0 FOR_NAME |
| 21 |
11 2 1 524292 0 REF_NAME |
| 22 |
11 3 6 0 4 N_COLS |
| 23 |
12 0 1 524292 0 ID |
| 24 |
12 1 6 0 4 POS |
| 25 |
12 2 1 524292 0 FOR_COL_NAME |
| 26 |
12 3 1 524292 0 REF_COL_NAME |
| 27 |
13 0 6 0 8 TABLE_ID |
| 28 |
13 1 6 0 4 POS |
| 29 |
13 2 6 0 4 BASE_POS |
| 30 |
16 0 6 1800 8 transaction_id |
| 31 |
16 1 6 1800 8 commit_id |
| 32 |
16 2 3 526087 7 begin_timestamp |
| 33 |
16 3 3 526087 7 commit_timestamp |
| 34 |
16 4 6 1022 1 isolation_level |
| 35 |
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES |
| 36 |
WHERE table_id NOT IN (@table_stats_id, @index_stats_id) ORDER BY index_id; |
| 37 |
INDEX_ID NAME TABLE_ID TYPE N_FIELDS PAGE_NO SPACE MERGE_THRESHOLD |
| 38 |
# ID_IND # 3 1 # # 50 |
| 39 |
# FOR_IND # 0 1 # # 50 |
| 40 |
# REF_IND # 0 1 # # 50 |
| 41 |
# ID_IND # 3 2 # # 50 |
| 42 |
# BASE_IDX # 3 3 # # 50 |
| 43 |
# PRIMARY # 3 1 # # 50 |
| 44 |
# commit_id # 2 1 # # 50 |
| 45 |
# begin_timestamp # 0 1 # # 50 |
| 46 |
# commit_timestamp # 0 2 # # 50 |
| 47 |
SELECT index_id,pos,name FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS |
| 48 |
WHERE name NOT IN ('database_name', 'table_name', 'index_name', 'stat_name') |
| 49 |
ORDER BY index_id, pos; |
| 50 |
index_id pos name |
| 51 |
11 0 ID |
| 52 |
12 0 FOR_NAME |
| 53 |
13 0 REF_NAME |
| 54 |
14 0 ID |
| 55 |
14 1 POS |
| 56 |
15 0 TABLE_ID |
| 57 |
15 1 POS |
| 58 |
15 2 BASE_POS |
| 59 |
18 0 transaction_id |
| 60 |
19 0 commit_id |
| 61 |
20 0 begin_timestamp |
| 62 |
21 0 commit_timestamp |
| 63 |
21 1 transaction_id |
| 64 |
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; |
| 65 |
ID FOR_NAME REF_NAME N_COLS TYPE |
| 66 |
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; |
| 67 |
ID FOR_COL_NAME REF_COL_NAME POS |
| 68 |
CREATE TABLE t_redundant (a INT KEY, b TEXT) ROW_FORMAT=REDUNDANT ENGINE=innodb; |
| 69 |
CREATE TABLE t_compact (a INT KEY, b TEXT) ROW_FORMAT=COMPACT ENGINE=innodb; |
| 70 |
CREATE TABLE t_compressed (a INT KEY, b TEXT) ROW_FORMAT=COMPRESSED ENGINE=innodb KEY_BLOCK_SIZE=2; |
| 71 |
CREATE TABLE t_dynamic (a INT KEY, b TEXT) ROW_FORMAT=DYNAMIC ENGINE=innodb; |
| 72 |
=== information_schema.innodb_sys_tables and innodb_sys_tablespaces === |
| 73 |
Table Name Tablespace Table Flags Columns Row Format Zip Size |
| 74 |
test/t_compact test/t_compact 1 5 Compact 0 |
| 75 |
test/t_compressed test/t_compressed 37 5 Compressed 2048 |
| 76 |
test/t_dynamic test/t_dynamic 33 5 Dynamic 0 |
| 77 |
test/t_redundant test/t_redundant 0 5 Redundant 0 |
| 78 |
=== information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === |
| 79 |
Space_Name Page_Size Zip_Size Path |
| 80 |
innodb_undo001 DEFAULT DEFAULT MYSQLD_DATADIR//undo001 |
| 81 |
innodb_undo002 DEFAULT DEFAULT MYSQLD_DATADIR//undo002 |
| 82 |
innodb_undo003 DEFAULT DEFAULT MYSQLD_DATADIR//undo003 |
| 83 |
test/t_redundant DEFAULT DEFAULT MYSQLD_DATADIR/test/t_redundant.ibd |
| 84 |
test/t_compact DEFAULT DEFAULT MYSQLD_DATADIR/test/t_compact.ibd |
| 85 |
test/t_compressed DEFAULT 2048 MYSQLD_DATADIR/test/t_compressed.ibd |
| 86 |
test/t_dynamic DEFAULT DEFAULT MYSQLD_DATADIR/test/t_dynamic.ibd |
| 87 |
innodb_temporary DEFAULT DEFAULT MYSQLD_DATADIR/ibtmp1 |
| 88 |
DROP TABLE t_redundant, t_compact, t_compressed, t_dynamic; |
| 89 |
SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS; |
| 90 |
count(*) |
| 91 |
6 |
| 92 |
CREATE TABLE parent (id INT NOT NULL, |
| 93 |
PRIMARY KEY (id)) ENGINE=INNODB; |
| 94 |
CREATE TABLE child (id INT, parent_id INT, |
| 95 |
INDEX par_ind (parent_id), |
| 96 |
CONSTRAINT constraint_test |
| 97 |
FOREIGN KEY (parent_id) REFERENCES parent(id) |
| 98 |
ON DELETE CASCADE) ENGINE=INNODB; |
| 99 |
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; |
| 100 |
ID FOR_NAME REF_NAME N_COLS TYPE |
| 101 |
test/constraint_test test/child test/parent 1 1 |
| 102 |
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; |
| 103 |
ID FOR_COL_NAME REF_COL_NAME POS |
| 104 |
test/constraint_test parent_id id 0 |
| 105 |
INSERT INTO parent VALUES(1); |
| 106 |
InnoDB 0 transactions not purged |
| 107 |
SELECT name, num_rows, ref_count |
| 108 |
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS |
| 109 |
WHERE name LIKE "%parent"; |
| 110 |
name num_rows ref_count |
| 111 |
test/parent 1 1 |
| 112 |
SELECT NAME, FLAG, N_COLS FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES |
| 113 |
WHERE name NOT LIKE 'sys/%'; |
| 114 |
NAME FLAG N_COLS |
| 115 |
SYS_FOREIGN 0 7 |
| 116 |
SYS_FOREIGN_COLS 0 7 |
| 117 |
SYS_VIRTUAL 0 6 |
| 118 |
mysql/innodb_index_stats 33 11 |
| 119 |
mysql/innodb_table_stats 33 9 |
| 120 |
mysql/transaction_registry 33 8 |
| 121 |
test/child 33 5 |
| 122 |
test/parent 33 4 |
| 123 |
SELECT name, n_fields |
| 124 |
from INFORMATION_SCHEMA.INNODB_SYS_INDEXES |
| 125 |
WHERE table_id In (SELECT table_id from |
| 126 |
INFORMATION_SCHEMA.INNODB_SYS_TABLES |
| 127 |
WHERE name LIKE "%parent%"); |
| 128 |
name n_fields |
| 129 |
PRIMARY 1 |
| 130 |
SELECT name, n_fields |
| 131 |
from INFORMATION_SCHEMA.INNODB_SYS_INDEXES |
| 132 |
WHERE table_id In (SELECT table_id from |
| 133 |
INFORMATION_SCHEMA.INNODB_SYS_TABLES |
| 134 |
WHERE name LIKE "%child%"); |
| 135 |
name n_fields |
| 136 |
GEN_CLUST_INDEX 0 |
| 137 |
par_ind 1 |
| 138 |
SELECT name, pos, mtype, len |
| 139 |
from INFORMATION_SCHEMA.INNODB_SYS_COLUMNS |
| 140 |
WHERE table_id In (SELECT table_id from |
| 141 |
INFORMATION_SCHEMA.INNODB_SYS_TABLES |
| 142 |
WHERE name LIKE "%child%"); |
| 143 |
name pos mtype len |
| 144 |
id 0 6 4 |
| 145 |
parent_id 1 6 4 |
| 146 |
DROP TABLE child; |
| 147 |
DROP TABLE parent; |
| 148 |
CREATE TABLE parent (id INT NOT NULL, newid INT NOT NULL, |
| 149 |
PRIMARY KEY (id, newid)) ENGINE=INNODB; |
| 150 |
CREATE TABLE child (id INT, parent_id INT, |
| 151 |
INDEX par_ind (parent_id), |
| 152 |
CONSTRAINT constraint_test |
| 153 |
FOREIGN KEY (id, parent_id) REFERENCES parent(id, newid) |
| 154 |
ON DELETE CASCADE) ENGINE=INNODB; |
| 155 |
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; |
| 156 |
ID FOR_NAME REF_NAME N_COLS TYPE |
| 157 |
test/constraint_test test/child test/parent 2 1 |
| 158 |
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; |
| 159 |
ID FOR_COL_NAME REF_COL_NAME POS |
| 160 |
test/constraint_test id id 0 |
| 161 |
test/constraint_test parent_id newid 1 |
| 162 |
INSERT INTO parent VALUES(1, 9); |
| 163 |
SELECT * FROM parent WHERE id IN (SELECT id FROM parent); |
| 164 |
id newid |
| 165 |
1 9 |
| 166 |
InnoDB 0 transactions not purged |
| 167 |
SELECT name, num_rows, ref_count |
| 168 |
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS |
| 169 |
WHERE name LIKE "%parent"; |
| 170 |
name num_rows ref_count |
| 171 |
test/parent 1 2 |
| 172 |
DROP TABLE child; |
| 173 |
DROP TABLE parent; |
| 174 |
# |
| 175 |
# MDEV-29479 I_S.INNODB_SYS_TABLESPACES doesn't have |
| 176 |
# temporary tablespace information |
| 177 |
# |
| 178 |
SELECT SPACE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE name like 'innodb_temporary'; |
| 179 |
SPACE |
| 180 |
4294967294 |
| 181 |
SET GLOBAL innodb_stats_persistent = @save_stats_persistent; |