Kill session in SQLServer has executeQuery continue without any Exception on the client

[mreuvers]

Driver version

8.4.1.jre11

SQL Server version

Microsoft SQL Server 2019 (RTM-CU8-GDR) (KB4583459) - 15.0.4083.2 (X64) \n Nov 2 2020 18:35:09 \n Copyright (C) 2019 Microsoft Corporation\n Developer Edition (64-bit) on Linux (Ubuntu 18.04.5 LTS)

Client Operating System

MacOS Mojave (I expect the issue no matter which OS).

JAVA/JVM version

openjdk version "11.0.5" 2019-10-15
OpenJDK Runtime Environment AdoptOpenJDK (build 11.0.5+10)
OpenJDK 64-Bit Server VM AdoptOpenJDK (build 11.0.5+10, mixed mode)

Table schema

Not relevant, reproduction case attached.

Problem description

Long running query client (preparedStatement.executeQuery()) is waiting for it's ResultSet. In SQLServer find the relevant session id, and kill it using: KILL <IDHERE>

1. Expected behaviour: SQLException at the client
2. Actual behaviour: Continues as if nothing was wrong (no results at least)
3. Error message/stack trace: None, not even warnings.

JDBC trace logs

Not really relevant, but when issuing the KILL sessionid ->
2021-01-27 13:25:53.68 spid70 Process ID 55 was killed by hostname , host process ID 0.

Reproduction code

Maven reproduction case attached. Please read below for full instructions.

Reproduction case

• Install and setup jdk from https://adoptopenjdk.net, pick one of: 11 LTS + HOTSPOT (or OPENJ9)
• Install and setup maven from https://maven.apache.org
• Docker (makes this easier, or you'll have to setup the SQLServer version mentioned above manually and change the test code to match your credentials/url):
  • Install and setup docker
  • Install and setup docker-compose
• Extract the .zip
• Open a shell and "cd" into the docker directory
  • docker-compose up -d (see settings.env for details), it sets up the latest docker SQLServer 19 image and binds to port 1433
• Go back to the root directory again and run: mvn clean package This will run the test (see Main.java for details)

What you'll see is that it prints "Test started..." and then "hangs", at this point the long running query started.

• Go to SQLServer and execute the following query to find the session of this query:

SELECT r.session_id,
       st.TEXT AS batch_text,
       qp.query_plan AS 'XML Plan',
       r.start_time,
       r.status,
       r.total_elapsed_time
FROM sys.dm_exec_requests AS r
inner join sys.databases d on r.database_id=d.database_id
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
     CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE r.database_id = d.database_id
and  d.name='master'
ORDER BY r.cpu_time DESC;

• Then for the correct session id execute: KILL <ID> to kill it.

The maven build will continue as if nothing happened (it will succeed). An SQLException was expected because the user session (and thus the Connection on the client) was killed/aborted.

The current behavior will result in the client application assuming things went fine, while they clearly were not, but the application is unaware of this. This could result in serious problems for applications in general.

To bring down docker and purge the database and it's volumes, go to the docker directory again and execute: docker-compose down -v

I hope this can be fixed in a future release, if you have any questions please reach out.

Thank you for your time.
Martijn

reproduction.zip

[rene-ye]

Hi @mreuvers, I can investigate this behavior with another JDBC driver, but I don't think it's reasonable to expect the behavior desired in the issue. The driver will infinitely wait on its read operation by default, especially if SQL Server doesn't send anything. If the server is silently killing the connection, and sends no messages to the driver when doing so, how does the driver react to this? For customers that actually have this concern, we usually suggest setting some kind of socket/read timeout and handling the exception this way.

Edit: I don't think the reprocode actually consistently reproduces the issue described, it's difficult to kill the connection at the right time during execute query.

[mreuvers]

Hello @rene-ye

Thank you for the quick reply. This test case reproduces all the time (it's an insane query on purpose here that essentially would never return or after a very very long time).

In any case, the client does react immediately on the kill (it falls out of the executeQuery()) ,but without any Exception. Causing the application to just continue as if nothing was wrong. It's exactly this behavior that is problematic that it just continues.

Since the client does react to the kill immediately, it should also be able to throw an Exception instead (I am pretty sure if you'd start using the broken connection you'd get Exceptions as well).

[lilgreenbird]

hi @mreuvers

Thanks we can see the issue with your repro code. I've added this to our backlog to be triaged. Thank you for your contribution.

[mreuvers]

Hello @lilgreenbird

No problem, you're welcome. I hope it helps to solve the issue. :)

[pedorro]

Hi,
We are also experiencing this issue. A long-running query that is killed on the server, is received by the JDBC driver as a successfully completed query, with whatever (incomplete) results have been received so far.

I assume that since it's marked 'Open', it has not yet been resolved in newer driver versions. But is there any indication about when this bug was introduced? Does anyone know if there is a working older version we could use until this is resolved?

[lilgreenbird]

hi @pedorro

I just tried this looks like this is a problem in earlier versions of the driver as well (I went as far back as 6.4.0). Unfortunately we do not have the resources to look into a fix right now this issue is in our backlog it will be triaged along with other issues/features.

[praskutti]

Quick question @pedorro @lilgreenbird : What happens if you SET XACT_ABORT ON before the query?