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:
Category:MySQL Server Severity:S4 (Feature request)
Version:5.7.13, 5.7.15 OS:Any
Assigned to: CPU Architecture:Any

[15 Oct 2016 20:19] monty solomon
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


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.


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.
[17 Oct 2016 13:18] MySQL Verification Team
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 ???
[17 Oct 2016 18:47] monty solomon
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.
[18 Oct 2016 13:27] MySQL Verification Team

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.
[19 Oct 2016 21:12] monty solomon
Please explain why "it can't work the same".

[20 Oct 2016 12:48] MySQL Verification Team
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.
[20 Oct 2016 16:00] monty solomon
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.
[20 Oct 2016 16:45] MySQL Verification Team
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.
[24 Oct 2016 20:26] monty solomon
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.

[23 Nov 2016 4:25] monty solomon
I changed this to a feature request.