MySQL Bugs: #110417: Stored Procedures call locking

Bug #110417	Stored Procedures call locking
Submitted:	17 Mar 2023 18:47	Modified:	20 Mar 2023 12:55
Reporter:	Luca Scomparin	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Stored procedure locks erroneously a temporary table create inside it and report "Cannot reopen table"

How to repeat:
Call this instruction 2 times:

CALL TestA('', nzi(0));
-- results: ok
CALL TestA('', nzi(0));
-- results: ERROR: Error Code: 1137 Can't reopen table: 'o_table'

The temporary table o_table is DROPPED and RECREATED INSIDE a STORED PROCEDURE called inside the FIRST ONE.

NOTE: the error seems to be link to the INITIAL CALLING INSTRUCTION: if the stored function NZI is removed the stored doesn't report the error:
CALL TestA('', nzi(0)); -> 2nd time: ERROR 1137 Can't reopen table: 'o_table'
CALL TestA('', 0); -> 2nd time: NO ERROR FOREVER

THE STORED PROCEDURES INSTRUCTIONS:

CREATE PROCEDURE `TestA`(_valore CHAR(20), _idazione INT)
BEGIN
  CALL Test_Init();
  INSERT INTO o_table(campo) VALUES (_valore);
  CALL TestB();
END

CREATE PROCEDURE `Test_init`()
BEGIN
DROP TEMPORARY TABLE IF EXISTS o_table;
CREATE TEMPORARY TABLE o_table(
campo CHAR(20),
PRIMARY KEY (campo)) ;
END

CREATE PROCEDURE `TestB`()
BEGIN
INSERT INTO o_table(campo) VALUES (123);
END

CREATE FUNCTION nzi(_test INT) RETURNS INT DETERMINISTIC
    BEGIN
RETURN _test;
    END$$

Hi Mr. Scomparin,

Thank you for your bug report.

However, you have not set the attributes of all of your stored routines so that changing table can be expected.

Please, read our Reference Manual.

Our Reference Manual describes many restrictions imposed to stored routines, by SQL standards and hence by MySQL.

Among others:

Statements that perform explicit or implicit commit or rollback, like INSERT.
A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

Not a bug.