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

    XMLWordPrintable

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

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.