from:"phpbug at smithii dot com"

#47782 [Bgs]: Anomalous results from stored procedure with a PREPAREd statement

2009-04-29 Thread phpbug at smithii dot com

 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
(3334)
Executing: CALL echo2(?) with '1234'
inp=1234 (31323334)
out=34
(3334)
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

#47782 [Bgs]: Anomalous results from stored procedure with a PREPAREd statement

2009-04-26 Thread phpbug at smithii dot com

 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:

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


Previous Comments:


[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
(3334)
Executing: CALL echo2(?) with '1234'
inp=1234 (31323334)
out=34
(3334)
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  â¦ (636404)

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

#47782 [Opn]: Anomalous results from stored procedure with a PREPAREd statement

2009-04-15 Thread phpbug at smithii dot com

 ID:   47782
 User updated by:  phpbug at smithii dot com
 Reported By:  phpbug at smithii dot com
 Status:   Open
 Bug Type: MySQLi related
-Operating System: *
+Operating System: Linux, Windows
-PHP Version:  5.2.9
+PHP Version:  5.2.9, 5.3.0RC2
 New Comment:

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
(3334)
Executing: CALL echo2(?) with '1234'
inp=1234 (31323334)
out=34
(3334)
Executing: CALL echo3(?) with '1234'
inp=1234 (31323334)
out=3420978 (33343230393738)


Previous Comments:


[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  â¦ (636404)

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  â¦ (636404)





-- 
Edit this bug report at http://bugs.php.net/?id=47782edit=1

#47782 [NEW]: Anomalous results from stored procedure with a PREPAREd statement

2009-03-25 Thread phpbug at smithii dot com

From: phpbug at smithii dot com
Operating system: Windows XP SP3  Debian 4.0
PHP version:  5.2.9
PHP Bug Type: MySQLi related
Bug description:  Anomalous results from stored procedure with a PREPAREd 
statement

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  â¦ (636404)

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  â¦ (636404)

-- 
Edit bug report at http://bugs.php.net/?id=47782edit=1
-- 
Try a CVS snapshot (PHP 5.2):
http://bugs.php.net/fix.php?id=47782r=trysnapshot52
Try a CVS snapshot (PHP 5.3):
http://bugs.php.net/fix.php?id=47782r=trysnapshot53
Try a CVS snapshot (PHP 6.0):
http://bugs.php.net/fix.php?id=47782r=trysnapshot60
Fixed in CVS:
http://bugs.php.net/fix.php?id=47782r=fixedcvs
Fixed in CVS and need be documented: 
http://bugs.php.net/fix.php?id=47782r=needdocs
Fixed in release:
http://bugs.php.net/fix.php?id=47782r=alreadyfixed
Need backtrace:  
http://bugs.php.net/fix.php?id=47782r=needtrace
Need Reproduce Script:   
http://bugs.php.net/fix.php?id=47782r=needscript
Try newer version:   
http://bugs.php.net/fix.php?id=47782r=oldversion
Not developer issue: 
http://bugs.php.net/fix.php?id=47782r=support
Expected behavior:   
http://bugs.php.net/fix.php?id=47782r=notwrong
Not enough info: 
http://bugs.php.net/fix.php?id=47782r=notenoughinfo
Submitted twice: 
http://bugs.php.net/fix.php?id=47782r=submittedtwice
register_globals:
http://bugs.php.net/fix.php?id=47782r=globals
PHP 4 support discontinued:  http://bugs.php.net/fix.php?id=47782r=php4
Daylight Savings:http://bugs.php.net/fix.php?id=47782r=dst
IIS Stability:   
http://bugs.php.net/fix.php?id=47782r=isapi
Install GNU Sed: 
http://bugs.php.net/fix.php?id=47782r=gnused
Floating point limitations:  
http://bugs.php.net/fix.php?id=47782r=float
No Zend Extensions:  
http://bugs.php.net/fix.php?id=47782r=nozend
MySQL Configuration Error:   
http://bugs.php.net/fix.php?id=47782r=mysqlcfg

#47782 [Bgs]: Anomalous results from stored procedure with a PREPAREd statement

#47782 [Bgs]: Anomalous results from stored procedure with a PREPAREd statement

#47782 [Opn]: Anomalous results from stored procedure with a PREPAREd statement

#47782 [NEW]: Anomalous results from stored procedure with a PREPAREd statement

4 matches

Site Navigation

Mail list logo

Footer information