MySQL Bugs: #26650: Can't CREATE TEMPORARY TABLE in iterations in a stored procedure

Bug #26650	Can't CREATE TEMPORARY TABLE in iterations in a stored procedure
Submitted:	26 Feb 2007 23:10	Modified:	4 Jun 2007 21:48
Reporter:	William Chiquito	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.1 BK, 5.2-falcon	OS:	Linux (Linux, Windows)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	CREATE TABLE

Description:
Problem Create Table

How to repeat:
Create Stored Procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS `world`.`bug`$$

CREATE PROCEDURE `world`.`bug`(pid INT)
    BEGIN
	DROP TEMPORARY TABLE IF EXISTS mycity;
	CREATE TEMPORARY TABLE IF NOT EXISTS mycity (myid int(11) NOT NULL auto_increment,
					   name char(35),
					   PRIMARY KEY (myid))
	ENGINE=MEMORY SELECT name FROM city WHERE id = pid;
	SELECT myid, name FROM mycity;
    END$$

DELIMITER ;

Soon MySQL monitor:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 51
Server version: 5.0.33-community-max Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use world
Database changed

mysql> CALL bug(1);
+------+-------+
| myid | name  |
+------+-------+
|    1 | Kabul |
+------+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL bug(2);
ERROR 1054 (42S22): Unknown column 'myid' in 'field list'

Thank you for the report.

I can not repeat it with current 5.0 development sources, but can with 5.1 and 5.2-falcon development sources.

I could repeat it with 5.0.26-community-nt, 5.0.27-community-max and 5.0.33-community-max

Hello,

I can't repeat it with the latest sources:

mysql> CALL bug(1);
+------+-------+
| myid | name  |
+------+-------+
|    1 | Kabul | 
+------+-------+
1 row in set (0.36 sec)

Query OK, 0 rows affected, 1 warning (0.36 sec)

mysql> CALL bug(1);
+------+-------+
| myid | name  |
+------+-------+
|    1 | Kabul | 
+------+-------+
1 row in set (0.05 sec)

Query OK, 0 rows affected (0.06 sec)

mysql> CALL bug(1);
+------+-------+
| myid | name  |
+------+-------+
|    1 | Kabul | 
+------+-------+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> select version();
+---------------------------+
| version()                 |
+---------------------------+
| 5.0.44-valgrind-max-debug | 
+---------------------------+
1 row in set (0.01 sec)

mysql> CALL bug(1);
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: world

+------+-------+
| myid | name  |
+------+-------+
|    1 | Kabul | 
+------+-------+
1 row in set (0.08 sec)

Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> CALL bug(1);
+------+-------+
| myid | name  |
+------+-------+
|    1 | Kabul | 
+------+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL bug(1);
+------+-------+
| myid | name  |
+------+-------+
|    1 | Kabul | 
+------+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL bug(1);
+------+-------+
| myid | name  |
+------+-------+
|    1 | Kabul | 
+------+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select version();
+--------------------------------+
| version()                      |
+--------------------------------+
| 5.1.20-beta-valgrind-max-debug | 
+--------------------------------+
1 row in set (0.00 sec)

This is likely a duplicate of Bug#4968 and the family of bugs fixed by the patch for Bug#4968.

Thank you for your interest in MySQL.