Bug #8766 Stored procedures: Can't CREATE and access a table inside a stored prcoedure

• Submitted: 24 Feb 2005 3:35
• Modified: 3 Aug 2005 6:56
• Reporter: Trudy Pelzer
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.3-alpha-debug
• OS: Linux (SuSE 9.1)
• Assigned to: Assigned Account
• CPU Architecture: Any

[24 Feb 2005 3:35] Trudy Pelzer

Description:
Until recently, it was possible to create a table and manipulate
it within a stored procedure. Now, it is no longer possible to
do this -- although one can still create a table within a stored
procedure, one can no longer do anything with that table.
Although the CREATE PROCEDURE statements returns OK,a 
CALL of the resulting procedure returns an error saying the 
table does not exist. This is not true however -- accessing 
the table shows that it really was created, but further work
on that table from within the stored procedure is not possible.

The MySQL Reference Manual says we do this; the SQL
Standard says we should do this; other DBMSs do this.

NOTE: This is probably related to Bug#8765.

How to repeat:
mysql> delimiter //
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists t1//
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure sp1() begin create table t1 (col1 int); insert into t1 values (10); end//
Query OK, 0 rows affected (0.00 sec)

mysql> call sp1()//
ERROR 1146 (42S02): Table 'tp.t1' doesn't exist
-- This is a misleading error message; the table has been created.

mysql> insert into t1 values(10)//
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1//
Empty set (0.00 sec)
-- This shows that the table really was created, but that the
INSERT statement from the stored procedure failed.

[24 Feb 2005 4:34] MySQL Verification Team

Thank you for the bug report

[29 Apr 2005 12:31] Per-Erik Martin

The part about the table being created after all is not repeatable. (See BUG#8765).

[3 Aug 2005 6:53] Sergey Petrunya

Fixed by fix for BUG#11126