Bug #36332 JDBC statement.cancel() needs to actually cancel the current query
Submitted: 25 Apr 2008 4:31 Modified: 29 Apr 2008 11:16
Reporter: David Van Couvering Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.1.5 OS:MacOS
Assigned to: CPU Architecture:Any
Tags: cancel

[25 Apr 2008 4:31] David Van Couvering 
Description:
Statement.cancel() appears to do nothing.  I am marking this as S2 because there is no workaround except to kill your process, and a long-running query can therefore lock up your client program.

I've worked in client libraries, I know how hard it is to detect a cancel on a thread listening on a network, but this really needs to be solved.

How to repeat:
Do a select against a table with say 20 million records, and then while it's chugging along, issue a statement.cancel() on another thread.  No-op.
[25 Apr 2008 6:13] Tonci Grgin 
Hi David and thanks for your report.

Can you please attach small test case proving your point?
[25 Apr 2008 16:04] Mark Matthews 
Hi David,

The issue as I see it is that the JDBC specification requires that cancel() (or statement timeout expiring) leaves the connection in a useful state. Therefore, the JDBC driver needs to use a mechanism that doesn't clobber the *network* side of the connection.

Therefore, we use "KILL QUERY" (on MySQL-5.0), which does have the interesting properties that it doesn't always return immediately, and sometimes one can wait a *long* time for the cancel, as SELECTs only check if they're killed in very specific places.
[25 Apr 2008 23:24] David Van Couvering 
Hi, Mark, we meet again :)

So if I get this right, the cancel does work, but it can take some time?  I think I did see that behavior, but only once, so I thought it was a fluke. 

If that's really how it behaves, then please feel free to close this issue.

I think I remember seeing a request in the JDBC EG to have a "force cancel" method to allow you to cancel with no holds barred.  Is that still in play?
[29 Apr 2008 11:16] Tonci Grgin 
Hi David. Closing the report as you requested. I have notified Mark of your question.