C# : Cancelling MySqlCommand using CancellationToken giving NULLReferenceException

Question

I was trying to cancel a MySqlCommand using a CancellationToken. The query executes successfully when cancellation is not requested.

public async Task<int> ExecuteNonQueryAsync(string connectionString, string query, 
       CancellationToken cancellationToken)
{
    int affectedRowsCount = 0;
    await Task.Run(() =>
    {
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            using (MySqlCommand command = new MySqlCommand())
            {
                connection.Open();
                command.Connection = connection;
                cancellationToken.Register(() => command.Cancel());

                command.CommandText = query;
                command.CommandTimeout = 0;

                affectedRowsCount = command.ExecuteNonQuery();
                connection.Close();
             }
         }
     });

     return affectedRowsCount;
}

But when cancellation is requested it is producing NullReferenceException. Can't figure out what is NULL.

I am calling the above method by

deletedRowsInLastIteration = await 
    mySqlHelperService.ExecuteNonQueryAsync(
       connectionString,
       query, 
       cancellationToken);

if I try

cancellationToken.ThrowIfCancellationRequested();

before calling the ExecuteNonQueryAsync() method, it works. But the cancel of MySqlCommand is not working.

This is the stack trace

System.NullReferenceException HResult=0x80004003 Message=Object reference not set to an instance of an object. Source=MySql.Data
StackTrace: at MySql.Data.MySqlClient.MySqlConnection.CancelQuery(Int32 timeout)
at MySql.Data.MySqlClient.MySqlCommand.Cancel() at ProjectName.Common.MySqlHelperService.<>c__DisplayClass1_1.b__1() in C:\Users\username\source\repos\ProjectName\Applications\ProjectName.Common\MySqlHelperService.cs:line 55 at System.Threading.CancellationToken.ActionToActionObjShunt(Object obj) at System.Threading.CancellationCallbackInfo.ExecutionContextCallback(Object obj) at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.CancellationCallbackInfo.ExecuteCallback() at System.Threading.CancellationTokenSource.CancellationCallbackCoreWork(CancellationCallbackCoreWorkArguments args) at System.Threading.CancellationTokenSource.ExecuteCallbackHandlers(Boolean throwOnFirstException)

Post the full exception text, not just a screenshot of the message. Click on `Copy Details` and paste the text in the question itself. That text contains the stack trace that shows *where* the NRE was thrown and what method were involved. It could be that the command is disposed by the time the cancellation occurs. Or it could be that there's yet another bug in Connector/Net's implementation of MySqlCommand — Panagiotis Kanavos, Dec 19 '19 at 09:55
Why don't you use `ExecuteNonQueryAsync(CancellationToken)` though? Why waste a thread with Task.Run and hack cancellation like this? — Panagiotis Kanavos, Dec 19 '19 at 09:56
This is probably failing because the cancellation token registration (set up by `cancellationToken.Register(() => command.Cancel());`) is never deregistered, so it might try to cancel the command long after it's been disposed. — Bradley Grainger, Dec 19 '19 at 22:02
@BradleyGrainger But this is working fine with SqlCommand. Dont know what's the problem with MySqlCommand — jophab, Dec 20 '19 at 06:00
The problem with MySqlCommand is that it comes from the MySql.Data package, which has a large number of outstanding known bugs. I strongly recommend switching to MySqlConnector, to get all these bug fixes: https://mysqlconnector.net/tutorials/migrating-from-connector-net/#fixed-bugs — Bradley Grainger, Dec 20 '19 at 16:54

score 4 · Accepted Answer · edited Dec 19 '19 at 12:21

4

You shouldn't use Task.Run to convert synchronous methods to asynchronous ones. At best, this wastes a thread just waiting for some IO operation to complete.

MySqlCommand has an ExecuteNonQueryAsync method that accepts a cancellation token. MySqlConnection itself has an OpenAsync method. You should change your code to :

public async Task<int> ExecuteNonQueryAsync(string connectionString, string query, 
       CancellationToken cancellationToken)
{
    using (MySqlConnection connection = new MySqlConnection(connectionString))
    {
        using (MySqlCommand command = new MySqlCommand(query,connection))
        {
            await connection.OpenAsync();
            command.CommandTimeout = 0;

            var affectedRowsCount = await command.ExecuteNonQuery(cancellationToken);
         }
    }

    return affectedRowsCount;
}

edited Dec 19 '19 at 12:21

jophab

5,356
14
41
60

answered Dec 19 '19 at 10:05

Panagiotis Kanavos

120,703
13
188
236

Yes. I will try this out and let you know +1 – jophab Dec 19 '19 at 10:15
1

This won't cancel the query if you're using MySQL Connector/NET (aka MySql.Data) since it doesn't support async methods: https://bugs.mysql.com/bug.php?id=70111 Switch to https://www.nuget.org/packages/MySqlConnector/ instead, which has full async I/O support. – Bradley Grainger Dec 19 '19 at 22:03
@BradleyGrainger looking at the code - it's worse than I remember, if that's possible. BeginXXX ends up starting a new thread but the Async methods *fake* it? They don't even try to use BeginXXX? And CancelQuery doesn't cancel the query. It tries to issue a `KILL QUERY` on another connection – Panagiotis Kanavos Dec 20 '19 at 08:35
1

@jophab it looks like it doesn't work after all. MySQL fakes async execution and the method runs *synchronously*. Even cancellation is faked. `Cancel` executes a `KILL QUERY` on a *new* connection. I keep forgetting just how bad Connector/NET is. You shouldn't use it if possible. If you want real async execution use the MySqlConnector package. Which, btw, doesn't have *sync* methods, and fakes them by blocking on the async methods – Panagiotis Kanavos Dec 20 '19 at 09:00
Yeah I will try it out – jophab Dec 20 '19 at 10:16
"Which, btw, doesn't have sync methods, and fakes them by blocking on the async methods" @PanagiotisKanavos This is not true. The sync methods are truly sync all the way down to the Socket level. Calling `GetAwaiter().GetResult()` on a synchronously-completed task is not "blocking on async". Source: I wrote it. – Bradley Grainger Dec 20 '19 at 16:52

score 0 · Answer 2 · answered Dec 19 '19 at 07:32

How are you creating your cancellation Token and what is his value?

Also here is a solution how to cancel a sql command with a cancellation token

private CancellationTokenSource cts;
private async void TestSqlServerCancelSprocExecution()
{
cts = new CancellationTokenSource();
try
{
    await Task.Run(() =>
    {
        using (SqlConnection conn = new SqlConnection("connStr"))
        {
            conn.InfoMessage += conn_InfoMessage;
            conn.FireInfoMessageEventOnUserErrors = true;
            conn.Open();

            var cmd = conn.CreateCommand();
            cts.Token.Register(() => cmd.Cancel());
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "dbo.[CancelSprocTest]";
            cmd.ExecuteNonQuery();
        }
   });
}
catch (SqlException)
{
    // sproc was cancelled
}

}

The code above is from this question, which had kinda the same problem, that the cancellation token won't cancel the sql command.

Cancellation is working fine with SqlCommand. My problem is with MySqlCommand — jophab, Dec 19 '19 at 09:45

C# : Cancelling MySqlCommand using CancellationToken giving NULLReferenceException

2 Answers2