Environment
OceanBase CE 4.2.1 (MySQL Mode)
Deployment: Single-node test environment
ob_server_memory_limit = 8G
I'm trying to create a recursive stored procedure in OceanBase (MySQL Mode, Community Edition 4.x), but it either fails with a lost connection error or produces incorrect results.
Here's my simplified factorial calculation example:
DELIMITER //
CREATE PROCEDURE calculate_factorial(
IN p_num INT,
OUT p_result INT
)
BEGIN
IF p_num = 1 THEN
SET p_result = 1;
ELSE
CALL calculate_factorial(p_num - 1, @temp_result);
SET p_result = p_num * @temp_result;
END IF;
END //
DELIMITER ;
-- Calling the procedure
CALL calculate_factorial(5, @result);
SELECT @result; -- Expected: 120, Actual: Error or wrong value
Problems Observed
The same code works in native MySQL 8.0.
Question
Is recursive stored procedure execution fully supported in OceanBase's MySQL mode? If yes, what configuration (e.g., memory/stack limits) is required to make it stable?
Attempt content
Write recursive stored procedures using MySQL syntax, occasionally successful within 5 layers, and disconnect directly beyond 5 layers (Error 2013);
Expected result
Should support deep recursion like MySQL 8.0;
Actual result
Connection crashes or silently returns NULL
As you claim the problem pops up with the recursion depth after it reaches a certain boundary, I suppose this probably has something to do with a system variable. I just deployed a single node oceanbase server using package oceanbase-all-in-one-4.2.1_bp11_20250402.el7.x86_64.tar.gz
. The config file is based on mini-single-example.yaml with the following changes regarding the system resources:
memory_limit: 4G
cpu_count: 4
After that, I created a procedure using EXACTLY your code.
select version();
+-------------------------------+
| version() |
+-------------------------------+
| 5.7.25-OceanBase_CE-v4.2.1.11 |
+-------------------------------+
show create procedure calculate_factorial;
+---------------------+-------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
+---------------------+-------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| calculate_factorial | STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER | CREATE DEFINER = `root`@`%` PROCEDURE `calculate_factorial`(
IN p_num INT,
OUT p_result INT
)
READS SQL DATA
BEGIN
IF p_num = 1 THEN
SET p_result = 1;
ELSE
CALL calculate_factorial(p_num - 1, @temp_result);
SET p_result = p_num * @temp_result;
END IF;
END | utf8mb4 | utf8mb4_general_ci | utf8mb4_general_ci |
+---------------------+-------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
When I first called your procedure using integer value 3, I ran into an error which suggested that the max_sp_recursion_depth variable was to blame.
CALL calculate_factorial(3, @result);
ERROR 1456 (HY000): Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine
MySQL [testdb]> show variables like 'max_sp_recursion_depth';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| max_sp_recursion_depth | 0 |
+------------------------+-------+
After setting its session value to a higher figure, everything works as intended.
set session max_sp_recursion_depth=99;
CALL calculate_factorial(3, @result);
Query OK, 0 rows affected (0.009 sec)
select @result;
+---------+
| @result |
+---------+
| 6 |
+---------+
CALL calculate_factorial(5, @result);
Query OK, 0 rows affected (0.004 sec)
select @result;
+---------+
| @result |
+---------+
| 120 |
+---------+
CALL calculate_factorial(10, @result);
Query OK, 0 rows affected (0.005 sec)
select @result;
+---------+
| @result |
+---------+
| 3628800 |
+---------+
To wrap up, apart from the max_sp_recursion_depth
issue, no other problem has emerged. So if the error persists after modifying the variable's value, please execute select version();
and give us the feedback.