MySQL Bugs: #6760: Request SLEEP() function

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

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.

Turning off showstopper flag.

i need this to write tests.

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

Fixed in 5.0.12.

Documented function in "Miscellaneous Functions" section.
Noted in 5.0.12 changelog.

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

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

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.