MySQL Bugs: #43760: Connector/NET times out but acts as if query completed

Bug #43760	Connector/NET times out but acts as if query completed
Submitted:	19 Mar 2009 20:38	Modified:	7 May 2009 10:00
Reporter:	d di (Basic Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	5.0.67	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
Some queries produce the message (worded "Fatal error encountered during command execution") when a Connector/NET timeout happens, others don't and simply return as if the query had completed.

The latter is a problem when user code expects a non-error return to mean that the query has succeeded.

How to repeat:
Run attached test case after replacing <server>, <user> and <password> with actual values.

test case.  Expected output: "Exception: (timeout msg)".  Actual output: no errors.

Attachment: issue43760.cs (application/octet-stream, text), 1.45 KiB.

Hi again and thanks for your report.

It can be reproduced with same test case as for Bug#43761 by adding one more query after "SELECT SLEEP" so it's actually same bug.

I changed your code to execute faster:
    cmd.CommandTimeout = 10;
    cmd.CommandText = "SELECT SLEEP(60); DROP TABLE IF EXISTS testbug43760";
I have also added "allow batch=True" for sake of completeness.

In my case all seems to be going on well:
090320 17:43:59    2 Connect	root@QCW2K8.lan on test
	    2 Query	SHOW VARIABLES
	    2 Query	SHOW COLLATION
	    2 Query	SET character_set_results=NULL
	    2 Init DB	test
	    2 Query	SELECT SLEEP(60); DROP TABLE IF EXISTS testbug43760

and 10 seconds later:

090320 17:44:09    3 Connect	root@QCW2K8.lan on test
	    3 Query	SHOW VARIABLES
	    3 Query	SHOW COLLATION
	    3 Query	SET character_set_results=NULL
	    3 Init DB	test
	    3 Query	KILL QUERY 2
	    3 Quit	

but the program never returns. Same as in Bug#43761.

Hm?  Not the same bug.  This one is about specific MySQL commands causing the driver to return OK instead of FAIL when a timeout does occur.

43761 is about the when the time out does /not/ occur.  Eg the driver failing to time out.  Different issue..

I think the bottom of this issue is that you haven't tested how KILL QUERY affects all SQL queries.

To further expand on that notion, here's another test case that uses the CLI instead of Connector/NET:

=========
1. Start two instances ("A" and "B") of the mysql cli.
2. As a preparation, in cli B, do a "show processlist" and find the cli A connection id.
3. In cli A, perform a query that takes a long time - for example (expand as necessary):
SELECT * FROM mysql.user a, mysql.user b, mysql.user c, mysql.user d, mysql.user e, mysql.user f, mysql.user g, mysql.user h;
4. In cli B, kill the query before it finishes with KILL QUERY <...>.
5. Notice the output.
6. Try another query, this time using SLEEP (as in the Connector/NET example):
SELECT SLEEP(600); TRUNCATE TABLE test.test;
7. Kill the query again from B.
8. Notice the output.
=========

Here's a copy/paste of the output from the above recipe:

=========
mysql> ### step 3-5 ###
mysql> SELECT * FROM mysql.user a, mysql.user b, mysql.user c, mysql.user d, mysql.user e, mysql.user f, mysql.user g;
ERROR 1317 (70100): Query execution was interrupted

mysql> ### step 6-8 ###
mysql> SELECT SLEEP(600); TRUNCATE TABLE test.test;
+------------+
| SLEEP(600) |
+------------+
|          1 |
+------------+
1 row in set (3.07 sec)
=========

Of interest is that the client realizes that the query got cancelled in the first case, but in the second case the client happily reports that the sleep command returned a result set containing a single field value, "1".

* "affects all SQL queries" ==> "affects all SQL commands"

As I said, problem regarding *c/NET* is same as in Bug#43761 while CLI library problem is reported in Bug#43805 where Sveta will check on it.

What version of Connector/Net does this apply to? The bug lists 5.0.67, but this is a MySQL Server Version no, not a Connector/net version.

I am getting "Fatal error encountered during command execution" and I suspect it's due to a timeout. "Timeout occured during command execution" would be a far more helpful error message.

This bug (see original text) was reported as a server problem, because the client probably does not have any effect on why /no timeout message is produced/ (see original test case).

If you're looking for one of the timeout bugs in the client, see issue #43761:
http://bugs.mysql.com/bug.php?id=43761

(Another unrelated note is about the "fatal ..." message: The connector does not assign error codes to client errors, and in addition wraps all errors which does not have an error code (hint) in the "fatal ..." nonsense.)