1SET @global_slow_query_log = @@global.slow_query_log;
2SET @global_log_output = @@global.log_output;
3SET @@session.long_query_time=1;
4SET @@global.log_output = 'TABLE';
5'----When global.slow_query_log = OFF----'
6SET @@global.slow_query_log = OFF;
7TRUNCATE mysql.slow_log;
8SELECT sleep(2);
9sleep(2)
100
11SELECT count(*) FROM mysql.slow_log;
12count(*)
130
14'----When global.slow_query_log = ON-----'
15SET @@global.slow_query_log = ON;
16TRUNCATE mysql.slow_log;
17SELECT sleep(2);
18sleep(2)
190
20SELECT count(*) > 0 FROM mysql.slow_log;
21count(*) > 0
221
23'----When local.slow_query_log = OFF-----'
24SET @@local.slow_query_log = OFF;
25TRUNCATE mysql.slow_log;
26SELECT sleep(2);
27sleep(2)
280
29SELECT count(*) FROM mysql.slow_log;
30count(*)
310
32SET @@local.slow_query_log = ON;
33'Bug#47905 stored procedures not logged correctly to slow query log'
34TRUNCATE mysql.slow_log;
35CREATE PROCEDURE p_test()
36BEGIN
37select sleep(2);
38select 1;
39END//
40CALL p_test();
41sleep(2)
420
431
441
45SELECT count(*) > 0 FROM mysql.slow_log;
46count(*) > 0
471
48DROP PROCEDURE p_test;
49Bug53191 Lock_time in slow log is negative when logging stored routines
50TRUNCATE mysql.slow_log;
51connect  con2,localhost,root,,;
52connection default;
53CREATE TABLE t1 (c0 INT PRIMARY KEY AUTO_INCREMENT, c1 TIMESTAMP, c2 TIMESTAMP);
54CREATE FUNCTION f_slow_now() RETURNS TIMESTAMP
55BEGIN
56DO SLEEP(2);
57RETURN NOW();
58END//
59CREATE FUNCTION f_slow_current_time() RETURNS TIME
60BEGIN
61DO SLEEP(2);
62RETURN CURRENT_TIME();
63END
64//
65INSERT INTO t1 (c1,c2) VALUES (now(), f_slow_now())//
66CREATE TRIGGER tf_before BEFORE INSERT ON t1
67FOR EACH ROW BEGIN
68SET new.c2 = f_slow_now();
69END//
70CREATE PROCEDURE p1()
71BEGIN
72INSERT INTO t1 (c1,c2) values (now(),now());
73DO SLEEP(2);
74INSERT INTO t1 (c1,c2) values (now(),now());
75end//
76INSERT INTO t1 (c1,c2) VALUES (now(), now());
77CALL p1();
78SELECT c1-c2 FROM t1;
79c1-c2
800
810
820
830
84*** There shouldn't less than 1 s difference between each row
85SELECT t1.c1-self.c1 > 1 FROM t1, t1 as self WHERE t1.c0=self.c0+1 ORDER BY t1.c0;
86t1.c1-self.c1 > 1
871
881
891
90DROP TRIGGER tf_before;
91DROP FUNCTION f_slow_now;
92DROP FUNCTION f_slow_current_time;
93DROP TABLE t1;
94DROP TABLE IF EXISTS t1;
95Warnings:
96Note	1051	Unknown table 'test.t1'
97CREATE TABLE t1(c1 INT) ENGINE=MyISAM;
98DROP PROCEDURE IF EXISTS p1;
99CREATE PROCEDURE p1()
100BEGIN
101INSERT INTO t1 VALUES (1);
102SELECT COUNT(*) FROM t1 WHERE c1= 1;
103UPDATE t1 SET c1=c1*2;
104END|
105connection con2;
106LOCK TABLE t1 WRITE;
107connection default;
108CALL p1();
109Wait three seconds and unlock the table
110connection con2;
111UNLOCK TABLES;
112connection default;
113COUNT(*)
1141
115Slow log:
116**** 1 == we have slow log entries
117SELECT count(*) > 0 FROM mysql.slow_log;
118count(*) > 0
1191
120**** 0 == None of the entries have a lock time greater than 10 s
121SELECT count(*) FROM mysql.slow_log WHERE lock_time > 10;
122count(*)
1230
124disconnect con2;
125DROP TABLE t1;
126DROP PROCEDURE p1;
127SET @@global.log_output = @global_log_output;
128SET @global.slow_query_log = @global_slow_query_log;
129