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
Tags: stored function

[22 Sep 2006 3:47] Lachlan Mulcahy
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.
[22 Sep 2006 6:32] Valerii Kravchuk
Thank you for a problem report. Verified just as described, also with 5.0.26-BK on Linux.
[20 Oct 2006 8:31] Konstantin Osipov
Marc, this is likely a dup of the prelocking bug you have.
[8 Jan 2007 22:38] Marc Alff
For reference, the related pre-locking bug is Bug#8407.
[7 Mar 2007 1:06] Marc Alff
This bug has been fixed with Bug#8407.
[7 Mar 2007 22:04] Konstantin Osipov
Pushed into 5.0.38 and 5.1.17.
[9 Mar 2007 3:21] Jon Stephens
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.