mirror of
https://github.com/MariaDB/server.git
synced 2025-11-04 04:46:15 +01:00
186 lines
4.5 KiB
Text
186 lines
4.5 KiB
Text
|
|
# Test the MAX_STATEMENT_TIME option.
|
|
|
|
SET @@MAX_STATEMENT_TIME=2;
|
|
select @@max_statement_time;
|
|
@@max_statement_time
|
|
2.000000
|
|
SELECT SLEEP(1);
|
|
SLEEP(1)
|
|
0
|
|
SELECT SLEEP(3);
|
|
SLEEP(3)
|
|
1
|
|
SET @@MAX_STATEMENT_TIME=0;
|
|
SELECT SLEEP(1);
|
|
SLEEP(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');
|
|
SELECT 0;
|
|
0
|
|
0
|
|
|
|
# Test the MAX_STATEMENT_TIME option with SF (should have no effect).
|
|
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
declare tmp int;
|
|
SET @@MAX_STATEMENT_TIME=0.0001;
|
|
SELECT COUNT(*) INTO tmp FROM t1 WHERE b LIKE '%z%';
|
|
SET @@MAX_STATEMENT_TIME=0;
|
|
END|
|
|
CREATE PROCEDURE p2()
|
|
BEGIN
|
|
SET @@MAX_STATEMENT_TIME=5;
|
|
END|
|
|
SELECT @@MAX_STATEMENT_TIME;
|
|
@@MAX_STATEMENT_TIME
|
|
0.000000
|
|
CALL p1();
|
|
CALL p2();
|
|
SELECT @@MAX_STATEMENT_TIME;
|
|
@@MAX_STATEMENT_TIME
|
|
5.000000
|
|
SET @@MAX_STATEMENT_TIME=0;
|
|
DROP PROCEDURE p1;
|
|
DROP PROCEDURE p2;
|
|
DROP TABLE t1;
|
|
|
|
# MAX_STATEMENT_TIME account resource
|
|
|
|
set statement sql_mode="" for
|
|
GRANT USAGE ON *.* TO user1@localhost WITH MAX_STATEMENT_TIME 1.005;
|
|
# con1
|
|
SELECT @@max_statement_time;
|
|
@@max_statement_time
|
|
1.005000
|
|
# restart and reconnect
|
|
set @global.userstat=1;
|
|
SELECT @@global.max_statement_time,@@session.max_statement_time;
|
|
@@global.max_statement_time @@session.max_statement_time
|
|
0.000000 1.005000
|
|
select sleep(100);
|
|
sleep(100)
|
|
1
|
|
SHOW STATUS LIKE "max_statement_time_exceeded";
|
|
Variable_name Value
|
|
Max_statement_time_exceeded 1
|
|
show grants for user1@localhost;
|
|
Grants for user1@localhost
|
|
GRANT USAGE ON *.* TO 'user1'@'localhost' WITH MAX_STATEMENT_TIME 1.005000
|
|
set @global.userstat=0;
|
|
DROP USER user1@localhost;
|
|
|
|
# MAX_STATEMENT_TIME status variables.
|
|
|
|
flush status;
|
|
SET @@max_statement_time=0;
|
|
SELECT CONVERT(VARIABLE_VALUE, UNSIGNED) INTO @time_exceeded
|
|
FROM INFORMATION_SCHEMA.GLOBAL_STATUS
|
|
WHERE VARIABLE_NAME = 'max_statement_time_exceeded';
|
|
SET @@max_statement_time=0.5;
|
|
SELECT SLEEP(2);
|
|
SLEEP(2)
|
|
1
|
|
SHOW STATUS LIKE '%timeout%';
|
|
Variable_name Value
|
|
Binlog_group_commit_trigger_timeout 0
|
|
Master_gtid_wait_timeouts 0
|
|
Ssl_default_timeout 0
|
|
Ssl_session_cache_timeouts 0
|
|
SET @@max_statement_time=0;
|
|
# Ensure that the counters for:
|
|
# - statements that exceeded their maximum execution time
|
|
# are incremented.
|
|
SELECT 1 AS STATUS FROM INFORMATION_SCHEMA.GLOBAL_STATUS
|
|
WHERE VARIABLE_NAME = 'max_statement_time_exceeded'
|
|
AND CONVERT(VARIABLE_VALUE, UNSIGNED) > @time_exceeded;
|
|
STATUS
|
|
1
|
|
|
|
# Check that the appropriate error status is set.
|
|
|
|
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (1);
|
|
START TRANSACTION;
|
|
SELECT * FROM t1 FOR UPDATE;
|
|
a
|
|
1
|
|
SET @@SESSION.max_statement_time = 0.5;
|
|
UPDATE t1 SET a = 2;
|
|
ERROR 70100: Query execution was interrupted (max_statement_time exceeded)
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1969 Query execution was interrupted (max_statement_time exceeded)
|
|
ROLLBACK;
|
|
DROP TABLE t1;
|
|
|
|
# Test interaction with lock waits.
|
|
|
|
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (1);
|
|
SET @@SESSION.max_statement_time= 0.5;
|
|
LOCK TABLES t1 WRITE;
|
|
SELECT @@SESSION.max_statement_time;
|
|
@@SESSION.max_statement_time
|
|
0.500000
|
|
LOCK TABLES t1 READ;
|
|
ERROR 70100: Query execution was interrupted (max_statement_time exceeded)
|
|
UNLOCK TABLES;
|
|
BEGIN;
|
|
SELECT * FROM t1;
|
|
a
|
|
1
|
|
ALTER TABLE t1 ADD COLUMN b INT;
|
|
ERROR 70100: Query execution was interrupted (max_statement_time exceeded)
|
|
ROLLBACK;
|
|
SELECT GET_LOCK('lock', 1);
|
|
GET_LOCK('lock', 1)
|
|
1
|
|
SELECT GET_LOCK('lock', 1);
|
|
GET_LOCK('lock', 1)
|
|
NULL
|
|
SELECT RELEASE_LOCK('lock');
|
|
RELEASE_LOCK('lock')
|
|
1
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-7011:MAX_STATEMENT_TIME has no effect in a procedure after
|
|
# a previous successful statement
|
|
#
|
|
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 sleep(2);
|
|
end |
|
|
set max_statement_time = 0.001;
|
|
call pr();
|
|
1
|
|
1
|
|
ERROR 70100: Query execution was interrupted (max_statement_time exceeded)
|
|
set max_statement_time = 0;
|
|
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 sleep(2);
|
|
end |
|
|
set max_statement_time = 0.001;
|
|
call pr();
|
|
ERROR 70100: Query execution was interrupted (max_statement_time exceeded)
|
|
set max_statement_time = 0;
|
|
drop procedure pr;
|
|
drop table t1;
|
|
SET max_statement_time= 1;
|
|
CREATE TABLE t ENGINE=InnoDB SELECT * FROM seq_1_to_50000;
|
|
ERROR 70100: Query execution was interrupted (max_statement_time exceeded)
|