MySQL Bugs: #22580: DROP TABLE in nested stored procedure causes strange dependency error

Bug #22580	DROP TABLE in nested stored procedure causes strange dependency error
Submitted:	22 Sep 2006 3:47	Modified:	9 Mar 2007 3:21
Reporter:	Lachlan Mulcahy	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.26-BK, 5.0.24a	OS:	Any (Any)
Assigned to:	Marc ALFF	CPU Architecture:	Any
Tags:	stored function

Description:
When nesting some procedures within a trigger on a table, a false dependancy error is thrown due to the DROP TABLE command.

How to repeat:
Run the SQL below on an empty test database and observe the error upon the final INSERT statement

ERROR 1146 (42S02): Table 'test.tmp1' doesn't exist

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT, b INT);

DROP PROCEDURE IF EXISTS USETMPTABLE;
DELIMITER |
CREATE PROCEDURE USETMPTABLE()
BEGIN

DROP TABLE IF EXISTS tmp1;
CREATE TEMPORARY TABLE tmp1 (a INT);

DROP TABLE tmp1;
END|
DELIMITER ;

DROP PROCEDURE if exists TRIGGEREDPROCEDURE;
DELIMITER |
CREATE PROCEDURE TRIGGEREDPROCEDURE()
BEGIN
CALL USETMPTABLE();
END|
DELIMITER ;

DELIMITER |
CREATE TRIGGER t1bu BEFORE UPDATE ON t1
FOR EACH ROW 
BEGIN
CALL TRIGGEREDPROCEDURE();
END|
DELIMITER ;

INSERT INTO t1 VALUES (1,1);

Note: Interestingly I found that if I dumped the test database structure out and reloaded it again that the INSERT would no longer cause the error. Perhaps it is something specific to the way/order in which they are created?

Also, removing the DROP TABLE commands or changing them to DROP TEMPORARY TABLE would not cause the error.

Suggested fix:
I believe in general that the DROP TABLE commands should not cause the dependancy error that they do in the example above.

Thank you for a problem report. Verified just as described, also with 5.0.26-BK on Linux.

Marc, this is likely a dup of the prelocking bug you have.

For reference, the related pre-locking bug is Bug#8407.

This bug has been fixed with Bug#8407.

Pushed into 5.0.38 and 5.1.17.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.38 and 5.1.17 changelogs.