1
SET @old_general_log= @@global.general_log;
2
SET @old_general_log_file= @@global.general_log_file;
3
SET @old_slow_query_log= @@global.slow_query_log;
4
SET @old_slow_query_log_file= @@global.slow_query_log_file;
5
set global general_log= OFF;
6
truncate table mysql.general_log;
7
truncate table mysql.slow_log;
9
where Variable_name = 'log' or Variable_name = 'log_slow_queries' or
10
Variable_name = 'general_log' or Variable_name = 'slow_query_log';
17
set global general_log= ON;
18
create table t1(f1 int);
19
select * from mysql.general_log;
20
event_time user_host thread_id server_id command_type argument
21
TIMESTAMP USER_HOST # 1 Query create table t1(f1 int)
22
TIMESTAMP USER_HOST # 1 Query select * from mysql.general_log
23
set global general_log= OFF;
25
select * from mysql.general_log;
26
event_time user_host thread_id server_id command_type argument
27
TIMESTAMP USER_HOST # 1 Query create table t1(f1 int)
28
TIMESTAMP USER_HOST # 1 Query select * from mysql.general_log
29
TIMESTAMP USER_HOST # 1 Query set global general_log= OFF
30
set global general_log= ON;
33
where Variable_name = 'log' or Variable_name = 'log_slow_queries' or
34
Variable_name = 'general_log' or Variable_name = 'slow_query_log';
40
# Establish connection con1 (user=root)
41
# Switch to connection con1
42
set @long_query_time = <long_query_time>;
43
set session long_query_time = @long_query_time;
44
select sleep(@long_query_time + 1);
45
sleep(@long_query_time + 1)
47
select * from mysql.slow_log where sql_text NOT LIKE '%slow_log%';
48
start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text
49
# Switch to connection default
50
set global slow_query_log= ON;
51
# Switch to connection con1
52
set session long_query_time = @long_query_time;
53
select sleep(@long_query_time + 1);
54
sleep(@long_query_time + 1)
56
select * from mysql.slow_log where sql_text NOT LIKE '%slow_log%';
57
start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text
58
TIMESTAMP USER_HOST QUERY_TIME 00:00:00 1 0 test 0 0 1 select sleep(@long_query_time + 1)
59
# Switch to connection default
61
where Variable_name = 'log' or Variable_name = 'log_slow_queries' or
62
Variable_name = 'general_log' or Variable_name = 'slow_query_log';
68
set global general_log= ON;
69
set global general_log= OFF;
70
set global general_log= OFF;
71
set global slow_query_log= ON;
72
set global slow_query_log= OFF;
73
set global slow_query_log= OFF;
74
set global general_log= ON;
75
truncate table mysql.general_log;
76
create table t1(f1 int);
78
select * from mysql.general_log;
79
event_time user_host thread_id server_id command_type argument
80
TIMESTAMP USER_HOST # 1 Query create table t1(f1 int)
81
TIMESTAMP USER_HOST # 1 Query drop table t1
82
TIMESTAMP USER_HOST # 1 Query select * from mysql.general_log
83
set global general_log= OFF;
84
truncate table mysql.general_log;
85
select * from mysql.general_log;
86
event_time user_host thread_id server_id command_type argument
87
set global general_log= ON;
89
where Variable_name = 'log' or Variable_name = 'log_slow_queries' or
90
Variable_name = 'general_log' or Variable_name = 'slow_query_log';
96
show variables like 'general_log_file';
99
show variables like 'slow_query_log_file';
101
slow_query_log_file #
102
show variables like 'log_output';
104
log_output FILE,TABLE
105
set global general_log_file='/not existing path/log.master';
106
ERROR 42000: Variable 'general_log_file' can't be set to the value of '/not existing path/log.master'
107
set global general_log_file='MYSQLTEST_VARDIR';
108
ERROR 42000: Variable 'general_log_file' can't be set to the value of 'MYSQLTEST_VARDIR'
109
set global general_log_file='';
110
ERROR 42000: Variable 'general_log_file' can't be set to the value of ''
111
show variables like 'general_log_file';
114
set global general_log= OFF;
115
set global general_log_file='MYSQLTEST_VARDIR/tmp/log.master';
116
set global general_log= ON;
117
create table t1(f1 int);
119
set global general_log= OFF;
120
set global general_log_file=default;
121
set global general_log= ON;
122
create table t1(f1 int);
124
show variables like 'general_log_file';
127
show variables like 'slow_query_log_file';
129
slow_query_log_file #
130
set global general_log= default;
131
set global slow_query_log= default;
132
set global general_log_file= default;
133
set global slow_query_log_file= default;
134
show variables like 'general_log';
137
show variables like 'slow_query_log';
140
set global general_log=ON;
141
set global log_output=default;
142
show variables like 'log_output';
145
set global general_log=OFF;
146
set global log_output=FILE;
147
truncate table mysql.general_log;
148
show variables like 'log_output';
151
set global general_log=ON;
152
create table t1(f1 int);
153
select * from mysql.general_log;
154
event_time user_host thread_id server_id command_type argument
155
set global general_log=OFF;
156
set global log_output="FILE,TABLE";
157
show variables like 'log_output';
159
log_output FILE,TABLE
160
set global general_log=ON;
162
select * from mysql.general_log;
163
event_time user_host thread_id server_id command_type argument
164
TIMESTAMP USER_HOST # 1 Query drop table t1
165
TIMESTAMP USER_HOST # 1 Query select * from mysql.general_log
166
SET @@global.general_log = @old_general_log;
167
SET @@global.general_log_file = @old_general_log_file;
168
SET @@global.slow_query_log = @old_slow_query_log;
169
SET @@global.slow_query_log_file = @old_slow_query_log_file;
170
SET GLOBAL general_log = ON;
171
SET GLOBAL slow_query_log = ON;
172
FLUSH TABLES WITH READ LOCK;
173
SET GLOBAL general_log = OFF;
174
SET GLOBAL slow_query_log = OFF;
176
FLUSH TABLES WITH READ LOCK;
177
SET GLOBAL general_log = ON;
178
SET GLOBAL slow_query_log = ON;
180
SET GLOBAL READ_ONLY = ON;
181
SET GLOBAL general_log = OFF;
182
SET GLOBAL slow_query_log = OFF;
183
SET GLOBAL READ_ONLY = OFF;
184
SET GLOBAL READ_ONLY = ON;
185
SET GLOBAL general_log = ON;
186
SET GLOBAL slow_query_log = ON;
187
SET GLOBAL READ_ONLY = OFF;
188
SET GLOBAL general_log = @old_general_log;
189
SET GLOBAL slow_query_log = @old_slow_query_log;
190
SET GLOBAL general_log = ON;
191
SHOW VARIABLES LIKE 'general_log';
194
SHOW VARIABLES LIKE 'log';
197
SELECT @@general_log, @@log;
202
Warning 1287 The syntax '@@log' is deprecated and will be removed in MySQL 7.0. Please use '@@general_log' instead
203
SHOW VARIABLES LIKE 'general_log';
206
SHOW VARIABLES LIKE 'log';
209
SELECT @@general_log, @@log;
212
SET GLOBAL general_log = 1;
213
SHOW VARIABLES LIKE 'general_log';
216
SHOW VARIABLES LIKE 'log';
219
SELECT @@general_log, @@log;
222
SHOW VARIABLES LIKE 'slow_query_log';
225
SHOW VARIABLES LIKE 'log_slow_queries';
228
SELECT @@slow_query_log, @@log_slow_queries;
229
@@slow_query_log @@log_slow_queries
231
SET GLOBAL log_slow_queries = 0;
233
Warning 1287 The syntax '@@log_slow_queries' is deprecated and will be removed in MySQL 7.0. Please use '@@slow_query_log' instead
234
SHOW VARIABLES LIKE 'slow_query_log';
237
SHOW VARIABLES LIKE 'log_slow_queries';
240
SELECT @@slow_query_log, @@log_slow_queries;
241
@@slow_query_log @@log_slow_queries
243
SET GLOBAL slow_query_log = 1;
244
SHOW VARIABLES LIKE 'slow_query_log';
247
SHOW VARIABLES LIKE 'log_slow_queries';
250
SELECT @@slow_query_log, @@log_slow_queries;
251
@@slow_query_log @@log_slow_queries
253
SET GLOBAL general_log = @old_general_log;
254
SET GLOBAL slow_query_log = @old_slow_query_log;
255
SET GLOBAL general_log_file= CONCAT('/not existing path/log.maste', 'r');
256
ERROR 42000: Variable 'general_log_file' can't be set to the value of '/not existing path/log.master'
257
SET GLOBAL general_log_file= NULL;
258
ERROR 42000: Variable 'general_log_file' can't be set to the value of 'NULL'
259
SET GLOBAL slow_query_log_file= CONCAT('/not existing path/log.maste', 'r');
260
ERROR 42000: Variable 'slow_query_log_file' can't be set to the value of '/not existing path/log.master'
261
SET GLOBAL slow_query_log_file= NULL;
262
ERROR 42000: Variable 'slow_query_log_file' can't be set to the value of 'NULL'
263
SET GLOBAL general_log_file= @old_general_log_file;
264
SET GLOBAL slow_query_log_file= @old_slow_query_log_file;
267
# -- Bug#32748: Inconsistent handling of assignments to
268
# -- general_log_file/slow_query_log_file.
271
SET GLOBAL general_log_file = 'bug32748.query.log';
272
SET GLOBAL slow_query_log_file = 'bug32748.slow.log';
274
SHOW VARIABLES LIKE '%log_file';
276
general_log_file bug32748.query.log
277
slow_query_log_file bug32748.slow.log
279
SET GLOBAL general_log_file = @old_general_log_file;
280
SET GLOBAL slow_query_log_file = @old_slow_query_log_file;
282
# -- End of Bug#32748.
286
Warning 1287 The syntax '@@log' is deprecated and will be removed in MySQL 7.0. Please use '@@general_log' instead
287
SET GLOBAL log_slow_queries = 0;
289
Warning 1287 The syntax '@@log_slow_queries' is deprecated and will be removed in MySQL 7.0. Please use '@@slow_query_log' instead
290
SET GLOBAL log = DEFAULT;
292
Warning 1287 The syntax '@@log' is deprecated and will be removed in MySQL 7.0. Please use '@@general_log' instead
293
SET GLOBAL log_slow_queries = DEFAULT;
295
Warning 1287 The syntax '@@log_slow_queries' is deprecated and will be removed in MySQL 7.0. Please use '@@slow_query_log' instead
297
SELECT @@global.general_log_file INTO @my_glf;
298
SELECT @@global.slow_query_log_file INTO @my_sqlf;
299
SET GLOBAL general_log = 0;
300
SET GLOBAL slow_query_log = 0;
301
SET GLOBAL general_log_file = 'WL4403_G.log';
302
SET GLOBAL slow_query_log_file = 'WL4403_SQ.log';
303
SET GLOBAL general_log_file = @my_glf;
304
SET GLOBAL slow_query_log_file = @my_sqlf;
305
SET GLOBAL general_log = DEFAULT;
306
SET GLOBAL slow_query_log = DEFAULT;
307
SET @@global.general_log = @old_general_log;
308
SET @@global.general_log_file = @old_general_log_file;
309
SET @@global.slow_query_log = @old_slow_query_log;
310
SET @@global.slow_query_log_file = @old_slow_query_log_file;
312
# Bug #49756 Rows_examined is always 0 in the slow query log
313
# for update statements
315
SET @old_log_output = @@global.log_output;
316
SET GLOBAL log_output = "TABLE";
317
SET GLOBAL slow_query_log = ON;
318
SET GLOBAL long_query_time = 0.001;
319
TRUNCATE TABLE mysql.slow_log;
320
CREATE TABLE t1 (a INT);
321
CREATE TABLE t2 (b INT, PRIMARY KEY (b));
322
INSERT INTO t2 VALUES (3),(4);
323
INSERT INTO t1 VALUES (1+sleep(.01)),(2);
324
INSERT INTO t1 SELECT b+sleep(.01) from t2;
325
UPDATE t1 SET a=a+sleep(.01) WHERE a>2;
326
UPDATE t1 SET a=a+sleep(.01) ORDER BY a DESC;
327
UPDATE t2 set b=b+sleep(.01) limit 1;
328
UPDATE t1 SET a=a+sleep(.01) WHERE a in (SELECT b from t2);
329
DELETE FROM t1 WHERE a=a+sleep(.01) ORDER BY a LIMIT 2;
330
SELECT rows_examined,sql_text FROM mysql.slow_log;
331
rows_examined sql_text
332
0 INSERT INTO t1 VALUES (1+sleep(.01)),(2)
333
2 INSERT INTO t1 SELECT b+sleep(.01) from t2
334
4 UPDATE t1 SET a=a+sleep(.01) WHERE a>2
335
8 UPDATE t1 SET a=a+sleep(.01) ORDER BY a DESC
336
1 UPDATE t2 set b=b+sleep(.01) limit 1
337
4 UPDATE t1 SET a=a+sleep(.01) WHERE a in (SELECT b from t2)
338
6 DELETE FROM t1 WHERE a=a+sleep(.01) ORDER BY a LIMIT 2
340
TRUNCATE TABLE mysql.slow_log;
345
# -- Bug#38124: "general_log_file" variable silently unset when
346
# -- using expression
348
SET GLOBAL general_log_file = DEFAULT;
349
SELECT @@general_log_file INTO @my_glf;
350
SET GLOBAL general_log_file = 'BUG38124.LOG';
351
SELECT @@general_log_file;
354
SET GLOBAL general_log_file = concat('BUG38124-2.LOG');
355
SELECT @@general_log_file;
358
SET GLOBAL general_log_file = substr('BUG38124-2.LOG',3,6);
359
SELECT @@general_log_file;
362
SET GLOBAL general_log_file = DEFAULT;
363
SELECT @@general_log_file = @my_glf;
364
@@general_log_file = @my_glf
366
SET GLOBAL general_log_file = @old_general_log_file;
367
# Close connection con1
368
SET GLOBAL long_query_time = DEFAULT;
369
SET GLOBAL log_output = @old_log_output;
370
SET global general_log = @old_general_log;
371
SET global general_log_file = @old_general_log_file;
372
SET global slow_query_log = @old_slow_query_log;
373
SET global slow_query_log_file = @old_slow_query_log_file;