Skip to main content

[探索 5 分鐘] 執行 MySQL command 出現 : cannot execute queries while other unbuffered queries are active (php + Yii + extjs + mysql)

Labels:
 
PHP 不算我非常熟的語言, 但今天在 CNIS (Canadian Network for International Surgery, 非營利組織) 當志工時幫忙解了一個 bug, 覺得可以記錄一下

程式筆記

Environment

Front-end: Yii framework
Back-end: PHP
Database: MySQL
SQL Tool: SQLYog
OS: Linux

PHP Exception

其實這是我自己種下的坑, 因為我建議使用 stored-procedure, 讓後端程式碼乾淨, 然後就出現這個 exception:
CDbException  
CDbCommand failed to execute the SQL statement: 
 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.. 

 The SQL statement executed was: select @output

MySQL Stored-Procedure

一開始的方向是在看 store-procedure 以及後端給予的 @output 參數是否有問題, 結果徒勞無功, 同事也幫不上忙 
CREATE DEFINER=`my_db`@`%` PROCEDURE `my_sp`(  
 IN v_title TEXT,  
 OUT output INT  
 )  
 BEGIN  
      ...  
      SET output = LAST_INSERT_ID();  
      SELECT output;  
 END$$

fone-end.js

前端是沒什麼可疑之處, 僅是另外建議用了 Extjs 框架。這支 js 重點僅是把 submit 資料送到 controller.php 
myForm.submit({  
      url: '/myApp/index.php?r=myPage/controller'  
      ,clientValidation: false  
      ,params: { recordId: '' }  
      ,method: 'post'  
      ,success: function(form, action) {  
           window.location.href = Ext.String.format('/myApp/index.php?r=myPage/view&id={0}', action.result.returnValue );   
      }  
 });

controller.php

controller 就是除了 stored-procedure 之外, 最有嫌疑者, 有經驗的 php developer, 能看出什麼問題嗎 ? 
$command = Yii::app()->db->createCommand('CALL my_sp (  
                     :v_title,  
                     @output)');  
 $command->bindParam('v_title', $_POST['Title']);  
 $count = $command->execute(); // should be 1 cus one 1 record affected successfully when inserting  
 $command = false; // this line make me crazy
 $returnOut = Yii::app()->db->createCommand("select @output")->queryScalar();  
 header('Content-type: application/json');        
 $json = array(  
                     'success' => true,  
                     'returnValue' => intVal($returnOut),  
                     'data' => array(  
                          'count' => $count,  
                          'Msg' => ''  
                     )  
                );                           
 echo json_encode($json);
最後, 就是黃色高亮那一行是問題核心。因為 exception 提到 query buffer 的問題, 所以從 $command 的前後文來看, 由於已經執行過 execute(), 但需要再 select output parameter 回來, 所以又再 query 一次; 第二次疑似 query buffer 作怪, 所以嘗試把已執行過的 $command 清掉
$command = false;
再試一次就正常了。希望可以幫助到也有類似問題的同學。

參考資料

  • https://stackoverflow.com/questions/2066714/pdo-cannot-execute-queries-while-other-unbuffered-queries-are-active

Comments

Post a Comment