The cancellation token does not cancel the queue on ClickHouse.

[snaketbssk]

Good day!

I have an issue with the cancellation token when I throw cancel, an HTTP connection is interrupted, and a request is canceled, but the query is executing continues on ClickHouse.

For example:

string query = "";
string queryId = Guid.NewGuid().ToString();
TimeSpan timeout = TimeSpan.FromSeconds(5);

using CancellationTokenSource cancellationTokenSource = new CancellationTokenSource();
CancellationToken cancellationToken = cancellationTokenSource.Token;

try
{
    using ClickHouseConnection connection = new ClickHouseConnection(connectionString);
    await connection.OpenAsync(cancellationToken);
    using ClickHouseCommand command = connection.CreateCommand();
    command.CommandText = query;
    command.QueryId = queryId;
    _ = Task.Run(async () => await CancelAsync(cancellationTokenSource));
    int response = await command.ExecuteNonQueryAsync(cancellationToken);
}
catch (OperationCanceledException)
{
    Console.WriteLine("The query is canceled! The id of the query: {0}.", queryId);
}
catch (Exception exception)
{
    Console.WriteLine("The execution of the query threw an exception! The message of the exception: {0}", exception.Message);
}

async Task CancelAsync(CancellationTokenSource cancellationTokenSource)
{
    await Task.Delay(timeout);
    cancellationTokenSource.Cancel();
}

[snaketbssk]

I wrote this code, but it's not the best solution for me:

using System.Data;
using System.Threading.Tasks;
using ClickHouse.Client;
using ClickHouse.Client.ADO;
using ClickHouse.Client.Utility;

string connectionString = "";
string query = "";
string queryId = Guid.NewGuid().ToString();
TimeSpan timeout = TimeSpan.FromSeconds(5);

using CancellationTokenSource cancellationTokenSource = new CancellationTokenSource();
CancellationToken cancellationToken = cancellationTokenSource.Token;

using ClickHouseConnection connection = new ClickHouseConnection(connectionString);
await connection.OpenAsync(cancellationToken);

Console.WriteLine("The connection string: {0}", connectionString);
Console.WriteLine("The id of the query: {0}", queryId);
Console.WriteLine("The timeout: {0}", timeout);
Console.WriteLine("The query: {0}", query);

try
{
    Console.WriteLine("The query is executing!");

    using ClickHouseCommand command = connection.CreateCommand();
    command.CommandText = query;
    command.QueryId = queryId;
    _ = Task.Run(async () => await CancelAsync(cancellationTokenSource));
    int response = await command.ExecuteNonQueryAsync(cancellationToken);

    Console.WriteLine("The query is executed! The id of the query: {0}, the response: {1}", queryId, response);
}
catch (OperationCanceledException)
{
    using ClickHouseCommand command = connection.CreateCommand();
    command.CommandText = $"KILL QUERY WHERE query_id = '{queryId}'";
    int response = await command.ExecuteNonQueryAsync();

    Console.WriteLine("The query is canceled! The id of the query: {0}, the response: {1}", queryId, response);
}
catch (Exception exception)
{
    Console.WriteLine("The execution of the query threw an exception! The id of the query: {0}, the message of the exception: {1}", queryId, exception.Message);
}

async Task CancelAsync(CancellationTokenSource cancellationTokenSource)
{
    await Task.Delay(timeout);
    cancellationTokenSource.Cancel();
}

[ruslanen]

There is no support for cancelling: https://github.com/DarkWanderer/ClickHouse.Client/discussions/329

Your way is good, I am using same.

[EminemJK]

This method cancels the thread and has nothing to do with clickhouse, which has already executed the command and cannot be terminated or cancelled

[MikeAmputer]

Here is an example of a non-blocking approach using ContinueWith. I hope someone finds it useful.

[DarkWanderer]

Unfortunately I cannot make assumptions about particular way the usage of the client would be structured - e.g. I cannot rely on the fact that the connection object would still exist after the cancel token was triggered, or similar. The solution would be user-specific in each case - one proposed by @MikeAmputer would be great for many users, for example.

I will close this issue as 'wontfix' and move it to the discussion section for further questions