319 lines
10 KiB
Plaintext
319 lines
10 KiB
Plaintext
#
|
|
# 1. Test MAX_EXECUTION_TIME option syntax.
|
|
#
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES (10);
|
|
SET @var= (SELECT /*+ MAX_EXECUTION_TIME(0) */ 1);
|
|
Warnings:
|
|
Warning 3125 MAX_EXECUTION_TIME hint is supported by top-level standalone SELECT statements only
|
|
SELECT 1 FROM t1 WHERE a IN (SELECT /*+ MAX_EXECUTION_TIME(0) */ 1);
|
|
1
|
|
Warnings:
|
|
Warning 3125 MAX_EXECUTION_TIME hint is supported by top-level standalone SELECT statements only
|
|
SELECT (SELECT /*+ MAX_EXECUTION_TIME(0) */ a FROM t1);
|
|
(SELECT /*+ MAX_EXECUTION_TIME(0) */ a FROM t1)
|
|
10
|
|
Warnings:
|
|
Warning 3125 MAX_EXECUTION_TIME hint is supported by top-level standalone SELECT statements only
|
|
SELECT a FROM t1 WHERE a IN (SELECT /*+ MAX_EXECUTION_TIME(0) */ a FROM t1);
|
|
a
|
|
10
|
|
Warnings:
|
|
Warning 3125 MAX_EXECUTION_TIME hint is supported by top-level standalone SELECT statements only
|
|
SELECT * FROM t1 WHERE a IN (SELECT /*+ MAX_EXECUTION_TIME(0) */ a FROM t1);
|
|
a
|
|
10
|
|
Warnings:
|
|
Warning 3125 MAX_EXECUTION_TIME hint is supported by top-level standalone SELECT statements only
|
|
SELECT /*+ MAX_EXECUTION_TIME(0) */ * FROM t1
|
|
WHERE a IN (SELECT /*+ MAX_EXECUTION_TIME(0) */ a FROM t1);
|
|
a
|
|
10
|
|
Warnings:
|
|
Warning 3125 MAX_EXECUTION_TIME hint is supported by top-level standalone SELECT statements only
|
|
SELECT * FROM t1
|
|
WHERE a IN (SELECT a FROM t1 UNION SELECT /*+ MAX_EXECUTION_TIME(0) */ a FROM t1);
|
|
a
|
|
10
|
|
Warnings:
|
|
Warning 3125 MAX_EXECUTION_TIME hint is supported by top-level standalone SELECT statements only
|
|
SELECT /*+ MAX_EXECUTION_TIME(0) */ * FROM t1
|
|
WHERE a IN (SELECT a FROM t1 UNION SELECT /*+ MAX_EXECUTION_TIME(0) */ a FROM t1);
|
|
a
|
|
10
|
|
Warnings:
|
|
Warning 3125 MAX_EXECUTION_TIME hint is supported by top-level standalone SELECT statements only
|
|
SELECT * FROM t1 UNION SELECT /*+ MAX_EXECUTION_TIME(0) */ * FROM t1;
|
|
a
|
|
10
|
|
Warnings:
|
|
Warning 3125 MAX_EXECUTION_TIME hint is supported by top-level standalone SELECT statements only
|
|
SELECT /*+ MAX_EXECUTION_TIME(0) */ * FROM t1
|
|
UNION SELECT /*+ MAX_EXECUTION_TIME(0) */ * FROM t1;
|
|
a
|
|
10
|
|
Warnings:
|
|
Warning 3125 MAX_EXECUTION_TIME hint is supported by top-level standalone SELECT statements only
|
|
INSERT INTO t1 SELECT /*+ MAX_EXECUTION_TIME(0) */ * FROM t1;
|
|
Warnings:
|
|
Warning 3125 MAX_EXECUTION_TIME hint is supported by top-level standalone SELECT statements only
|
|
CREATE TABLE t2 AS SELECT /*+ MAX_EXECUTION_TIME(0) */ * FROM t1;
|
|
Warnings:
|
|
Warning 3125 MAX_EXECUTION_TIME hint is supported by top-level standalone SELECT statements only
|
|
CREATE TABLE t3 AS SELECT 1 A UNION SELECT 2 UNION SELECT /*+ MAX_EXECUTION_TIME(0) */ 3;
|
|
Warnings:
|
|
Warning 3125 MAX_EXECUTION_TIME hint is supported by top-level standalone SELECT statements only
|
|
CREATE TABLE /*+ MAX_EXECUTION_TIME(100) */ t4 (a int);
|
|
CREATE /*+ MAX_EXECUTION_TIME(100) */ TABLE t5 (a int);
|
|
DELETE /*+ MAX_EXECUTION_TIME(100) */ FROM t1;
|
|
Warnings:
|
|
Warning 3125 MAX_EXECUTION_TIME hint is supported by top-level standalone SELECT statements only
|
|
UPDATE /*+ MAX_EXECUTION_TIME(100) */ t1 SET a=20;
|
|
Warnings:
|
|
Warning 3125 MAX_EXECUTION_TIME hint is supported by top-level standalone SELECT statements only
|
|
ALTER TABLE /*+ MAX_EXECUTION_TIME(100) */ t1 ADD b VARCHAR(200);
|
|
ALTER /*+ MAX_EXECUTION_TIME(100) */ TABLE t1 ADD c VARCHAR(200);
|
|
SELECT /*+ MAX_EXECUTION_TIME(0) */ * FROM t1;
|
|
a b c
|
|
DROP TABLE t1, t2, t3, t4, t5;
|
|
#
|
|
# 2. Test MAX_EXECUTION_TIME value set at session level.
|
|
#
|
|
SELECT @@max_execution_time;
|
|
@@max_execution_time
|
|
0
|
|
SET @@SESSION.max_execution_time= 1000;
|
|
SELECT @@max_execution_time;
|
|
@@max_execution_time
|
|
1000
|
|
SET @@SESSION.max_execution_time= 0;
|
|
#
|
|
# 3. Test the MAX_EXECUTION_TIME option by setting value for it at,
|
|
# - STATEMENT
|
|
# - SESSION
|
|
#
|
|
SELECT /*+ MAX_EXECUTION_TIME(1000) */ SLEEP(5);
|
|
SLEEP(5)
|
|
1
|
|
SET @@SESSION.max_execution_time= 1000;
|
|
SELECT SLEEP(5);
|
|
SLEEP(5)
|
|
1
|
|
SET @@SESSION.max_execution_time= 0;
|
|
#
|
|
# 4. Test statement timeout functionality.
|
|
#
|
|
CREATE TABLE t1 (a INT, b VARCHAR(300));
|
|
INSERT INTO t1 VALUES (1, 'string');
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SET @@SESSION.max_execution_time= 2;
|
|
SELECT *, SLEEP(0.5) from t1;
|
|
ERROR HY000: Query execution was interrupted, maximum statement execution time exceeded
|
|
LOCK TABLE t1 WRITE;
|
|
SELECT * FROM t1;
|
|
ERROR HY000: Query execution was interrupted, maximum statement execution time exceeded
|
|
SELECT /*+ MAX_EXECUTION_TIME(1) */ * FROM t1;
|
|
ERROR HY000: Query execution was interrupted, maximum statement execution time exceeded
|
|
UNLOCK TABLES;
|
|
CREATE TABLE t2 SELECT * FROM t1;
|
|
ALTER TABLE t2 ADD c VARCHAR(200) default 'new_col';
|
|
UPDATE t1 SET b= 'new_string';
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
DELETE FROM t2;
|
|
#
|
|
# 5. Test SELECT with subquery.
|
|
#
|
|
SELECT /*+ MAX_EXECUTION_TIME(3600000) */ (SELECT SLEEP(0.5)) AS true_if_subquery_is_timedout;
|
|
true_if_subquery_is_timedout
|
|
0
|
|
#
|
|
# 6. Test max_execution_time with prepared statements.
|
|
#
|
|
PREPARE stmt1 FROM "SELECT *, SLEEP(0.5) FROM t1 WHERE b= 'new_string'";
|
|
PREPARE stmt2 FROM "SELECT /*+ MAX_EXECUTION_TIME(2) */ *, SLEEP(0.5) FROM t1 WHERE b= 'new_string'";
|
|
PREPARE stmt3 FROM "SELECT /*+ MAX_EXECUTION_TIME(3600000) */ count(*) FROM t1";
|
|
EXECUTE stmt1;
|
|
ERROR HY000: Query execution was interrupted, maximum statement execution time exceeded
|
|
EXECUTE stmt2;
|
|
ERROR HY000: Query execution was interrupted, maximum statement execution time exceeded
|
|
EXECUTE stmt3;
|
|
count(*)
|
|
4096
|
|
DEALLOCATE PREPARE stmt1;
|
|
DEALLOCATE PREPARE stmt2;
|
|
DEALLOCATE PREPARE stmt3;
|
|
#
|
|
# 7. Test max_execution_time with Stored Routines.
|
|
#
|
|
CREATE FUNCTION f1() RETURNS INT
|
|
BEGIN
|
|
SELECT /*+ MAX_EXECUTION_TIME(1) */ SLEEP(1.5) INTO @a;
|
|
RETURN 1;
|
|
END|
|
|
Warnings:
|
|
Warning 3125 MAX_EXECUTION_TIME hint is supported by top-level standalone SELECT statements only
|
|
DROP FUNCTION f1|
|
|
CREATE FUNCTION f1() RETURNS INT
|
|
BEGIN
|
|
SELECT SLEEP(3) INTO @a;
|
|
RETURN 1;
|
|
END|
|
|
CREATE FUNCTION f2() RETURNS INT
|
|
BEGIN
|
|
INSERT INTO t2 SELECT * FROM t2;
|
|
RETURN 1;
|
|
END|
|
|
INSERT INTO t2 VALUES (1, 'string1', 'string2');
|
|
SET @@SESSION.max_execution_time= 2;
|
|
SELECT f1();
|
|
ERROR HY000: Query execution was interrupted, maximum statement execution time exceeded
|
|
SELECT /*+ MAX_EXECUTION_TIME(60000) */ f2();
|
|
f2()
|
|
1
|
|
Warnings:
|
|
Note 3025 Select is not a read only statement, disabling timer
|
|
DROP FUNCTION f1;
|
|
DROP FUNCTION f2;
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
SELECT /*+ MAX_EXECUTION_TIME(1) */ SLEEP(1.5);
|
|
INSERT INTO t2 SELECT DISTINCT * FROM t2;
|
|
END|
|
|
Warnings:
|
|
Warning 3125 MAX_EXECUTION_TIME hint is supported by top-level standalone SELECT statements only
|
|
DROP PROCEDURE p1|
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
INSERT INTO t2 SELECT DISTINCT * FROM t2;
|
|
SELECT SLEEP(3);
|
|
END|
|
|
CALL p1();
|
|
SLEEP(3)
|
|
0
|
|
DROP PROCEDURE p1;
|
|
DROP TABLE t2;
|
|
SET @global_event_scheduler_status= @@global.event_scheduler;
|
|
SET @@global.event_scheduler= ON;
|
|
SET @@global.max_execution_time= 1000;
|
|
CREATE TABLE t2 (f1 int, f2 int);
|
|
SELECT SLEEP(5) into @a;
|
|
SELECT @a;
|
|
@a
|
|
1
|
|
CREATE EVENT event1 ON SCHEDULE AT CURRENT_TIMESTAMP
|
|
DO BEGIN
|
|
SELECT SLEEP(2) into @a;
|
|
SELECT /*+ MAX_EXECUTION_TIME(1) */ SLEEP(2) into @b;
|
|
INSERT INTO t2 VALUES(@a, @b);
|
|
END|
|
|
Warnings:
|
|
Warning 3125 MAX_EXECUTION_TIME hint is supported by top-level standalone SELECT statements only
|
|
# Wait until at least one instance of event is executed.
|
|
SELECT /*+ MAX_EXECUTION_TIME(3600000) */ * FROM t2;
|
|
f1 f2
|
|
0 0
|
|
DELETE FROM t2;
|
|
SET @@global.event_scheduler= @global_event_scheduler_status;
|
|
SET @@global.max_execution_time= 0;
|
|
CREATE TABLE t3 (f1 int)|
|
|
CREATE TRIGGER t1_before_trigger BEFORE INSERT ON t1 FOR EACH ROW
|
|
BEGIN
|
|
SELECT SLEEP(2) into @a;
|
|
SELECT /*+ MAX_EXECUTION_TIME(1) */ SLEEP(2) into @b;
|
|
INSERT INTO t3 VALUES(@a);
|
|
INSERT INTO t3 VALUES(@b);
|
|
END|
|
|
Warnings:
|
|
Warning 3125 MAX_EXECUTION_TIME hint is supported by top-level standalone SELECT statements only
|
|
DROP TRIGGER t1_before_trigger|
|
|
CREATE TRIGGER t1_before_trigger BEFORE INSERT ON t1 FOR EACH ROW
|
|
BEGIN
|
|
SELECT SLEEP(2) into @a;
|
|
INSERT INTO t3 VALUES(@a);
|
|
END
|
|
|
|
|
SELECT SLEEP(5) into @a;
|
|
SELECT /*+ MAX_EXECUTION_TIME(1000) */ SLEEP(2) into @b;
|
|
SELECT @a, @b;
|
|
@a @b
|
|
1 1
|
|
INSERT INTO t1 VALUES (1, 'string');
|
|
SELECT /*+ MAX_EXECUTION_TIME(3600000) */ * FROM t3;
|
|
f1
|
|
0
|
|
DROP TABLE t1,t2,t3;
|
|
#
|
|
# 8. Test MAX_EXECUTION_TIME precedence set at different levels.
|
|
#
|
|
SET @@SESSION.max_execution_time= 1000;
|
|
SELECT sleep(5);
|
|
sleep(5)
|
|
1
|
|
SELECT /*+ MAX_EXECUTION_TIME(20000) */ sleep(5);
|
|
sleep(5)
|
|
0
|
|
SET @@SESSION.max_execution_time= 0;
|
|
#
|
|
# 9. MAX_EXECUTION_TIME status variables.
|
|
#
|
|
SELECT CONVERT(VARIABLE_VALUE, UNSIGNED) INTO @time_set
|
|
FROM performance_schema.global_status
|
|
WHERE VARIABLE_NAME= 'MAX_EXECUTION_TIME_SET';
|
|
SELECT CONVERT(VARIABLE_VALUE, UNSIGNED) INTO @time_exceeded
|
|
FROM performance_schema.global_status
|
|
WHERE VARIABLE_NAME= 'MAX_EXECUTION_TIME_EXCEEDED';
|
|
SELECT /*+ MAX_EXECUTION_TIME(10) */ SLEEP(1);
|
|
SLEEP(1)
|
|
1
|
|
# Ensure that the counters for:
|
|
# - statements that are time limited; and
|
|
# - statements that exceeded their maximum execution time
|
|
# are incremented.
|
|
SELECT 1 AS STATUS FROM performance_schema.global_status
|
|
WHERE VARIABLE_NAME= 'MAX_EXECUTION_TIME_SET'
|
|
AND CONVERT(VARIABLE_VALUE, UNSIGNED) > @time_set;
|
|
STATUS
|
|
1
|
|
SELECT 1 AS STATUS FROM performance_schema.global_status
|
|
WHERE VARIABLE_NAME= 'MAX_EXECUTION_TIME_EXCEEDED'
|
|
AND CONVERT(VARIABLE_VALUE, UNSIGNED) > @time_exceeded;
|
|
STATUS
|
|
1
|
|
SELECT CONVERT(VARIABLE_VALUE, UNSIGNED) INTO @time_set_failed
|
|
FROM performance_schema.global_status
|
|
WHERE VARIABLE_NAME= 'MAX_EXECUTION_TIME_SET_FAILED';
|
|
SET DEBUG= '+d,thd_timer_create_failure';
|
|
SELECT /*+ MAX_EXECUTION_TIME(10) */ SLEEP(1);
|
|
SLEEP(1)
|
|
0
|
|
SET DEBUG= '-d,thd_timer_create_failure';
|
|
SELECT 1 AS STATUS FROM performance_schema.global_status
|
|
WHERE VARIABLE_NAME= 'MAX_EXECUTION_TIME_SET_FAILED'
|
|
AND CONVERT(VARIABLE_VALUE, UNSIGNED) > @time_set_failed;
|
|
STATUS
|
|
1
|
|
SELECT CONVERT(VARIABLE_VALUE, UNSIGNED) INTO @time_set_failed
|
|
FROM performance_schema.global_status
|
|
WHERE VARIABLE_NAME= 'MAX_EXECUTION_TIME_SET_FAILED';
|
|
SET DEBUG= '+d,thd_timer_set_failure';
|
|
SELECT /*+ MAX_EXECUTION_TIME(10) */ SLEEP(1);
|
|
SLEEP(1)
|
|
0
|
|
SET DEBUG= '-d,thd_timer_set_failure';
|
|
SELECT 1 AS STATUS FROM performance_schema.global_status
|
|
WHERE VARIABLE_NAME= 'MAX_EXECUTION_TIME_SET_FAILED'
|
|
AND CONVERT(VARIABLE_VALUE, UNSIGNED) > @time_set_failed;
|
|
STATUS
|
|
1
|
|
SET @@SESSION.max_execution_time= 0;
|