MySQL does not support recursive functions? why? since when?

Question

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).

Einsteinium

A database is for retrieving data, not for programming. Is there some reason you're trying to do complex, difficult-to-predict-or-optimize logic in a stored procedure instead of in your application? – Billmyre

#3438611 – Southwest

A shameless error! There is a way you can enable recursive functions; you have to modify the MySQL code to make them work. – Antennule

Which MySQL code? Don't ask me to make a custom build of MySQL Server. – Einsteinium

Answer 1

No problem, Jenco. Not so efficient as PostgreSQL functions, but it's possible in MySQL procedures also:

DELIMITER $$
DROP PROCEDURE IF EXISTS test.factorial_proc$$
CREATE PROCEDURE test.factorial_proc
(
   IN n   BIGINT, 
  OUT res BIGINT 
) 
BEGIN
  SET max_sp_recursion_depth=10; 
  IF n >= 2 THEN
    CALL test.factorial_proc (n-1, res);
    SELECT n * res INTO res;
  ELSE
    SELECT n INTO res;
  END IF;
END$$
DELIMITER ;

[test]> CALL test.factorial_proc (5, @res);
[test]> CALL test.factorial_proc (5, @res1);
[test]> select @res * @res1;
+--------------+
| @res * @res1 |
+--------------+
|        14400 |
+--------------+

Sergei Zaytsev.

Ixtle

Answer 2

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_depth server system variable to a value greater than zero. Stored procedure recursion increases the demand on thread stack space. If you increase the value of max_sp_recursion_depth, it may be necessary to increase thread stack size by increasing the value of thread_stack at server startup.

Pomfret

I would still like a solution using FUNCTIONS because I have a recursive method that calls itself expecting return values. If I used a PROCEDURE there's no way I'll be able to do that... would I? – Einsteinium

@Jenko: Anything that can be done using recursion can be rewritten using iteration: #932262 – Pomfret

Answer 3

Probably recursion in stored routines is discouraged because MySQL needs to limit its threads' stack size.

MySQL typically uses one thread per connection. 100s or 1000s of connections are common.

On 32-bit platforms, there is significant address-space pressure when running 1,000 threads, so the stacks need to be set very small to avoid address-space exhaustion.

Stack overflow is, of course, very bad - it can't be recovered from safely. So I think MySQL does this to prevent stack overflows, especially on 32-bit platforms.

That said, anyone using a 32-bit OS for a production MySQL server nowadays is insane.

Firooc

"Stack overflow is, of course, very bad - it can't be recovered from safely" this is outright wrong. ALL reasonable higher-level programming languages manage to recover safely from stack overflows, such as Java, Perl, Python, ... – Sphygmoid