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

Answer 1

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.

Answer 2

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.

Answer 3

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.