ID: 47782 User updated by: phpbug at smithii dot com Reported By: phpbug at smithii dot com Status: Bogus Bug Type: MySQLi related Operating System: Linux, Windows PHP Version: 5.2.9, 5.3.0RC2 New Comment:
Ulf, I concur. Thank you for taking the time to file the MySQL bug reports. Best, Ross Previous Comments: ------------------------------------------------------------------------ [2009-04-29 15:28:47] u...@php.net 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 ------------------------------------------------------------------------ [2009-04-26 15:58:05] phpbug at smithii dot com j...@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 ------------------------------------------------------------------------ [2009-04-26 15:41:48] j...@php.net For calling stored procedures you have to use mysqli_multi_query. ------------------------------------------------------------------------ [2009-04-15 10:09:31] phpbug at smithii dot com 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 (3334000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000) Executing: CALL echo2(?) with '1234' inp=1234 (31323334) out=34 (3334000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000) Executing: CALL echo3(?) with '1234' inp=1234 (31323334) out=3420978 (33343230393738) ------------------------------------------------------------------------ [2009-03-26 00:08:39] phpbug at smithii dot com 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) ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=47782&edit=1
