Bug #11803 lost connection if call statements in store procedure

• Submitted: 8 Jul 2005 2:55
• Modified: 8 Jul 2005 3:33
• Reporter: Nguyen Anh
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: Distrib 5.0.7-beta
• OS: Linux (RedHat Enterprise Linux 3)
• CPU Architecture: Any

[8 Jul 2005 2:55] Nguyen Anh

Description:
I could preprare 1 statement and execute in store procedure. However, with 2 statements in store procedure i got error: "ERROR 2013 (HY000): Lost connection to MySQL server during query"

How to repeat:
mysql> DELIMITER $$
mysql> CREATE PROCEDURE `adsl`.`test`(IN user varchar(20),IN pass varchar(20))
    -> BEGIN
    -> SET @sql_stmt = 'CREATE TABLE IF NOT EXISTS testtable (username varchar(20) not null default \'\',password varchar(20) not null default \'\' )';
    -> PREPARE stmt FROM @sql_stmt;
    -> EXECUTE stmt;
    -> DEALLOCATE PREPARE stmt;
    -> SET @sql_stmt1 = CONCAT('INSERT INTO testtable (username,password) VALUES(\'',user,'\',\'',pass,'\')');
    -> PREPARE stmt1 FROM @sql_stmt1;
    -> EXECUTE stmt1;
    -> DEALLOCATE PREPARE stmt1;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> call test('123','123');
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

[8 Jul 2005 3:33] MySQL Verification Team

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 bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

PREPARE is not more allowed in stored procedures:

miguel@hegel:/share/dbs/5.0$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.10-beta-debug

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

mysql> DELIMITER $$
mysql> CREATE PROCEDURE `adsl`.`test`(IN user varchar(20),IN pass varchar(20))
    -> BEGIN
    -> SET @sql_stmt = 'CREATE TABLE IF NOT EXISTS testtable (username
    '> varchar(20) not null default \'\',password varchar(20) not null default \'\')';
    -> PREPARE stmt FROM @sql_stmt;
    -> EXECUTE stmt;
    -> DEALLOCATE PREPARE stmt;
    ->  SET @sql_stmt1 = CONCAT('INSERT INTO testtable (username,password)
    '> VALUES(\'',user,'\',\'',pass,'\')');
    -> PREPARE stmt1 FROM @sql_stmt1;
    -> EXECUTE stmt1;
    -> DEALLOCATE PREPARE stmt1;
    -> END$$
ERROR 1314 (0A000): PREPARE is not allowed in stored procedures
mysql>