Enforcing MySQL SELECT Timeout When max_execution_time Does Not Apply

MySQL provides the max_execution_time system variable to limit the execution time of read‑only SELECT statements, but it only applies to standalone SELECTs and not to those executed inside stored procedures, triggers, or other stored programs.

The manual explains that the global value sets the default for new sessions, the session value applies to SELECTs that contain no MAX_EXECUTION_TIME(...) hint, and the variable is ignored for SELECTs inside stored programs.

When the variable is set, a long‑running SELECT is cancelled with an error, as shown:

mysql> set @@max_execution_time=1000;
Query OK, 0 rows affected (0.00 sec)

mysql> select sleep(2) from t1 limit 1;
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

Alternatively, the hint syntax can be used:

mysql> select /*+ max_execution_time(1000) */ sleep(2) from t1 limit 2;
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

mysql> select /*+ set_var(max_execution_time=1000) */ sleep(2) from t1 limit 2;
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

However, when the same SELECT is placed inside a stored procedure, the timeout does not take effect:

DELIMITER $$
USE `ytt`$$
DROP PROCEDURE IF EXISTS `sp_test`$$
CREATE DEFINER=`admin`@`%` PROCEDURE `sp_test`()
BEGIN
      select sleep(2) from t1 limit 1;
END$$
DELIMITER ;

Calling the procedure runs for the full two seconds despite max_execution_time being set to 1 second.

To enforce a timeout for such embedded SELECTs, you can run an external script that periodically kills long‑running queries, for example:

#!/bin/sh
QUERY_ID=`mysql -ss -e "select id from information_schema.processlist where user='admin' and db='ytt' and time>10 and regexp_like(info,'^select','i')"`
if [ $QUERY_ID ]; then
  echo "kill query $QUERY_ID"
  mysql -e "kill query $QUERY_ID"
fi

Schedule this script via cron or a MySQL event. The Percona Toolkit also offers pt‑kill , which can automatically cancel queries matching certain criteria:

pt-kill --match-db=ytt --match-user=admin --match-host=% --match-info='^select' --victims=all --busy-time='10s' --print --kill-query

Note that automatically cancelling queries is generally unsuitable for production environments because it may interfere with expected transaction behavior.