max_execution_time compatibility issues

[glkappe]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

The first point：Does not take effect for ddl statements

run long time, it > max_execution_time

mysql> alter table sbtest1 add index idx_aa(id,c,pad);
^@ERROR 1317 (70100): Query execution was interrupted

mysql> select @@max_execution_time;
+----------------------+
| @@max_execution_time |
+----------------------+
|                 1000 |
+----------------------+
1 row in set (0.03 sec)

mysql> admin show ddl jobs;
+--------+---------+---------------------+---------------+--------------+-----------+----------+-----------+---------------------+---------------------+--------+
| JOB_ID | DB_NAME | TABLE_NAME          | JOB_TYPE      | SCHEMA_STATE | SCHEMA_ID | TABLE_ID | ROW_COUNT | START_TIME          | END_TIME            | STATE  |
+--------+---------+---------------------+---------------+--------------+-----------+----------+-----------+---------------------+---------------------+--------+
|    123 | test    | sbtest1             | add index     | public       |         1 |       71 |   1600007 | 2021-11-24 02:35:08 | 2021-11-24 02:37:27 | synced |
+--------+---------+---------------------+---------------+--------------+-----------+----------+-----------+---------------------+---------------------+--------+
10 rows in set (0.05 sec)

mysql> show create table sbtest1;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                   |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `k_1` (`k`),
  KEY `idx_aa` (`id`,`c`,`pad`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=8691384 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

mysql>

secondy point：

This may affect the normal execution of other sql, it is recommended to only leave the select, consistent with mysql

mysql> set max_execute_time = 2;
ERROR 1193 (HY000): Unknown system variable 'max_execute_time'
mysql> set max_execution_time = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> analyze table test.sbtest1;
ERROR 1317 (70100): Query execution was interrupted
mysql>

[bb7133]

The current behavior of max_execution_time is by design(not a bug), I will treat it as an enhancement.

[shellderp]

To me it is a feature that max_execution_time applies to update and delete, not only select. It is a dangerous implementation in MySQL. That said, maybe perfect compatibility is more important.

[morgo]

To me it is a feature that max_execution_time applies to update and delete, not only select. It is a dangerous implementation in MySQL. That said, maybe perfect compatibility is more important.

MySQL implements MVCC differently, so rolling back a statement can be something like 30x more expensive than the corresponding apply. If the limit applied to all statements, there is a high risk that under some loads DMLs could hit the threshold and effectively DoS the server.

Since TiDB does not have this issue, I think it is quite a reasonable feature extension. But we can perhaps leave this open as a feature request and if there is a lot of user feedback, we can change it.

[mjonss]

For reference: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_execution_time

[Defined2014]

After https://github.com/pingcap/tidb/pull/38671, max_execution_time only affects read-only query just like MySQL, so close this issue.