Description:
------------
Using MySQL 5.0.77, and calling a stored procedure with a PREPAREd
statement, execute()/bind_result()/fetch() return anomalous results.

Reproduce code:
---------------
Using MySQL 5.0.77, and calling any stored procedure with a PREPAREd
statement, such as:

DROP PROCEDURE IF EXISTS echo;
DELIMITER //
CREATE PROCEDURE echo(p VARCHAR(255))
BEGIN
    SET @sql = CONCAT('SELECT ',QUOTE(p));
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DROP PREPARE stmt;
END;
//
DELIMITER ;

via this script:

<?php
$mysqli = new mysqli('localhost', 'root', '', 'test');
$sql = 'CALL echo(?)';
$s = $mysqli->prepare($sql);
$i = $argv[1];
printf("i=%s\n", $i);
$s->bind_param('s', $i);
$s->execute();
$s->bind_result($o);
while ($s->fetch()) {
   printf("o=%s (%s)\n", $o, bin2hex($o));
}
$s->close();

produces anomalous results at least 50% of the time. For example:

$ php echo.php abcd
i=abcd
o=cd  ♦ (6364000004)

If I remove the PREPAREd statement:

DROP PROCEDURE IF EXISTS echo;
DELIMITER //
CREATE PROCEDURE echo(p VARCHAR(255))
BEGIN
    SELECT p;
END;
//
DELIMITER ;

everything works fine.

Replacing execute()/bind_result()/fetch(), with query()/fetch_assoc()
also fixes the issue.

Other details:

mysqli_get_client_info=5.0.51a
mysqli_get_client_version=50051
mysqli_get_server_info=5.0.77-community-nt
mysqli_get_server_version=50077
mysqli_get_host_info=localhost via TCP/IP
mysqli_get_proto_info=10

Expected result:
----------------
$ php echo.php abcd
i=abcd
o=abcd (63646566)

Actual result:
--------------
$ php echo.php abcd
i=abcd
o=cd  ♦ (6364000004)

The following script produces errors on 5.2.9 and 5.3.0RC2, on both
Linux and Windows:

<?php
$mysqli = new mysqli('localhost', 'root', '', 'test');
if (!$mysqli) die(mysqli_connect_error());
$sqls[] = <<<EOT
CREATE PROCEDURE echo0(p VARCHAR(255)) 
BEGIN 
	SELECT p; 
END
EOT;
$sqls[] = <<<EOT
CREATE PROCEDURE echo1(p VARCHAR(255))
BEGIN
	SET @sql = CONCAT('SELECT ', QUOTE(p));
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DROP PREPARE stmt;
END
EOT;
$sqls[] = <<<EOT
CREATE PROCEDURE echo2(p VARCHAR(255))
BEGIN
	PREPARE stmt FROM 'SELECT ?';
	SET @p = p;
	EXECUTE stmt USING @p;
	DROP PREPARE stmt;
END
EOT;
$sqls[] = <<<EOT
CREATE PROCEDURE echo3(p VARCHAR(255))
BEGIN
	PREPARE stmt FROM 'SELECT 1234';
	EXECUTE stmt;
	DROP PREPARE stmt;
END
EOT;
$inp = strval($argv[1]);
foreach ($sqls as $i => $sql) {
	$mysqli->query("DROP PROCEDURE IF EXISTS echo$i");
	$mysqli->query($sql) || die($mysqli->error);
	$sql = "CALL echo$i(?)";
	printf("Executing: %s with '%s'\n", $sql, $inp);
	$s = $mysqli->prepare($sql);
	if (!$s) die($mysqli->error);
	printf("inp=%s (%s)\n", $inp, bin2hex($inp));
	$s->bind_param('s', $inp) || die($mysqli->error);
	$s->execute() || die($mysqli->error);
	$s->bind_result($out) || die($mysqli->error);
	while ($s->fetch()) {
	   printf("out=%s (%s)\n", $out, bin2hex($out));
	}
	$s->close();
}

Here's the script's output:

Executing: CALL echo0(?) with '1234'
inp=1234 (31323334)
out=1234 (31323334)
Executing: CALL echo1(?) with '1234'
inp=1234 (31323334)
out=34                                                
(33340000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000)
Executing: CALL echo2(?) with '1234'
inp=1234 (31323334)
out=34                                                
(33340000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000)
Executing: CALL echo3(?) with '1234'
inp=1234 (31323334)
out=3420978 (33343230393738)

For calling stored procedures you have to use mysqli_multi_query.

jani@php.net: I can call stored procedures just fine with
execute()/bind_result()/fetch(). This is the method the Zend Framework
uses, and I don't want to rewrite their classes. I'm sure there are many
other PHP frameworks that use these functions as well.

Also, the mysqli_multi_query() function does not allow me to prepare() a
statement once, and then execute() it multiple times.

The only problem I'm having, and it's still a problem with PHP 5.3 and
MySQL 5.1, is that if the stored procedure contains a PREPAREd
statement, then the fetch() function is returning corrupted data.

This sure seems to me to be an actual bug, and not just that I'm using
the wrong function call.

Therefore, please mark this bug as open.

I will attempt to fix this myself. Can you point me to any documentation
or IRC channel, that would help me to get started?

Thanks for any assistance,

Ross

Uuupps, accidently changed the Status?

I am quite sure this is not an API issue. Using Prepared Statements with
CALL should be possible a late MySQL 5.0 beta. However, your bug report
made me test some things using the C-API. I ended up filing two MySQL
Server bugs:

http://bugs.mysql.com/bug.php?id=44521
http://bugs.mysql.com/bug.php?id=44495

I suggest we close this report. Most likely you have hit a server issue
and not PHP problem.

Ulf

Ulf, I concur. Thank you for taking the time to file the MySQL bug
reports.

Best,

Ross