#42548 [Asn]: PROCEDURE xxx can't return a result set in the given context (works in 5.2.3!!)
ID: 42548
User updated by: garethjo at usc dot edu
Reported By: garethjo at usc dot edu
Status: Assigned
Bug Type: MySQLi related
Operating System: Windows XP, Windows 2003
-PHP Version: 5.2.4
+PHP Version: 5.2.4 & 5.2.5
Assigned To: georg
New Comment:
This bug persists through to version 5.2.5
Previous Comments:
[2007-11-19 20:07:46] [EMAIL PROTECTED]
test case:
--TEST--
Bug #42548 PROCEDURE xxx can't return a result set in the given context
(works in 5.2.3!!)
--SKIPIF--
--FILE--
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 18:22:01] [EMAIL PROTECTED]
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 18:21:19] [EMAIL PROTECTED]
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.c17 Oct 2007 08:19:50 -
1.118.2.22.2.18
+++ mysqli_api.c19 Nov 2007 18:20:28 -
@@ -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-10-21 14:35:13] ajs at ictpro dot ch
Will this bug be fixed in 5.2.5?
Thanks...
[2007-10-02 20:47:37] 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-
#42548 [Asn]: PROCEDURE xxx can't return a result set in the given context (works in 5.2.3!!)
ID: 42548
User updated by: garethjo at usc dot edu
Reported By: garethjo at usc dot edu
Status: Assigned
Bug Type: MySQLi related
Operating System: Windows XP, Windows 2003
PHP Version: 5.2.4
Assigned To: georg
New Comment:
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"]."\r\n";
}
$objResult->close();
if($mysqli->more_results())
{
print "";
}
}
else
{
print "no results found";
}
}while ( $mysqli->next_result());
}
else
{
print $mysqli->error;
}
$mysqli->close();
?>
Previous Comments:
[2007-09-12 08:55:24] 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-11 17:44:25] 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-11 15:54:04] garethjo at usc dot edu
No it doesn't, I tried it with both before sending in the bug report.
[2007-09-11 10:00:30] 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-09 10:42:13] [EMAIL PROTECTED]
Georg (or whoever maintains mysqli nowadays), check this out. Seems
like some regression bug between 5.2.3 / 5.2.4 crept in..
The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
http://bugs.php.net/42548
--
Edit this bug report at http://bugs.php.net/?id=42548&edit=1
#42548 [Asn]: PROCEDURE xxx can't return a result set in the given context (works in 5.2.3!!)
ID: 42548 User updated by: garethjo at usc dot edu Reported By: garethjo at usc dot edu Status: Assigned Bug Type: MySQLi related Operating System: Windows XP, Windows 2003 PHP Version: 5.2.4 Assigned To: georg New Comment: No it doesn't, I tried it with both before sending in the bug report. Previous Comments: [2007-09-11 10:00:30] 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-09 10:42:13] [EMAIL PROTECTED] 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-07 18:00:17] 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-07 14:50:18] 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-06 22:31:42] [EMAIL PROTECTED] Have you seen bug #32882 and bug #35203 ?? The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at http://bugs.php.net/42548 -- Edit this bug report at http://bugs.php.net/?id=42548&edit=1
#42548 [Fbk->Opn]: PROCEDURE xxx can't return a result set in the given context
ID: 42548
User updated by: garethjo at usc dot edu
Reported By: garethjo at usc dot edu
-Status: Feedback
+Status: Open
Bug Type: MySQLi related
Operating System: Windows XP, Windows 2003
PHP Version: 5.2.4
New Comment:
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.
Previous Comments:
[2007-09-06 22:31:42] [EMAIL PROTECTED]
Have you seen bug #32882 and bug #35203 ??
[2007-09-05 00:10:23] garethjo at usc dot edu
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\r\n.", $mysqli->host_info);
if($mysqli->multi_query ($strDatabaseCreation))
{
print "Databse created successfully\r\n";
}
else
{
print "failed to create database\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"]."\r\n";
}
$objResult->free_result();
}
else
{
print "no results found";
}
}
else
{
print $mysqli->error;
}
$mysqli->close();
?>
[2007-09-04 19:24:08] 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
--
Edit this bug report at http://bugs.php.net/?id=42548&edit=1
#42548 [Fbk->Opn]: PROCEDURE xxx can't return a result set in the given context
ID: 42548
User updated by: garethjo at usc dot edu
Reported By: garethjo at usc dot edu
-Status: Feedback
+Status: Open
Bug Type: MySQLi related
Operating System: Windows XP, Windows 2003
PHP Version: 5.2.4
New Comment:
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\r\n.", $mysqli->host_info);
if($mysqli->multi_query ($strDatabaseCreation))
{
print "Databse created successfully\r\n";
}
else
{
print "failed to create database\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"]."\r\n";
}
$objResult->free_result();
}
else
{
print "no results found";
}
}
else
{
print $mysqli->error;
}
$mysqli->close();
?>
Previous Comments:
[2007-09-04 22:34:09] [EMAIL PROTECTED]
Thank you for this bug report. To properly diagnose the problem, we
need a short but complete example script to be able to reproduce
this bug ourselves.
A proper reproducing script starts with ,
is max. 10-20 lines long and does not require any external
resources such as databases, etc. If the script requires a
database to demonstrate the issue, please make sure it creates
all necessary tables, stored procedures etc.
Please avoid embedding huge scripts into the report.
[2007-09-04 19:24:08] 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
--
Edit this bug report at http://bugs.php.net/?id=42548&edit=1
#42548 [NEW]: PROCEDURE xxx can't return a result set in the given context
From: garethjo at usc dot edu Operating system: Windows XP, Windows 2003 PHP version: 5.2.4 PHP Bug Type: MySQLi related Bug description: PROCEDURE xxx can't return a result set in the given context 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 -- Edit bug report at http://bugs.php.net/?id=42548&edit=1 -- Try a CVS snapshot (PHP 4.4): http://bugs.php.net/fix.php?id=42548&r=trysnapshot44 Try a CVS snapshot (PHP 5.2): http://bugs.php.net/fix.php?id=42548&r=trysnapshot52 Try a CVS snapshot (PHP 6.0): http://bugs.php.net/fix.php?id=42548&r=trysnapshot60 Fixed in CVS: http://bugs.php.net/fix.php?id=42548&r=fixedcvs Fixed in release: http://bugs.php.net/fix.php?id=42548&r=alreadyfixed Need backtrace: http://bugs.php.net/fix.php?id=42548&r=needtrace Need Reproduce Script:http://bugs.php.net/fix.php?id=42548&r=needscript Try newer version:http://bugs.php.net/fix.php?id=42548&r=oldversion Not developer issue: http://bugs.php.net/fix.php?id=42548&r=support Expected behavior:http://bugs.php.net/fix.php?id=42548&r=notwrong Not enough info: http://bugs.php.net/fix.php?id=42548&r=notenoughinfo Submitted twice: http://bugs.php.net/fix.php?id=42548&r=submittedtwice register_globals: http://bugs.php.net/fix.php?id=42548&r=globals PHP 3 support discontinued: http://bugs.php.net/fix.php?id=42548&r=php3 Daylight Savings: http://bugs.php.net/fix.php?id=42548&r=dst IIS Stability:http://bugs.php.net/fix.php?id=42548&r=isapi Install GNU Sed: http://bugs.php.net/fix.php?id=42548&r=gnused Floating point limitations: http://bugs.php.net/fix.php?id=42548&r=float No Zend Extensions: http://bugs.php.net/fix.php?id=42548&r=nozend MySQL Configuration Error:http://bugs.php.net/fix.php?id=42548&r=mysqlcfg
