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
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
|
set @save_use_stat_tables = @@use_stat_tables;
set @@use_stat_tables = COMPLEMENTARY;
CREATE TABLE test_ps_fetch
(a INT, b INT, c INT, d INT, PRIMARY KEY (a, b), INDEX idx (c, d))
ENGINE=INNODB STATS_PERSISTENT=1;
ANALYZE TABLE test_ps_fetch;
Table test.test_ps_fetch
Op analyze
Msg_type status
Msg_text Engine-independent statistics collected
Table test.test_ps_fetch
Op analyze
Msg_type status
Msg_text OK
SELECT n_rows, clustered_index_size, sum_of_other_index_sizes
FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_fetch';
n_rows 0
clustered_index_size 1
sum_of_other_index_sizes 1
SELECT index_name, stat_name, stat_value, sample_size, stat_description
FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_fetch'
ORDER BY index_name, stat_name;
index_name PRIMARY
stat_name n_diff_pfx01
stat_value 0
sample_size 1
stat_description a
index_name PRIMARY
stat_name n_diff_pfx02
stat_value 0
sample_size 1
stat_description a,b
index_name PRIMARY
stat_name n_leaf_pages
stat_value 1
sample_size NULL
stat_description Number of leaf pages in the index
index_name PRIMARY
stat_name size
stat_value 1
sample_size NULL
stat_description Number of pages in the index
index_name idx
stat_name n_diff_pfx01
stat_value 0
sample_size 1
stat_description c
index_name idx
stat_name n_diff_pfx02
stat_value 0
sample_size 1
stat_description c,d
index_name idx
stat_name n_diff_pfx03
stat_value 0
sample_size 1
stat_description c,d,a
index_name idx
stat_name n_diff_pfx04
stat_value 0
sample_size 1
stat_description c,d,a,b
index_name idx
stat_name n_leaf_pages
stat_value 1
sample_size NULL
stat_description Number of leaf pages in the index
index_name idx
stat_name size
stat_value 1
sample_size NULL
stat_description Number of pages in the index
SELECT index_name, seq_in_index, column_name, cardinality
FROM information_schema.statistics WHERE table_name = 'test_ps_fetch'
ORDER BY index_name, seq_in_index;
index_name idx
seq_in_index 1
column_name c
cardinality 0
index_name idx
seq_in_index 2
column_name d
cardinality 0
index_name PRIMARY
seq_in_index 1
column_name a
cardinality 0
index_name PRIMARY
seq_in_index 2
column_name b
cardinality 0
SELECT
table_rows, avg_row_length, max_data_length, index_length
FROM information_schema.tables WHERE table_name = 'test_ps_fetch';
table_rows 0
avg_row_length 0
max_data_length 0
index_length 16384
UPDATE mysql.innodb_table_stats SET
n_rows = 1000,
clustered_index_size = 5
WHERE
table_name = 'test_ps_fetch';
UPDATE mysql.innodb_index_stats SET
stat_value = 20
WHERE
table_name = 'test_ps_fetch' AND
index_name = 'PRIMARY' AND
stat_name = 'n_diff_pfx01';
UPDATE mysql.innodb_index_stats SET
stat_value = 90
WHERE
table_name = 'test_ps_fetch' AND
index_name = 'PRIMARY' AND
stat_name = 'n_diff_pfx02';
UPDATE mysql.innodb_index_stats SET
stat_value = 3
WHERE
table_name = 'test_ps_fetch' AND
index_name = 'idx' AND
stat_name = 'n_diff_pfx01';
UPDATE mysql.innodb_index_stats SET
stat_value = 11
WHERE
table_name = 'test_ps_fetch' AND
index_name = 'idx' AND
stat_name = 'n_diff_pfx02';
RENAME TABLE test_ps_fetch TO tmp, tmp TO test_ps_fetch;
SELECT seq_in_index, column_name, cardinality
FROM information_schema.statistics WHERE table_name = 'test_ps_fetch'
ORDER BY index_name, seq_in_index;
seq_in_index 1
column_name c
cardinality 3
seq_in_index 2
column_name d
cardinality 11
seq_in_index 1
column_name a
cardinality 20
seq_in_index 2
column_name b
cardinality 90
SELECT
table_rows, avg_row_length, max_data_length, index_length
FROM information_schema.tables WHERE table_name = 'test_ps_fetch';
table_rows 1000
avg_row_length 81
max_data_length 0
index_length 16384
DROP TABLE test_ps_fetch;
set @@use_stat_tables = @save_use_stat_tables;
#
# MDEV-28613 LeakSanitizer caused by I_S query using LIMIT ROWS EXAMINED
#
CREATE TABLE t1(f1 VARCHAR(255), FULLTEXT(f1))ENGINE=InnoDB;
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES LIMIT ROWS EXAMINED 5;
Warnings:
Level Warning
Code 1931
Message Query execution was interrupted. The query exceeded LIMIT ROWS EXAMINED 5. The query result may be incomplete
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES LIMIT ROWS EXAMINED 5;
Warnings:
Level Warning
Code 1931
Message Query execution was interrupted. The query exceeded LIMIT ROWS EXAMINED 5. The query result may be incomplete
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS LIMIT ROWS EXAMINED 5;
Warnings:
Level Warning
Code 1931
Message Query execution was interrupted. The query exceeded LIMIT ROWS EXAMINED 5. The query result may be incomplete
SELECT SPACE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES LIMIT ROWS EXAMINED 5;
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_VIRTUAL LIMIT ROWS EXAMINED 5;
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN LIMIT ROWS EXAMINED 5;
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS LIMIT ROWS EXAMINED 5;
DROP TABLE t1;
#
# MDEV-33462 Disallow LOCK=NONE operation on statistics table
#
ALTER TABLE mysql.innodb_table_stats FORCE, LOCK=NONE, ALGORITHM=INPLACE;
ERROR 0A000: LOCK=NONE is not supported. Reason: innodb_table_stats. Try LOCK=SHARED
ALTER TABLE mysql.innodb_index_stats FORCE, LOCK=NONE, ALGORITHM=INPLACE;
ERROR 0A000: LOCK=NONE is not supported. Reason: innodb_index_stats. Try LOCK=SHARED
#
# MDEV-34474 InnoDB: Failing assertion: stat_n_leaf_pages > 0
# in ha_innobase::estimate_rows_upper_bound
#
CREATE TABLE t (c1 INT,c2 INT,
INDEX(c1))STATS_PERSISTENT=1 ENGINE=INNODB;
UPDATE mysql.innodb_index_stats SET stat_value=0 WHERE database_name like "test" and table_name like 't';
UPDATE mysql.innodb_table_stats SET clustered_index_size= 0, sum_of_other_index_sizes=0 WHERE database_name like "test" and table_name like 't';
UPDATE t SET c1=+1 ORDER BY c2;
DROP TABLE t;
|
