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
|
# save
SET @global_slow_query_log = @@global.slow_query_log;
SET @global_log_output = @@global.log_output;
###############################################################################
# Begin the functionality testing for slow_query_log #
###############################################################################
SET @@session.long_query_time=1;
SET @@global.log_output = 'TABLE';
#=========================================
--echo '----When global.slow_query_log = OFF----'
#=========================================
SET @@global.slow_query_log = OFF;
TRUNCATE mysql.slow_log;
# The sleep is the slow query
SELECT sleep(2);
SELECT count(*) FROM mysql.slow_log;
#=========================================
--echo '----When global.slow_query_log = ON-----'
#=========================================
SET @@global.slow_query_log = ON;
TRUNCATE mysql.slow_log;
# The sleep is the slow query
SELECT sleep(2);
SELECT count(*) > 0 FROM mysql.slow_log;
#=========================================
--echo '----When local.slow_query_log = OFF-----'
#=========================================
SET @@local.slow_query_log = OFF;
TRUNCATE mysql.slow_log;
# The sleep is the slow query
SELECT sleep(2);
SELECT count(*) FROM mysql.slow_log;
SET @@local.slow_query_log = ON;
#==========================================================================
--echo 'Bug#47905 stored procedures not logged correctly to slow query log'
#==========================================================================
# assumes logging to table turned on with long_query_time=1 as above
TRUNCATE mysql.slow_log;
DELIMITER //;
CREATE PROCEDURE p_test()
BEGIN
select sleep(2);
select 1;
END//
DELIMITER ;//
CALL p_test();
SELECT count(*) > 0 FROM mysql.slow_log;
DROP PROCEDURE p_test;
#==============================================================================
--echo Bug53191 Lock_time in slow log is negative when logging stored routines
#==============================================================================
TRUNCATE mysql.slow_log;
connect (con2,localhost,root,,);
connection default;
CREATE TABLE t1 (c0 INT PRIMARY KEY AUTO_INCREMENT, c1 TIMESTAMP, c2 TIMESTAMP);
delimiter //;
CREATE FUNCTION f_slow_now() RETURNS TIMESTAMP
BEGIN
DO SLEEP(2);
RETURN NOW();
END//
CREATE FUNCTION f_slow_current_time() RETURNS TIME
BEGIN
DO SLEEP(2);
RETURN CURRENT_TIME();
END
//
INSERT INTO t1 (c1,c2) VALUES (now(), f_slow_now())//
CREATE TRIGGER tf_before BEFORE INSERT ON t1
FOR EACH ROW BEGIN
SET new.c2 = f_slow_now();
END//
CREATE PROCEDURE p1()
BEGIN
INSERT INTO t1 (c1,c2) values (now(),now());
DO SLEEP(2);
INSERT INTO t1 (c1,c2) values (now(),now());
end//
delimiter ;//
INSERT INTO t1 (c1,c2) VALUES (now(), now());
CALL p1();
SELECT c1-c2 FROM t1;
--echo *** There shouldn't less than 1 s difference between each row
SELECT t1.c1-self.c1 > 1 FROM t1, t1 as self WHERE t1.c0=self.c0+1 ORDER BY t1.c0;
DROP TRIGGER tf_before;
DROP FUNCTION f_slow_now;
DROP FUNCTION f_slow_current_time;
DROP TABLE t1;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(c1 INT) ENGINE=MyISAM;
DROP PROCEDURE IF EXISTS p1;
delimiter |;
CREATE PROCEDURE p1()
BEGIN
INSERT INTO t1 VALUES (1);
SELECT COUNT(*) FROM t1 WHERE c1= 1;
UPDATE t1 SET c1=c1*2;
END|
delimiter ;|
--echo Connection 2
connection con2;
LOCK TABLE t1 WRITE;
--echo Back to default connection
connection default;
send CALL p1();
--echo Wait three seconds and unlock the table
connection con2;
sleep 3;
UNLOCK TABLES;
connection default;
reap;
--echo Slow log:
--echo **** 1 == we have slow log entries
SELECT count(*) > 0 FROM mysql.slow_log;
--echo **** 0 == None of the entries have a lock time greater than 10 s
SELECT count(*) FROM mysql.slow_log WHERE lock_time > 10;
disconnect con2;
DROP TABLE t1;
DROP PROCEDURE p1;
#================================================================== Restore
SET @@global.log_output = @global_log_output;
SET @global.slow_query_log = @global_slow_query_log;
###############################################################################
# End of the functionality test for slow_query_log #
###############################################################################
|
