Today I hit an error in the PDO transaction-handling part:
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
Looking through the error log I saw:
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.
My code is:
###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; } }
After searching a bunch of sites, I’ve summed up several ways to solve this kind of problem.
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/)
Many people have tested this and it does solve the “Cannot execute queries while other unbuffered queries are active” problem.
But I added this line and it still didn’t work.
And some say that since PHP 5.2.1 this attribute has defaulted to true.
2. fetchAll() the query result set to release the PDOStatement:
A passage from the PDO::query docs:
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)
In other words, issuing a new query before the previous one has finished will throw an error, so you have to release the previous query. Exactly as the error message says: “Consider using PDOStatement::fetchAll()”.
3. Install the latest PHP…..
http://bugs.php.net/bug.php?id=44081 mentions that PHP 5.2.5 has a bug, and this guy was going out of his mind: “ however i closed the cursor after each result fetch!!!! ”
He suspected he’d hit a bug (one that appears in PHP 5.2.5). I think that suspicion is not unreasonable — my code runs fine on the company’s internal PHP 5.2.6, but on the company’s external server (PHP 5.1.6) it throws the error above. Fixing it properly (installing PHP 5.2.6 or later) seems like a real hassle, so it’s better to work around it and find another way.
4. Only one CREATE TABLE statement can be executed at a time
Then I saw this comrade: http://blog.chinaunix.net/u/4891/showart.php?id=1672950 ran into the same error, and the cause was putting two CREATE TABLE statements separated by a semicolon into a single statement to execute, whereas php.net says: “CREATE TABLE statements can only be executed one at a time”
5. Use closeCursor() to release resources
Before querying, if you have already executed query(), you need to run PDOStatement::closeCursor() to release the database resource connection. So I changed it to:
###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; } }
Problem solved. (In fact this solution is the same as #2.)