Debian Codegraph @

mysql-test/suite/innodb/r/innodb_stats_fetch.result

raw
1 set @save_use_stat_tables = @@use_stat_tables;
2 set @@use_stat_tables = COMPLEMENTARY;
3 CREATE TABLE test_ps_fetch
4 (a INT, b INT, c INT, d INT, PRIMARY KEY (a, b), INDEX idx (c, d))
5 ENGINE=INNODB STATS_PERSISTENT=1;
6 ANALYZE TABLE test_ps_fetch;
7 Table test.test_ps_fetch
8 Op analyze
9 Msg_type status
10 Msg_text Engine-independent statistics collected
11 Table test.test_ps_fetch
12 Op analyze
13 Msg_type status
14 Msg_text OK
15 SELECT n_rows, clustered_index_size, sum_of_other_index_sizes
16 FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_fetch';
17 n_rows 0
18 clustered_index_size 1
19 sum_of_other_index_sizes 1
20 SELECT index_name, stat_name, stat_value, sample_size, stat_description
21 FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_fetch'
22 ORDER BY index_name, stat_name;
23 index_name PRIMARY
24 stat_name n_diff_pfx01
25 stat_value 0
26 sample_size 1
27 stat_description a
28 index_name PRIMARY
29 stat_name n_diff_pfx02
30 stat_value 0
31 sample_size 1
32 stat_description a,b
33 index_name PRIMARY
34 stat_name n_leaf_pages
35 stat_value 1
36 sample_size NULL
37 stat_description Number of leaf pages in the index
38 index_name PRIMARY
39 stat_name size
40 stat_value 1
41 sample_size NULL
42 stat_description Number of pages in the index
43 index_name idx
44 stat_name n_diff_pfx01
45 stat_value 0
46 sample_size 1
47 stat_description c
48 index_name idx
49 stat_name n_diff_pfx02
50 stat_value 0
51 sample_size 1
52 stat_description c,d
53 index_name idx
54 stat_name n_diff_pfx03
55 stat_value 0
56 sample_size 1
57 stat_description c,d,a
58 index_name idx
59 stat_name n_diff_pfx04
60 stat_value 0
61 sample_size 1
62 stat_description c,d,a,b
63 index_name idx
64 stat_name n_leaf_pages
65 stat_value 1
66 sample_size NULL
67 stat_description Number of leaf pages in the index
68 index_name idx
69 stat_name size
70 stat_value 1
71 sample_size NULL
72 stat_description Number of pages in the index
73 SELECT index_name, seq_in_index, column_name, cardinality
74 FROM information_schema.statistics WHERE table_name = 'test_ps_fetch'
75 ORDER BY index_name, seq_in_index;
76 index_name idx
77 seq_in_index 1
78 column_name c
79 cardinality 0
80 index_name idx
81 seq_in_index 2
82 column_name d
83 cardinality 0
84 index_name PRIMARY
85 seq_in_index 1
86 column_name a
87 cardinality 0
88 index_name PRIMARY
89 seq_in_index 2
90 column_name b
91 cardinality 0
92 SELECT
93 table_rows, avg_row_length, max_data_length, index_length
94 FROM information_schema.tables WHERE table_name = 'test_ps_fetch';
95 table_rows 0
96 avg_row_length 0
97 max_data_length 0
98 index_length 16384
99 UPDATE mysql.innodb_table_stats SET
100 n_rows = 1000,
101 clustered_index_size = 5
102 WHERE
103 table_name = 'test_ps_fetch';
104 UPDATE mysql.innodb_index_stats SET
105 stat_value = 20
106 WHERE
107 table_name = 'test_ps_fetch' AND
108 index_name = 'PRIMARY' AND
109 stat_name = 'n_diff_pfx01';
110 UPDATE mysql.innodb_index_stats SET
111 stat_value = 90
112 WHERE
113 table_name = 'test_ps_fetch' AND
114 index_name = 'PRIMARY' AND
115 stat_name = 'n_diff_pfx02';
116 UPDATE mysql.innodb_index_stats SET
117 stat_value = 3
118 WHERE
119 table_name = 'test_ps_fetch' AND
120 index_name = 'idx' AND
121 stat_name = 'n_diff_pfx01';
122 UPDATE mysql.innodb_index_stats SET
123 stat_value = 11
124 WHERE
125 table_name = 'test_ps_fetch' AND
126 index_name = 'idx' AND
127 stat_name = 'n_diff_pfx02';
128 RENAME TABLE test_ps_fetch TO tmp, tmp TO test_ps_fetch;
129 SELECT seq_in_index, column_name, cardinality
130 FROM information_schema.statistics WHERE table_name = 'test_ps_fetch'
131 ORDER BY index_name, seq_in_index;
132 seq_in_index 1
133 column_name c
134 cardinality 3
135 seq_in_index 2
136 column_name d
137 cardinality 11
138 seq_in_index 1
139 column_name a
140 cardinality 20
141 seq_in_index 2
142 column_name b
143 cardinality 90
144 SELECT
145 table_rows, avg_row_length, max_data_length, index_length
146 FROM information_schema.tables WHERE table_name = 'test_ps_fetch';
147 table_rows 1000
148 avg_row_length 81
149 max_data_length 0
150 index_length 16384
151 DROP TABLE test_ps_fetch;
152 set @@use_stat_tables = @save_use_stat_tables;
153 #
154 # MDEV-28613 LeakSanitizer caused by I_S query using LIMIT ROWS EXAMINED
155 #
156 CREATE TABLE t1(f1 VARCHAR(255), FULLTEXT(f1))ENGINE=InnoDB;
157 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES LIMIT ROWS EXAMINED 5;
158 Warnings:
159 Level Warning
160 Code 1931
161 Message Query execution was interrupted. The query exceeded LIMIT ROWS EXAMINED 5. The query result may be incomplete
162 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES LIMIT ROWS EXAMINED 5;
163 Warnings:
164 Level Warning
165 Code 1931
166 Message Query execution was interrupted. The query exceeded LIMIT ROWS EXAMINED 5. The query result may be incomplete
167 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS LIMIT ROWS EXAMINED 5;
168 Warnings:
169 Level Warning
170 Code 1931
171 Message Query execution was interrupted. The query exceeded LIMIT ROWS EXAMINED 5. The query result may be incomplete
172 SELECT SPACE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES LIMIT ROWS EXAMINED 5;
173 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_VIRTUAL LIMIT ROWS EXAMINED 5;
174 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN LIMIT ROWS EXAMINED 5;
175 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS LIMIT ROWS EXAMINED 5;
176 DROP TABLE t1;
177 #
178 # MDEV-33462 Disallow LOCK=NONE operation on statistics table
179 #
180 ALTER TABLE mysql.innodb_table_stats FORCE, LOCK=NONE, ALGORITHM=INPLACE;
181 ERROR 0A000: LOCK=NONE is not supported. Reason: innodb_table_stats. Try LOCK=SHARED
182 ALTER TABLE mysql.innodb_index_stats FORCE, LOCK=NONE, ALGORITHM=INPLACE;
183 ERROR 0A000: LOCK=NONE is not supported. Reason: innodb_index_stats. Try LOCK=SHARED
184 #
185 # MDEV-34474 InnoDB: Failing assertion: stat_n_leaf_pages > 0
186 # in ha_innobase::estimate_rows_upper_bound
187 #
188 CREATE TABLE t (c1 INT,c2 INT,
189 INDEX(c1))STATS_PERSISTENT=1 ENGINE=INNODB;
190 UPDATE mysql.innodb_index_stats SET stat_value=0 WHERE database_name like "test" and table_name like 't';
191 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';
192 UPDATE t SET c1=+1 ORDER BY c2;
193 DROP TABLE t;