From: pcdinh at gmail dot com
Operating system: Windows XP SP3
PHP version: 5.3.0beta1
PHP Bug Type: MySQLi related
Bug description: MySQLi, SP, loop count and the amount of result sets
Description:
------------
When executing a stored procedure that returns multiple result sets,
MySQLi driver always does an additional loop after all the result set are
returned.
The last loop always returns a FALSE without any error, which indicates as
if there is a query that does not return a result set in the SP. PHP Manual
does not mention about the additional special loop.
SP:
CREATE PROCEDURE foo()
BEGIN
SELECT 'foo' FROM DUAL;
SELECT 'bar', 'bar2' FROM DUAL;
END $$
Reproduce code:
---------------
<?php
$mysqli = new mysqli("localhost", "root", "123456", "test");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$sql = "
DELIMITER $$
DROP PROCEDURE IF EXISTS foo $$
CREATE PROCEDURE foo()
BEGIN
SELECT 'foo' FROM DUAL;
SELECT 'bar', 'bar2' FROM DUAL;
END $$";
$rsCount = 0;
$loopCount = 0;
$hasNext = null;
if ($mysqli->real_query("CALL foo()"))
{
do
{
$loopCount++;
if ($hasNext === null)
{
echo 'Loop begins before any check. The first result set is
available. '."\n";
}
else
{
echo "Check if there is any result set: ".(int)$hasNext." at
loop ".$loopCount.". \n";
}
$result = $mysqli->store_result();
if ($mysqli->errno > 0)
{
echo "Error: ".$mysqli->error." <<<<<<<<";
continue;
}
if (is_object($result))
{
$rsCount++;
// $result->free_result();
echo 'Result set at loop '.$loopCount.'.'."\n";
}
else
{
// No result set returns
echo 'No result set at loop '.$loopCount.'.'."\n";
}
} while ($hasNext = $mysqli->next_result()); // mysqli_next_result
always returns true if an error occured.
}
echo "Total loop: $loopCount; Total result sets: $rsCount;";
/* close connection */
$mysqli->close();
?>
Expected result:
----------------
I created a stored procedure named foo() that is expected to return 2
result sets
DELIMITER $$
DROP PROCEDURE IF EXISTS foo $$
CREATE PROCEDURE foo()
BEGIN
SELECT 'foo' FROM DUAL;
SELECT 'bar', 'bar2' FROM DUAL;
END $$
The PHP code should print out:
Loop begins before any check. The first result set is available.
Result set at loop 1.
Check if there is any result set: 1 at loop 2.
Result set at loop 2.
Total loop: 2; Total result sets: 2;
Actual result:
--------------
The PHP code prints out:
Loop begins before any check. The first result set is available.
Result set at loop 1.
Check if there is any result set: 1 at loop 2.
Result set at loop 2.
Check if there is any result set: 1 at loop 3.
No result set at loop 3.
Total loop: 3; Total result sets: 2;
--
Edit bug report at http://bugs.php.net/?id=47471&edit=1
--
Try a CVS snapshot (PHP 5.2):
http://bugs.php.net/fix.php?id=47471&r=trysnapshot52
Try a CVS snapshot (PHP 5.3):
http://bugs.php.net/fix.php?id=47471&r=trysnapshot53
Try a CVS snapshot (PHP 6.0):
http://bugs.php.net/fix.php?id=47471&r=trysnapshot60
Fixed in CVS:
http://bugs.php.net/fix.php?id=47471&r=fixedcvs
Fixed in CVS and need be documented:
http://bugs.php.net/fix.php?id=47471&r=needdocs
Fixed in release:
http://bugs.php.net/fix.php?id=47471&r=alreadyfixed
Need backtrace:
http://bugs.php.net/fix.php?id=47471&r=needtrace
Need Reproduce Script:
http://bugs.php.net/fix.php?id=47471&r=needscript
Try newer version:
http://bugs.php.net/fix.php?id=47471&r=oldversion
Not developer issue:
http://bugs.php.net/fix.php?id=47471&r=support
Expected behavior:
http://bugs.php.net/fix.php?id=47471&r=notwrong
Not enough info:
http://bugs.php.net/fix.php?id=47471&r=notenoughinfo
Submitted twice:
http://bugs.php.net/fix.php?id=47471&r=submittedtwice
register_globals:
http://bugs.php.net/fix.php?id=47471&r=globals
PHP 4 support discontinued: http://bugs.php.net/fix.php?id=47471&r=php4
Daylight Savings: http://bugs.php.net/fix.php?id=47471&r=dst
IIS Stability:
http://bugs.php.net/fix.php?id=47471&r=isapi
Install GNU Sed:
http://bugs.php.net/fix.php?id=47471&r=gnused
Floating point limitations:
http://bugs.php.net/fix.php?id=47471&r=float
No Zend Extensions:
http://bugs.php.net/fix.php?id=47471&r=nozend
MySQL Configuration Error:
http://bugs.php.net/fix.php?id=47471&r=mysqlcfg
