Debian Codegraph @

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