Error for MAX_EXECUTION_TIME isn't compatible with MySQL

[dveeden]

Bug Report

1. Minimal reproduce step (Required)

Get a statement that gets interrupted because it reaches the MAX_EXECUTION_TIME.

Different statements are used for MySQL and TiDB because of #34344 (and #43027 )

2. What did you expect to see? (Required)

MySQL 8.0.32

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.12 sec)

mysql> CREATE TABLE t1 (a INT, b VARCHAR(300));
Query OK, 0 rows affected (0.15 sec)

mysql> INSERT INTO t1 VALUES (1, 'string');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT /*+ MAX_EXECUTION_TIME(1) */ *, SLEEP(5) FROM t1;
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

3. What did you see instead (Required)

tiup playground --without-monitor --tiflash 0 7.0.0
tiup bench tpcc prepare --warehouses 100

sql> SELECT /*+ MAX_EXECUTION_TIME(1) */ * FROM stock;
ERROR: 1317 (70100): Query execution was interrupted

4. What is your TiDB version? (Required)

Release Version: v7.0.0
Edition: Community
Git Commit Hash: 7376954cd868dbc44fc3015c9ef89c53749339a7
Git Branch: heads/refs/tags/v7.0.0
UTC Build Time: 2023-03-29 13:32:13
GoVersion: go1.20.2
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv

The error number isn't the same as MySQL.

See also: https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html#error_er_query_timeout

[dveeden]

Not specific to MySQL 8.0:

mysql> use test
Database changed
mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t1 (a INT, b VARCHAR(300));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t1 VALUES (1, 'string');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT /*+ MAX_EXECUTION_TIME(1) */ *, SLEEP(5) FROM t1;
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.36    |
+-----------+
1 row in set (0.00 sec)

[dveeden]

Looks like #37574 might be the same issue.

[dveeden]

From the logs:

[2023/04/13 11:13:52.691 +02:00] [WARN] [expensivequery.go:70] ["execution timeout, kill it"] [costTime=71.976355ms] [maxExecutionTime=1ms] [processInfo="{id:2199023255959, user:root, host:127.0.0.1:44906, db:test, command:Query, time:0, state:autocommit, info:SELECT /*+ MAX_EXECUTION_TIME(1) */ * FROM stock}"]
[2023/04/13 11:13:52.691 +02:00] [INFO] [server.go:800] [kill] [conn=2199023255959] [query=true]
[2023/04/13 11:13:52.710 +02:00] [INFO] [conn.go:1151] ["command dispatched failed"] [conn=2199023255959] [connInfo="id:2199023255959, addr:127.0.0.1:44906 status:10, collation:utf8mb4_0900_ai_ci, user:root"] [command=Query] [status="inTxn:0, autocommit:1"] [sql="SELECT /*+ MAX_EXECUTION_TIME(1) */ * FROM stock"] [txn_mode=PESSIMISTIC] [timestamp=440762953267937280] [err="[executor:1317]Query execution was interrupted"]