Unexpected Result (-1) from MySqlCommand’s Async Method with Cancelled CancellationToken

[rubenfreire]

Software versions
MySqlConnector version: 2.3.7
Server type (MySQL) and version: 8.0.31
.NET version: .NET6

Describe the bug
When I call an Async method from MySqlCommand with a long running query, passing a CancellationToken, it returns -1 after the token is cancelled.

This is possibly caused by some misunderstanding on my part of the documentation and what I'm trying to test.

Code sample

using MySqlConnector;
using System.Diagnostics;

var cancellationTokenSource = new CancellationTokenSource();

var stopWatch = new Stopwatch();
try
{
    await using var connection = new MySqlConnection("Server=localhost;Port=3306;Database=<db>;Uid=<user>;Pwd=<pass>");
    await connection.OpenAsync();

    Console.WriteLine("Executing a long running query...");
    stopWatch.Start();

    using var command = new MySqlCommand("SELECT SLEEP(5);", connection);
    var task = command.ExecuteNonQueryAsync(cancellationTokenSource.Token);

    await Task.Delay(500);
    cancellationTokenSource.Cancel();
    Console.WriteLine($"Cancelling the query after {stopWatch.Elapsed.TotalMilliseconds}ms.");

    var result = await task;

    Console.WriteLine($"Query Completed after {stopWatch.Elapsed.TotalMilliseconds}ms with result: {result}");
}
catch (OperationCanceledException)
{
    stopWatch.Stop();
    Console.WriteLine($"Operation was cancelled as expected after {stopWatch.Elapsed.TotalMilliseconds}ms.");
}
catch (Exception ex)
{
    stopWatch.Stop();
    Console.WriteLine($"An exception occurred after: {stopWatch.Elapsed.TotalMilliseconds}ms. Message: {ex.Message}\nStackTrace: {ex.StackTrace}");
}

Expected behavior
Reading the documentation and checking the unit tests examples, I expected that an OperationCanceledException should be throw after I cancel the ct. The query is canceled but it returns the number of rows (-1 in this case).

Executing a long running query...
Cancelling the query after 745.4734ms.
Operation was cancelled as expected after 753.6106ms.

Actual behavior

Executing a long running query...
Cancelling the query after 745.4734ms.
Query Completed after 753.6106ms with result: -1

If I change the code to cancel the ct after a delay, it works as expected, throwing an OperationCanceledException.

- var cancellationTokenSource = new CancellationTokenSource();
- cancellationTokenSource .Cancel();
+ var cancellationTokenSource = new CancellationTokenSource(1000);

[bgrainger]

This is a side-effect of the "long running query" you're using for testing. SLEEP(n) is an interruptible method and the "soft cancellation" that MySqlConnector uses causes it to be gracefully cancelled.

If you use a "real" query, you'll get the cancellation behaviour you expect, e.g.,

using var command = new MySqlCommand("""
	with ints as (select * from (values row(1), row(2), row(3), row(4), row(5), row(6), row(7), row(8), row(9)) temp)
	select * from ints a join ints b join ints c join ints d join ints e join ints f join ints g join ints h;
	""", connection);

[rubenfreire]

Thanks!

I did the test and it behaves as expected, a OperationCanceledException is thrown.