MDEV-8346: main.max_statement_time fails in buildbot in different waysbb-10.1-MDEV-8346

- the test dependence on computer characteristic removed
- reduced time of execution of the test

2 files changed, 35 insertions, 37 deletions

diff --git a/mysql-test/r/max_statement_time.result b/mysql-test/r/max_statement_time.result
index c0cffb27a37..458f3c785a9 100644
--- a/mysql-test/r/max_statement_time.result
+++ b/mysql-test/r/max_statement_time.result
@@ -1,25 +1,25 @@
 
 # Test the MAX_STATEMENT_TIME option.
 
-SET @@MAX_STATEMENT_TIME=2;
+SET @@MAX_STATEMENT_TIME=0.2;
 select @@max_statement_time;
 @@max_statement_time
-2.000000
-SELECT SLEEP(1);
-SLEEP(1)
+0.200000
+SELECT SLEEP(0.1);
+SLEEP(0.1)
 0
-SELECT SLEEP(3);
-SLEEP(3)
+SELECT SLEEP(0.3);
+SLEEP(0.3)
 1
 SET @@MAX_STATEMENT_TIME=0;
-SELECT SLEEP(1);
-SLEEP(1)
+SELECT SLEEP(0.1);
+SLEEP(0.1)
 0
 SHOW STATUS LIKE "max_statement_time_exceeded";
 Variable_name	Value
 Max_statement_time_exceeded	1
-CREATE TABLE t1 (a INT, b VARCHAR(300)) engine=myisam;
-INSERT INTO t1 VALUES (1, 'string');
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3),(4);
 SELECT 0;
 0
 0
@@ -30,7 +30,7 @@ CREATE PROCEDURE p1()
 BEGIN
 declare tmp int;
 SET @@MAX_STATEMENT_TIME=0.0001;
-SELECT COUNT(*) INTO tmp FROM t1 WHERE b LIKE '%z%';
+SELECT a,sleep(0.05) FROM t1;
 SET @@MAX_STATEMENT_TIME=0;
 END|
 CREATE PROCEDURE p2()
@@ -41,6 +41,11 @@ SELECT @@MAX_STATEMENT_TIME;
 @@MAX_STATEMENT_TIME
 0.000000
 CALL p1();
+a	sleep(0.05)
+1	0
+2	0
+3	0
+4	0
 CALL p2();
 SELECT @@MAX_STATEMENT_TIME;
 @@MAX_STATEMENT_TIME
@@ -153,12 +158,11 @@ DROP TABLE t1;
 #
 create table t1 (i int);
 insert into t1 values (1),(2),(3),(4);
-insert into t1 select a.* from t1 a, t1 b, t1 c, t1 d, t1 e, t1 f, t1 g;
 create procedure pr()
 begin
 select 1;
-select sql_no_cache * from t1 where i > 5;
-select sql_no_cache * from t1 where i > 5;
+select sql_no_cache *, sleep(0.01) from t1;
+select sql_no_cache *, sleep(0.01) from t1;
 end |
 set max_statement_time = 0.001;
 call pr();
@@ -168,8 +172,8 @@ ERROR 70100: Query execution was interrupted (max_statement_time exceeded)
 drop procedure pr;
 create procedure pr()
 begin
-select sql_no_cache * from t1 where i > 5;
-select sql_no_cache * from t1 where i > 5;
+select sql_no_cache *, sleep(0.01) from t1;
+select sql_no_cache *, sleep(0.01) from t1;
 end |
 set max_statement_time = 0.001;
 call pr();
diff --git a/mysql-test/t/max_statement_time.test b/mysql-test/t/max_statement_time.test
index d994f04be83..aec43be4b6c 100644
--- a/mysql-test/t/max_statement_time.test
+++ b/mysql-test/t/max_statement_time.test
@@ -9,32 +9,27 @@
 --echo # Test the MAX_STATEMENT_TIME option.
 --echo
 
-SET @@MAX_STATEMENT_TIME=2;
+SET @@MAX_STATEMENT_TIME=0.2;
 select @@max_statement_time;
-SELECT SLEEP(1);
-SELECT SLEEP(3);
+SELECT SLEEP(0.1);
+SELECT SLEEP(0.3);
 SET @@MAX_STATEMENT_TIME=0;
-SELECT SLEEP(1);
+SELECT SLEEP(0.1);
 SHOW STATUS LIKE "max_statement_time_exceeded";
 
-CREATE TABLE t1 (a INT, b VARCHAR(300)) engine=myisam;
+CREATE TABLE t1 (a INT);
 
-INSERT INTO t1 VALUES (1, 'string');
+INSERT INTO t1 VALUES (1),(2),(3),(4);
 
 --disable_result_log
 --disable_query_log
 
 SET @@MAX_STATEMENT_TIME=2;
 
-SET @@MAX_STATEMENT_TIME=0.1;
-WHILE (! $mysql_errno)
-{
-  SET @@MAX_STATEMENT_TIME=0;
-  INSERT INTO t1 SELECT * FROM t1;
-  SET @@MAX_STATEMENT_TIME=0.1;
-  --error 0,ER_STATEMENT_TIMEOUT
-  SELECT COUNT(*) FROM t1 WHERE b LIKE '%z%';
-}
+SET @@MAX_STATEMENT_TIME=0.01;
+SET @@MAX_STATEMENT_TIME=0.01;
+--error 0,ER_STATEMENT_TIMEOUT
+SELECT a,sleep(0.005) FROM t1;
 SET @@MAX_STATEMENT_TIME=0;
 
 --enable_query_log
@@ -52,7 +47,7 @@ CREATE PROCEDURE p1()
 BEGIN
   declare tmp int;
   SET @@MAX_STATEMENT_TIME=0.0001;
-  SELECT COUNT(*) INTO tmp FROM t1 WHERE b LIKE '%z%';
+  SELECT a,sleep(0.05) FROM t1;
   SET @@MAX_STATEMENT_TIME=0;
 END|
 
@@ -194,13 +189,12 @@ DROP TABLE t1;
 --echo #
 create table t1 (i int);
 insert into t1 values (1),(2),(3),(4);
-insert into t1 select a.* from t1 a, t1 b, t1 c, t1 d, t1 e, t1 f, t1 g;
 delimiter |;
 create procedure pr()
  begin
    select 1;
-   select sql_no_cache * from t1 where i > 5;
-   select sql_no_cache * from t1 where i > 5;
+   select sql_no_cache *, sleep(0.01) from t1;
+   select sql_no_cache *, sleep(0.01) from t1;
  end |
 delimiter ;|
 set max_statement_time = 0.001;
@@ -210,8 +204,8 @@ drop procedure pr;
 delimiter |;
 create procedure pr()
  begin
-   select sql_no_cache * from t1 where i > 5;
-   select sql_no_cache * from t1 where i > 5;
+   select sql_no_cache *, sleep(0.01) from t1;
+   select sql_no_cache *, sleep(0.01) from t1;
  end |
 delimiter ;|
 set max_statement_time = 0.001;