MySQL Bugs: #10605: Stored procedure with multiple SQL prepared statements disconnects client

Bug #10605	Stored procedure with multiple SQL prepared statements disconnects client
Submitted:	12 May 2005 17:32	Modified:	10 Jun 2005 18:26
Reporter:	Dean Ellis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.6	OS:
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
A stored procedure with an input parameter, which executes some SQL prepared statements, disconnects the client after executing the procedure and SELECTing the user variable passed in as a parameter.

How to repeat:
DROP TABLE IF EXISTS t1, t2;
DROP PROCEDURE IF EXISTS p1;
DELIMITER //
CREATE PROCEDURE p1 (a INT)
 LANGUAGE SQL
 DETERMINISTIC
 BEGIN
  DECLARE rsql VARCHAR(100);
  SET @rsql = "CREATE TABLE t1 ( a INT )";
  SELECT @rsql;
  PREPARE pst FROM @rsql;
  EXECUTE pst;
  SET @rsql = NULL;
  SET @rsql = "CREATE TABLE t2 ( a INT )";
  SELECT @rsql;
  DEALLOCATE PREPARE pst;
  PREPARE pst FROM @rsql;
  EXECUTE pst;
 END//
DELIMITER ;
SET @a:=0;
CALL p1(@a);
SELECT @a;

Suggested fix:
n/a

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/25782

The patch is pushed into 5.0.8 tree.
Subject: bk commit - 5.0 tree (konstantin:1.1958) BUG#10605

ChangeSet
  1.1958 05/06/09 01:09:05 konstantin@mysql.com +3 -0
  Disable dynamic SQL in stored routines.
  This is to close Bug#10975, Bug#7115, Bug#10605
  This feature will be implemented in a future release.

Noted in 5.0.8 changelog.  Added note to prepared
statement section that SQL PS cannot be used in
stored routines.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29262