Menu
I've written a stored FUNCTION that calls itself, recursively.
However when I run it in a query I get this shameless error:
Error: 1424 SQLSTATE: HY000 (ER_SP_NO_RECURSION)
Message: Recursive stored functions and triggers are not allowed.
"Not allowed"?
Right. Why don't we just disable WHILE loops also, while we're at it?
Can I enable recursive functions in any way?
I found a bug report, but are there any workarounds?
I'm running MySQL 5.1.41 on Windows XP (XAMPP Server).
582
MySQL does offer recursive common table expressions, but compared to SQL Server CTE's, they have major limitations and won't solve this problem. MySQL functions do not handle recursion at all. This article will explore all of these options.
MySQL version 5.7 does not offer such a feature.
MySQL does not allow recursive FUNCTIONs, even if you set max_sp_recursion_depth. It does allow up to 255 recursion in a PROCEDURE if you set max_sp_recursion_depth. So I recommend that you replace your function with a procedure, using an INOUT variable for the return_path. Show activity on this post.
Recursive algorithms are often inefficient for small data, due to the overhead of repeated function calls and returns. For this reason efficient implementations of recursive algorithms often start with the recursive algorithm, but then switch to a different algorithm when the input becomes small.
MySQL 5.1 supports recursive stored procedures, but not recursive functions. Quoting the docs:
Stored functions cannot be recursive.
Recursion in stored procedures is permitted but disabled by default. To enable recursion, set the
max_sp_recursion_depthserver system variable to a value greater than zero. Stored procedure recursion increases the demand on thread stack space. If you increase the value ofmax_sp_recursion_depth, it may be necessary to increase thread stack size by increasing the value ofthread_stackat server startup.
193
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
