Bug #10605 Stored procedure with multiple SQL prepared statements disconnects client
Submitted: 12 May 2005 19:32 Modified: 10 Jun 2005 20:26
Reporter: Dean Ellis
Status: Closed
Category:Server Severity:S2 (Serious)
Version:5.0.6 OS:
Assigned to: Konstantin Osipov Target Version:

[12 May 2005 19:32] Dean Ellis 
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
[8 Jun 2005 23:11] Bugs System 
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
[8 Jun 2005 23:54] Konstantin Osipov 
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.
[10 Jun 2005 20:26] Paul DuBois 
Noted in 5.0.8 changelog.  Added note to prepared
statement section that SQL PS cannot be used in
stored routines.
[3 Sep 2005 1:13] Bugs System 
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