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

[12 May 2005 17: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 21: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 21: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 18:26] Paul Dubois
Noted in 5.0.8 changelog.  Added note to prepared
statement section that SQL PS cannot be used in
stored routines.
[2 Sep 2005 23: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