Description:
Team,
I have noticed that the max_execution_time variable and optimizer hint are not applicable in all processlist states. For example, during stages like "Opening tables", "checking permissions", "sending data", etc.
Sometimes, its important to kill the query irrespective of its state to avoid resource (memory/cpu/disk) consumption. This will help to ensure that unintended queries wont run on the DB server.
How to repeat:
Example-1: (sbtest database has 365k tables)
~~~~~~~~~
session-1:
MySQL [(none)]> select /*+ MAX_EXECUTION_TIME(1000) */ TABLE_SCHEMA, TABLE_NAME, CHECKSUM from information_schema.tables where table_schema='sbtest' and table_type='view' and table_comment like '%references invalid table%';
Empty set (10 min 42.03 sec)
session-2: (mysqladmin -i 1 processlist)
+------+----------+--------------------+----+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+----------+--------------------+----+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 7879 | master57 | 172.31.21.51:39782 | | Query | 6 | checking permissions | select TABLE_SCHEMA, TABLE_NAME, CHECKSUM from information_schema.tables where table_schema='sbtest |
| 7880 | master57 | 172.31.21.51:39784 | | Query | 0 | starting | show processlist |
+------+----------+--------------------+----+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
+------+----------+--------------------+----+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+----------+--------------------+----+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| 7879 | master57 | 172.31.21.51:39782 | | Query | 639 | Opening tables | select TABLE_SCHEMA, TABLE_NAME, CHECKSUM from information_schema.tables where table_schema='sbtest |
| 7880 | master57 | 172.31.21.51:39784 | | Query | 0 | starting | show processlist |
+------+----------+--------------------+----+---------+------+----------------+------------------------------------------------------------------------------------------------------+
+------+----------+--------------------+----+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+----------+--------------------+----+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 7879 | master57 | 172.31.21.51:39782 | | Query | 640 | Sending data | select TABLE_SCHEMA, TABLE_NAME, CHECKSUM from information_schema.tables where table_schema='sbtest |
| 7880 | master57 | 172.31.21.51:39784 | | Query | 0 | starting | show processlist |
+------+----------+--------------------+----+---------+------+--------------+------------------------------------------------------------------------------------------------------+
Example-2: (sbtest database has approx 2k tables)
~~~~~~~~~~
>> Here the query timed out after 13 seconds and during the time the query was in "opening tables" state.
session-1
MySQL [(none)]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 7873 |
+-----------------+
1 row in set (0.01 sec)
MySQL [(none)]> set max_execution_time=1000;
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> select TABLE_SCHEMA, TABLE_NAME, CHECKSUM from information_schema.tables where table_schema='sbtest' and table_type='view' and table_comment like '%references invalid table%';
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded
MySQL [(none)]>
session-2: (show processlist)
MySQL [(none)]> show processlist;
+------+----------+--------------------+------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+----------+--------------------+------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| 7873 | master57 | 172.31.21.51:39776 | NULL | Query | 13 | Opening tables | select TABLE_SCHEMA, TABLE_NAME, CHECKSUM from information_schema.tables where table_schema='sbtest' |
| 7874 | master57 | 172.31.21.51:39778 | NULL | Query | 0 | starting | show processlist |
+------+----------+--------------------+------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
Suggested fix:
It would be nice to modify the behavior to kill the query when it reaches the mentioned time irrespective of the state of the query. Alternatively, we can implement another variable "max_query_timeout".
