MySQL Bugs: #64204: ResultSet.close hangs if streaming query is killed

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:	None
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

Description:
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?

An example Java program that demonstrates the socketRead hang

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

A stacktrace of the thread hung on socketRead

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

Sorry, last stacktrace was the wrong stacktrace... This is the proper stacktrace!!

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

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.

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.

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!

Any update?

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.

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

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.

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