ID: 42548
Updated by: [EMAIL PROTECTED]
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:
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
Previous Comments:
------------------------------------------------------------------------
[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.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-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-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-09-12 16:42:58] 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();
?>
------------------------------------------------------------------------
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
