CancellationToken vs the CancellationTimeout in v1.2.1

[penguinawesome]

Hi,

I would like to clarify on how the Default Command Timeout (connection string), CancellationTimeout (connection string) and the CancellationToken (c# code async) works in the latest library version 1.2.1.

Before, we are using CancellationToken to cancel and kill the query if it exceeds the running time since the Default Command Timeout in the connection string doesn't cancel and kill the query, it just closes the socket and it can cause a lot of negative domino effects on the Db server.

using (CancellationTokenSource cts = new CancellationTokenSource(DbDefaultCommandTimeoutClientMilliseconds))
{
result = await myCommand.ExecuteReaderAsync(cts.Token);

            if (cts.IsCancellationRequested)
            {
                throw new Exception(string.Format("MySQL Cancelled Task Command Timeout after {0} milliseconds.", DbDefaultCommandTimeoutClientMilliseconds));
            }
        }

Now we have tried updating to the latest version, and i am now seeing a new config (CancellationTimeout ) and changes on how Default Command Timeout works already, i am still confused on how these timeouts works. Could you ellaborate further?

With the new library, can I safely assume that the Default Command Timeout + CancellationTimeout will trigger the canceling and killing the query if it reaches the timeout? What will happen if i would still use the CancellationToken approach? And also Is the CancellationTimeout who is the one killing the query (based on your documentation here: https://mysqlconnector.net/connection-options/) after X seconds? So basically the Default Command Timeout works like before if there is no CancellationTimeout (-1) right? It is important to set the CancellationTimeout (above 0) so that it will auto kill and cancel the running query?

[bgrainger]

The details are currently discussed here: #455 (comment). In summary:

• DefaultCommandTimeout (connection string) / MySqlCommand.CommandTimeout - the length of time (in seconds) a command can execute. Since v1.1.0, exceeding the timeout causes a KILL QUERY to be sent to the server, which will result in a MySqlException with MySqlErrorCode.QueryInterrupted. The connection is still open an usable after this happens.
• Once Throw CommandTimeoutExpired exception when query is cancelled #939 is implemented, this will change to throwing a MySqlException with MySqlErrorCode.CommandTimeoutExpired.
• CancellationTimeout - the number of seconds to wait for the KILL QUERY to succeed. When this expires, the socket is closed and the connection is torn down. (This was the pre-1.1.0 behaviour.)
• CancellationToken passed to ExecuteXAsync - this cancels the query by executing KILL QUERY on the server when the token is cancelled. An OperationCanceledException (with that CancellationToken) will be thrown.

Before, we are using CancellationToken to cancel and kill the query if it exceeds the running time since the Default Command Timeout in the connection string doesn't cancel and kill the query, it just closes the socket and it can cause a lot of negative domino effects on the Db server.

This is improved in 1.1.0 (for exactly these reasons).

With the new library, can I safely assume that the Default Command Timeout + CancellationTimeout will trigger the canceling and killing the query if it reaches the timeout?

Yes.

What will happen if i would still use the CancellationToken approach?

It should be redundant if you're setting it for the same amount of time.

So basically the Default Command Timeout works like before if there is no CancellationTimeout (-1) right?

No, opposite. DefaultCommandTimeout has the new behaviour (i.e., KILL QUERY) unless CancellationTimeout is set to -1, in which case DefaultCommandTimeout works the same way as before. This is not recommended, but is provided for backwards compatibility.

[penguinawesome]

Thanks! Everything is more clear now. We didn't set the CancellationTimeout in our connection string, basically this will be set as 2 seconds right? since the default value for CancellationTimeout is 2 seconds i think?

lastly, will it be safe if we will retain our approach on using the CancellationToken in our ExecuteReaderAsync with a value that is lowered the Default CommandTimout by 2 secs? (e.g. example the Default CommandTimout is 8 secs, and the CancellationToken is 6 secs).

[bgrainger]

We didn't set the CancellationTimeout in our connection string, basically this will be set as 2 seconds right? since the default value for CancellationTimeout is 2 seconds i think?

That's correct. (You may want to increase it if there's significant latency between your database client and database server, but the default should be fine.)

lastly, will it be safe if we will retain our approach on using the CancellationToken in our ExecuteReaderAsync with a value that is lowered the Default CommandTimout by 2 secs? (e.g. example the Default CommandTimout is 8 secs, and the CancellationToken is 6 secs).

There's nothing "unsafe" about that. This will result in an OperationCanceledException, not a MySqlException.

Personally, I reserve the CancellationToken for user-initiated cancellation, not timeout. If you have an ASP.NET Core Web API, you can pass the CancellationToken from a controller action through to the DB operation, and it will cancel if the HTTP client disconnects. (See https://andrewlock.net/using-cancellationtokens-in-asp-net-core-mvc-controllers/.) That way, I know if the exception came from the user (OperationCanceledException) or from timeout (MySqlException). But that's up to you; there's no one "right" way to do it.

[penguinawesome]

Thanks @bgrainger! We will remove the CancellationToken timeout and will totally rely on the Default CommandTimout already since this will cancel the running query if timeout reaches.

[bgrainger]

I've added some documentation for this: https://mysqlconnector.net/overview/command-cancellation/

[penguinawesome]

Thank you! @bgrainger