MySQL Bugs: #94572: execute procedures in non-recursive manner but get 1444 error

Bug #94572	execute procedures in non-recursive manner but get 1444 error
Submitted:	6 Mar 2019 5:07	Modified:	8 Apr 2019 12:51
Reporter:	镇熙林	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.7.21	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	1444, prepared statement, stored routine

Description:
execute procedures in non-recursive manner but got 1444 error

How to repeat:
DROP PROCEDURE IF EXISTS `PROC_1`; 
DELIMITER $$ 
CREATE PROCEDURE `PROC_1`( 
        IN IN_PROC_NAME varchar(100), 
        OUT OUT_FLAG INT, 
        OUT OUT_MESSAGE varchar(4000) 
) 
BEGIN 
        DECLARE EXIT HANDLER FOR NOT FOUND, SQLEXCEPTION 
        BEGIN 
                ROLLBACK; 
                GET STACKED DIAGNOSTICS CONDITION 1 OUT_FLAG = MYSQL_ERRNO, OUT_MESSAGE = MESSAGE_TEXT; 
        END; 
        SET OUT_FLAG = 0; 
        SET OUT_MESSAGE = ''; 
        set @v_sql =  concat('call ', IN_PROC_NAME, '(?,?)'); 
        prepare stmt from @v_sql; 
        execute stmt using @OUT_FLAG,@OUT_MESSAGE; 
        deallocate prepare stmt; 
        SET OUT_FLAG = @OUT_FLAG; 
        SET OUT_MESSAGE = @OUT_MESSAGE; 
END 
$$ 
DELIMITER ; 

DROP PROCEDURE IF EXISTS `PROC_2`; 
DELIMITER $$ 
CREATE PROCEDURE `PROC_2`( 
        OUT OUT_FLAG      INT, 
        OUT OUT_MESSAGE   VARCHAR(4000) 
) 
BEGIN 
        DECLARE EXIT HANDLER FOR NOT FOUND, SQLEXCEPTION 
        BEGIN 
                ROLLBACK; 
                GET STACKED DIAGNOSTICS CONDITION 1 OUT_FLAG = MYSQL_ERRNO, OUT_MESSAGE = MESSAGE_TEXT; 
        END; 
        SET OUT_FLAG = 0; 
        SET OUT_MESSAGE = ''; 
        set @v_sql =  'call PROC_3 (?,?)'; 
        prepare stmt from @v_sql; 
        execute stmt using @OUT_FLAG,@OUT_MESSAGE; 
        deallocate prepare stmt; 
        SET OUT_FLAG = @OUT_FLAG; 
        SET OUT_MESSAGE = @OUT_MESSAGE; 
END 
$$ 
DELIMITER ; 

DROP PROCEDURE IF EXISTS `PROC_3`; 
DELIMITER $$ 
CREATE PROCEDURE `PROC_3`( 
        OUT OUT_FLAG      INT, 
        OUT OUT_MESSAGE   VARCHAR(4000) 
) 
BEGIN 
        DECLARE EXIT HANDLER FOR NOT FOUND, SQLEXCEPTION 
        BEGIN 
                ROLLBACK; 
                GET STACKED DIAGNOSTICS CONDITION 1 OUT_FLAG = MYSQL_ERRNO, OUT_MESSAGE = MESSAGE_TEXT; 
        END; 
        SET OUT_FLAG = 0; 
        SET OUT_MESSAGE = ''; 
END 
$$ 
DELIMITER ; 

[root@localhost][tt]> CALL PROC_1('PROC_2',@OUT_FLAG,@OUT_MESSAGE); 
Query OK, 0 rows affected (0.00 sec) 

[root@localhost][tt]> SELECT @OUT_FLAG,@OUT_MESSAGE; 
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
| @OUT_FLAG | @OUT_MESSAGE                                                                                                                                                          | 
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
|      1444 | The prepared statement contains a stored routine call that refers to that same statement. It's not allowed to execute a prepared statement in such a recursive manner | 
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
1 row in set (0.00 sec) 

if you modify 'stmt' in PROC_2 to another name, for example 'stmt1', PROC_1 can execute successfully.

Hi,

This could be due to the usage of the user variable, which you SET in the procedure.

Instead of user variable, start using local variables.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I can verify that this is still an issue in MySQL 8.

MYSQL Verification team evidently is unaware that user variables are required when executing prepared statements. I would prefer to use declared variables, but unfortunately this is not yet allowed.

It seems the team also misunderstood the issue -- that changing the prepared statement name "stmt" to "stmt1" resolves the error.

I would like to bump this thread and reiterate that it IS a bug, albeit one that very few coders would come across.

Additionally, why should recursive prepared statements be disallowed? It should be allowed just as recursion is allowed for procedures. At the very least, a feature request should be logged. Many would benefit (myself included).

Hi,

This issue has been discussed internally and it was concluded that local variable should be used. Your stored routines should not be prepared, nor should they contain prepared statement. There is simply no need for that.

Hi,

This issue has been discussed internally and it was concluded that local variable should be used. Your stored routines should not be prepared, nor should they contain prepared statement. There is simply no need for that.

Hello,

Thanks for the response.

I disagree that there is "simply no need" for calling routines via prepared statements. Why then would call statements be allowed in the first place?

I have a use case for you:

I store procedure names as varchars in a table.

I then call these procedures dynamically using prepared statements. The procedures use a similar signature -- i.e. '(?,?,?)'.

select concat('call ', export_procedure, '(?,?,?)')
into @stmt_sql
from example_table
where object_code = p_object_code;

These procedures may pass the same parameters to the dynamic procedure, but each procedure itself may perform a unique, yet similar task depending on the procedure called. In another programming language, we might call this "implementing an interface."

When I prepare this statement, I must pass user variables. MySQL does not allow me to pass declared variables...

prepare stmt from @stmt_sql;
execute stmt using @user_id, @settings, @object_id;
deallocate prepare stmt;

If, when calling my dynamic procedure, I would like to create another prepared statement inside of it, I would not be able to prepare it using the same name ("stmt" in the example above). As the original poster proves, the procedures need not even be recursive - though I would argue that should be allowed as well.

Hi,

You can accomplish all that with local variables and without prepared statements. You should familiarise yourself more with full syntax of our stored routines.

For example, you can call different procedures with CASE ...... WHEN ...... constructs.

In any case, you are using local variables where in a manner with which they are not supposed to be used at all. This is in compliance with SQL standard, chapter 4.

I would prefer to use declared local variables in prepared statements, but the language does not allow for this.

And using case statements to accomplish this task becomes unmanageable when the table has a large number of cases... this is the whole point of having data.

I am requesting the following features be added. They would make MySQL more compliant with chapter 4, and more useful as well. Take it or leave it.

1) Allow declared local variables in prepare and execute statements.
2) Allow prepared statement recursion.

Thanks for your consideration.

J