Debian Codegraph @

mysql-test/suite/innodb/t/innodb_stats_fetch.test

raw
1 #
2 # Test fetching from stats tables after manual update (ie FLUSH TABLE
3 # can be used to forcibly (re)fetch the stats from disk)
4 #
5
6 -- source include/have_innodb.inc
7 # Various sizes printed in this test depend on the page size and the
8 # functionality tested here is not related to the page size, so we only
9 # test with 16k page size.
10 -- source include/have_innodb_16k.inc
11
12 -- vertical_results
13
14 set @save_use_stat_tables = @@use_stat_tables;
15 set @@use_stat_tables = COMPLEMENTARY;
16
17 CREATE TABLE test_ps_fetch
18 (a INT, b INT, c INT, d INT, PRIMARY KEY (a, b), INDEX idx (c, d))
19 ENGINE=INNODB STATS_PERSISTENT=1;
20
21 ANALYZE TABLE test_ps_fetch;
22
23 SELECT n_rows, clustered_index_size, sum_of_other_index_sizes
24 FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_fetch';
25
26 SELECT index_name, stat_name, stat_value, sample_size, stat_description
27 FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_fetch'
28 ORDER BY index_name, stat_name;
29
30 SELECT index_name, seq_in_index, column_name, cardinality
31 FROM information_schema.statistics WHERE table_name = 'test_ps_fetch'
32 ORDER BY index_name, seq_in_index;
33
34 SELECT
35 table_rows, avg_row_length, max_data_length, index_length
36 FROM information_schema.tables WHERE table_name = 'test_ps_fetch';
37
38 UPDATE mysql.innodb_table_stats SET
39 n_rows = 1000,
40 clustered_index_size = 5
41 WHERE
42 table_name = 'test_ps_fetch';
43
44 UPDATE mysql.innodb_index_stats SET
45 stat_value = 20
46 WHERE
47 table_name = 'test_ps_fetch' AND
48 index_name = 'PRIMARY' AND
49 stat_name = 'n_diff_pfx01';
50
51 UPDATE mysql.innodb_index_stats SET
52 stat_value = 90
53 WHERE
54 table_name = 'test_ps_fetch' AND
55 index_name = 'PRIMARY' AND
56 stat_name = 'n_diff_pfx02';
57
58 UPDATE mysql.innodb_index_stats SET
59 stat_value = 3
60 WHERE
61 table_name = 'test_ps_fetch' AND
62 index_name = 'idx' AND
63 stat_name = 'n_diff_pfx01';
64
65 UPDATE mysql.innodb_index_stats SET
66 stat_value = 11
67 WHERE
68 table_name = 'test_ps_fetch' AND
69 index_name = 'idx' AND
70 stat_name = 'n_diff_pfx02';
71
72 RENAME TABLE test_ps_fetch TO tmp, tmp TO test_ps_fetch;
73
74 SELECT seq_in_index, column_name, cardinality
75 FROM information_schema.statistics WHERE table_name = 'test_ps_fetch'
76 ORDER BY index_name, seq_in_index;
77
78 SELECT
79 table_rows, avg_row_length, max_data_length, index_length
80 FROM information_schema.tables WHERE table_name = 'test_ps_fetch';
81
82 DROP TABLE test_ps_fetch;
83 set @@use_stat_tables = @save_use_stat_tables;
84
85 --echo #
86 --echo # MDEV-28613 LeakSanitizer caused by I_S query using LIMIT ROWS EXAMINED
87 --echo #
88 CREATE TABLE t1(f1 VARCHAR(255), FULLTEXT(f1))ENGINE=InnoDB;
89 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES LIMIT ROWS EXAMINED 5;
90 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES LIMIT ROWS EXAMINED 5;
91 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS LIMIT ROWS EXAMINED 5;
92 --disable_result_log
93 SELECT SPACE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES LIMIT ROWS EXAMINED 5;
94 --enable_result_log
95 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_VIRTUAL LIMIT ROWS EXAMINED 5;
96 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN LIMIT ROWS EXAMINED 5;
97 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS LIMIT ROWS EXAMINED 5;
98 DROP TABLE t1;
99
100 --echo #
101 --echo # MDEV-33462 Disallow LOCK=NONE operation on statistics table
102 --echo #
103 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
104 ALTER TABLE mysql.innodb_table_stats FORCE, LOCK=NONE, ALGORITHM=INPLACE;
105 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
106 ALTER TABLE mysql.innodb_index_stats FORCE, LOCK=NONE, ALGORITHM=INPLACE;
107
108 --echo #
109 --echo # MDEV-34474 InnoDB: Failing assertion: stat_n_leaf_pages > 0
110 --echo # in ha_innobase::estimate_rows_upper_bound
111 --echo #
112
113 CREATE TABLE t (c1 INT,c2 INT,
114 INDEX(c1))STATS_PERSISTENT=1 ENGINE=INNODB;
115 UPDATE mysql.innodb_index_stats SET stat_value=0 WHERE database_name like "test" and table_name like 't';
116 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';
117 UPDATE t SET c1=+1 ORDER BY c2;
118 DROP TABLE t;