php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #56949 Stored procedure + Prepare + PDO returns garbled data
Submitted: 2006-04-11 09:40 UTC Modified: 2008-07-25 19:03 UTC
From: dhrubab at gmail dot com Assigned:
Status: Closed Package: PDO_MYSQL (PECL)
PHP Version: 5.1.2 OS: Linux (Gentoo, Debian)
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: dhrubab at gmail dot com
New email:
PHP Version: OS:

 

 [2006-04-11 09:40 UTC] dhrubab at gmail dot com
Description:
------------
I'm using PHP 5.1.2&5.1.3-RC2, MySQL 5.0.19 and PECL-PDO-MYSQL-1.0.1.  All packages are latest.
MY CONFIGURE LINE:

'./configure' '--prefix=/usr/lib/php5' '--sysconfdir=/etc' '--cache-file=./config.cache' '--disable-cli' '--with-apxs2=/usr/sbin/apxs2' '--with-config-file-path=/etc/php/apache2-php5' '--with-config-file-scan-dir=/etc/php/apache2-php5/ext-active' '--without-pear' '--disable-bcmath' '--with-bz2=shared' '--enable-calendar=shared' '--with-curl=shared' '--with-curlwrappers=shared' '--disable-dbase' '--enable-exif=shared' '--without-fbsql' '--without-fdftk' '--disable-filepro' '--enable-ftp=shared' '--with-gettext=shared' '--without-gmp' '--disable-hash' '--without-hwapi' '--without-iconv' '--without-informix' '--disable-ipv6' '--without-kerberos' '--enable-mbstring=shared' '--with-mcrypt=shared' '--disable-memory-limit' '--without-mhash' '--without-ming' '--without-msql' '--without-mssql' '--with-ncurses=shared' '--with-openssl' '--with-openssl-dir=/usr' '--enable-pcntl=shared' '--disable-pdo' '--without-pgsql' '--with-pspell=shared' '--without-recode' '--disable-shmop' '--without-snmp' '--enable-soap=shared' '--enable-sockets=shared' '--without-sybase' '--without-sybase-ct' '--disable-sysvmsg' '--disable-sysvsem' '--disable-sysvshm' '--with-tidy=shared' '--enable-wddx=shared' '--with-xmlrpc=shared' '--with-xsl=shared' '--with-zlib=shared' '--disable-debug' '--without-cdb' '--without-db4' '--without-flatfile' '--without-gdbm' '--without-inifile' '--without-qdbm' '--with-freetype-dir=/usr' '--with-t1lib=/usr' '--disable-gd-jis-conv' '--enable-gd-native-ttf' '--with-jpeg-dir=/usr' '--with-png-dir=/usr' '--without-xpm-dir' '--with-gd' '--with-imap=shared' '--with-imap-ssl' '--with-mysql=shared,/usr/lib/mysql' '--with-mysql-sock=/var/run/mysqld/mysqld.sock' '--with-mysqli=shared,/usr/bin/mysql_config' '--with-readline' '--without-libedit' '--without-mm' '--enable-sqlite-utf8'

Reproduce code:
---------------
SQL CODE:

DELIMITER $
DROP PROCEDURE IF EXISTS `dummy` $
CREATE PROCEDURE `dummy`()
BEGIN
	SET @components = 'SHOW VARIABLES';
	PREPARE query FROM @components;
	EXECUTE query;
END $
DELIMITER ;

PHP+PDO CODE:

$dbh = new PDO('mysql:host=localhost;dbname=blah', 'blah', 'blah');
$stm = $dbh->prepare('CALL dummy()');
$stm->execute();
var_dump($stm->fetchAll());

END RESULT:

Returns several pages of garbled text that is not readable.  I am not sure of the cause.  This only happens when using a prepare inside a stored procedure.  It also happens both on web and cli.  If you need anything else please let me know.

Expected result:
----------------
EXPECTED RESULT:

I expected to see exactly the same data as when you do SHOW VARIABLES on the mysql command line.  The stored procedure works on mysql command line and mysql query browser so I've narrowed it down to PDO.

Actual result:
--------------
The actual result is just garbled text that looks like it is in the wrong charset or collation but I've no idea what is causing it.

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2006-04-11 11:08 UTC] wez@php.net
Try the latest 5.1.x snapshot from http://snaps.php.net
 [2006-04-11 12:06 UTC] dhrubab at gmail dot com
Hi.  I've tried the latest extensions of PDO and PDO_MYSQL from the latest snapshot and run my php script both on command line and on web.  It still returns garbled output.  Does it do the same for you?
 [2006-04-11 12:11 UTC] dhrubab at gmail dot com
This works with mysqli but not with PDO-MYSQL.  Here is a link to the mysql bug report.

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

