MySQL Bugs: #83394: MAX_EXECUTION_TIME optimizer hint value inconsistent with system variable

Bug #83394	MAX_EXECUTION_TIME optimizer hint value inconsistent with system variable
Submitted:	15 Oct 2016 20:19	Modified:	23 Nov 2016 4:25
Reporter:	monty solomon	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.7.13, 5.7.15	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When the system variable MAX_EXECUTION_TIME value is set to 0 it means no timeout but when the optimizer hint MAX_EXECUTION_TIME value is set to 0 it means ignore the hint instead of no timeout.

Statements need to use an optimizer hint like MAX_EXECUTION_TIME(999999) instead of MAX_EXECUTION_TIME(0) for no timeout.

It is documented to work this way but it is inconsistent and not intuitive.

How to repeat:
mysql> SELECT /*+ MAX_EXECUTION_TIME(0) */ ... \G
ERROR 1317 (70100): Query execution was interrupted

http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_execution_t...

The execution timeout for SELECT statements, in milliseconds. If the value is 0, timeouts are not enabled.

The global max_execution_time value provides the default for the session value for new connections. The session value applies to SELECT executions executed within the session that include no MAX_EXECUTION_TIME(N) optimizer hint or for which N is 0.

https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html#optimizer-hints-execution-tim...

The MAX_EXECUTION_TIME(N) hint sets a statement execution timeout of N milliseconds. If this option is absent or N is 0, the statement timeout established by the max_execution_time system variable applies. 

Suggested fix:
Change the behavior of the MAX_EXECUTION_TIME optimizer hint value to be consistent with the value of the MAX_EXECUTION_TIME system variable.

Hi Monty,

You have stated well, this hint works as documented. Hence, it is not a bug, but it could be a feature request. However, here we hit upon the problem. Every query takes more then 0 seconds, hance setting MAX_EXECUTION_TIME to 0, with the effect as you describe it, means that every single query should be killed as soon as it starts.

Is that what you were desiring ???

No, the default setting of 0 for the system variable means infinite (if the value is 0, timeouts are not enabled). I was expecting the value set in the optimizer hint to work identically to the value set in the system variable.

Hi!

No, it does not work the same, as it can't work the same. Hence, we documented it properly, so that confusion does not arise.

Thank you for your report.

Please explain why "it can't work the same".

Thanks.

It can not work the same as this is the only way to turn timeout off. Using the large value as a parameter would not turn that part of code off, hence CPU cycles would be wasted.

It sounds like you agree with me. Setting the hint value to 0 does not turn the timeout off. A large hint value needs to be use to effectively get around the timeout.

No, I do not agree. As our manual says:

" If the value is 0, timeouts are not enabled."

Hence, they are disabled. That saves some CPU time.

Also, a considerate number of our customers has this value at 0. Last thing that we would like to do is force them to change their numerous applications.

I think you are conflating the system variable and the hint. Your most recent comment was about the system variable. I was writing about the optimizer hint.

I was advocating changing the hint so that a value of zero for the hint worked the same as a value of zero for the system variable.

In the current implementation, when using a non-zero value of the system variable to enable the max execution time it can not be disabled by using a hint value of zero. In order to disable the max execution time setting the hint value needs to be some arbitrary large value. If that value is not large enough then the query will get interrupted anyway.

There should be a mechanism to disable the max execution time via the hint. A value of zero for the hint should work the same as a value of zero for the system variable.

Thanks.

I changed this to a feature request.