Bug #10664 Truncate in stored procedure returns table does not exist

• Submitted: 16 May 2005 11:40
• Modified: 19 May 2005 15:32
• Reporter: Giles McArdell
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.4
• OS: Windows (Windows/Linux)
• Assigned to: Assigned Account
• CPU Architecture: Any

[16 May 2005 11:40] Giles McArdell

Description:
Using the Truncate statement in a stored Procedure causes a 'table does not exist' error.

This may only occur if the table does not exist when the procedure is called (IE for a table created by the procedure itself)

May be linked to bug 10285.

How to repeat:
drop procedure if exists sp_test;
drop table if exists t;
delimiter //
create procedure sp_test()
begin
     drop table if exists t;
     create temporary table t (val int);
     insert t (val) values (1);
     truncate t;
end//
delimiter ;

call sp_test();

Suggested fix:
I though this could be fixed by making sure the table existed before calling the stored procedure as in :-
drop procedure if exists sp_test;
drop table if exists t;
create temporary table t (val int);
delimiter //
create procedure sp_test()
begin
     drop table if exists t;
     create temporary table t (val int);
     insert t (val) values (1);
     truncate t;
end//
delimiter ;

call sp_test();

But this causes mysql to stop, so I will be adding 2 bug reports today!

[16 May 2005 16:43] MySQL Verification Team

Thank you for the bug report.

[19 May 2005 15:32] Geert Vanderkelen

A handler should take care of eventual warnings or errors that occur in a stored procedure: http://dev.mysql.com/doc/mysql/en/declare-handlers.html

But it should continue even with the handler.

Duplicate of:

http://bugs.mysql.com/bug.php?id=10285
http://bugs.mysql.com/bug.php?id=9227