Bug #64204 ResultSet.close hangs if streaming query is killed
Submitted: 2 Feb 2012 3:39 Modified: 1 Mar 2013 7:43
Reporter: Bow Ruggeri Email Updates:
Status: Closed Impact on me:
Category:Connector / J Severity:S2 (Serious)
Version:5.1.18 OS:Any
Assigned to: Alexander Soklakov CPU Architecture:Any
Tags: hangs, KILL QUERY, socketRead, Stuck

[2 Feb 2012 3:39] Bow Ruggeri
If in streaming mode and something issues a KILL QUERY on your statement, invoking ResultSet.close will hang on socketRead.

I will attach the stacktrace and example java code to reproduce!

How to repeat:
- Create a new statement in streaming mode.  
- Issue a SELECT against a table that has some rows in it and takes a little while to process (I used sleep to help simulate a longer query)
- Iterate the ResultSet using .next()
- On another connection, KILL QUERY <id> that query that is running
- The ResultSet.next will throw a SQLException with message "Query execution was interrupted"
- Calling ResultSet.close() or Statement.close() will cause the JDBC driver to attempt to close the ResultSet. Part of the process it invokes checkErrorPacket which hangs on socketRead.

Suggested fix:
I would assume the ResultSet is already closed?  So maybe a boolean in the ResultSet to know it's interrupted and not to try and read from the socket?
[2 Feb 2012 3:41] Bow Ruggeri
An example Java program that demonstrates the socketRead hang

Attachment: MySQLBugTest.java (application/octet-stream, text), 5.42 KiB.

[2 Feb 2012 3:42] Bow Ruggeri
A stacktrace of the thread hung on socketRead

Attachment: stacktrace.txt (text/plain), 1.50 KiB.

[2 Feb 2012 3:45] Bow Ruggeri
Sorry, last stacktrace was the wrong stacktrace... This is the proper stacktrace!!

Attachment: stacktracenew.txt (text/plain), 1.28 KiB.

[2 Feb 2012 18:56] Bow Ruggeri
Some more details... 

Interesting enough, if I call statement.cancel() directly, it sometimes wont hang. Looking at the code, it looks like statement.cancel() sets this.wasCancelled to be true. This causes the statement to throw a MySQLStatementCancelledException.

However, he still get's the "Query execution was interrupted", it just happens to occur while he's closing the ResultSet.  This happens in ResultSetImpl when he is doing this.rowData.close().  The rowData (RowDataDynamic) tries to read the rest of the rows before he closes.

However, the statement.cancel() appears to be a race condition.  As in, if the ResultSet.next() is read before the this.wasCancelled is checked, then the "Query execution was interrupted" is thrown and you're hung on ResultSet.close().  But if the wasCancelled is checked first, then you're OK.
[7 Feb 2012 9:25] Tonci Grgin
Hi Bow and thanks for your report.

For now, I'm thinking this could be related to fix for Bug#24721 but will have to investigate.
[8 Feb 2012 20:33] Bow Ruggeri
Hey Tonci,

Thanks for the response!  I looked at 24721 and I think it's a little different.
That bug says there is an NPE being thrown.  In my case, I dont see an NPE. 

I am seeing the ResultSet.close hanging up my thread.

The other issue I brought up was for Statement.cancel -- it sets an extra boolean that (most of the time) prevents the hangup. However, there is a race condition....  If the resultset is read before the boolean is checked, the thread will hang.

Anyways, I really appreciate you taking the time and looking at this bug!
[18 Apr 2012 20:45] Bow Ruggeri
Any update?
[21 Jan 2013 9:37] Alexander Soklakov
Hi Bow,

I just closed BUG#56411 as "Won't fix", but it seems it has the close nature to your bug.

Please check if useReadAheadInput=false solves your problem.
[21 Jan 2013 11:27] Alexander Soklakov
After some tests I can say that neither useReadAheadInput=false helps, nor we can workaround underlying implementation of socket read.  The only way now to avoid hang is to use socketTimeout > 0.  So try to find appropriate balance with socketTimeout. And I'll try to find what can we do with Statement.close().
[1 Mar 2013 7:43] John Russell
Added to changelog for 5.1.24: 

When a statement was accessing a table in streaming mode, terminating
the statement by issuing KILL QUERY from another session could cause
a SQLException "Query execution was interrupted" and a stall when the
original session issued a ResultSet.close() or Statement.close()
method call. This issue resulted from a race condition and so could
happen intermittently.
[24 Feb 2015 17:39] Quartz 12h
This is not fixed. I am using server 5.6.12 and connector/J 5.1.34 (also tried 3.1.10 and 5.1.29) and the KILL QUERY still causes the subsequent close() to hang.

Additionally, we noticed the bug by the hung thread, on a closed system that doesn't even have code to issue a "kill query #". We only "kill #" and it froze like that, although I couldn't reproduce (probably still the race condition).