php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #42548 PROCEDURE xxx can't return a result set in the given context (works in 5.2.3!!)
Submitted: 2007-09-04 19:24 UTC Modified: 2008-01-01 16:51 UTC
Votes:13
Avg. Score:4.5 ± 1.1
Reproduced:12 of 13 (92.3%)
Same Version:12 (100.0%)
Same OS:5 (41.7%)
From: garethjo at usc dot edu Assigned: hholzgra
Status: Closed Package: MySQLi related
PHP Version: 5.2.4 & 5.2.5 OS: Windows XP, Windows 2003
Private report: No CVE-ID:
 [2007-09-04 19:24 UTC] garethjo at usc dot edu
Description:
------------
All stored procedure call which would return a result set (and did in the past) produces the error 

"PROCEDURE procedure.Name can't return a result set in the given context"

However the expected results are returned via command line and other mysql clients.

Reproduce code:
---------------
test code can be found at:

http://128.125.64.37/bug.zip

Expected result:
----------------
resultset returned

Actual result:
--------------
PROCEDURE procedure.Name can't return a result set in the given context

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2007-09-05 00:10 UTC] garethjo at usc dot edu
<?php
//------------ DATABASE SETUP------------------------
$mysqli = mysqli_init();
$mysqli->real_connect('localhost', 'root', 'root_pass', 'test');
if (mysqli_connect_errno()) 
{
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
$strDatabaseCreation = 'CREATE DATABASE IF NOT EXISTS test;
USE test;
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
  `intProductId` int(10) unsigned NOT NULL auto_increment,
  `strProductName` varchar(45) NOT NULL,
  `douProductPrice` double NOT NULL,
  `intQuantity` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`intProductId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `products` (`intProductId`,`strProductName`,`douProductPrice`,`intQuantity`) VALUES
 (1,\'Mugs\',10,5),
 (2,\'Boots\',75,12);

CREATE PROCEDURE `test`.`spGetProducts`()
BEGIN
  SELECT * FROM Products;
END
';
printf ("Connection: %s<br>\r\n.", $mysqli->host_info);
if($mysqli->multi_query ($strDatabaseCreation))
{
	print "Databse created successfully<br>\r\n";
}
else
{
	print "failed to create database<br>\r\n".$mysqli->error;
	$mysqli->close();
	die;
}
$mysqli->close();

//------------ BUG TEST START  --------------------------
$mysqli = mysqli_init();	
$mysqli->real_connect('localhost', 'root', 'root_pass', 'test');
if (mysqli_connect_errno())
{
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
if($mysqli->real_query ("CALL spGetProducts();"))
{
	if($objResult = $mysqli->store_result())
	{
		while($row = $objResult->fetch_assoc())
		{
			print $row["strProductName"]." ".$row["strProductName"]."<br>\r\n";
		}
		$objResult->free_result();
	}
	else
	{
		print "no results found";
	}
}
else
{	
	print $mysqli->error;
}
$mysqli->close();
?>
 [2007-09-06 22:31 UTC] jani@php.net
Have you seen bug #32882 and bug #35203 ??

 [2007-09-07 14:50 UTC] garethjo at usc dot edu
Yes I have seen those bugs and they are not what I am experiencing. Any stored procedure that would normally return a resultset whether it be the first or not produces the "PROCEDURE procedure.Name can't return a result set in the given context" error not a lost connection.  In my example code, the first query is just used to create the database table and stored procedure. So even if it is separated out and ran separately so that the bug test is in a separate file and runs by itself after the database tables and proc are created, it will still produce the same result even though it is the first proc that was run.
 [2007-09-07 18:00 UTC] al dot smith at aeschi dot ch dot eu dot org
I'm seeing this exact bug as well. Rolling back to 5.2.3 fixes the problem.

For me, executing the CALL() statement within a mysql> client session works just fine...
 [2007-09-09 10:42 UTC] jani@php.net
Georg (or whoever maintains mysqli nowadays), check this out. Seems like some regression bug between 5.2.3 / 5.2.4 crept in..
 [2007-09-11 10:00 UTC] uwendel at mysql dot com
Does using mysqli_multi_query() work for you? Currently you are using mysqli_real_query() to call the SP. See also, http://dev.mysql.com/doc/refman/5.1/en/call.html. If a stored procedure produces result sets, you must use mysqli_multi_query(). 

Ulf
 [2007-09-11 15:54 UTC] garethjo at usc dot edu
No it doesn't, I tried it with both before sending in the bug report.
 [2007-09-11 17:44 UTC] al dot smith at aeschi dot ch dot eu dot org
$query = "CALL count_runs(".$row["id"].", ".$minyear.", ".$maxyear.")";
$db->multi_query($query) or die ("Error in query: $query. " . $db->error);

This was my query.
 [2007-09-12 08:55 UTC] uwendel at mysql dot com
Your code snippets does not show proper usage of mysqli_multi_query(). 

Stored Procedures that return n result sets will return n + 1 result sets. In your case, it's two result sets to fetch and eat up before you can reuse the line. The error message from the server is exactly about that. Proper usage of mysqli_multi_query() looks like this:

if (mysqli_multi_query($link, 'CALL p()')) {
  do {
    if ($res = mysqli_store_result($link)) {
     while ($row = mysqli_fetch_assoc($res))
       var_dump($row);
     mysqli_free_result($res);
    }
 } while (mysqli_more_results($link) && mysqli_next_result($link));

} else {
  printf("Cannot call SP, [%d] %s\n", 
    mysqli_errno($link), mysqli_error($link));
}

I see you replacing mysqli_[real_]query() with mysqli_multi_query() but I do not see the more_results()/next_result() loop etc. You continue using the syntax for SPs which do not return a result set.

Ulf
 [2007-09-12 16:42 UTC] garethjo at usc dot edu
This is the example of the code from my initial bug report reworked to use the multi_query.  It uses the same database setup script as the original script in the first bug report and produces the same error:


//------------ BUG TEST START  --------------------------
$mysqli = mysqli_init();	
$mysqli->real_connect('localhost', 'root', 'root_pass', 'test');
if (mysqli_connect_errno())
{
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

if($mysqli->multi_query ("CALL spGetProducts();"))
{
	do
	{
			if($objResult = $mysqli->store_result())
			{
				while($row = $objResult->fetch_assoc())
				{
					print $row["strProductName"]." ".$row["douProductPrice"]."<br>\r\n";
				}
				$objResult->close();
				if($mysqli->more_results())
				{
					print "------------------------<BR>";
				}
	
			}
			else
			{
				print "no results found";
			}
	}while ( $mysqli->next_result());
	
}
else
{	
	print $mysqli->error;
}
$mysqli->close();
?>
 [2007-10-02 20:47 UTC] Al dot Smith at aeschi dot ch dot eu dot org
Ok, so it turns out that this bug is amazingly easy to figure out. I just started looking at changes between 5.2.3 and 5.2.4 in ext/mysqli. It turns out that there really aren't many changes in the mysqli code, and of course one of them deals with the CLIENT_MULTI_STATEMENTS flag.

Reverting this change between 5.2.3 and 5.2.4 means that 5.2.4 is once again able to do multi-queries.

Goody gumdrops.


diff -ur php-5.2.4-orig/ext/mysqli/mysqli_api.c php-5.2.4/ext/mysqli/mysqli_api.c
--- php-5.2.4-orig/ext/mysqli/mysqli_api.c      2007-07-24 11:22:16.000000000 +0200
+++ php-5.2.4/ext/mysqli/mysqli_api.c   2007-10-02 22:43:49.000000000 +0200
@@ -1433,7 +1433,7 @@
        MYSQLI_FETCH_RESOURCE(mysql, MY_MYSQL *, &mysql_link, "mysqli_link", MYSQLI_STATUS_INITIALIZED);

        /* remove some insecure options */
-       flags &= ~CLIENT_MULTI_STATEMENTS;   /* don't allow multi_queries via connect parameter */
+       flags ^= CLIENT_MULTI_STATEMENTS; /* don't allow multi_queries via connect parameter */
        if ((PG(open_basedir) && PG(open_basedir)[0] != '\0') || PG(safe_mode)) {
                flags &= ~CLIENT_LOCAL_FILES;
        }
 [2007-10-21 14:35 UTC] ajs at ictpro dot ch
Will this bug be fixed in 5.2.5?

Thanks...
 [2007-11-19 18:21 UTC] hholzgra@php.net
The change to 5.2.4 was correct, previously the CLIENT_MULTI_STATEMENTS
flag was actually inverted instead of reset, and as the default value for flags is
0 it was actually set, not reset, most of the time.

Multiple statements are only temporarily enabled using the 
MYSQL_OPTION_MULTI_STATEMENTS_ON and _OFF arguments
to  mysql_set_server_option().

The problem here is that CLIENT_MULTI_STATEMENTS in mysql_real_connects()
implicitly enables CLIENT_MULTI_RESULTS, too, but 
MYSQL_OPTION_MULTI_STATEMENTS_ON only enables multiple statements, 
*not* multiple results.

So the solution is to always remove CLIENT_MULTI_STATEMENTS on connect
but at the same time to always enable CLIENT_MULTI_RESULTS as this can't be modified later (patch against latest 5.2 CVS):

$ cvs diff -u mysqli_api.c 
Index: mysqli_api.c
===================================================================
RCS file: /repository/php-src/ext/mysqli/mysqli_api.c,v
retrieving revision 1.118.2.22.2.18
diff -u -u -r1.118.2.22.2.18 mysqli_api.c
--- mysqli_api.c        17 Oct 2007 08:19:50 -0000      1.118.2.22.2.18
+++ mysqli_api.c        19 Nov 2007 18:20:28 -0000
@@ -1438,6 +1438,8 @@
 
        MYSQLI_FETCH_RESOURCE(mysql, MY_MYSQL *, &mysql_link, "mysqli_link", MYSQLI_STATUS_INITIALIZED);
 
+       /* set some required options */
+       flags |= CLIENT_MULTI_RESULTS; /* needed for mysql_multi_query() */
        /* remove some insecure options */
        flags &= ~CLIENT_MULTI_STATEMENTS;   /* don't allow multi_queries via connect parameter */
        if ((PG(open_basedir) && PG(open_basedir)[0] != '\0') || PG(safe_mode)) {


 [2007-11-19 18:22 UTC] hholzgra@php.net
See also http://dev.mysql.com/doc/refman/5.0/en/mysql-set-server-option.html

  Enabling multiple-statement support with MYSQL_OPTION_MULTI_STATEMENTS_ON
  does not have quite the same effect as enabling it by passing the 
  CLIENT_MULTI_STATEMENTS flag to mysql_real_connect(): 
  CLIENT_MULTI_STATEMENTS also enables CLIENT_MULTI_RESULTS. 
  If you are using the CALL SQL statement in your programs, multiple-result 
  support must be enabled; this means that MYSQL_OPTION_MULTI_STATEMENTS_ON
  by itself is insufficient to allow the use of CALL.


 [2007-11-19 20:07 UTC] hholzgra@php.net
test case:

--TEST--
Bug #42548 PROCEDURE xxx can't return a result set in the given context (works in 5.2.3!!)
--SKIPIF--
<?php if (!extension_loaded("mysqli")) print "skip"; ?>
--FILE--
<?php
$mysqli = mysqli_init();
$mysqli->real_connect('localhost', 'root', '', 'test');
if (mysqli_connect_errno())
{
  printf("Connect failed: %s\n", mysqli_connect_error());
  exit();
}

$mysqli->query("DROP PROCEDURE IF EXISTS p1") or die($mysqli->error);
$mysqli->query("CREATE PROCEDURE p1() BEGIN SELECT 42; END") or die($mysqli->error);

if($mysqli->multi_query ("CALL p1();"))
{
  do
  {
    if($objResult = $mysqli->store_result())
    {
      while($row = $objResult->fetch_assoc())
      {
        var_dump($row);
      }
      $objResult->close();
      if($mysqli->more_results())
      {
        print "----- next result -----------\n";
      }
    }
    else
    {
      print "no results found";
    }
  }while ( $mysqli->next_result());
}
else
{
        print $mysqli->error;
}
$mysqli->query("DROP PROCEDURE p1") or die($mysqli->error);
$mysqli->close();
?>
--EXPECT--
array(1) {
  [42]=>
  string(2) "42"
}
----- next result -----------
no results found

 [2007-11-19 20:37 UTC] garethjo at usc dot edu
This bug persists through to version 5.2.5
 [2008-01-01 16:51 UTC] hholzgra@php.net
This bug has been fixed in CVS.

Snapshots of the sources are packaged every three hours; this change
will be in the next snapshot. You can grab the snapshot at
http://snaps.php.net/.
 
Thank you for the report, and for helping us make PHP better.


 
PHP Copyright © 2001-2014 The PHP Group
All rights reserved.
Last updated: Mon Apr 21 04:01:57 2014 UTC