Debian Codegraph @

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

raw
1 TRUNCATE TABLE test_innodb_stats;
2 SELECT 'dummy INSERT, the table should be empty';
3 dummy INSERT, the table should be empty
4 dummy INSERT, the table should be empty
5 ANALYZE TABLE test_innodb_stats;
6 Table Op Msg_type Msg_text
7 test.test_innodb_stats analyze status OK
8 SELECT
9 stat_name,
10 stat_value,
11 sample_size,
12 stat_description
13 FROM mysql.innodb_index_stats
14 WHERE
15 database_name = DATABASE() AND
16 table_name = 'test_innodb_stats' AND
17 index_name = 'a_key' AND
18 stat_name IN ('n_diff_pfx01', 'n_diff_pfx02', 'n_leaf_pages', 'size')
19 ORDER BY stat_name;
20 stat_name n_diff_pfx01
21 stat_value 0
22 sample_size 1
23 stat_description a
24 stat_name n_diff_pfx02
25 stat_value 0
26 sample_size 1
27 stat_description a,DB_ROW_ID
28 stat_name n_leaf_pages
29 stat_value 1
30 sample_size NULL
31 stat_description Number of leaf pages in the index
32 stat_name size
33 stat_value 1
34 sample_size NULL
35 stat_description Number of pages in the index
36 FLUSH TABLE test_innodb_stats;
37 SELECT * FROM information_schema.statistics
38 WHERE table_name = 'test_innodb_stats';
39 TABLE_CATALOG def
40 TABLE_SCHEMA test
41 TABLE_NAME test_innodb_stats
42 NON_UNIQUE 1
43 INDEX_SCHEMA test
44 INDEX_NAME a_key
45 SEQ_IN_INDEX 1
46 COLUMN_NAME a
47 COLLATION A
48 CARDINALITY 0
49 SUB_PART NULL
50 PACKED NULL
51 NULLABLE YES
52 INDEX_TYPE BTREE
53 COMMENT
54 INDEX_COMMENT
55 IGNORED NO
56 TRUNCATE TABLE test_innodb_stats;
57 INSERT INTO test_innodb_stats (a) VALUES (1);
58 ANALYZE TABLE test_innodb_stats;
59 Table Op Msg_type Msg_text
60 test.test_innodb_stats analyze status OK
61 SELECT
62 stat_name,
63 stat_value,
64 sample_size,
65 stat_description
66 FROM mysql.innodb_index_stats
67 WHERE
68 database_name = DATABASE() AND
69 table_name = 'test_innodb_stats' AND
70 index_name = 'a_key' AND
71 stat_name IN ('n_diff_pfx01', 'n_diff_pfx02', 'n_leaf_pages', 'size')
72 ORDER BY stat_name;
73 stat_name n_diff_pfx01
74 stat_value 1
75 sample_size 1
76 stat_description a
77 stat_name n_diff_pfx02
78 stat_value 1
79 sample_size 1
80 stat_description a,DB_ROW_ID
81 stat_name n_leaf_pages
82 stat_value 1
83 sample_size NULL
84 stat_description Number of leaf pages in the index
85 stat_name size
86 stat_value 1
87 sample_size NULL
88 stat_description Number of pages in the index
89 FLUSH TABLE test_innodb_stats;
90 SELECT * FROM information_schema.statistics
91 WHERE table_name = 'test_innodb_stats';
92 TABLE_CATALOG def
93 TABLE_SCHEMA test
94 TABLE_NAME test_innodb_stats
95 NON_UNIQUE 1
96 INDEX_SCHEMA test
97 INDEX_NAME a_key
98 SEQ_IN_INDEX 1
99 COLUMN_NAME a
100 COLLATION A
101 CARDINALITY 1
102 SUB_PART NULL
103 PACKED NULL
104 NULLABLE YES
105 INDEX_TYPE BTREE
106 COMMENT
107 INDEX_COMMENT
108 IGNORED NO
109 TRUNCATE TABLE test_innodb_stats;
110 INSERT INTO test_innodb_stats (a) VALUES (1), (1);
111 ANALYZE TABLE test_innodb_stats;
112 Table Op Msg_type Msg_text
113 test.test_innodb_stats analyze status OK
114 SELECT
115 stat_name,
116 stat_value,
117 sample_size,
118 stat_description
119 FROM mysql.innodb_index_stats
120 WHERE
121 database_name = DATABASE() AND
122 table_name = 'test_innodb_stats' AND
123 index_name = 'a_key' AND
124 stat_name IN ('n_diff_pfx01', 'n_diff_pfx02', 'n_leaf_pages', 'size')
125 ORDER BY stat_name;
126 stat_name n_diff_pfx01
127 stat_value 1
128 sample_size 1
129 stat_description a
130 stat_name n_diff_pfx02
131 stat_value 2
132 sample_size 1
133 stat_description a,DB_ROW_ID
134 stat_name n_leaf_pages
135 stat_value 1
136 sample_size NULL
137 stat_description Number of leaf pages in the index
138 stat_name size
139 stat_value 1
140 sample_size NULL
141 stat_description Number of pages in the index
142 FLUSH TABLE test_innodb_stats;
143 SELECT * FROM information_schema.statistics
144 WHERE table_name = 'test_innodb_stats';
145 TABLE_CATALOG def
146 TABLE_SCHEMA test
147 TABLE_NAME test_innodb_stats
148 NON_UNIQUE 1
149 INDEX_SCHEMA test
150 INDEX_NAME a_key
151 SEQ_IN_INDEX 1
152 COLUMN_NAME a
153 COLLATION A
154 CARDINALITY 1
155 SUB_PART NULL
156 PACKED NULL
157 NULLABLE YES
158 INDEX_TYPE BTREE
159 COMMENT
160 INDEX_COMMENT
161 IGNORED NO
162 TRUNCATE TABLE test_innodb_stats;
163 INSERT INTO test_innodb_stats (a) VALUES (1), (1), (1);
164 ANALYZE TABLE test_innodb_stats;
165 Table Op Msg_type Msg_text
166 test.test_innodb_stats analyze status OK
167 SELECT
168 stat_name,
169 stat_value,
170 sample_size,
171 stat_description
172 FROM mysql.innodb_index_stats
173 WHERE
174 database_name = DATABASE() AND
175 table_name = 'test_innodb_stats' AND
176 index_name = 'a_key' AND
177 stat_name IN ('n_diff_pfx01', 'n_diff_pfx02', 'n_leaf_pages', 'size')
178 ORDER BY stat_name;
179 stat_name n_diff_pfx01
180 stat_value 1
181 sample_size 1
182 stat_description a
183 stat_name n_diff_pfx02
184 stat_value 3
185 sample_size 1
186 stat_description a,DB_ROW_ID
187 stat_name n_leaf_pages
188 stat_value 1
189 sample_size NULL
190 stat_description Number of leaf pages in the index
191 stat_name size
192 stat_value 1
193 sample_size NULL
194 stat_description Number of pages in the index
195 FLUSH TABLE test_innodb_stats;
196 SELECT * FROM information_schema.statistics
197 WHERE table_name = 'test_innodb_stats';
198 TABLE_CATALOG def
199 TABLE_SCHEMA test
200 TABLE_NAME test_innodb_stats
201 NON_UNIQUE 1
202 INDEX_SCHEMA test
203 INDEX_NAME a_key
204 SEQ_IN_INDEX 1
205 COLUMN_NAME a
206 COLLATION A
207 CARDINALITY 1
208 SUB_PART NULL
209 PACKED NULL
210 NULLABLE YES
211 INDEX_TYPE BTREE
212 COMMENT
213 INDEX_COMMENT
214 IGNORED NO
215 TRUNCATE TABLE test_innodb_stats;
216 INSERT INTO test_innodb_stats (a) VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1);
217 ANALYZE TABLE test_innodb_stats;
218 Table Op Msg_type Msg_text
219 test.test_innodb_stats analyze status OK
220 SELECT
221 stat_name,
222 stat_value,
223 sample_size,
224 stat_description
225 FROM mysql.innodb_index_stats
226 WHERE
227 database_name = DATABASE() AND
228 table_name = 'test_innodb_stats' AND
229 index_name = 'a_key' AND
230 stat_name IN ('n_diff_pfx01', 'n_diff_pfx02', 'n_leaf_pages', 'size')
231 ORDER BY stat_name;
232 stat_name n_diff_pfx01
233 stat_value 1
234 sample_size 1
235 stat_description a
236 stat_name n_diff_pfx02
237 stat_value 10
238 sample_size 1
239 stat_description a,DB_ROW_ID
240 stat_name n_leaf_pages
241 stat_value 1
242 sample_size NULL
243 stat_description Number of leaf pages in the index
244 stat_name size
245 stat_value 1
246 sample_size NULL
247 stat_description Number of pages in the index
248 FLUSH TABLE test_innodb_stats;
249 SELECT * FROM information_schema.statistics
250 WHERE table_name = 'test_innodb_stats';
251 TABLE_CATALOG def
252 TABLE_SCHEMA test
253 TABLE_NAME test_innodb_stats
254 NON_UNIQUE 1
255 INDEX_SCHEMA test
256 INDEX_NAME a_key
257 SEQ_IN_INDEX 1
258 COLUMN_NAME a
259 COLLATION A
260 CARDINALITY 1
261 SUB_PART NULL
262 PACKED NULL
263 NULLABLE YES
264 INDEX_TYPE BTREE
265 COMMENT
266 INDEX_COMMENT
267 IGNORED NO
268 TRUNCATE TABLE test_innodb_stats;
269 INSERT INTO test_innodb_stats (a) VALUES (1), (2);
270 ANALYZE TABLE test_innodb_stats;
271 Table Op Msg_type Msg_text
272 test.test_innodb_stats analyze status OK
273 SELECT
274 stat_name,
275 stat_value,
276 sample_size,
277 stat_description
278 FROM mysql.innodb_index_stats
279 WHERE
280 database_name = DATABASE() AND
281 table_name = 'test_innodb_stats' AND
282 index_name = 'a_key' AND
283 stat_name IN ('n_diff_pfx01', 'n_diff_pfx02', 'n_leaf_pages', 'size')
284 ORDER BY stat_name;
285 stat_name n_diff_pfx01
286 stat_value 2
287 sample_size 1
288 stat_description a
289 stat_name n_diff_pfx02
290 stat_value 2
291 sample_size 1
292 stat_description a,DB_ROW_ID
293 stat_name n_leaf_pages
294 stat_value 1
295 sample_size NULL
296 stat_description Number of leaf pages in the index
297 stat_name size
298 stat_value 1
299 sample_size NULL
300 stat_description Number of pages in the index
301 FLUSH TABLE test_innodb_stats;
302 SELECT * FROM information_schema.statistics
303 WHERE table_name = 'test_innodb_stats';
304 TABLE_CATALOG def
305 TABLE_SCHEMA test
306 TABLE_NAME test_innodb_stats
307 NON_UNIQUE 1
308 INDEX_SCHEMA test
309 INDEX_NAME a_key
310 SEQ_IN_INDEX 1
311 COLUMN_NAME a
312 COLLATION A
313 CARDINALITY 2
314 SUB_PART NULL
315 PACKED NULL
316 NULLABLE YES
317 INDEX_TYPE BTREE
318 COMMENT
319 INDEX_COMMENT
320 IGNORED NO
321 TRUNCATE TABLE test_innodb_stats;
322 INSERT INTO test_innodb_stats (a) VALUES (1), (1), (2);
323 ANALYZE TABLE test_innodb_stats;
324 Table Op Msg_type Msg_text
325 test.test_innodb_stats analyze status OK
326 SELECT
327 stat_name,
328 stat_value,
329 sample_size,
330 stat_description
331 FROM mysql.innodb_index_stats
332 WHERE
333 database_name = DATABASE() AND
334 table_name = 'test_innodb_stats' AND
335 index_name = 'a_key' AND
336 stat_name IN ('n_diff_pfx01', 'n_diff_pfx02', 'n_leaf_pages', 'size')
337 ORDER BY stat_name;
338 stat_name n_diff_pfx01
339 stat_value 2
340 sample_size 1
341 stat_description a
342 stat_name n_diff_pfx02
343 stat_value 3
344 sample_size 1
345 stat_description a,DB_ROW_ID
346 stat_name n_leaf_pages
347 stat_value 1
348 sample_size NULL
349 stat_description Number of leaf pages in the index
350 stat_name size
351 stat_value 1
352 sample_size NULL
353 stat_description Number of pages in the index
354 FLUSH TABLE test_innodb_stats;
355 SELECT * FROM information_schema.statistics
356 WHERE table_name = 'test_innodb_stats';
357 TABLE_CATALOG def
358 TABLE_SCHEMA test
359 TABLE_NAME test_innodb_stats
360 NON_UNIQUE 1
361 INDEX_SCHEMA test
362 INDEX_NAME a_key
363 SEQ_IN_INDEX 1
364 COLUMN_NAME a
365 COLLATION A
366 CARDINALITY 3
367 SUB_PART NULL
368 PACKED NULL
369 NULLABLE YES
370 INDEX_TYPE BTREE
371 COMMENT
372 INDEX_COMMENT
373 IGNORED NO
374 TRUNCATE TABLE test_innodb_stats;
375 INSERT INTO test_innodb_stats (a) VALUES (1), (2), (3);
376 ANALYZE TABLE test_innodb_stats;
377 Table Op Msg_type Msg_text
378 test.test_innodb_stats analyze status OK
379 SELECT
380 stat_name,
381 stat_value,
382 sample_size,
383 stat_description
384 FROM mysql.innodb_index_stats
385 WHERE
386 database_name = DATABASE() AND
387 table_name = 'test_innodb_stats' AND
388 index_name = 'a_key' AND
389 stat_name IN ('n_diff_pfx01', 'n_diff_pfx02', 'n_leaf_pages', 'size')
390 ORDER BY stat_name;
391 stat_name n_diff_pfx01
392 stat_value 3
393 sample_size 1
394 stat_description a
395 stat_name n_diff_pfx02
396 stat_value 3
397 sample_size 1
398 stat_description a,DB_ROW_ID
399 stat_name n_leaf_pages
400 stat_value 1
401 sample_size NULL
402 stat_description Number of leaf pages in the index
403 stat_name size
404 stat_value 1
405 sample_size NULL
406 stat_description Number of pages in the index
407 FLUSH TABLE test_innodb_stats;
408 SELECT * FROM information_schema.statistics
409 WHERE table_name = 'test_innodb_stats';
410 TABLE_CATALOG def
411 TABLE_SCHEMA test
412 TABLE_NAME test_innodb_stats
413 NON_UNIQUE 1
414 INDEX_SCHEMA test
415 INDEX_NAME a_key
416 SEQ_IN_INDEX 1
417 COLUMN_NAME a
418 COLLATION A
419 CARDINALITY 3
420 SUB_PART NULL
421 PACKED NULL
422 NULLABLE YES
423 INDEX_TYPE BTREE
424 COMMENT
425 INDEX_COMMENT
426 IGNORED NO
427 TRUNCATE TABLE test_innodb_stats;
428 INSERT INTO test_innodb_stats (a) VALUES (1), (1), (2), (3), (3);
429 ANALYZE TABLE test_innodb_stats;
430 Table Op Msg_type Msg_text
431 test.test_innodb_stats analyze status OK
432 SELECT
433 stat_name,
434 stat_value,
435 sample_size,
436 stat_description
437 FROM mysql.innodb_index_stats
438 WHERE
439 database_name = DATABASE() AND
440 table_name = 'test_innodb_stats' AND
441 index_name = 'a_key' AND
442 stat_name IN ('n_diff_pfx01', 'n_diff_pfx02', 'n_leaf_pages', 'size')
443 ORDER BY stat_name;
444 stat_name n_diff_pfx01
445 stat_value 3
446 sample_size 1
447 stat_description a
448 stat_name n_diff_pfx02
449 stat_value 5
450 sample_size 1
451 stat_description a,DB_ROW_ID
452 stat_name n_leaf_pages
453 stat_value 1
454 sample_size NULL
455 stat_description Number of leaf pages in the index
456 stat_name size
457 stat_value 1
458 sample_size NULL
459 stat_description Number of pages in the index
460 FLUSH TABLE test_innodb_stats;
461 SELECT * FROM information_schema.statistics
462 WHERE table_name = 'test_innodb_stats';
463 TABLE_CATALOG def
464 TABLE_SCHEMA test
465 TABLE_NAME test_innodb_stats
466 NON_UNIQUE 1
467 INDEX_SCHEMA test
468 INDEX_NAME a_key
469 SEQ_IN_INDEX 1
470 COLUMN_NAME a
471 COLLATION A
472 CARDINALITY 5
473 SUB_PART NULL
474 PACKED NULL
475 NULLABLE YES
476 INDEX_TYPE BTREE
477 COMMENT
478 INDEX_COMMENT
479 IGNORED NO
480 TRUNCATE TABLE test_innodb_stats;
481 INSERT INTO test_innodb_stats (a) VALUES (1), (2), (3), (4), (5), (1), (2), (3), (4), (5);
482 ANALYZE TABLE test_innodb_stats;
483 Table Op Msg_type Msg_text
484 test.test_innodb_stats analyze status OK
485 SELECT
486 stat_name,
487 stat_value,
488 sample_size,
489 stat_description
490 FROM mysql.innodb_index_stats
491 WHERE
492 database_name = DATABASE() AND
493 table_name = 'test_innodb_stats' AND
494 index_name = 'a_key' AND
495 stat_name IN ('n_diff_pfx01', 'n_diff_pfx02', 'n_leaf_pages', 'size')
496 ORDER BY stat_name;
497 stat_name n_diff_pfx01
498 stat_value 5
499 sample_size 1
500 stat_description a
501 stat_name n_diff_pfx02
502 stat_value 10
503 sample_size 1
504 stat_description a,DB_ROW_ID
505 stat_name n_leaf_pages
506 stat_value 1
507 sample_size NULL
508 stat_description Number of leaf pages in the index
509 stat_name size
510 stat_value 1
511 sample_size NULL
512 stat_description Number of pages in the index
513 FLUSH TABLE test_innodb_stats;
514 SELECT * FROM information_schema.statistics
515 WHERE table_name = 'test_innodb_stats';
516 TABLE_CATALOG def
517 TABLE_SCHEMA test
518 TABLE_NAME test_innodb_stats
519 NON_UNIQUE 1
520 INDEX_SCHEMA test
521 INDEX_NAME a_key
522 SEQ_IN_INDEX 1
523 COLUMN_NAME a
524 COLLATION A
525 CARDINALITY 5
526 SUB_PART NULL
527 PACKED NULL
528 NULLABLE YES
529 INDEX_TYPE BTREE
530 COMMENT
531 INDEX_COMMENT
532 IGNORED NO
533 #
534 # MDEV-31740 InnoDB statistics column length validation failed
535 #
536 call mtr.add_suppression("InnoDB: Unexpected length of mysql\\.innodb_table_stats\\.last_update");
537 ALTER TABLE mysql.innodb_table_stats MODIFY LAST_UPDATE DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP();
538 CREATE TABLE t (a INT KEY)Engine=InnoDB STATS_PERSISTENT=1;
539 FOUND 1 /InnoDB: Unexpected length of mysql\.innodb_table_stats\.last_update/ in mysqld.1.err
540 ALTER TABLE mysql.innodb_table_stats MODIFY last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP();
541 CREATE TABLE t2(a INT KEY)ENGINE=InnoDB STATS_PERSISTENT= 1;
542 SELECT table_name FROM mysql.innodb_table_stats;
543 table_name
544 t2
545 DROP TABLE t, t2;