<?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();
?>

Have you seen bug #32882 and bug #35203 ??

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.

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...

Georg (or whoever maintains mysqli nowadays), check this out. Seems like some regression bug between 5.2.3 / 5.2.4 crept in..

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

No it doesn't, I tried it with both before sending in the bug report.

$query = "CALL count_runs(".$row["id"].", ".$minyear.", ".$maxyear.")";
$db->multi_query($query) or die ("Error in query: $query. " . $db->error);

This was my query.

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

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();
?>

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;
        }

Will this bug be fixed in 5.2.5?

Thanks...

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)) {

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.

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

This bug persists through to version 5.2.5

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.