Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-3228

LP #1388533: MAX_STATEMENT_TIME has no effect in a procedure after an interrupted sleep

    Details

    • Type: Bug
    • Status: Done
    • Priority: Low
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None

      Description

      **Reported in Launchpad by Elena Stepanova last update 25-03-2016 14:33:54

      In the procedure below, SELECT SLEEP(..) is interrupted as expected; but SELECT COUNT takes clearly longer that 1 millisecond, however it is not interrupted.

      #----------------------------------

      MySQL [test]> create table t1 (i int) engine=InnoDB;
      Query OK, 0 rows affected (1.12 sec)

      MySQL [test]> insert into t1 values (1),(2),(3),(4);
      Query OK, 4 rows affected (0.56 sec)
      Records: 4 Duplicates: 0 Warnings: 0

      MySQL [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.47 sec)
      Records: 16384 Duplicates: 0 Warnings: 0

      MySQL [test]>
      MySQL [test]> delimiter |
      MySQL [test]> create procedure pr()
      -> begin
      -> select sleep(0.5);
      -> select sql_no_cache count from t1;
      -> select sql_no_cache count from t1;
      -> end |
      Query OK, 0 rows affected (0.00 sec)

      MySQL [test]> delimiter ;
      MySQL [test]>
      MySQL [test]> set max_statement_time = 1;
      Query OK, 0 rows affected (0.00 sec)

      MySQL [test]> call pr();
      ------------

      sleep(0.5)

      ------------

      1

      ------------
      1 row in set (0.00 sec)

      ----------

      count

      ----------

      16388

      ----------
      1 row in set (0.29 sec)

      ----------

      count

      ----------

      16388

      ----------
      1 row in set (0.58 sec)

      Query OK, 0 rows affected (0.58 sec)

      #----------------------------------

      Compare with this:

      #----------------------------------

      MySQL [test]> delimiter |
      MySQL [test]> create procedure pr2()
      -> begin
      -> select 1;
      -> select sql_no_cache count from t1;
      -> select sql_no_cache count from t1;
      -> end |
      Query OK, 0 rows affected (0.00 sec)

      MySQL [test]> delimiter ;
      MySQL [test]>
      MySQL [test]> set max_statement_time = 1;
      Query OK, 0 rows affected (0.00 sec)

      MySQL [test]> call pr2();
      ---

      1

      ---

      1

      ---
      1 row in set (0.00 sec)

      ERROR 1885 (70101): Query execution was interrupted, max_statement_time exceeded

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                lpjirasync lpjirasync (Inactive)
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: