MySQL Bugs: #77658: Incorrect java.sql.Statement.cancel() behavior

Bug #77658	Incorrect java.sql.Statement.cancel() behavior
Submitted:	8 Jul 2015 15:16	Modified:	5 Oct 2015 16:13
Reporter:	Sergey Savenko	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.35	OS:	Any
Assigned to:	Filipe Silva	CPU Architecture:	Any

Description:
Running a statement via java.sql.Statement.execute() and then cancelling it via java.sql.Statement.cancel() renders the connection unusable.

Any subsequent attempts to run a query lead to the following error: 
"Streaming result set com.mysql.jdbc.RowDataDynamic@6568d251 is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries."

How to repeat:
1) Run a long running statement via java.sql.Statement.execute(), e.g. "SELECT SLEEP(100)".
2) Call java.sql.Statement.cancel() on the same statement instance from a different thread.
3) Attempt to execute any other query using the same connection.

As far as I know, this is a protocol limitation, if using a streaming query, one won't know it's cancelled, until a row is read and instead one gets an error from the server. Given the race between cancelling, and the application not reading any more rows (dependent), the driver can't blindly read rows looking for the error either.

Hi Sergey,

Thank you for taking the time to write this bug report.
I tried to reproduce it but, as far as I can tell, everything is working fine with the scenario you exposed. Can you please provide some more information about your environment and, ideally, a test case showing the failure you mentioned?

Hi Sergey,

I missed the fact that you are using streaming results. In that case, the observed behavior is exactly as you reported.
Let me analyse it deeper to see if there is anything else we could do about it.

Thank you.

Hi Sergey,

You may be interested in following the related Bug#77987. In the meantime this one is considered as verified as described and should be fixed.

Thanks,

I observe 3 different "flavours" how killing a dynamic resultset query affects subsequent queries execution (2 different errors and 1 workaround)
I'd like to share my test program illustrating these cases.

Case 1) (bad) when we kill a query and then JDBC tries to read data from RS.
This had been fixed by "Fix for Bug#75309 (20272931) mysql connector/J driver in streaming mode will in the blocking state. -- see https://github.com/mysql/mysql-connector-j/commit/3ee21505359e23168919f8bbda5ac36a265bee09 .

Case 2) (bad) when we kill a query and then JDBC tries to close this RS (not reading it anymore). I could reproduce this bug with Connector/J 5.1.40 (latest 5.1).

Case 3) (good) either of #1 or #2 preconditions PLUS the "clobberStreamingResults" connection property set to "true". To my understanding, the clobberStreamingResults=true looks like a good workaround for this bug.

java test program illustrating the bug

Attachment: MySqlKillExecTest.java (application/octet-stream, text), 5.96 KiB.

Results of several runs of MySqlKillExecTest.java, different modes and different connectors

Attachment: MySqlKillExecTest_result.txt (text/plain), 8.85 KiB.

It seems like this is still an issue as of 8.0.21. To reproduce:

1. Open up a streaming result set
2. While it is open, kill the streaming query (e.g. at the database level, or in another thread in your application: `KILL QUERY {pid}`)
3. Close the result set (without reading any more rows). When the result set is closed, `ResultsetRowsStreaming#close` will try and drain any remaining results; since the query has been killed, this will throw an exception once it goes to the server for more results. Notably, this means that this class never calls `this.protocol.unsetStreamingData(this)`.
4. If this connection is then reused, you get the exception indicated in the initial report: "Streaming result set com.mysql.jdbc.RowDataDynamic@6568d251 is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries."

I'm not familiar enough with this library to know what we would expect the state of a connection to be in after a connection has been killed (although it seems like that is how statements are normally cancelled and shouldn't break anything). Perhaps the solution is better cleanup in `ResultsetRowsStreaming#close` to make sure that the connection internal state is correctly cleaned up/reset even if there is an exception reading the remaining rows.

If it would help, I can provide a minimal implementation that demonstrates this issue (although I suspect the one that has already been provided does the trick!).