Debian Codegraph @

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

raw
1 =====
2 === Test ANALYZE behavior after default creation
3 =====
4 CREATE TABLE test_ps_flag (a INT) ENGINE=INNODB;
5 SHOW CREATE TABLE test_ps_flag;
6 Table Create Table
7 test_ps_flag CREATE TABLE `test_ps_flag` (
8 `a` int(11) DEFAULT NULL
9 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
10 DELETE FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_flag';
11 DELETE FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
12 SELECT COUNT(*) AS cnt_before FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
13 cnt_before
14 0
15 SET STATEMENT use_stat_tables=never FOR
16 ANALYZE TABLE test_ps_flag;
17 Table Op Msg_type Msg_text
18 test.test_ps_flag analyze status OK
19 SELECT COUNT(*) AS cnt_after FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
20 cnt_after
21 1
22 DROP TABLE test_ps_flag;
23 CREATE TABLE test_ps_flag (a INT) ENGINE=INNODB STATS_PERSISTENT=default;
24 SHOW CREATE TABLE test_ps_flag;
25 Table Create Table
26 test_ps_flag CREATE TABLE `test_ps_flag` (
27 `a` int(11) DEFAULT NULL
28 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
29 DELETE FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_flag';
30 DELETE FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
31 SELECT COUNT(*) AS cnt_before FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
32 cnt_before
33 0
34 SET STATEMENT use_stat_tables=never FOR
35 ANALYZE TABLE test_ps_flag;
36 Table Op Msg_type Msg_text
37 test.test_ps_flag analyze status OK
38 SELECT COUNT(*) AS cnt_after FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
39 cnt_after
40 1
41 DROP TABLE test_ps_flag;
42 =====
43 === Test ANALYZE behavior after creation with explicit PS=OFF
44 =====
45 CREATE TABLE test_ps_flag (a INT) ENGINE=INNODB STATS_PERSISTENT=0;
46 SHOW CREATE TABLE test_ps_flag;
47 Table Create Table
48 test_ps_flag CREATE TABLE `test_ps_flag` (
49 `a` int(11) DEFAULT NULL
50 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci STATS_PERSISTENT=0
51 DELETE FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_flag';
52 DELETE FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
53 SELECT COUNT(*) AS cnt_before FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
54 cnt_before
55 0
56 SET STATEMENT use_stat_tables=never FOR
57 ANALYZE TABLE test_ps_flag;
58 Table Op Msg_type Msg_text
59 test.test_ps_flag analyze status OK
60 SELECT COUNT(*) AS cnt_after FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
61 cnt_after
62 0
63 DROP TABLE test_ps_flag;
64 =====
65 === Test ANALYZE behavior after creation with explicit PS=ON
66 =====
67 CREATE TABLE test_ps_flag (a INT) ENGINE=INNODB STATS_PERSISTENT=1;
68 SHOW CREATE TABLE test_ps_flag;
69 Table Create Table
70 test_ps_flag CREATE TABLE `test_ps_flag` (
71 `a` int(11) DEFAULT NULL
72 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci STATS_PERSISTENT=1
73 DELETE FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_flag';
74 DELETE FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
75 SELECT COUNT(*) AS cnt_before FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
76 cnt_before
77 0
78 SET STATEMENT use_stat_tables=never FOR
79 ANALYZE TABLE test_ps_flag;
80 Table Op Msg_type Msg_text
81 test.test_ps_flag analyze status OK
82 SELECT COUNT(*) AS cnt_after FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
83 cnt_after
84 1
85 DROP TABLE test_ps_flag;
86 =====
87 === Test ANALYZE behavior after creation with explicit PS=OFF,
88 === then ALTER to ON, then ALTER to OFF, then ALTER to default
89 =====
90 CREATE TABLE test_ps_flag (a INT) ENGINE=INNODB STATS_PERSISTENT=0;
91 ALTER TABLE test_ps_flag STATS_PERSISTENT=1;
92 # restart
93 SHOW CREATE TABLE test_ps_flag;
94 Table Create Table
95 test_ps_flag CREATE TABLE `test_ps_flag` (
96 `a` int(11) DEFAULT NULL
97 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci STATS_PERSISTENT=1
98 DELETE FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_flag';
99 DELETE FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
100 SELECT COUNT(*) AS cnt_before FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
101 cnt_before
102 0
103 SET STATEMENT use_stat_tables=never FOR
104 ANALYZE TABLE test_ps_flag;
105 Table Op Msg_type Msg_text
106 test.test_ps_flag analyze status OK
107 SELECT COUNT(*) AS cnt_after FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
108 cnt_after
109 1
110 ALTER TABLE test_ps_flag STATS_PERSISTENT=0;
111 SHOW CREATE TABLE test_ps_flag;
112 Table Create Table
113 test_ps_flag CREATE TABLE `test_ps_flag` (
114 `a` int(11) DEFAULT NULL
115 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci STATS_PERSISTENT=0
116 DELETE FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_flag';
117 DELETE FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
118 SELECT COUNT(*) AS cnt_before FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
119 cnt_before
120 0
121 SET STATEMENT use_stat_tables=never FOR
122 ANALYZE TABLE test_ps_flag;
123 Table Op Msg_type Msg_text
124 test.test_ps_flag analyze status OK
125 SELECT COUNT(*) AS cnt_after FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
126 cnt_after
127 0
128 ALTER TABLE test_ps_flag STATS_PERSISTENT=default;
129 SHOW CREATE TABLE test_ps_flag;
130 Table Create Table
131 test_ps_flag CREATE TABLE `test_ps_flag` (
132 `a` int(11) DEFAULT NULL
133 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
134 DELETE FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_flag';
135 DELETE FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
136 SELECT COUNT(*) AS cnt_before FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
137 cnt_before
138 0
139 SET STATEMENT use_stat_tables=never FOR
140 ANALYZE TABLE test_ps_flag;
141 Table Op Msg_type Msg_text
142 test.test_ps_flag analyze status OK
143 SELECT COUNT(*) AS cnt_after FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
144 cnt_after
145 1
146 DROP TABLE test_ps_flag;
147 =====
148 === Test ANALYZE behavior after creation with explicit PS=ON,
149 === then ALTER to OFF, then ALTER to ON, then ALTER to default
150 =====
151 CREATE TABLE test_ps_flag (a INT) ENGINE=INNODB STATS_PERSISTENT=1;
152 ALTER TABLE test_ps_flag STATS_PERSISTENT=0;
153 # restart
154 SHOW CREATE TABLE test_ps_flag;
155 Table Create Table
156 test_ps_flag CREATE TABLE `test_ps_flag` (
157 `a` int(11) DEFAULT NULL
158 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci STATS_PERSISTENT=0
159 DELETE FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_flag';
160 DELETE FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
161 SELECT COUNT(*) AS cnt_before FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
162 cnt_before
163 0
164 SET STATEMENT use_stat_tables=never FOR
165 ANALYZE TABLE test_ps_flag;
166 Table Op Msg_type Msg_text
167 test.test_ps_flag analyze status OK
168 SELECT COUNT(*) AS cnt_after FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
169 cnt_after
170 0
171 ALTER TABLE test_ps_flag STATS_PERSISTENT=1;
172 SHOW CREATE TABLE test_ps_flag;
173 Table Create Table
174 test_ps_flag CREATE TABLE `test_ps_flag` (
175 `a` int(11) DEFAULT NULL
176 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci STATS_PERSISTENT=1
177 DELETE FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_flag';
178 DELETE FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
179 SELECT COUNT(*) AS cnt_before FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
180 cnt_before
181 0
182 SET STATEMENT use_stat_tables=never FOR
183 ANALYZE TABLE test_ps_flag;
184 Table Op Msg_type Msg_text
185 test.test_ps_flag analyze status OK
186 SELECT COUNT(*) AS cnt_after FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
187 cnt_after
188 1
189 ALTER TABLE test_ps_flag STATS_PERSISTENT=default;
190 SHOW CREATE TABLE test_ps_flag;
191 Table Create Table
192 test_ps_flag CREATE TABLE `test_ps_flag` (
193 `a` int(11) DEFAULT NULL
194 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
195 DELETE FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_flag';
196 DELETE FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
197 SELECT COUNT(*) AS cnt_before FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
198 cnt_before
199 0
200 SET STATEMENT use_stat_tables=never FOR
201 ANALYZE TABLE test_ps_flag;
202 Table Op Msg_type Msg_text
203 test.test_ps_flag analyze status OK
204 SELECT COUNT(*) AS cnt_after FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
205 cnt_after
206 1
207 DROP TABLE test_ps_flag;