Bug #15601 SQLCancel does not work to stop a query on the database server
Submitted: 8 Dec 2005 20:43 Modified: 14 Dec 2007 15:58
Reporter: Matt Walton Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:5.1 OS:Windows (Windows 2000 SP4)
Assigned to: Jim Winstead CPU Architecture:Any

[8 Dec 2005 20:43] Matt Walton
Description:
If I try to cancel an executing SQL statement via ODBC using the SQLCancel call I see that the query still executes on the server side.  Since the driver also does not support Query Timeout the SQL will run until I cancel on the server side.  This would not be an option for a client user.  Is there a way to implement SQLCancel with the MySQL ODBC Driver?

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqlcancel.as...

How to repeat:
Steps to reproduce:

use Windows MySQL 5.0 and Windows ODBC Driver 3.51.12.00

You'll need an application that calls SQLCancel (like QTODBC test tool found at http://gpoulose.home.att.net/)

After you make a connection with the tool you need to execute an SQL statement that will run for some time (I will attach a sample table that you can use).  See below for example based on the table

First create a table to insert into:

CREATE TABLE ODBCAPITEST5 (COLA INT)

Next, execute the SQL that will run for a long time:

Insert into ODBCAPITEST5 Select MAX(A.ORDER_ID) from LU_ORDER A, LU_ORDER C, LU_ORDER B, LU_ORDER D, LU_ORDER E, LU_ORDER F, LU_ORDER G WHERE A.CUSTOMER_ID<>B.CUSTOMER_ID AND A.CUSTOMER_ID<>C.CUSTOMER_ID AND D.CUSTOMER_ID=C.CUSTOMER_ID AND B.CUSTOMER_ID<>E.CUSTOMER_ID AND A.CUSTOMER_ID<>E.CUSTOMER_ID AND F.CUSTOMER_ID<>G.CUSTOMER_ID

Suggested fix:
support for SQL Cancel with the MySQL ODBC driver
[8 Dec 2005 20:50] Matt Walton
file is too large to upload here.  I can send from my gmail account if that works
[8 Dec 2005 21:09] Matt Walton
You can just run any statement that will execute on the server for a relatively long time.  Sorry that I cannot upload the table since it exceeds the limitation of 200 KB (mine is 1400KB zip file)
[9 Dec 2005 8:08] Valeriy Kravchuk
Thank you for a problem report. Can you, please, try to create a trace file for the ODBC data source used and send a part of it that will include the "long running" statement and SQLCancel call. I hope, that this file can be compressed to something less that 200K in size. Moreover, tracing will slow down execution and you should be able to repeat the problem with smaller data set.
[9 Dec 2005 16:21] Matt Walton
Thanks for the follow-up.  I'm going to include a smaller table that will still work.  If you can import and then use the following SQL along with the QTODBC or similar tool that allows you to call SQLCancel (stop button typically).  I think you can do from Microsoft's ODBC Test tool if you use Asynchronous connectivity since synchronous will lock up the tool until the query completes.

CREATE TABLE ODBCAPITEST5 (COLA INT)

Insert into ODBCAPITEST5 Select MAX(A.INCOME_ID) from LU_CUSTOMER A, LU_CUSTOMER C, LU_CUSTOMER B, LU_CUSTOMER D, LU_CUSTOMER E, LU_CUSTOMER F, LU_CUSTOMER G WHERE A.CUSTOMER_ID<>B.CUSTOMER_ID AND A.CUSTOMER_ID<>C.CUSTOMER_ID AND D.CUSTOMER_ID=C.CUSTOMER_ID AND B.CUSTOMER_ID<>E.CUSTOMER_ID AND A.CUSTOMER_ID<>E.CUSTOMER_ID AND F.CUSTOMER_ID<>G.CUSTOMER_ID
[9 Dec 2005 19:31] Vasily Kishkin
Could you please write here table definition LU_ORDER ?
[12 Dec 2005 21:31] Matt Walton
Sorry for the late response.

CREATE TABLE lu_customer (
CUSTOMER_ID	DECIMAL(10, 0),
CUST_FIRST_NAME	VARCHAR(50),
CUST_LAST_NAME	VARCHAR(50),
CUST_BIRTHDATE	DATE,
ADDRESS	VARCHAR(50),
INCOME_ID	DECIMAL(10, 0),
EMAIL	VARCHAR(50),
CUST_CITY_ID	DECIMAL(10, 0)
)
[14 Dec 2005 10:52] Vasily Kishkin
Thanks for table definition and data. I was able to reproduce the bug. SQLCancel() really does't stop sql query. My test case is attached.
[14 Dec 2005 10:52] Vasily Kishkin
Test case

Attachment: test.c (text/plain), 3.93 KiB.

[8 Feb 2006 15:31] Matt Walton
Any idea when this bug might be addressed?
Thanks
[11 Jul 2007 15:53] Matt Walton
This still happens with 3.51.16 so as it stands now there is no support for SQLCancel with MySQL ODBC.  Combine this with a lack of support for Query Timeout and you have a driver that leaves long running queries to be cancelled by the administrator even if the user wishes to cancel.
[12 Jul 2007 21:17] Richard Wesley
> Combine this with a lack of support for
> Query Timeout and you have a driver that leaves long running queries to
> be cancelled by the administrator even if the user wishes to cancel.

We have resorted to spinning up another connection with the same credentials and issuing a KILL on the original connection ID.  Gross, but it works.
[30 Jul 2007 18:59] Jim Winstead
This won't be added to the 3.51 series, but is currently planned for the next major release.
[13 Sep 2007 17:57] Jim Winstead
SQLCancel support is scheduled for Connector/ODBC 5.1.
[8 Nov 2007 0:08] Jim Winstead
Preliminary patch to add SQLCancel() support

Attachment: bug15601.patch (text/plain), 6.44 KiB.

[3 Dec 2007 18:11] Jess Balint
FYI, this only works on Windows if built against 5.0.52 client library or later
[3 Dec 2007 18:33] Jim Winstead
SQLCancel support has been committed to the repository, and will be in 5.1.1.
[14 Dec 2007 15:58] MC Brown
A note has been added to the 5.1.1 changelog: 

Implemented support for SQLCancel().