Bug #86010 SLEEP command does not happen on subsequent UPDATEs
Submitted: 19 Apr 2017 18:25 Modified: 21 Apr 2017 9:53
Reporter: Matt Houser Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.9.9 OS:Any
Assigned to: CPU Architecture:Any
Tags: timeout

[19 Apr 2017 18:25] Matt Houser
Description:
Referencing the following UPDATE command:

SET @sl = SLEEP (45);
UPDATE `TestTable`
SET
    `Value` = @Value,
    `Value2` = @sl
WHERE
    `Id` = @Id;
SELECT row_count() AS RowCount;

When executed twice in subsequently,

(a) the first execution times out (expected) due to the 45 second wait and 30 second default command timeout,
(b) the second execution executes in less than 1 second.

Two issues occur:

(a) The SLEEP command is ignored on the second execution, and
(b) The value of 1 is inserted into the `Value2` column of the database, whereas SLEEP(45) should return 0 if it executed without interruption.

Accoring to the documentation, SLEEP (45) will return 1 if it's execution is interrupted.

https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_sleep

If the first execution does not timeout (by reducing sleep to 25), then the second SLEEP command executes normally.

How to repeat:
Table:

CREATE TABLE `TestTable` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Value` varchar(45) DEFAULT NULL,
  `Value2` int(11) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB;

Execute the attached program. The following is the output:

Inserting value: f30f0195-382c-44b3-996e-3f22c950f101
Updating value: b7c8e873-6f1d-4bc4-b324-bbd3ee378d5e
Exception: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Elapsed Time: 00:00:45.1431869
Current values: b7c8e873-6f1d-4bc4-b324-bbd3ee378d5e,0
Updating value again: 97267ab5-3326-4f23-8a40-e48cd02234a3
Elapsed Time: 00:00:00.0038702
Current values: 97267ab5-3326-4f23-8a40-e48cd02234a3,1
Press Enter to continue...

Suggested fix:
SLEEP should not be interrupted.
[19 Apr 2017 18:25] Matt Houser
Demonstration Program

Attachment: Program.cs (text/plain), 4.23 KiB.

[19 Apr 2017 18:41] Matt Houser
I am using MySQL Server 5.6.34
[21 Apr 2017 9:53] Chiranjeevi Battula
Hello  Matt Houser,

Thank you for the bug report and test case.
Verified this behavior on Visual Studio 2013 (C#.Net) and Connector/NET 6.9.9 version.

Thanks,
Chiranjeevi.
[21 Apr 2017 9:54] Chiranjeevi Battula
Inserting value: f507f827-154a-48dd-8210-77ab25650565
Updating value: f1f5a023-b6bb-4628-b52d-cc96afbe0593
Exception: Timeout expired.  The timeout period elapsed prior to completion of 
the operation or the server is not responding.
Elapsed Time: 00:00:45.3995265
Current values: f1f5a023-b6bb-4628-b52d-cc96afbe0593,0
Updating value again: 55151514-afe7-4aef-a8de-2e5dafc3c026
Elapsed Time: 00:00:00.0463493
Current values: 55151514-afe7-4aef-a8de-2e5dafc3c026,1
Press Enter to continue...