Debian Codegraph @

mysql-test/suite/innodb/r/innodb-system-table-view.result

raw
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;