Debian Codegraph @

mysql-test/suite/sys_vars/t/slow_query_log_func.test

raw
1 # save
2 SET @global_slow_query_log = @@global.slow_query_log;
3 SET @global_log_output = @@global.log_output;
4 ###############################################################################
5 # Begin the functionality testing for slow_query_log #
6 ###############################################################################
7
8 if (`SELECT $CURSOR_PROTOCOL != 0`)
9 {
10 --skip Test temporarily disabled for cursor-protocol
11 }
12
13 SET @@session.long_query_time=1;
14
15 SET @@global.log_output = 'TABLE';
16
17 #=========================================
18 --echo '----When global.log_slow_query = OFF----'
19 #=========================================
20
21 SET @@global.log_slow_query = OFF;
22 TRUNCATE mysql.slow_log;
23 # The sleep is the slow query
24 SELECT sleep(2);
25
26 SELECT count(*) FROM mysql.slow_log;
27
28 #=========================================
29 --echo '----When global.slow_query_log = ON-----'
30 #=========================================
31
32 SET @@global.slow_query_log = ON;
33 TRUNCATE mysql.slow_log;
34 # The sleep is the slow query
35 SELECT sleep(2);
36
37 SELECT count(*) > 0 FROM mysql.slow_log;
38
39 #=========================================
40 --echo '----When local.log_slow_query = OFF-----'
41 #=========================================
42
43 SET @@local.log_slow_query = OFF;
44 TRUNCATE mysql.slow_log;
45 # The sleep is the slow query
46 SELECT sleep(2);
47
48 SELECT count(*) FROM mysql.slow_log;
49 SET @@local.slow_query_log = ON;
50
51 #==========================================================================
52 --echo 'Bug#47905 stored procedures not logged correctly to slow query log'
53 #==========================================================================
54 # assumes logging to table turned on with long_query_time=1 as above
55
56 TRUNCATE mysql.slow_log;
57
58 DELIMITER //;
59 CREATE PROCEDURE p_test()
60 BEGIN
61 select sleep(2);
62 select 1;
63 END//
64 DELIMITER ;//
65
66 CALL p_test();
67 SELECT count(*) > 0 FROM mysql.slow_log;
68 DROP PROCEDURE p_test;
69
70 #==============================================================================
71 --echo Bug53191 Lock_time in slow log is negative when logging stored routines
72 #==============================================================================
73 TRUNCATE mysql.slow_log;
74 connect (con2,localhost,root,,);
75 connection default;
76
77 CREATE TABLE t1 (c0 INT PRIMARY KEY AUTO_INCREMENT, c1 TIMESTAMP, c2 TIMESTAMP);
78 delimiter //;
79 CREATE FUNCTION f_slow_now() RETURNS TIMESTAMP
80 BEGIN
81 DO SLEEP(2);
82 RETURN NOW();
83 END//
84
85 CREATE FUNCTION f_slow_current_time() RETURNS TIME
86 BEGIN
87 DO SLEEP(2);
88 RETURN CURRENT_TIME();
89 END
90 //
91
92 INSERT INTO t1 (c1,c2) VALUES (now(), f_slow_now())//
93
94 CREATE TRIGGER tf_before BEFORE INSERT ON t1
95 FOR EACH ROW BEGIN
96 SET new.c2 = f_slow_now();
97 END//
98
99 CREATE PROCEDURE p1()
100 BEGIN
101 INSERT INTO t1 (c1,c2) values (now(),now());
102 DO SLEEP(2);
103 INSERT INTO t1 (c1,c2) values (now(),now());
104 end//
105
106 delimiter ;//
107
108 INSERT INTO t1 (c1,c2) VALUES (now(), now());
109 CALL p1();
110
111 SELECT c1-c2 FROM t1;
112 --echo *** There shouldn't less than 1 s difference between each row
113 SELECT t1.c1-self.c1 > 1 FROM t1, t1 as self WHERE t1.c0=self.c0+1 ORDER BY t1.c0;
114
115 DROP TRIGGER tf_before;
116 DROP FUNCTION f_slow_now;
117 DROP FUNCTION f_slow_current_time;
118 DROP TABLE t1;
119
120 DROP TABLE IF EXISTS t1;
121 CREATE TABLE t1(c1 INT) ENGINE=MyISAM;
122 DROP PROCEDURE IF EXISTS p1;
123 delimiter |;
124 CREATE PROCEDURE p1()
125 BEGIN
126 INSERT INTO t1 VALUES (1);
127 SELECT COUNT(*) FROM t1 WHERE c1= 1;
128 UPDATE t1 SET c1=c1*2;
129 END|
130 delimiter ;|
131
132 connection con2;
133 LOCK TABLE t1 WRITE;
134
135 connection default;
136 send CALL p1();
137
138 --echo Wait three seconds and unlock the table
139 connection con2;
140 sleep 3;
141 UNLOCK TABLES;
142 connection default;
143 reap;
144 --echo Slow log:
145 --echo **** 1 == we have slow log entries
146 SELECT count(*) > 0 FROM mysql.slow_log;
147 --echo **** 0 == None of the entries have a lock time greater than 10 s
148 SELECT count(*) FROM mysql.slow_log WHERE lock_time > 10;
149 disconnect con2;
150 DROP TABLE t1;
151 DROP PROCEDURE p1;
152
153
154 #================================================================== Restore
155 SET @@global.log_output = @global_log_output;
156 SET @global.slow_query_log = @global_slow_query_log;
157
158 ###############################################################################
159 # End of the functionality test for slow_query_log #
160 ###############################################################################