MySQL Bugs: #28009: SP with PREPARE needs CLIENT_MULTI_RESULTS for statements without result

Bug #28009	SP with PREPARE needs CLIENT_MULTI_RESULTS for statements without result
Submitted:	22 Apr 2007 3:01	Modified:	3 Sep 2008 16:51
Reporter:	Ondra Zizka	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.37, 5.1, 4.1	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	CLIENT_MULTI_RESULTS, prepare, prepare statement, stored procedure

Description:
A stored procedure containing SQL with prepared statement needs the flag CLIENT_MULTI_RESULTS even if it does not execute a statement returning a result.

How to repeat:
----------------
DELIMITER $$

DROP PROCEDURE IF EXISTS `TestPreparedMultiResults` $$
CREATE PROCEDURE `TestPreparedMultiResults` ()
BEGIN

  SET @sql = 'SET @a = 10';
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

END $$
DELIMITER ;
----------------

Now in PHP connect to MySQL using mysql extension and do:

    $sql = "CALL TestPreparedMultiResults()";
    $res = mysql_query($sql);

That results in:

    PROCEDURE TestPreparedMultiResults can't return a result set in the given context 

Suggested fix:
Check the prepared statement whether it contains a statement that can potentially return a result (SELECT or SHOW) and if it can't, don't set some "CLIENT_MULTI_RESULTS-needed flag".

I set Severity to S2 because this in fact blocks using PREPARE in PHP with old MySQL extension and other clients. A connection flag 131072 could be used in PHP, but not in cases where some library or framework is used, and even in the rest of cases, there is a risk of "Commands out of sync" errors.

Thus, thanks for fixing.

Thank you for the report.

Verified as described.

test case

Attachment: bug28009.c (text/plain), 1.14 KiB.

At the time the procedure is loaded it's impossible to tell whether dynamic sql statements will or will not return any result sets.
Please consider upgrading your client - or, alternatively, fixing the old MySQL extension in PHP.

This needs to be documented.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.