Clone this wiki locally
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.