|
|
php.net | support | documentation | report a bug | advanced search | search howto | statistics | random bug | login |
[2005-12-24 03:15 UTC] deadman_great at mail dot ru
Description:
------------
Current script works fine on localhost (winxp), and throw errors on remote server (rh-fedora).
Software:
Local: winxp-sp2,php-5.1.1,mysql-5.0.3
Remote: fedora-c2,php-5.1.1,mysql-5.0.9
Reproduce code:
---------------
$query = 'SELECT `created`, to_days(from_unixtime(`created`)) as `day`,count(`id`) as `count` FROM `orders` WHERE `partner`=:partner AND `created`>=:date GROUP BY `day`';
$vars = array(':partner'=>9,':date'=>1132797644);
$stm = $pdo->Prepare($query);
$result = $stm->Execute($vars);
foreach ($result as $day) // LINE-123
{
...
}
Expected result:
----------------
Rows.
Actual result:
--------------
Warning: main() [function.main]: SQLSTATE[HY000]: General error: 2050 in ... on line 123
PatchesPull RequestsHistoryAllCommentsChangesGit/SVN commits
|
|||||||||||||||||||||||||||||||||||||
Copyright © 2001-2024 The PHP GroupAll rights reserved. |
Last updated: Sun Nov 24 06:01:32 2024 UTC |
OK, without OO: <? $pdo = new PDO('mysql:host=localhost;dbname=mydb','login','pass'); $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING); function Query () { global $pdo; $query = 'SELECT `created`, to_days(from_unixtime(`created`)) as `day`,count(`id`) as `count` '. 'FROM `orders` '. 'WHERE `partner`=:partner '. 'AND `created`>=:date '. 'GROUP BY `day`'; $vars = array(':partner'=>9,':date'=>1132797644); $stm = $pdo->Prepare($query); $stm->Execute($vars); return $stm; } $result = Query(); foreach ($result as $day) { print_r($day); echo '<hr>'; } $result = Query(); foreach ($result as $day) { print_r($day); echo '<hr>'; } $result = Query(); foreach ($result as $day) { print_r($day); echo '<hr>'; } ?> SQL: CREATE TABLE `orders` ( `id` int(10) unsigned NOT NULL auto_increment, `state` tinyint(3) unsigned NOT NULL default '0', `partner` int(10) unsigned NOT NULL, `created` int(10) unsigned NOT NULL default '0', `completed` int(10) unsigned default NULL, `count` tinyint(3) unsigned NOT NULL, `cost` float(11,2) unsigned NOT NULL default '0.00', `zip` int(10) unsigned default NULL, `region` varchar(80) default NULL, `city` varchar(120) default NULL, `street` varchar(255) default NULL, `house` varchar(32) default NULL, `building` varchar(32) default NULL, `apartment` varchar(32) default NULL, `name` varchar(255) default NULL, `surname` varchar(255) default NULL, `patronymic` varchar(255) default NULL, `mail` varchar(120) default NULL, `comments` text, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO `orders` VALUES (1,0,9,1134531133,1134931138,2,20,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO `orders` VALUES (2,4,9,1134911133,1134911139,1,10,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO `orders` VALUES (3,1,9,1134811133,1134811137,4,40,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO `orders` VALUES (4,3,9,1134711133,1134711533,1,10,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO `orders` VALUES (5,4,9,1134511131,1134511138,1,10,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO `orders` VALUES (6,1,0,1135521534,NULL,1,10,121231,'test','test','test','12',NULL,NULL,'test','test','test','one@test.com',NULL); INSERT INTO `orders` VALUES (7,0,9,1134522614,NULL,1,10,354354,'test2','test2','test2','12',NULL,NULL,'test2','test2','test2','two@test.com',NULL);I can reproduce this bug on two machines (athlon-xp and pentium3) with PHP 5.1.1-gentoo (cli) (built: Feb 10 2006 18:06:50) Zend Engine v2.1.0 This is the test case: <?php /** * Test case for bug #35793 * @see http://bugs.php.net/bug.php?id=35793 * * @author Markus Tacker <m@tacker.org> * @version $Id: pdo-proxy-bug.php 760 2006-02-14 14:59:19Z mtacker $ */ // If you set this to true I will reconnect before each select // at line 56 // => no crash $reconnect_before_select = false; error_reporting(E_ALL|E_STRICT); ini_set('display_errors', 1); $dsn = array( 'dbtype' => 'mysql', 'host' => 'localhost', 'database' => 'test', 'username' => 'test', 'password' => '', ); // $DB = new Database; $DB = new PDO($dsn['dbtype'] . ':host=' . $dsn['host'] . ';dbname=' . $dsn['database'], $dsn['username'], $dsn['password']); $table = uniqid(); // Create a test table $sql = 'CREATE TABLE `' . $table . '` (' . "varname varchar(64) NOT NULL default ''," . "varvalue tinytext NOT NULL default ''," . 'PRIMARY KEY (varname)' . ') ENGINE=InnoDB DEFAULT CHARSET=utf8'; $result = $DB->exec($sql); if ($result === false and $result->errorCode() != PDO::ERR_NONE) { exit('Query failed: ' . $sql . "\n"); } echo 'OK: ' . $sql . "\n"; // Insert into test table $sql = 'INSERT INTO `' . $table . '`' . ' (varname, varvalue)' . ' VALUES (' . $DB->quote('uniqid') . ', ' . $DB->quote($table) . ')'; $result = $DB->exec($sql); if ($result === false and $result->errorCode() != PDO::ERR_NONE) { exit('Query failed: ' . $sql . "\n"); } echo 'OK: ' . $sql . "\n"; // Select from table for ($i = 0; $i < 10; $i++) { if ($reconnect_before_select) { unset($DB); $DB = new PDO($dsn['dbtype'] . ':host=' . $dsn['host'] . ';dbname=' . $dsn['database'], $dsn['username'], $dsn['password']); } $sql = 'SELECT * FROM `' . $table . '` LIMIT 1'; $result = $DB->query($sql); if ($result === false or $result->errorCode() != PDO::ERR_NONE) { exit('Query failed: ' . $sql . "\n"); } echo 'OK: ' . $sql . "\n"; $row = $result->fetchObject(); // $row = $result->fetch(); // $row = $result->fetchAll(); if ($row === false or $result->errorCode() != PDO::ERR_NONE) { $info = $result->errorInfo(); exit('Fetch failed: ' . $info[2] . ' (' . $info[0] . '/' . $info[1] . ')' . "\n"); } echo 'OK: ' . $table . ' == ' . $row->varvalue . "\n"; $result->closeCursor(); } // Delete temp table $DB->exec('DROP TABLE `' . $table . '`'); ?>I'd got same problem with this piece of code: $s = $db->query("SELECT * FROM test ORDER BY poledrugie;"); var_dump($s->fetchAll()); $s = $db->query("SELECT * FROM test ORDER BY poletrzecie;"); var_dump($s->fetchAll()); Changed it to: $s = $db->query("SELECT * FROM test ORDER BY poledrugie;"); var_dump($s->fetchAll()); $st = $db->query("SELECT * FROM test ORDER BY poletrzecie;"); var_dump($st->fetchAll()); So I think it's wrong use of PHP objects...$result = $db->query('SELECT COUNT(*) FROM XYZ'); $row = $result->fetch(PDO::FETCH_ASSOC); $result->closeCursor(); $result = $db->query('SELECT * FROM XYZ'); $row = $result->fetch(PDO::FETCH_ASSOC); // this one throws the error an $result = null; between the statements fixes the issue, so I think this is a problem of php...I can confirm the statement on Centos 4.1, PHP 5.1.2, Mysql 4.1.16 "You cannot use the same variable for a PDOStatement object twice. As others have pointed out it works when you set this variable to null in between." <?php $db = new PDO(SQL_DSN,SQL_USER,SQL_PASSWORD); $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION); $result = $db->query('SELECT COUNT(*) FROM Locations'); echo $result."<br>"; $row = $result->fetchAll(PDO::FETCH_ASSOC); /* Comment the next line out and script returns an error */ //$result = null; $result = $db->query('SELECT COUNT(*) FROM Accounts'); echo $result."<br>"; $row = $result->fetch(PDO::FETCH_ASSOC); /* This line causes the error */ ?> With line "$result = null;" commented out: Object id #2 Object id #3 PDOException Object ( [message:protected] => SQLSTATE[HY000]: General error: 2050 With line "$result = null;" not commented out: Object id #2 Object id #2 No error message (script works)This bug/problem is not solved with PHP 5.2.0 under Debian stable (etch). Full code : <?php $db = new pdo( 'mysql:host=localhost;dbname=admin', 'administration', 'XXX' ); $db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING ); if( ( $res = $db->query( 'select id from user_group' ) ) !== false ) { print_r( $res->fetchAll() ); } if( ( $res = $db->query( 'select description from user_group' ) ) !== false ) { print_r( $res->fetchAll() ); } ?> And result for the second query : Warning: PDOStatement::fetchAll(): SQLSTATE[HY000]: General error: 2050 in /home/dev-olivier/toutgagner.admin/test.php on line 14 Array ( ) Won't it be fixed ?I guess this is a problem with pdo. When you connect to localhost using pdo, fire a query and store the result in variable, the variable needs to be reset before it can be reused, given that mysql resids on a remote server and not on localhost. $conn = new PDO("localhost".... $sql1 = "SELECT * from TEST"; $res = $conn->query($sql1); ... ... $sql2 = "SELECT * from MYTAB"; $res = $conn->query($sql2); // In this line you will get mysql general error if $res has not been reset before being used againThe problem seems to be still unsolved. I reproduced it today when using the statement variable twice or more for different queries Code : // Request writting $strSQL = " SELECT id FROM mybase.mytable "; // Request running $objStatement = $oPDO->query($strSQL); // Results browsing while ($arrRow = $objStatement->fetch(PDO::FETCH_NUM)) { array_push($arrData, $arrRow[0]); } // Cursor closing $objStatement->closeCursor(); // Request writting $strSQL = " SELECT id FROM mybase.myOTHERtable "; // Request running $objStatement = $oPDO->query($strSQL); // Results browsing while ($arrRow = $objStatement->fetch(PDO::FETCH_NUM)) { array_push($arrData, $arrRow[0]); } // Cursor closing $objStatement->closeCursor(); To solve this problem I use the following after $objStatement->closeCursor() : unset($objStatement); As $objStatement is affected an new PDOStatement instance, I think it would be nice if PHP could remove the previous PDOStatement instance as it is no longer usable.