Vscode-mssql: MS-SQL Extension, unable to cancel query.

[msard-oz]

• MSSQL Extension Version:
• VSCode Version:
• OS Version:

Steps to Reproduce:

1. Select language SQL.
2. Start a long running SELECT (in my case it was a JOIN).
3. Try to cancel the query with the provided "Cancel query" command.

Result:
The query doesn't seem to get cancelled.
In some cases partial results are displayed, but the "Messages" pane still shows the query as running.
There is no way to re-start the query, until Visual Studio Code is re-started.

[kburtram]

@benrr101 could you please try to repro this issue?

[kstolte]

This issue is still happening as of v 1.13.0. Same steps to reproduce.

[kburtram]

@benrr101 did you have a chance to try to repro this issue yet? thanks!

[benrr101]

This must've slipped off my list of priorities. Looking at this on Windows, I was able to repro the scenario, albeit with a bit of differences

The query I ran was

SELECT *
FROM sys.all_columns AS a
CROSS JOIN sys.objects AS b
CROSS JOIN sys.objects AS c

which usually generates several gigs worth of data, and will take a very long time.

If I start the query and leave the focus on the SQL file tab, I will get the query to stop executing properly. This will return a partial result set and (in my tests) will always show that the query was cancelled by user. Occasionally I seem to get the "Cannot cancel query..." message. This may indicate a race condition, but I'm not entirely sure.

If I start the query and change focus to the results tab, I get the error message "Cannot cancel query as no query is running." The query continues merrily, but I can change focus back to the SQL file tab and cancel the query. This is definitely a bug that we need to address.

@kstolteYDI and @msard-oz does this describe the scenario you're experiencing?

[kstolte]

not quite for me. I was running a three statements first two came back appropriately, the last one took too long to come back so I went to cancel the query. And like the OP, it displays a message saying that the query has been cancelled by the user however after about 5 minutes the 'Executing Query' in the messages tab is still spinning; the bottom status bar displays cancelling query and the indicator next to it is still spinning.

I should probably mention that I am running MacOS.
The behavior happened while I was hitting a database through VPN as well as when I was on the same local network. SQL is 2016 SP1 developer running on Windows 10 Creators.

[kstolte]

If I start the query and change focus to the results tab, I get the error message "Cannot cancel query as no query is running." The query continues merrily, but I can change focus back to the SQL file tab and cancel the query. This is definitely a bug that we need to address.

I was able to reproduce this as well, but this is a separate issue, as this is not the behavior that I was reporting in my comment above.

[msard-oz]

@benrr101, I had the chance to try and reproduced the issue as you are describing it. As long as the results tab is focused the query cannot be cancelled, switching back to the editor tab allows me to cancel it again. It is possible that this was the issue I initially encountered, as I'm unable to reproduce a scenario where the query cannot be cancelled right now.

[benrr101]

@kstolteYDI I think I'm able to repro your scenario, however not very consistently. I've noticed that if I wait a couple minutes before I attempt to cancel the query, it is more likely that this scenario will happen. After enabling the logs and looking at them, I can see that the batch complete notification and query complete notifications aren't being sent in this scenario. Therefore, the extension believes the query is still running even though it has stopped.

To aid in matching your scenario, I've changed my query to:

USE testytest
GO
PRINT 'Something'
GO
Print 'Something else'
GO
SELECT *
FROM sys.all_columns AS a
CROSS JOIN sys.objects AS b
CROSS JOIN sys.objects AS c

I'll continue to look into this and hopefully get a more consistent repro.

[benrr101]

@kstolteYDI I've done some more investigation on this and have tracked down the root cause. The way our SQL Server service cancels queries, it just cancels a cancellation token and trusts that the sqlclient code will properly cancel the query. However, after some debugging, I found out that the service will hang during disposal of the query result reader. This stack overflow post explains that this is due to the sqlclient not actually telling the server to stop sending results after the cancellation token is raised. Instead, the server needs to be told explicitly to stop sending results.

Now that we know the issue, we can fix it, but it's unfortunately too large of a fix to make it into the upcoming update (beginning of next week). Nevertheless, we will get to it next in the stack.

[benrr101]

This issue should be fixed in https://github.com/Microsoft/sqltoolsservice/pull/498
The change should be rolled out in the next release (though I'm not sure the timeframe for that)

[kstolte]

Great Thanks for the followup

From: Benjamin Russell [mailto:[email protected]]
Sent: Monday, October 16, 2017 2:27 PM
To: Microsoft/vscode-mssql [e-mail]
Cc: Keith Stolte [e-mail]; Mention [e-mail]
Subject: Re: [Microsoft/vscode-mssql] MS-SQL Extension, unable to cancel query. (#879)

This issue should be fixed in Microsoft/sqltoolsservice#498https://github.com/Microsoft/sqltoolsservice/pull/498
The change should be rolled out in the next release (though I'm not sure the timeframe for that)

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHubhttps://github.com/Microsoft/vscode-mssql/issues/879#issuecomment-336984272, or mute the threadhttps://github.com/notifications/unsubscribe-auth/ATKRChq0SNP_jircooljZ1rbWmh8bf9Kks5ss5_xgaJpZM4NfW7X.

[tigerhawkvok]

Seems this didn't make it into 1.2.1 (I still see it there, and don't see a fix in patch notes). Still no timeline on this?

[smithaitufe]

Unfortunately, it is not fixed yet. I don't even see how to cancel a running query anymore.
Version: 1.5.0
OS: Linux

[kstolte]

For the time being I’ve been using azure data studio for creating sql scripts and querying the db. It has been working for me pretty well.

[lalessandrorizzo]

any updates on this issue?

[TunerBuilder]

@lalessandrorizzo
does clicking F1 and then writing mssql and selecting cancel query not working for you?


Make sure you have a focus on the SQL script before doing this

[coso-wes]

Lols, here it is March 2020 and this still broken on macOS at least. I tried cancelling in the results window, cancelling in the query window, neither have any effect and I can't re-run the query unless I restart vscode.

[amanuelz91]

For anyone still struggling with this, right click the code editor and click "Command palette". From there, search "Cancel query" which should cause the MS SQL command option to pop up. Click that and it will execute the "cancel query" command causing your current running query to stop. Note it might take some time to finish executing.

[Chimera111x]

Almost mid 2021, and this issue is still occurring on PC