MySQL Bugs: #79071: Extend MAX_EXECUTION_TIME optimizer hint to work for all statements

Bug #79071	Extend MAX_EXECUTION_TIME optimizer hint to work for all statements
Submitted:	2 Nov 2015 12:13	Modified:	16 Jul 2021 11:04
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	mariadb 10.1.2, max_statement_time

Description:
MySQL 5.7 provides a new facility to limit the execution time of a SELECT query.
See: https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html

e.g. a hint such as /*+ MAX_EXECUTION_TIME(1000) */

How to repeat:
However, this does not work on other types of statements such as DELETE / UPDATE, or INSERT / IODKU where the statement may be followed by a WHERE clause and if the query plan is wrong the statement may run for longer than expected.

It also does not work for DDL changes where the statement may be blocked by usage from another statement user etc.

Suggested fix:
So generally it would be good to extend this feature to work with _ANY_ statement. If it's a DML type operation and the tables involved are transactional the statement should be rolled back if not completed in the requested time.

For other statements, or statements on tables which are not transactional, interrupting the statement may not be safe, so perhaps an option to only do this "where it's safe" may be appropriate and that should be the default configuration.  DDL changes which are blocked by another user/thread would simply fail and not be executed.

Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0

Hello Simon,

Thank you for the feature request!

Thanks,
Umesh