| Bug #19008 | Stored procedure + Concat + Prepare results in garbled data | ||
|---|---|---|---|
| Submitted: | 11 Apr 2006 13:32 | Modified: | 21 Jul 2006 16:25 |
| Reporter: | Dhruba Bandopadhyay | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.22 | OS: | Linux (Linux) |
| Assigned to: | CPU Architecture: | Any | |
[11 Apr 2006 14:47]
Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with mysql command line client and: prepare stmt1 from 'call dummy()'; execute stmt1; Will you get the same garbled results?
[11 Apr 2006 14:55]
Dhruba Bandopadhyay
I tried that and the output is fine. On MySQL command line and on query browser it works. However through that PDO code it becomes garbled. Thanks for your help.
[11 Apr 2006 15:08]
Valeriy Kravchuk
So, it is not MySQL server-related problem. Please, specify the exact PHP+PDO versions used. Please, upload that garbled results as a separate file also.
[11 Apr 2006 15:20]
Dhruba Bandopadhyay
phpinfo() (will give versions)
Attachment: phpinfo.php.html (text/html), 54.44 KiB.
[11 Apr 2006 15:22]
Dhruba Bandopadhyay
Garbled web page (note garble changes on every page reload)
Attachment: garble.php.html (text/html), 131.29 KiB.
[11 Apr 2006 15:39]
Dhruba Bandopadhyay
I'm using PHP 5.1.2 and PDO-MYSQL driver 1.0.1. The garbled attachment includes colourised output due to the use of the xdebug extension which is not part of the problem as I've tried disabling it and still the same thing happens.
[11 Apr 2006 19:32]
Dhruba Bandopadhyay
Is this due to charset or collation? I've spent 24 hours trying all kinds of different variations of charset and collation settings but I've not been able to resolve this issue so I doubt that is the problem.
[11 Apr 2006 23:42]
Wez Furlong
The output lengths are not updated when using the prepared statement API.
The following script highlights the problem using mysqli.
if ($stmt->prepare("CALL dummy()")) {
$stmt->execute();
$stmt->bind_result($name, $value);
while ($stmt->fetch()) {
echo $name, " ", $value, "\n";
}
}
[3 May 2006 5:31]
Thomas Madej
I'm having (or had) the same problem with a prepared statements and a LONGTEXT field. I am also using PHP 5.1 and MySQL 5.0.18. I saw Dhruba Bandopadhyay's website since I was having the same problem, which brought me here. I read the comment on output lengths not being updated so I figured I would play around with the schema of the table. I switched the field from LONGTEXT to TEXT and it resolved the problem. I hope this helps. I would be happy to provide more information.
[17 May 2006 21:15]
Valeriy Kravchuk
What is the value of @components variable used? Is that a query to some table with a LONGTEXT filed? Complete test case is needed. Please, try to repeat with a newer version of MySQLserver, 5.0.21, and inform about the results. In any case, the idea of preparing call to procedure with prepare statement inside seems strange and useless for me...
[17 May 2006 21:23]
Dhruba Bandopadhyay
Hi. Thanks for your feedback. I cannot comment on what Thomas Madej has said as I'm not using TEXT or LONGTEXT fields. However, I will test on 5.0.21 and get back to you. In the meantime I would like to point out in response to your comment that this happens even if you don't do prepare() in PHP and do query() instead. Whether or not it makes sense to do two prepares is not the issue here but rather that it returns garbled data. Wez Furlong is the main PDO developer and his comment confirms that this is a bug in the mysql client library and is reproducible in both mysqli and pdo. I will post results with latest mysql in the next day or so.
[18 May 2006 8:46]
Dhruba Bandopadhyay
I can confirm that this does indeed happen with latest versions of all the tools specifically including PHP 5.1.4 and MySQL 5.0.21. I have also reproduced it using both PDO and MySQLi (mysqli example taken from Wez Furlong's comment).
[1 Jun 2006 14:08]
Dhruba Bandopadhyay
Happens with 5.0.22 as well.
[14 Jun 2006 14:16]
Valeriy Kravchuk
Sorry, but I still do not understand how exactly to repeat the behaviour described. Dhruba Bandopadhyay: What is the value of @components variable used? All reporters: Complete test case is needed. Better based on C API directly, but with all CREATE TABLE, INSERT, SET etc. statements to repeat.
[1 Jul 2006 9:40]
Dhruba Bandopadhyay
I'm not quite sure what you are asking about @components. @components is set to simply the query string of 'SHOW VARIABLES'. Then it is prepared and executed. I've not added any placeholders in there for simplicity's sake.
[21 Jul 2006 16:25]
Valeriy Kravchuk
Sorry, but this is not a bug. It is documented limitation. According to the manual (http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html): "The multiple-statement capability can be used with mysql_query() or mysql_real_query(). It cannot be used with the prepared statement interface. Prepared statement handles are defined to work only with strings that contain a single statement." In case of SP call, we have multiple statements, in essence. Even with one SELECT inside SP, we heve result for this select and for CALL dummy() itself. This does not work with prepared statements. You'll get something like: openxs@suse:~/dbs/5.0> ./19008 mysql_stmt_bind_result() failed Prepared statement contains no metadata for C API prepared 'CALL dummy()", or garbage results in PHP etc (because metadata are not updated).
[21 Jul 2006 16:28]
Valeriy Kravchuk
One more quote (http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statements.html): "The following statements can be used as prepared statements: CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, and most SHOW statements. Other statements are not supported in MySQL 5.0."
[17 Oct 2007 13:09]
Kevin Smith
I think this is still a bug. The following SP works fine on the command line, but when called through PHP via mysqli comes back with garbled data, and the data is all stored in one field.
Environment: MySQL 5.0.27 and PHP 5.1.6 on Fedora Core 6
CREATE PROCEDURE `ADMIN_GlobalSearch`(IN pExactMatch TINYINT, IN pSite VARCHAR(50), IN pCompanyName VARCHAR(255), IN pUserName VARCHAR(20), IN pSurname VARCHAR(50))
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
COMMENT ''
BEGIN
DECLARE iSQLFields VARCHAR(1000);
DECLARE iSQLJoins VARCHAR(1000);
DECLARE iSQLWhereClause VARCHAR(1000);
DECLARE iSQLGroupBy VARCHAR(1000);
SET iSQLFields = 'SELECT U.USER_FORENAME, U.USER_SURNAME, C.COMPANY_NAME, A.ADDRESS_TOWN, A.ADDRESS_POST_CODE, U.USER_ID, C.COMPANY_ID, U.ADDRESS_ID, U.USERNAME, U.USER_PASSWORD, U.USER_EMAIL, U.USER_TEL, U.USER_POSITION, U.USER_DISABLED, U.USER_CUT_OFF, U.USER_UPDATED, U.USER_ADMIN_LEVEL, U.USER_FAX ';
SET iSQLJoins = 'FROM ADDRESS A INNER JOIN SITE_SCHEME SS ON (A.ADDRESS_ID = SS.ADDRESS_ID) INNER JOIN USER U ON (SS.USER_ID = U.USER_ID) INNER JOIN COMPANY C ON (U.COMPANY_ID = C.COMPANY_ID) ';
SET iSQLWhereClause = 'WHERE 0 = 0 ';
IF pExactMatch = 1 THEN
IF pSite <> '' THEN
SET iSQLWhereClause = CONCAT(iSQLWhereClause, 'AND A.ADDRESS_SITE_REFERENCE = ''', pSite, ''') ');
END IF;
IF pCompanyName <> '' THEN
SET iSQLWhereClause = CONCAT(iSQLWhereClause, 'AND MATCH (COMPANY_NAME) AGAINST (''', pCompanyName, ''') ');
END IF;
IF pUserName <> '' THEN
SET iSQLWhereClause = CONCAT(iSQLWhereClause, 'AND MATCH (USERNAME) AGAINST (''', pUserName, ''') ');
END IF;
IF pSurname <> '' THEN
SET iSQLWhereClause = CONCAT(iSQLWhereClause, 'AND MATCH (USER_SURNAME) AGAINST (''', pSurname, ''') ');
END IF;
ELSE
IF pSite <> '' THEN
SET iSQLWhereClause = CONCAT(iSQLWhereClause, 'AND A.ADDRESS_SITE_REFERENCE LIKE ''%', pSite, '%'' ');
END IF;
IF pCompanyName <> '' THEN
SET iSQLWhereClause = CONCAT(iSQLWhereClause, 'AND C.COMPANY_NAME LIKE ''%', pCompanyName, '%'' ');
END IF;
IF pUserName <> '' THEN
SET iSQLWhereClause = CONCAT(iSQLWhereClause, 'AND U.USERNAME LIKE ''%', pUserName, '%'' ');
END IF;
IF pSurname <> '' THEN
SET iSQLWhereClause = CONCAT(iSQLWhereClause, 'AND U.USER_SURNAME LIKE ''%', pSurname, '%'' ');
END IF;
END IF;
SET iSQLGroupBy = 'GROUP BY U.USER_FORENAME, U.USER_SURNAME, C.COMPANY_NAME, A.ADDRESS_TOWN, A.ADDRESS_POST_CODE, U.USER_ID, C.COMPANY_ID, U.ADDRESS_ID, U.USERNAME, U.USER_PASSWORD, U.USER_EMAIL, U.USER_TEL, U.USER_POSITION, U.USER_DISABLED, U.USER_CUT_OFF, U.USER_UPDATED, U.USER_ADMIN_LEVEL, U.USER_FAX';
SET @iSQL = CONCAT(iSQLFields,iSQLJoins,iSQLWhereClause,iSQLGroupBy);
PREPARE stmt1 FROM @iSQL;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END;
Garbled data from PHP is:
Array
(
[0] => Array
(
[USER_FORENAME] => henHaqProAcumen CHP Ltd (TEST)Henley-on-Thames & pooRG9 1TM4234001426stevefespasssteve.h2@proacumen.co
)
)
[31 Oct 2007 3:25]
James Addison
I concur with the previous poster - this is still a bug, but I suspect it is a bug in PHP, not MySQL.
[31 Oct 2007 3:29]
James Addison
Just to add on some additional information as to why this is an issue - this other mysql bug: http://bugs.mysql.com/bug.php?id=11918 is what is triggering this problem. Solving one problem introduces this problem.
Description: This problem only occurs when using a stored procedure in conjunction with prepare. If one of them is omitted then the data is returned fine otherwise it is garbled. I have reproduced it on a few machines. How to repeat: SQL: 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: Several pages of garbled data that looks like it is in the wrong character set. I'm not sure of the cause.