stored-proceduresoceanbase

Recursive stored procedure in OceanBase MySQL mode fails with error 2013 (lost connection)


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


Solution

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