Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7011

MAX_STATEMENT_TIME has no effect in a procedure after a previous successful statement

    Details

      Description

      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.

        Attachments

          Issue links

            Activity

              People

              • Assignee:
                sanja Oleksandr Byelkin
                Reporter:
                elenst Elena Stepanova
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: