1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
|
set @@log_slow_verbosity="";
select @@log_slow_filter;
@@log_slow_filter
admin,filesort,filesort_on_disk,filesort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
select @@log_slow_rate_limit;
@@log_slow_rate_limit
1
select @@log_slow_verbosity;
@@log_slow_verbosity
show variables like "log_slow%";
Variable_name Value
log_slow_admin_statements ON
log_slow_always_query_time 31536000.000000
log_slow_disabled_statements sp
log_slow_filter admin,filesort,filesort_on_disk,filesort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
log_slow_max_warnings 10
log_slow_min_examined_row_limit 0
log_slow_query ON
log_slow_query_file $PATH/mysqld-slow.log
log_slow_query_time 10.000000
log_slow_rate_limit 1
log_slow_slave_statements ON
log_slow_verbosity
set @org_slow_query_log= @@global.slow_query_log;
set @@log_slow_filter= "filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk,admin";
select @@log_slow_filter;
@@log_slow_filter
admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
set @@log_slow_filter="admin,admin";
select @@log_slow_filter;
@@log_slow_filter
admin
set @@log_slow_filter=7;
select @@log_slow_filter;
@@log_slow_filter
admin,filesort,filesort_on_disk
set @@log_slow_filter= "filesort,impossible,impossible2,admin";
ERROR 42000: Variable 'log_slow_filter' can't be set to the value of 'impossible'
set @@log_slow_filter= "filesort, admin";
ERROR 42000: Variable 'log_slow_filter' can't be set to the value of ' admin'
set @@log_slow_filter= 1<<31;
ERROR 42000: Variable 'log_slow_filter' can't be set to the value of '2147483648'
select @@log_slow_filter;
@@log_slow_filter
admin,filesort,filesort_on_disk
set @@log_slow_verbosity= "query_plan,innodb";
select @@log_slow_verbosity;
@@log_slow_verbosity
innodb,query_plan
set @@log_slow_verbosity=1;
select @@log_slow_verbosity;
@@log_slow_verbosity
innodb
show fields from mysql.slow_log;
Field Type Null Key Default Extra
start_time timestamp(6) NO current_timestamp(6) on update current_timestamp(6)
user_host mediumtext NO NULL
query_time time(6) NO NULL
lock_time time(6) NO NULL
rows_sent bigint(20) unsigned NO NULL
rows_examined bigint(20) unsigned NO NULL
db varchar(512) NO NULL
last_insert_id int(11) NO NULL
insert_id int(11) NO NULL
server_id int(10) unsigned NO NULL
sql_text mediumtext NO NULL
thread_id bigint(21) unsigned NO NULL
rows_affected bigint(20) unsigned NO NULL
flush slow logs;
set long_query_time=0.1;
set log_slow_filter='';
set slow_query_log=1;
set global log_output='TABLE';
select sleep(0.5);
sleep(0.5)
0
select count(*) FROM mysql.slow_log;
count(*)
1
set @@long_query_time=default;
set @@slow_query_log=default;
set @@log_slow_filter=default;
set @@log_slow_verbosity=default;
set global log_output= default;
truncate mysql.slow_log;
#
# MDEV-18333 Slow_queries count doesn't increase when slow_query_log is turned off
#
SET SESSION slow_query_log=OFF;
SET GLOBAL slow_query_log=OFF;
SET long_query_time=0.1;
# Although this query is disallowed by slow_query_log, it should still increment Slow_queries
SELECT VARIABLE_VALUE INTO @global_slow_queries
FROM INFORMATION_SCHEMA.GLOBAL_STATUS
WHERE VARIABLE_NAME='SLOW_QUERIES';
SELECT sleep(0.2) INTO @tmp FROM DUAL;
SELECT
CAST(VARIABLE_VALUE AS UNSIGNED)-@global_slow_queries AS Slow_queries_increment
FROM
INFORMATION_SCHEMA.GLOBAL_STATUS
WHERE
VARIABLE_NAME='SLOW_QUERIES';
Slow_queries_increment
1
# Although this query is disallowed by log_slow_filter, it should still increment Slow_queries
SET log_slow_filter=filesort;
SELECT sleep(0.2) INTO @tmp FROM DUAL;
SELECT VARIABLE_VALUE INTO @global_slow_queries
FROM INFORMATION_SCHEMA.GLOBAL_STATUS
WHERE VARIABLE_NAME='SLOW_QUERIES';
SELECT sleep(0.2) INTO @tmp FROM DUAL;
SELECT
CAST(VARIABLE_VALUE AS UNSIGNED)-@global_slow_queries AS Slow_queries_increment
FROM
INFORMATION_SCHEMA.GLOBAL_STATUS
WHERE
VARIABLE_NAME='SLOW_QUERIES';
Slow_queries_increment
1
SET log_slow_filter=DEFAULT;
SET @@long_query_time=default;
SET GLOBAL slow_query_log= @org_slow_query_log;
#
# MDEV-21187: log_slow_filter="" logs queries not using indexes
#
flush status;
create table t (id int);
insert into t values (1),(4);
set log_slow_filter='';
select * from t;
id
1
4
show session status like 'Slow_queries';
Variable_name Value
Slow_queries 0
drop table t;
#
# End of 10.3 tests
#
#
# MDEV-31742: incorrect examined rows in case of stored function usage
#
CREATE TABLE `tab_MDEV_30820` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`NAME_F` varchar(50) DEFAULT NULL,
PRIMARY KEY (`ID`)
);
CREATE TABLE `tab2` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`TAB1_ID` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
);
CREATE FUNCTION `get_zero`() RETURNS int(11)
BEGIN
RETURN(0) ;
END
//
for i in 1..100 do insert into tab_MDEV_30820 values (i,'qwerty'); end for ; //
for i in 1..1000 do insert into tab2 values (i,i+300); end for ; //
SET @old_slow_query_log= @@global.slow_query_log;
SET @old_log_output= @@global.log_output;
SET @old_long_query_time= @@long_query_time;
SET GLOBAL log_output= "TABLE";
SET GLOBAL slow_query_log= ON;
SET SESSION slow_query_log=ON;
SET SESSION long_query_time= 0;
SELECT 0 as zero, (SELECT ID FROM tab2 where tab2.TAB1_ID =
tab_MDEV_30820.ID ORDER BY 1 LIMIT 1 ) AS F1 FROM tab_MDEV_30820 ORDER BY 2 DESC LIMIT 2;
zero F1
0 NULL
0 NULL
SELECT get_zero() as zero, (SELECT ID FROM tab2 where tab2.TAB1_ID =
tab_MDEV_30820.ID ORDER BY 1 LIMIT 1) AS F1 FROM tab_MDEV_30820 ORDER BY 2 DESC LIMIT 2;
zero F1
0 NULL
0 NULL
# should be the same rows_examined
SELECT rows_examined FROM mysql.slow_log WHERE sql_text LIKE '%SELECT%tab_MDEV_30820%';
rows_examined
100202
100202
SET @@long_query_time= @old_long_query_time;
SET @@global.log_output= @old_log_output;
SET @@global.slow_query_log= @old_slow_query_log;
SET SESSION slow_query_log=default;
drop table tab_MDEV_30820, tab2;
drop function get_zero;
# End of 10.4 tests
#
# MDEV-34539 Invalid "use" and "Schema" in slow query log file with multi-line schema
#
set global log_output='file';
set @@log_slow_filter= 'not_using_index';
set slow_query_log=1;
set timestamp=1234567890;
create database `a
b`;
use `a
b`;
create table t1 (a int);
insert t1 values (1),(2),(3),(4),(5),(6),(7);
select count(*) from t1 where a>2;
count(*)
5
drop database `a
b`;
use test;
set global log_output= @old_log_output;
set slow_query_log=default;
set log_slow_filter=default;
set timestamp=default;
use `a
b`;
SET timestamp=1234567890;
select count(*) from t1 where a>2
#
# MDEV-31366 Assertion `thd->start_time' failed in bool LOGGER::slow_log_print(THD*, const char*, size_t, ulonglong)
#
TRUNCATE mysql.slow_log;
SET global log_output='TABLE';
SET timestamp=0.99;
SET long_query_time=0.00001;
SELECT SLEEP(0.1);
SLEEP(0.1)
0
SELECT COUNT(*)>0 AS c1 FROM mysql.slow_log;
c1
1
SET global log_output=default;
SET timestamp=default;
SET long_query_time=default;
TRUNCATE mysql.slow_log;
# End of 10.5 tests
#
# MDEV-34251 Conditional jump or move depends on uninitialised value in
# ha_handler_stats::has_stats
#
set @@global.log_slow_verbosity="";
connect con1,localhost,root,,;
connection con1;
set long_query_time=0.0, log_slow_verbosity='engine';
connection default;
disconnect con1;
# End of 10.6 tests
|
