# 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 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 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 '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 # ###############################################################################