今天遇到PDO处理事务部分的报错：
Fatal error: Uncaught exception ‘PDOException’ with message ‘There is no active transaction’ in /data/www/web.passport/pay/classes/class.Mydb.php:145 Stack trace: #0 /data/www/web.passport/pay/classes/class.Mydb.php(145): PDO->rollBack() #1 /data/www/web.passport/pay/yeepayOrder.php(138): Mydb->transaction(Array) #2 {main} thrown in /data/www/web.passport/pay/classes/class.Mydb.php on line 145
查找错误日志看到：
2009-01-16 09:27:57: 事务处理出错:SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.
我的代码是：
###Transaction function transaction($sqlQueue) { //$this->connection(); if(count($sqlQueue)>0) { if($this->charset !== NULL) { $this->db->exec(“SET NAMES “.$this->charset); }else{ $this->db->exec(“SET NAMES utf8”); } try { $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $this->db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true); $this->db->beginTransaction(); foreach ($sqlQueue as $sql) { $this->db->exec($sql); } $this->db->commit(); return true; } catch (Exception $e) { $this->logWriter->writeLog(“事务处理出错:”.$e->getMessage().”\nSQL语句:”.Utils::arrToString($sqlQueue)); $this->db->rollBack(); return false; } }else{ return false; } }
搜索N处网站，总结出几种解决此类问题之方法
1.setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
When you receive error like this:
General error: 2014 Cannot execute queries while other unbuffered queries are active.
This means that you need to uncomment the following:
On line 137 of “lib/Varien/Db/Adapter/Pdo/Mysql.php”, find:
#$this->_connection->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
and then uncomment it out, so it should be
$this->_connection->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
——(http://www.magentocommerce.com/boards/viewthread/35/)
这个经很多人测试可以解决Cannot execute queries while other unbuffered queries are active的问题。
但是我加上本行后还是不行。
且有人说从php5.2.1起,这个属性已经默认为true.
2.将查询的结果集fetchAll()以释放PDOStatement:
PDO::query里的一段话：
If you do not fetch all of the data in a result set before issuing your next call to PDO::query(), your call may fail. Call PDOStatement::closeCursor() to release the database resources associated with the PDOStatement object before issuing your next call to PDO::query().
——(http://www.unbe.cn/blog/?p=21)
也就是说，未结束之前的查询而再次查询，这样是会出错地，所以要释放掉之前的查询。正如错误提示所说的：Consider using PDOStatement::fetchAll()”。
3.安装最新PHP…..
http://bugs.php.net/bug.php?id=44081中提到PHP5.2.5中有bug，这位兄台都抓狂了：“ however i closed the cursor after each result fetch!!!! ”
怀疑自己遇到bug(出现于php5.2.5)。我觉得这个怀疑不无道理，我的代码在公司内部php5.2.6版本上可以正常执行过去，但公司外网服务器(php5.1.6)上就报上面的错。这个要解决(安装php5.2.6以上)似乎很麻烦了，还不如绕过，另想它法。
4.建表语句一次只能执行一条
又看到这位同志：http://blog.chinaunix.net/u/4891/showart.php?id=1672950 也遇到这个错误，原因是将两条建表语句用分号隔开放进一条语句执行，而php.net曰过：”建表语句一次只能执行一条”
5.用closeCursor()释放资源
在查询之前，如果执行过query()，要执行一下PDOStatement::closeCursor()以释放数据库资源连接。 所以我改成了：
###Transaction function transaction($sqlQueue) { //$this->connection(); if(count($sqlQueue)>0) { /* * Manual says: * If you do not fetch all of the data in a result set before issuing your next call to PDO::query(), your call may fail. Call PDOStatement::closeCursor() to release the database resources associated with the PDOStatement object before issuing your next call to PDO::query(). * */ $this->result->closeCursor(); if($this->charset !== NULL) { $this->db->exec(“SET NAMES “.$this->charset); }else{ $this->db->exec(“SET NAMES utf8”); } try { $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $this->db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true); $this->db->beginTransaction(); foreach ($sqlQueue as $sql) { $this->db->exec($sql); } $this->db->commit(); return true; } catch (Exception $e) { $this->logWriter->writeLog(“事务处理出错:”.$e->getMessage().”\nSQL语句:”.Utils::arrToString($sqlQueue)); $this->db->rollBack(); return false; } }else{ return false; } }
问题解决。（其实这个解决方案和2是一样的）
技术内容转载请注明来源，个人日记不允许转载，欢迎指出任何有错误或不够清晰的表达。可以邮件至 mousebomb@gmail.com