It doesn't work with the latest snapshot of PDO and PDO-MYSQL.
 [2006-04-11 12:54 UTC] akorthaus at web dot de
I can reproduce this issue, for an example output look at: http://bugs.mysql.com/file.php?id=3335 

It has nothing to do with mysql I think, because it works fine with ext/mysqli and mysql commandline client.

If I replace 

$stm = $dbh->prepare('CALL dummy()');
$stm->execute();

with

$stm = $dbh->query('CALL dummy()');

I get exactly the same output. This is not limited to this special stored procedure, happens with every SP I tried!

If I try it on my terminal, I get Array entries like that:

198 =>
  ▒⎼⎼▒≤ (
    'V▒⎼␋▒␉┌␊ ┼▒└␊' => NULL,
    0 => NULL,
    'V▒┌┤␊' => '└␊␍ └┤├␊│␊⎽OFF%⎽┤├50┌/└≤⎽─┌5.⎽⎺␌┐┼␍␊├⎽/?0?KϬ????␍␉114450 2',
    1 => '└␊␍ └┤├␊│␊⎽OFF%⎽┤├50┌/└≤⎽─┌5.⎽⎺␌┐┼␍␊├⎽/?0?KϬ????␍␉114450 2',
  ),

I tested with php5.1-200604091030 snapshot.

(you only need a MySQL 5 DB to reproduce this, no tables needed for this SP...)
 [2006-04-11 12:59 UTC] dhrubab at gmail dot com
I've tried the full latest php snapshot just now and the problem is still there.
 [2006-04-11 13:47 UTC] wez@php.net
Sounds like something is causing the mysql client to convert the data into another character set.
Please check related options and environmental variables that apply.
 [2006-04-11 15:13 UTC] dhrubab at gmail dot com
Hi.  This works with mysqli but not with pdo-mysql.  I've spent 24 hours trying every different variation in both /etc/mysql/my.cnf and setting charsets and collations using SET queries through PDO and nothing has resolved this issue.  Try and see if you can reproduce it.  Everyone I've asked to reproduce this problem has been able to on Linux.  Even on this bug two people have reported the same issue.  The mysql command line and the mysql query browser return correct results and as mentioned previously mysqli also returns the correct output.  It is only PDO_MySQL.  Kindly investigate.   Many thanks for your help.
 [2006-04-11 15:41 UTC] dhrubab at gmail dot com
The other thing that says to me that the problem is not charset related is that this only happens when there is a prepare inside the stored procedure.  If you do a straight select inside an SP that works.  If you do a prepare in PDO and do a select without an SP that works.  However, if you do a prepare inside an SP that doesn't work with pdo_mysql although does work with cmd line mysql, query browser and mysqli.
 [2006-04-11 19:31 UTC] wez@php.net
This appears to be another situation where mysql gives back bogus values for the column lengths when using native prepared statements.

The suggested workaround using the current snapshot is to turn on the PDO prepared statement emulator:

$db = new PDO('mysql:dbname=test', 'root', '');
$db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
$stm = $db->prepare('CALL dummy()');
$stm->execute();
var_dump($stm->fetchAll());
 [2006-04-11 19:41 UTC] wez@php.net
This script reproduces the problem using mysqli:

$db = new mysqli("localhost", "root", "", "test");

$stmt = $db->stmt_init();
if ($stmt->prepare("CALL dummy()")) {
    $stmt->execute();
    $stmt->bind_result($name, $value);
    while ($stmt->fetch()) {
        echo $name, " ", $value, "\n";
    }
}

This is a mysql client library bug.
 [2007-10-30 22:33 UTC] php at scottisheyes dot com
This is NOT a bogus bug, and I am not certain that it is in the MySQL codebase at all.

I think it is likely PHP based, as if I use the SQLyog mysql GUI client to call the stored procedure in question, there are no problems displaying the results, but if I use PHP in any form to manipulate/show the results, it's all garbled.

The reason WHY I need to use prepared statements inside of a stored procedure?  See this bug here:

http://bugs.mysql.com/bug.php?id=11918
 [2008-07-25 15:19 UTC] nagaev dot maksim at gmail dot com
Hey guys!

I'm using Zend Framework and facing the same problem. Somewhere in the deep Internet I found how to disable use of PREPARE statements by PDO MySQL. 

(works for me)
$db->setAttribute(PDO::MYSQL_ATTR_DIRECT_QUERY, 1);

This helps because of this:

http://shayanzadeh.com/2007/02/08/stored-procedures-with-dynamic-queries-in-mysql-through-php/
 [2008-07-25 19:03 UTC] dhrubab at gmail dot com
Closing.
 
PHP Copyright © 2001-2021 The PHP Group
All rights reserved.
Last updated: Sat Sep 25 22:03:39 2021 UTC