Improve command timeout handling

[vaintroub]

The query timeout handling in MySqlConnection can be simplified a lot, if it could use server support for query timeouts. E.g in MariaDB 10.1+ you can using per-statement max_query_time , described in https://mariadb.com/kb/en/library/aborting-statements/ . There is a max_statement_time session variable (double) for max time, measured in seconds

MySQL supports this functionality but chose to implement it slightly differently, and in a limited way (variable name is max_execution_time, and it does only work for SELECTs).

Traditional way to implement statement timeouts in other connectors (preceding server support) , was to start a timer task, right before execution of the query. If the task fires, i would create issue "KILL QUERY " from another connection. If query finishes before the timeout , the timer task would be canceled. For MySqlConnector, I feel that even this handling would simplify IO code, which currently relies on socket or stream timeouts( yet still has timer queues for async)

[bgrainger]

I initially started implementing command timeouts that way, but changed my mind: #67 (comment)

The primary reason the command timeout exists is to stop the client hanging forever if there is a network failure; it is not about cancelling a long-running command. (Use MySqlCommand.Cancel or a CancellationToken for that purpose.) The current behaviour is by design.

[bgrainger]

E.g in MariaDB 10.1+ you can using per-statement max_query_time

If I can unambiguously detect MariaDB, this seems like a great enhancement to add. (I looked into MySQL's max_execution_time but ruled it out because it's too limited.)

If the task fires, i would create issue "KILL QUERY " from another connection.

This is unreliable in the face of load-balanced servers, proxies, or network partitions.

However... we could try KILL QUERY first (to keep the connection open #453) then fall back to closing the socket (after some delay) if that doesn't work.

[vaintroub]

you can unambigously detect MariaDB. It has -MariaDB in server version string.

The "real" version is also prefixed by 5.5.5- prefix , due to https://jira.mariadb.org/browse/MDEV-4088 . MariaDB-aware Connectors strip off that prefix : https://jira.mariadb.org/browse/CONC-21 https://jira.mariadb.org/browse/CONJ-32

[vaintroub]

MySQL-protocol aware Proxies are supposed to support KILL QUERY and KILL CONNECTION, because those are quite widely used among all connectors.

[vaintroub]

"The primary reason the command timeout exists is to stop the client hanging forever if there is a network failure; it is not about cancelling a long-running command"

This is usually worked around not with query timeout, but with KeepAlive (with keepalive parameters), e.g SIO_KEEPALIVE_VALS on Windows, https://msdn.microsoft.com/en-us/library/system.net.sockets.iocontrolcode(v=vs.110).aspx . See also https://github.com/dotnet/corefx/issues/25040

[bgrainger]

Investigate what the Connector/J queryTimeoutKillsConnection option does, and if it's relevant to this issue.

[bgrainger]

Similar issue being discussed for npgsql (which might provide an implementation approach to copy): npgsql/npgsql#1567.

[penguinawesome]

+999! This feature is badly needed.

Setting it on a per connection level something like
Connection.MaxExecutionTime = 8000; (in milliseconds) or something similar that will indicate the library to make either auto cancel (reflected in the server side) or kill the query when the timeout reaches, not only in the client side.

[bgrainger]

The current situation for MySqlConnector is very similar to what @roji describes for npgsql, with the same good and bad points: npgsql/npgsql#1567 (comment)

Based on recent work in npgsql and recent feedback here, I'm proposing the following changes:

• MySqlCommand.Cancel() and MySqlCommand.ExecuteXAsync(CancellationToken) continue the same behaviour of performing a server-side cancellation (via KILL QUERY).
• The handling of MySqlCommand.CommandTimeout (which is set from MySqlConnectionStringBuilder.DefaultCommandTimeout) is unified with that; i.e., when the CommandTimeout expires, MySqlConnector will attempt to cancel the query server-side.
  • If the server-side cancellation is successful, the Execute/Read/etc. method will throw a MySqlException with an ErrorCode set to MySqlErrorCode.QueryInterrupted. This is a change from current behaviour, which throws a MySqlException with MySqlErrorCode.CommandTimeoutExpired. set to MySqlErrorCode.CommandTimeoutExpired.
  • If the server-side cancellation is successful, the connection is still usable (and will be returned to the pool when it's closed). This is a change from current behaviour, which leaves the connection in a broken state.
• A new connection string option, Cancellation Timeout, is introduced to handle failure in the case of a network partition:
  • The default is 2 seconds (cf. Cancelling queries on timeouts npgsql/npgsql#3130 (comment)).
  • After CancellationTimeout seconds without receiving an "ERR" packet from the server (which will have a message indicating that the query was interrupted), the client socket will be closed; this is what the implementation of CommandTimeout used to do.
  • In the case of a network partition, the maximum delay (until ExecuteX throws an exception) is raised from CommandTimeout seconds to CommandTimeout + CancellationTimeout seconds; with the default settings, this is an increase from 30 to 32 seconds.
  • The user can distinguish server-side cancellation from local socket closure by testing the thrown exception for MySqlErrorCode.QueryInterrupted vs MySqlErrorCode.CommandTimeoutExpired. for an InnerException with MySqlErrorCode.QueryInterrupted.
• To opt out of this change and return to the current behaviour of closing the socket when CommandTimeout expires, set CancellationTimeout=-1 in the connection string. When CancellationTimeout is -1, the socket will be closed immediately after CommandTimeout seconds.

There's an open question of whether exceptions should be unified between sync and async cancellation (cf. npgsql/npgsql#1146 (comment) ff). Under this proposal MySqlCommand.Cancel() and MySqlCommand.CommandTimeout handling will cause the Execute/Read/etc. method to throw MySqlException with MySqlErrorCode.QueryInterrupted, but the ExecuteXAsync/ReadAsync overloads with a CancellationToken will throw OperationCanceledException if the CancellationToken is cancelled (and MySqlException if the CommandTimeout expires). This could be unified so that OperationCanceledException is thrown in all cases, no matter how the query is cancelled. This would (obviously) be a much more significant breaking change.