Statement Timeout

darnaut edited this page Jun 4, 2012 · 3 revisions

Statement timeout works by interrupting the execution of statements that take over a specified number of milliseconds to complete. After the specified number of milliseconds has passed, the server attempts to abort the statement without affecting the session (connection) itself. A interrupted statement results in the following error being returned to the client:

ERROR 996 (70100): Query execution was interrupted, max_statement_time exceeded

The maximum execution time can be set with the max_statement_time variable. Using the @@max_statement_time session variable a user is be able to specify the maximum execution time for any statement. The variable value is based on a unsigned 64 bit long integer, which represents statement execution time in milliseconds. A zero value represents an infinite timeout, meaning that no timeout is enforced.

The maximum statement time can also be set for specific SELECT statements using the MAX_STATEMENT_TIME option in the query specification. With the MAX_STATEMENT_TIME=N option, a user is able to specify the maximum execution time (in milliseconds) for a SELECT statement. For example, SELECT MAX_STATEMENT_TIME=10 * FROM table. The option is intended mainly for queries and is not supported if the SELECT statement where the option is used is not a top-level statement (e.g. a subquery). Also, the MAX_STATEMENT_TIME query option takes precedence over the @@max_statement_time session variable.

Also, the account resource limit named MAX_STATEMENT_TIME can be used to set the default maximum execution time for any statement issued under a given account. The max_statement_time session variable is initialized at connect time using the current value of the equally named resource limit. The MAX_STATEMENT_TIME limit can be set with a CREATE USER or GRANT statement. For example, GRANT ... TO 'user'@'host' WITH MAX_STATEMENT_TIME 10. The server stores the specified value for an account in the max_statement_time column of the user table row corresponding to the account.

The maximum execution time only applies to top-level statements or queries; compound statements are treated as a regular component of the top-level statement.

You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.
Press h to open a hovercard with more details.