MySQL Bugs: #15601: SQLCancel does not work to stop a query on the database server

Bug #15601	SQLCancel does not work to stop a query on the database server
Submitted:	8 Dec 2005 21:43	Modified:	14 Dec 2007 16:58
Reporter:	Matt Walton
Status:	Closed
Category:	Connector/ODBC	Severity:	S1 (Critical)
Version:	5.1	OS:	Microsoft Windows (Windows 2000 SP4)
Assigned to:	Jim Winstead	Target Version:	5.1
Triage:	D3 (Medium)

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

file is too large to upload here.  I can send from my gmail account if that works

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)

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.

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

Could you please write here table definition LU_ORDER ?

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

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.

Test case

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

Any idea when this bug might be addressed?
Thanks

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.

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

This won't be added to the 3.51 series, but is currently planned for the next major
release.

SQLCancel support is scheduled for Connector/ODBC 5.1.

Preliminary patch to add SQLCancel() support

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

FYI, this only works on Windows if built against 5.0.52 client library or later

SQLCancel support has been committed to the repository, and will be in 5.1.1.

A note has been added to the 5.1.1 changelog: 

Implemented support for SQLCancel().