Bug #14094 SLEEP() and Windows seems to be a bad combination.
Submitted: 18 Oct 2005 0:04 Modified: 23 Jan 2007 20:51
Reporter: Tobias Asplund Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.13-rc/5.0.16 BK source OS:Microsoft Windows (Windows 2003 Server/Windows XP)
Assigned to: Magnus BlÄudd

[18 Oct 2005 0:04] Tobias Asplund
Description:
the SLEEP() function behaves very badly on Windows, it doesn't cut even close to the interval specified (it's within one second from the few results I tried, but some are so far off that it's actually a lot closer to the next interval).

Since this function takes a fractional part in microseconds too, it should be more reliable, since it can be +- 1 second, it wouldn't really make sense to specify any fraction of a second.

mysql> SELECT SLEEP(5);
+----------+
| SLEEP(5) |
+----------+
|        0 |
+----------+
1 row in set (5.67 sec)

mysql> SELECT SLEEP(1);
+----------+
| SLEEP(1) |
+----------+
|        0 |
+----------+
1 row in set (1.34 sec)

mysql> SELECT SLEEP(1);
+----------+
| SLEEP(1) |
+----------+
|        0 |
+----------+
1 row in set (2.00 sec)

mysql> SELECT SLEEP(1);
+----------+
| SLEEP(1) |
+----------+
|        0 |
+----------+
1 row in set (1.46 sec)

How to repeat:
SELECT SLEEP(1);
SELECT SLEEP(1);
SELECT SLEEP(1);
SELECT SLEEP(1);
[18 Oct 2005 0:13] Miguel Solorzano
c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.16-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT SLEEP(1);
+----------+
| SLEEP(1) |
+----------+
|        0 |
+----------+
1 row in set (1.23 sec)

mysql> SELECT SLEEP(1);
+----------+
| SLEEP(1) |
+----------+
|        0 |
+----------+
1 row in set (1.44 sec)

mysql> SELECT SLEEP(1);
+----------+
| SLEEP(1) |
+----------+
|        0 |
+----------+
1 row in set (1.88 sec)

mysql> SELECT SLEEP(1);
+----------+
| SLEEP(1) |
+----------+
|        0 |
+----------+
1 row in set (1.77 sec)

miguel@hegel:~/dbs/5.0> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.16-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT SLEEP(1);
+----------+
| SLEEP(1) |
+----------+
|        0 |
+----------+
1 row in set (1.00 sec)

mysql> SELECT SLEEP(1);
+----------+
| SLEEP(1) |
+----------+
|        0 |
+----------+
1 row in set (1.01 sec)

mysql> SELECT SLEEP(1);
+----------+
| SLEEP(1) |
+----------+
|        0 |
+----------+
1 row in set (1.00 sec)

mysql> SELECT SLEEP(1);
+----------+
| SLEEP(1) |
+----------+
|        0 |
+----------+
1 row in set (1.00 sec)
[22 Oct 2005 0:56] 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/31296
[1 Dec 2005 14:41] Luis A S Junior Camargo
I have similar problem, but my interval has increased by 1 minute, see:

mysql> select sleep(1);
+----------+
| sleep(1) |
+----------+
|        0 |
+----------+
1 row in set (1 min 2.37 sec)

mysql> select sleep(1);
+----------+
| sleep(1) |
+----------+
|        0 |
+----------+
1 row in set (1 min 2.06 sec)

mysql> select sleep(2);
+----------+
| sleep(2) |
+----------+
|        0 |
+----------+
1 row in set (1 min 2.93 sec)

mysql> select sleep(2);
+----------+
| sleep(2) |
+----------+
|        0 |
+----------+
1 row in set (1 min 3.03 sec)

mysql> select sleep(3);
+----------+
| sleep(3) |
+----------+
|        0 |
+----------+
1 row in set (1 min 4.11 sec)

mysql> select sleep(3);
+----------+
| sleep(3) |
+----------+
|        0 |
+----------+
1 row in set (1 min 4.01 sec)

mysql> select sleep(4);
+----------+
| sleep(4) |
+----------+
|        0 |
+----------+
1 row in set (1 min 4.67 sec)

mysql> select sleep(4);
+----------+
| sleep(4) |
+----------+
|        0 |
+----------+
1 row in set (1 min 4.92 sec)

mysql> select sleep(30);
+-----------+
| sleep(30) |
+-----------+
|         0 |
+-----------+
1 row in set (1 min 31.30 sec)

mysql> select sleep(30);
+-----------+
| sleep(30) |
+-----------+
|         0 |
+-----------+
1 row in set (1 min 31.40 sec)
[14 Dec 2005 20:11] Jim Winstead
Elliot noted that this patch means my_getsystime() will get called more than it already is, introducing a possible initialization issue explained in the comment for that function.
[16 Dec 2005 17:01] Jim Winstead
Ramil also provided this feedback:

A question: why did you use "long timeout" but "timeout= (longlong)..."         
typecast                                                                        
(and WaitForSingleObject()' second parameter declared as DWORD)?                
                                                                                
Please correct indentation:                                                     
timeout= (longlong) abstime->tv_sec * 1000L + abstime->tv_nsec / 1000000L -     
         curtime / 10000L;
[20 Nov 2006 13:19] Sergei Golubchik
This will be probably fixed by a fix for Bug#17635
[23 Jan 2007 20:51] Paul Dubois
Noted in 5.0.36, 5.1.15 changelogs.

On Windows, the SLEEP() function could sleep too long, especially
after a change to the system clock.