Syntax error in SingleStore in an async operation when CancellationToken is cancelled

[rubenfreire]

Software versions MySqlConnector version: 2.3.7 Server type (SingleStore) and version: 8.7.5 .NET version: .NET6

Describe the bug When I cancel the token in an Async call, it throws an exception if i'm using SingleStore. If I change the connection string to MySql, it works as expected.

Exception You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?MySqlConnector?Sleep' at line 1

at MySqlConnector.Core.ServerSession.ReceiveReplyAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in //src/MySqlConnector/Core/ServerSession.cs:line 885 at MySqlConnector.Core.ServerSession.FinishQuerying() in //src/MySqlConnector/Core/ServerSession.cs:line 321 at MySqlConnector.MySqlConnection.FinishQuerying(Boolean hasWarnings) in //src/MySqlConnector/MySqlConnection.cs:line 1016 at MySqlConnector.MySqlDataReader.DisposeAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in //src/MySqlConnector/MySqlDataReader.cs:line 635 at MySqlConnector.MySqlDataReader.Dispose(Boolean disposing) in /_/src/MySqlConnector/MySqlDataReader.cs:line 437 at System.Data.Common.DbDataReader.Dispose()

Code sample

using MySqlConnector;

var cancellationTokenSource = new CancellationTokenSource();

try
{
    await using var connection = new MySqlConnection("Server=localhost;Port=3308;Database=<DB>;Uid=<User>;Pwd=<Pass>;ConnectionReset=false;");
    await connection.OpenAsync();

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

    cancellationTokenSource.Cancel();

    using var reader = await task;
    while (await reader.ReadAsync(cancellationTokenSource.Token))
    {
        Console.WriteLine("Reader has returned. Query Completed.");
    }
}
catch (Exception ex)
{
    Console.WriteLine($"An exception occurred. Message: {ex.Message}\nStackTrace: {ex.StackTrace}");
}

Expected behavior That it throws an OperationCanceledException as it does if using MySql

The operation was canceled. at System.Threading.CancellationToken.ThrowOperationCanceledException() at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in //src/MySqlConnector/Core/CommandExecutor.cs:line 15 at MySqlConnector.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in //src/MySqlConnector/MySqlCommand.cs:line 357

[bgrainger]

Memsql forked this package to https://www.nuget.org/packages/SingleStoreConnector (https://github.com/memsql/SingleStoreNETConnector/) and added some changes that have not been upstreamed here. In particular, it looks like a fix for this particular issue was added here: https://github.com/memsql/SingleStoreNETConnector/commit/947fe458c5172e07ed12728928ff8da3d8513e63.

While SingleStore claims to be highly compatible with MySQL, it has some differences that may make it difficult to use the same client library against both MySQL and SingleStore. For now, I think I would recommend switching to their client library when using SingleStore for maximum compatibility.

[rubenfreire]

I've tested with SingleStoreConnector and this issue is resolved.

Thanks