Debian Codegraph @

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

raw
1 --source include/force_myisam_default.inc
2 --source include/have_myisam.inc
3 # save
4 SET @global_slow_query_log = @@global.slow_query_log;
5 SET @global_log_output = @@global.log_output;
6 ###############################################################################
7 # Begin the functionality testing for slow_query_log #
8 ###############################################################################
9
10 SET @@session.long_query_time=1;
11
12 SET @@global.log_output = 'TABLE';
13
14 #==============================================================================
15 --echo Bug53191 Lock_time in slow log is negative when logging stored routines
16 #==============================================================================
17 TRUNCATE mysql.slow_log;
18 connect (con2,localhost,root,,);
19 connection default;
20
21 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
22
23 CREATE TABLE t1 (c0 INT PRIMARY KEY AUTO_INCREMENT, c1 TIMESTAMP, c2 TIMESTAMP);
24 delimiter //;
25 CREATE FUNCTION f_slow_now() RETURNS TIMESTAMP
26 BEGIN
27 DO SLEEP(2);
28 RETURN NOW();
29 END//
30
31 CREATE FUNCTION f_slow_current_time() RETURNS TIME
32 BEGIN
33 DO SLEEP(2);
34 RETURN CURRENT_TIME();
35 END
36 //
37
38 INSERT INTO t1 (c1,c2) VALUES (now(), f_slow_now())//
39
40 CREATE TRIGGER tf_before BEFORE INSERT ON t1
41 FOR EACH ROW BEGIN
42 SET new.c2 = f_slow_now();
43 END//
44
45 CREATE PROCEDURE p1()
46 BEGIN
47 INSERT INTO t1 (c1,c2) values (now(),now());
48 DO SLEEP(2);
49 INSERT INTO t1 (c1,c2) values (now(),now());
50 end//
51
52 delimiter ;//
53
54 INSERT INTO t1 (c1,c2) VALUES (now(), now());
55 CALL p1();
56
57 SELECT c1-c2 FROM t1;
58 --echo *** There shouldn't less than 1 s difference between each row
59 SELECT t1.c1-self.c1 > 1 FROM t1, t1 as self WHERE t1.c0=self.c0+1 ORDER BY t1.c0;
60
61 DROP TRIGGER tf_before;
62 DROP FUNCTION f_slow_now;
63 DROP FUNCTION f_slow_current_time;
64 DROP TABLE t1;
65
66 DROP TABLE IF EXISTS t1;
67 CREATE TABLE t1(c1 INT) ENGINE=MyISAM;
68 DROP PROCEDURE IF EXISTS p1;
69 delimiter |;
70 CREATE PROCEDURE p1()
71 BEGIN
72 INSERT INTO t1 VALUES (1);
73 SELECT COUNT(*) FROM t1 WHERE c1= 1;
74 UPDATE t1 SET c1=c1*2;
75 END|
76 delimiter ;|
77
78 --echo Connection 2
79 connection con2;
80 LOCK TABLE t1 WRITE;
81
82 --echo Back to default connection
83 connection default;
84 send CALL p1();
85
86 --echo Wait three seconds and unlock the table
87 connection con2;
88 sleep 3;
89 UNLOCK TABLES;
90 connection default;
91 reap;
92 --echo Slow log:
93 --echo **** 1 == we have slow log entries
94 SELECT count(*) > 0 FROM mysql.slow_log;
95 --echo **** 0 == None of the entries have a lock time greater than 10 s
96 SELECT count(*) FROM mysql.slow_log WHERE lock_time > 10;
97 disconnect con2;
98 DROP TABLE t1;
99 DROP PROCEDURE p1;
100
101 SET sql_mode = default;
102
103
104 #================================================================== Restore
105 SET @@global.log_output = @global_log_output;
106 SET @global.slow_query_log = @global_slow_query_log;
107 TRUNCATE TABLE mysql.general_log;
108 TRUNCATE TABLE mysql.slow_log;
109 ###############################################################################
110 # End of the functionality test for slow_query_log #
111 ###############################################################################
112