Bug #6760 Request SLEEP() function
Submitted: 23 Nov 2004 0:05 Modified: 12 Aug 2005 2:57
Reporter: Paul Dubois Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:any OS:
Assigned to: Jim Winstead CPU Architecture:Any
Triage: D5 (Feature request)

[23 Nov 2004 0:05] Paul Dubois
Description:
In scripts that test temporal things such as whether TIMESTAMP
columns update, it would be helpful to have a function that
implements a delay, so that the script can know that the time
has changed.  One can do this with BENCHMARK(), sort of, but
that's subject to limitations: It's subject to processor speed.
A million-iteration BENCHMARK() call on a fast machine might
now actually execute long enough to allow a second to pass,
whereas on a slow machine it might delay a painfully long time.

A SLEEP(n) SQL function to sleep n seconds therefore would be
a nice diagnostic tool.

How to repeat:
See above.
[23 Nov 2004 0:06] Paul Dubois
Turning off showstopper flag.
[12 Aug 2005 1:05] Jim Winstead
i need this to write tests.
[12 Aug 2005 1:57] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28205
[12 Aug 2005 2:33] Jim Winstead
Fixed in 5.0.12.
[12 Aug 2005 2:57] Paul Dubois
Documented function in "Miscellaneous Functions" section.
Noted in 5.0.12 changelog.
[26 Jan 2007 9:14] ILya Kogan
The SLEEP function appears to work incorrectly. It suspends for much longer period than the one I pass as a input parameter. I tested it on localhost queries and in stored procedures and in both cases I got the following results:

select sleep(0.1);
----- 0.63 sec

select sleep(1);
----- 2.23 sec
etc;

This is especially visible for small values of the input parameter, e.g.

select sleep(0.05) 
----- 0.63 sec
[2 Apr 2007 5:01] Ondra Zizka
Mysql itself does not measure the time. I guess that additional delay in your case  would be caused by other element's in the roundtrip, or it was already fixed - works fine for me:

SELECT SLEEP(5); -- takes 5,0002 s in Query Browser
[2 Apr 2007 6:21] Jim Winstead
The original version of SLEEP() also had issues on Microsoft Windows, where the timing could be rather wildly off. This was fixed in 5.0.36 and 5.1.15. See Bug #14094.