| 2014-11-02 10:36:25 |
Elena Stepanova |
description |
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 |
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 |
|
