1# 2# Test behavior of MAX_STATEMENT_TIME. 3# We can't do this under valgrind as valgrind interferes with thread scheduling 4# 5 6--source include/not_embedded.inc 7--source include/have_innodb.inc 8--source include/have_sequence.inc 9--source include/not_valgrind.inc 10 11--echo 12--echo # Test the MAX_STATEMENT_TIME option. 13--echo 14 15SET @@MAX_STATEMENT_TIME=2; 16select @@max_statement_time; 17SELECT SLEEP(1); 18SELECT SLEEP(3); 19SET @@MAX_STATEMENT_TIME=0; 20SELECT SLEEP(1); 21SHOW STATUS LIKE "max_statement_time_exceeded"; 22 23CREATE TABLE t1 (a INT, b VARCHAR(300)) engine=myisam; 24 25INSERT INTO t1 VALUES (1, 'string'); 26 27--disable_result_log 28--disable_query_log 29 30SET @@MAX_STATEMENT_TIME=2; 31 32SET @@MAX_STATEMENT_TIME=0.1; 33WHILE (! $mysql_errno) 34{ 35 SET @@MAX_STATEMENT_TIME=0; 36 INSERT INTO t1 SELECT * FROM t1; 37 SET @@MAX_STATEMENT_TIME=0.1; 38 --error 0,ER_STATEMENT_TIMEOUT 39 SELECT COUNT(*) FROM t1 WHERE b LIKE '%z%'; 40} 41SET @@MAX_STATEMENT_TIME=0; 42 43--enable_query_log 44--enable_result_log 45 46eval SELECT $mysql_errno; 47 48--echo 49--echo # Test the MAX_STATEMENT_TIME option with SF (should have no effect). 50--echo 51 52DELIMITER |; 53 54CREATE PROCEDURE p1() 55BEGIN 56 declare tmp int; 57 SET @@MAX_STATEMENT_TIME=0.0001; 58 SELECT COUNT(*) INTO tmp FROM t1 WHERE b LIKE '%z%'; 59 SET @@MAX_STATEMENT_TIME=0; 60END| 61 62CREATE PROCEDURE p2() 63BEGIN 64 SET @@MAX_STATEMENT_TIME=5; 65END| 66 67DELIMITER ;| 68 69SELECT @@MAX_STATEMENT_TIME; 70CALL p1(); 71CALL p2(); 72SELECT @@MAX_STATEMENT_TIME; 73SET @@MAX_STATEMENT_TIME=0; 74 75DROP PROCEDURE p1; 76DROP PROCEDURE p2; 77DROP TABLE t1; 78 79--echo 80--echo # MAX_STATEMENT_TIME account resource 81--echo 82 83set statement sql_mode="" for 84GRANT USAGE ON *.* TO user1@localhost WITH MAX_STATEMENT_TIME 1.005; 85 86--echo # con1 87connect(con1,localhost,user1,,test,,); 88SELECT @@max_statement_time; 89disconnect con1; 90 91--echo # restart and reconnect 92connection default; 93source include/restart_mysqld.inc; 94 95set @global.userstat=1; 96connect(con1,localhost,user1,,test,,); 97SELECT @@global.max_statement_time,@@session.max_statement_time; 98select sleep(100); 99SHOW STATUS LIKE "max_statement_time_exceeded"; 100disconnect con1; 101 102connection default; 103show grants for user1@localhost; 104--disable_parsing 105select max_user_timeouts from information_schema.user_statistics where user="user1"; 106--enable_parsing 107 108set @global.userstat=0; 109DROP USER user1@localhost; 110 111--echo 112--echo # MAX_STATEMENT_TIME status variables. 113--echo 114 115flush status; 116 117SET @@max_statement_time=0; 118SELECT CONVERT(VARIABLE_VALUE, UNSIGNED) INTO @time_exceeded 119 FROM INFORMATION_SCHEMA.GLOBAL_STATUS 120 WHERE VARIABLE_NAME = 'max_statement_time_exceeded'; 121 122SET @@max_statement_time=0.5; 123SELECT SLEEP(2); 124SHOW STATUS LIKE '%timeout%'; 125SET @@max_statement_time=0; 126 127--echo # Ensure that the counters for: 128--echo # - statements that exceeded their maximum execution time 129--echo # are incremented. 130 131SELECT 1 AS STATUS FROM INFORMATION_SCHEMA.GLOBAL_STATUS 132 WHERE VARIABLE_NAME = 'max_statement_time_exceeded' 133 AND CONVERT(VARIABLE_VALUE, UNSIGNED) > @time_exceeded; 134 135--echo 136--echo # Check that the appropriate error status is set. 137--echo 138 139CREATE TABLE t1 (a INT) ENGINE=InnoDB; 140INSERT INTO t1 VALUES (1); 141 142START TRANSACTION; 143SELECT * FROM t1 FOR UPDATE; 144 145connect (con1,localhost,root,,test,,); 146SET @@SESSION.max_statement_time = 0.5; 147--error ER_STATEMENT_TIMEOUT 148UPDATE t1 SET a = 2; 149SHOW WARNINGS; 150disconnect con1; 151 152connection default; 153ROLLBACK; 154DROP TABLE t1; 155 156--echo 157--echo # Test interaction with lock waits. 158--echo 159 160CREATE TABLE t1 (a INT) ENGINE=InnoDB; 161INSERT INTO t1 VALUES (1); 162 163connect (con1,localhost,root,,test,,); 164SET @@SESSION.max_statement_time= 0.5; 165 166connection default; 167LOCK TABLES t1 WRITE; 168 169connection con1; 170SELECT @@SESSION.max_statement_time; 171--error ER_STATEMENT_TIMEOUT 172LOCK TABLES t1 READ; 173 174connection default; 175UNLOCK TABLES; 176BEGIN; 177SELECT * FROM t1; 178 179connection con1; 180--error ER_STATEMENT_TIMEOUT 181ALTER TABLE t1 ADD COLUMN b INT; 182 183connection default; 184ROLLBACK; 185SELECT GET_LOCK('lock', 1); 186 187connection con1; 188SELECT GET_LOCK('lock', 1); 189 190disconnect con1; 191connection default; 192SELECT RELEASE_LOCK('lock'); 193DROP TABLE t1; 194 195--echo # 196--echo # MDEV-7011:MAX_STATEMENT_TIME has no effect in a procedure after 197--echo # a previous successful statement 198--echo # 199create table t1 (i int); 200insert into t1 values (1),(2),(3),(4); 201insert into t1 select a.* from t1 a, t1 b, t1 c, t1 d, t1 e, t1 f, t1 g; 202delimiter |; 203create procedure pr() 204 begin 205 select 1; 206 select sql_no_cache * from t1 where i > 5; 207 select sql_no_cache * from t1 where i > 5; 208 select sleep(2); 209 end | 210delimiter ;| 211set max_statement_time = 0.001; 212--error ER_STATEMENT_TIMEOUT 213call pr(); 214set max_statement_time = 0; 215drop procedure pr; 216delimiter |; 217create procedure pr() 218 begin 219 select sql_no_cache * from t1 where i > 5; 220 select sql_no_cache * from t1 where i > 5; 221 select sleep(2); 222 end | 223delimiter ;| 224set max_statement_time = 0.001; 225--error ER_STATEMENT_TIMEOUT 226call pr(); 227set max_statement_time = 0; 228drop procedure pr; 229drop table t1; 230 231# 232# MDEV-16615 ASAN SEGV in handler::print_error or server crash after error upon CREATE TABLE 233# 234SET max_statement_time= 1; 235--error ER_STATEMENT_TIMEOUT 236CREATE TABLE t ENGINE=InnoDB SELECT * FROM seq_1_to_50000; 237