1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
|
# This is the test for Information Schema System Table View
# that displays the InnoDB system table content through
# information schema tables.
--source include/innodb_page_size_small.inc
SET @save_stats_persistent = @@GLOBAL.innodb_stats_persistent;
SET GLOBAL innodb_stats_persistent = 0;
LET $MYSQLD_DATADIR = `select @@datadir`;
LET $INNODB_PAGE_SIZE = `select @@innodb_page_size`;
# The IDs of mysql.innodb_table_stats and mysql.innodb_index_stats may
# vary depending on whether the tables have been rebuilt
# by previously run tests.
--disable_cursor_protocol
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';
--enable_cursor_protocol
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE table_id NOT IN (@table_stats_id, @index_stats_id) ORDER BY table_id;
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;
--replace_column 1 # 3 # 6 # 7 #
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES
WHERE table_id NOT IN (@table_stats_id, @index_stats_id) ORDER BY index_id;
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;
--sorted_result
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
--sorted_result
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
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;
--source suite/innodb/include/show_i_s_tables.inc
--source suite/innodb/include/show_i_s_tablespaces.inc
DROP TABLE t_redundant, t_compact, t_compressed, t_dynamic;
SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS;
# Create a foreign key constraint, and verify the information
# in INFORMATION_SCHEMA.INNODB_SYS_FOREIGN and
# INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS
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;
--sorted_result
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
--sorted_result
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
# Insert a row in the table "parent", and see whether that reflected in
# INNODB_SYS_TABLESTATS
INSERT INTO parent VALUES(1);
--source include/wait_all_purged.inc
--sorted_result
SELECT name, num_rows, ref_count
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
WHERE name LIKE "%parent";
--sorted_result
SELECT NAME, FLAG, N_COLS FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE name NOT LIKE 'sys/%';
--sorted_result
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%");
--sorted_result
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%");
--sorted_result
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%");
DROP TABLE child;
DROP TABLE parent;
# Create table with 2 columns in the foreign key constraint
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;
--sorted_result
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
--sorted_result
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
INSERT INTO parent VALUES(1, 9);
# Nested query will open the table handle twice
--sorted_result
SELECT * FROM parent WHERE id IN (SELECT id FROM parent);
--source include/wait_all_purged.inc
--sorted_result
SELECT name, num_rows, ref_count
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
WHERE name LIKE "%parent";
DROP TABLE child;
DROP TABLE parent;
--echo #
--echo # MDEV-29479 I_S.INNODB_SYS_TABLESPACES doesn't have
--echo # temporary tablespace information
--echo #
SELECT SPACE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE name like 'innodb_temporary';
SET GLOBAL innodb_stats_persistent = @save_stats_persistent;
|
