Technology Sharing | What should I do if the read-only statement in the MySQL stored procedure times out?

Author: Yang Taotao

Senior database expert, specializing in MySQL research for more than ten years. Good at MySQL, PostgreSQL, MongoDB and other open source databases related to backup and recovery, SQL tuning, monitoring operation and maintenance, high-availability architecture design, etc. Currently working in Aikesheng, providing MySQL-related technical support and MySQL-related course training for major operators and banking and financial enterprises.

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

---

MySQL has a parameter called max_execution_time, which is used to set the timeout time for the execution of read-only statements, but it is only valid for select statements executed independently; for select statements that are not executed independently, such as those contained in built-in transaction blocks such as stored procedures and triggers, invalid. The official manual explains this parameter as follows:

max_execution_time applies as follows:

• The global max_execution_time value provides the default for the session value for new connections. The session value applies to SELECT executions executed within the session that include no MAX_EXECUTION_TIME(* N *) optimizer hint or for which N is 0.
• max_execution_time applies to read-only SELECT statements. Statements that are not read only are those that invoke a stored function that modifies data as a side effect.
• max_execution_time is ignored for SELECT statements in stored programs.

How to control the timeout period for this kind of non-single select statement?

Let's first look at the effect of setting the parameter max_execution_time. After this parameter is set, if the execution time of the select statement is too long, it will be canceled directly and an error will be reported, as shown below:

 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

Or directly adding Hint can also limit the execution time of the select statement: The following two methods can limit the execution time of the select statement.

 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

Then if this select statement is encapsulated inside the stored procedure, according to the explanation of the parameter max_execution_time in the manual, it will not take effect. For example, create a new stored procedure sp_test:

 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 ;

Reset the max_execution_time value to 1 second: call the stored procedure sp_test, it can be executed normally, and the select statement has not been canceled!

 mysql> call sp_test;
+----------+
| sleep(2) |
+----------+
|        0 |
+----------+
1 rows in set (2.01 sec)

Query OK, 0 rows affected (2.01 sec)

So how to solve this problem?

To make it easier for everyone to test, change the statement select sleep(2) from t1 limit 1 to select sleep(2000) from t1 limit 1 . Since there are such restrictions at the MySQL level, it can only be done from the non-MySQL level. The most direct and effective way is to write a script to actively cancel the select statement. The script is as follows:

 root@ytt-normal:/home/ytt/script# cat kill_query 
#!/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

After that, put the script in crontab or MySQL's own event to execute it regularly. The effect of executing the script alone is as follows:

 root@ytt-normal:/home/ytt/script# ./kill_query 
kill query 50

In addition to writing your own scripts, there is a tool to achieve a similar effect, which is included in the well-known Percona-toolkit toolbox, called pt-kill.

The pt-kill tool can execute specified actions according to various trigger conditions: for example, cancel the specified SQL statement, kill the specified session, and so on. So you can use the pt-kill tool to automatically cancel the timeout of the select statement. As shown below: The pt-kill tool will always run in the background, monitoring the MySQL process, and once the trigger condition is activated, it can execute the corresponding action.

 root@ytt-normal:/home/ytt/script# pt-kill --match-db=ytt --match-user=admin --match-host=%  \--match-info='^select' --victims=all --busy-time='10s' --print --kill-query

# 2022-08-15T17:29:03 KILL QUERY 50 (Query 11 sec) select sleep(2000) from t1 limit 1

One thing to note: the function of automatically canceling the timeout of the select statement is not suitable for use in the production environment! Because you can't predict the timeliness of its execution results and whether the context is relevant or not.