mirror of
https://github.com/MariaDB/server.git
synced 2025-04-06 07:15:33 +02:00
118 lines
4.7 KiB
Text
118 lines
4.7 KiB
Text
#
|
|
# MAX_EXECUTION_TIME hint testing
|
|
#
|
|
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;
|
|
# Correct hint usage
|
|
SELECT /*+ MAX_EXECUTION_TIME(10) */* FROM t1 a, t1 b;
|
|
ERROR 70100: Query execution was interrupted (max_statement_time exceeded)
|
|
EXPLAIN EXTENDED SELECT /*+ MAX_EXECUTION_TIME(000149) */* FROM t1;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 512 100.00
|
|
Warnings:
|
|
Note 1003 select /*+ MAX_EXECUTION_TIME(000149) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`
|
|
SELECT /*+ MAX_EXECUTION_TIME(20) */ *, SLEEP(1) FROM t1 UNION SELECT 1, 2, 3;
|
|
ERROR 70100: Query execution was interrupted (max_statement_time exceeded)
|
|
(SELECT /*+ MAX_EXECUTION_TIME(30) */ *, SLEEP(1) FROM t1) UNION (SELECT 1, 2, 3);
|
|
ERROR 70100: Query execution was interrupted (max_statement_time exceeded)
|
|
((SELECT /*+ MAX_EXECUTION_TIME(50) */ *, SLEEP(1) FROM t1));
|
|
ERROR 70100: Query execution was interrupted (max_statement_time exceeded)
|
|
# Make sure the hint overrides global/session/statement settings.
|
|
# Global setting 30 seconds, won't be exceeded for sure
|
|
SET @@max_statement_time = 30;
|
|
SELECT /*+ MAX_EXECUTION_TIME(10) */* FROM t1 a, t1 b;
|
|
ERROR 70100: Query execution was interrupted (max_statement_time exceeded)
|
|
# Session setting 10 seconds, too long to be exceeded
|
|
SET SESSION max_statement_time = 10;
|
|
SELECT /*+ MAX_EXECUTION_TIME(15) */* FROM t1 a, t1 b;
|
|
ERROR 70100: Query execution was interrupted (max_statement_time exceeded)
|
|
SET STATEMENT max_statement_time = 20 FOR
|
|
SELECT /*+ MAX_EXECUTION_TIME(5) */* FROM t1 a, t1 b;
|
|
ERROR 70100: Query execution was interrupted (max_statement_time exceeded)
|
|
# Check that prepared statements process the hint correctly
|
|
PREPARE s FROM 'SELECT /*+ MAX_EXECUTION_TIME(20) */ seq, SLEEP(1) FROM seq_1_to_10';
|
|
EXECUTE s;
|
|
ERROR 70100: Query execution was interrupted (max_statement_time exceeded)
|
|
EXECUTE s;
|
|
ERROR 70100: Query execution was interrupted (max_statement_time exceeded)
|
|
# Hint duplication
|
|
SELECT /*+ MAX_EXECUTION_TIME(10) MAX_EXECUTION_TIME(100) */ count(*) FROM t1;
|
|
count(*)
|
|
512
|
|
Warnings:
|
|
Warning 4211 Hint MAX_EXECUTION_TIME(100) is ignored as conflicting/duplicated
|
|
# Wrong values
|
|
SELECT /*+ MAX_EXECUTION_TIME(0) */ count(*) FROM t1;
|
|
count(*)
|
|
512
|
|
Warnings:
|
|
Warning 1912 Incorrect value '0' for option 'MAX_EXECUTION_TIME'
|
|
SELECT /*+ MAX_EXECUTION_TIME(-1) */ count(*) FROM t1;
|
|
count(*)
|
|
512
|
|
Warnings:
|
|
Warning 1064 Optimizer hint syntax error near '-1) */ count(*) FROM t1' at line 1
|
|
SELECT /*+ MAX_EXECUTION_TIME(4294967296) */ count(*) FROM t1;
|
|
count(*)
|
|
512
|
|
Warnings:
|
|
Warning 1912 Incorrect value '4294967296' for option 'MAX_EXECUTION_TIME'
|
|
|
|
# only SELECT statements supports the MAX_EXECUTION_TIME hint (warning):
|
|
|
|
CREATE TABLE t2 (i INT);
|
|
INSERT /*+ MAX_EXECUTION_TIME(10) */ INTO t2 SELECT 1;
|
|
Warnings:
|
|
Warning 4172 'MAX_EXECUTION_TIME(10)' is not allowed in this context
|
|
REPLACE /*+ MAX_EXECUTION_TIME(15) */ INTO t2 SELECT 1;
|
|
Warnings:
|
|
Warning 4172 'MAX_EXECUTION_TIME(15)' is not allowed in this context
|
|
UPDATE /*+ MAX_EXECUTION_TIME(23) */ t2 SET i = 1;
|
|
Warnings:
|
|
Warning 4172 'MAX_EXECUTION_TIME(23)' is not allowed in this context
|
|
DELETE /*+ MAX_EXECUTION_TIME(5000) */ FROM t2 WHERE i = 1;
|
|
Warnings:
|
|
Warning 4172 'MAX_EXECUTION_TIME(5000)' is not allowed in this context
|
|
# Not supported inside stored procedures/functions
|
|
CREATE PROCEDURE p1() BEGIN SELECT /*+ MAX_EXECUTION_TIME(10) */ count(*) FROM t1 a, t1 b
|
|
INTO @a; END|
|
|
CALL p1();
|
|
Warnings:
|
|
Warning 4172 'MAX_EXECUTION_TIME(10)' is not allowed in this context
|
|
DROP PROCEDURE p1;
|
|
# Hint in a subquery is not allowed (warning):
|
|
SELECT 1 FROM (SELECT /*+ MAX_EXECUTION_TIME(10) */ 1) a;
|
|
1
|
|
1
|
|
Warnings:
|
|
Warning 4172 'MAX_EXECUTION_TIME(10)' is not allowed in this context
|
|
# Hint is allowed only for the first select of UNION (warning):
|
|
SELECT /*+ MAX_EXECUTION_TIME(20) */ count(*) FROM t1
|
|
UNION
|
|
SELECT /*+ MAX_EXECUTION_TIME(30) */ count(*) FROM t1;
|
|
count(*)
|
|
512
|
|
Warnings:
|
|
Warning 4211 Hint MAX_EXECUTION_TIME(30) is ignored as conflicting/duplicated
|
|
SELECT count(*) FROM t1
|
|
UNION
|
|
SELECT /*+ MAX_EXECUTION_TIME(30) */ count(*) FROM t1;
|
|
count(*)
|
|
512
|
|
Warnings:
|
|
Warning 4172 'MAX_EXECUTION_TIME(30)' is not allowed in this context
|
|
# Check that hint actually works:
|
|
SELECT /*+ MAX_EXECUTION_TIME(20) */ count(*), SLEEP(1) FROM t1
|
|
UNION
|
|
SELECT count(*), SLEEP(1) FROM t1;
|
|
ERROR 70100: Query execution was interrupted (max_statement_time exceeded)
|
|
DROP TABLE t1, t2;
|