In the procedure below, SELECT COUNT takes clearly longer that 1 millisecond, however it is not interrupted.
MariaDB [test]> create table t1 (i int) engine=InnoDB;
|
Query OK, 0 rows affected (0.52 sec)
|
|
MariaDB [test]> insert into t1 values (1),(2),(3),(4);
|
Query OK, 4 rows affected (0.03 sec)
|
Records: 4 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> insert into t1 select a.* from t1 a, t1 b, t1 c, t1 d, t1 e, t1 f, t1 g;
|
Query OK, 16384 rows affected (4.21 sec)
|
Records: 16384 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]>
|
MariaDB [test]> delimiter |
|
MariaDB [test]> create procedure pr()
|
-> begin
|
-> select 1;
|
-> select sql_no_cache count(*) from t1;
|
-> select sql_no_cache count(*) from t1;
|
-> end |
|
Query OK, 0 rows affected (0.08 sec)
|
|
MariaDB [test]> delimiter ;
|
MariaDB [test]>
|
MariaDB [test]> set max_statement_time = 0.001;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> call pr();
|
+---+
|
| 1 |
|
+---+
|
| 1 |
|
+---+
|
1 row in set (0.00 sec)
|
|
+----------+
|
| count(*) |
|
+----------+
|
| 16388 |
|
+----------+
|
1 row in set (0.18 sec)
|
|
+----------+
|
| count(*) |
|
+----------+
|
| 16388 |
|
+----------+
|
1 row in set (0.36 sec)
|
|
Query OK, 0 rows affected (0.36 sec)
|
Compare with this:
MariaDB [test]> delimiter |
|
MariaDB [test]> create procedure pr2()
|
-> begin
|
-> select sql_no_cache count(*) from t1;
|
-> select sql_no_cache count(*) from t1;
|
-> end |
|
Query OK, 0 rows affected (0.06 sec)
|
|
MariaDB [test]> delimiter ;
|
MariaDB [test]>
|
MariaDB [test]> set max_statement_time = 0.001;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> call pr2();
|
ERROR 1968 (70100): Query execution was interrupted (max_statement_time exceeded)
|
I saw a comment in KB which says
MariaDB's can kill any queries (excluding stored procedures).
https://mariadb.com/kb/en/mariadb/documentation/optimization-and-tuning/query-optimizations/aborting-statements-that-exceed-than-a-certain-time-to-execute/
I'm not sure what it means, i.e. whether the whole procedure can't be measured and compared to MAX_STATEMENT_TIME, or any individual statement in the procedure cannot; in any case, the above examples show that the behavior is inconsistent.
Bug
Major
MDEV-4427 query timeouts
