Bug #86444 Add a max_recursion variable to limit recursion in CTEs
Submitted: 24 May 2017 15:19 Modified: 3 Aug 2017 16:49
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any

[24 May 2017 15:19] Guilhem Bichot
Description:
We agreed in the team that we should add a global and session variable max_recursion in 8.0:
- default 1000
- when a recursive CTE does more than this number of iterations, stop and return informative error message
- value 0 means "no limit on the iterations".

This default is to protect the innocent user from making a runaway query if he forgets to add a WHERE to the recursive query block.

How to repeat:
run 
with recursive cte as(select null union all select 1 from cte)select 1 from
cte;
like if you had forgotten to add a WHERE to the recursive query block: it will run forever.
[3 Aug 2017 16:49] Paul Dubois
Posted by developer:
 
Fixed in 8.0.3.

The new cte_max_recursion_depth system variable implements a common
table expression (CTE) maximum recursion depth. The server terminates
execution of any CTE that recurses more levels than the value of this
variable.