MySQL Bugs: #86010: SLEEP command does not happen on subsequent UPDATEs

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

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.

Demonstration Program

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

I am using MySQL Server 5.6.34

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.

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...

Posted by developer:
 
This issue is no longer happening. Verified using Connector/NET v8.0.21.