|  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
 [2006-04-11 09:40 UTC] dhrubab at gmail dot com
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.

'./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:

	SET @components = 'SHOW VARIABLES';
	PREPARE query FROM @components;
	EXECUTE query;


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


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:

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.


Add a Patch

Pull Requests

Add a Pull Request


AllCommentsChangesGit/SVN commitsRelated reports
 [2006-04-11 11:08 UTC]
Try the latest 5.1.x snapshot from
 [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.

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: 

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 = $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]
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]
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_EMULATE_PREPARES, true);
$stm = $db->prepare('CALL dummy()');
 [2006-04-11 19:41 UTC]
This script reproduces the problem using mysqli:

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

$stmt = $db->stmt_init();
if ($stmt->prepare("CALL dummy()")) {
    $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:
 [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:
 [2008-07-25 19:03 UTC] dhrubab at gmail dot com
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sun May 19 22:01:32 2024 UTC