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
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• OS: Any
• 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.