Error on queryOne after execute

[aukarelin]

When I run the following code:

$db = \Yii::$app->getDb();
$cmd = $db->createCommand('SET @tmp1 = 1; SET @tmp2 = 2;');
$cmd->execute();

$result = $db->createCommand('SELECT @tmp1, @tmp2;')->queryOne();

I get the error:

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 being executed was: SELECT @TMP1, @Tmp2;

But when I use:

$db->createCommand('SET @tmp1 = 1; SET @tmp2 = 2;')->execute();

or

$cmd = $db->createCommand('SET @tmp1 = 1;')
$cmd->execute();
$cmd = $db->createCommand('SET @tmp2 = 2;')
$cmd->execute();

It works fine.

[cebe]

can you please post the complete stack trace of the error?

[aukarelin]

2014-06-05 08:23:19 [][][-][error][yii\db\Exception] exception 'PDOException' with message '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.' in /home/akarelin/www/platform/vendor/yiisoft/yii2/db/Command.php:398
Stack trace:
#0 /home/akarelin/www/platform/vendor/yiisoft/yii2/db/Command.php(398): PDOStatement->execute()
#1 /home/akarelin/www/platform/vendor/yiisoft/yii2/db/Command.php(326): yii\db\Command->queryInternal('fetch', NULL)
#2 /home/akarelin/www/platform/frontend/controllers/SiteController.php(82): yii\db\Command->queryOne()
#3 [internal function]: frontend\controllers\SiteController->actionIndex()
#4 /home/akarelin/www/platform/vendor/yiisoft/yii2/base/InlineAction.php(54): call_user_func_array(Array, Array)
#5 /home/akarelin/www/platform/vendor/yiisoft/yii2/base/Controller.php(147): yii\base\InlineAction->runWithParams(Array)
#6 /home/akarelin/www/platform/vendor/yiisoft/yii2/base/Module.php(423): yii\base\Controller->runAction('', Array)
#7 /home/akarelin/www/platform/vendor/yiisoft/yii2/web/Application.php(82): yii\base\Module->runAction('', Array)
#8 /home/akarelin/www/platform/vendor/yiisoft/yii2/base/Application.php(355): yii\web\Application->handleRequest(Object(yii\web\Request))
#9 /home/akarelin/www/platform/frontend/web/index.php(17): yii\base\Application->run()
#10 {main}

Next exception 'yii\db\Exception' with message '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 being executed was: SELECT @TMP1, @Tmp2;' in /home/akarelin/www/platform/vendor/yiisoft/yii2/db/Schema.php:503
Stack trace:
#0 /home/akarelin/www/platform/vendor/yiisoft/yii2/db/Command.php(420): yii\db\Schema->handleException(Object(PDOException), 'SELECT @TMP1, @...')
#1 /home/akarelin/www/platform/vendor/yiisoft/yii2/db/Command.php(326): yii\db\Command->queryInternal('fetch', NULL)
#2 /home/akarelin/www/platform/frontend/controllers/SiteController.php(82): yii\db\Command->queryOne()
#3 [internal function]: frontend\controllers\SiteController->actionIndex()
#4 /home/akarelin/www/platform/vendor/yiisoft/yii2/base/InlineAction.php(54): call_user_func_array(Array, Array)
#5 /home/akarelin/www/platform/vendor/yiisoft/yii2/base/Controller.php(147): yii\base\InlineAction->runWithParams(Array)
#6 /home/akarelin/www/platform/vendor/yiisoft/yii2/base/Module.php(423): yii\base\Controller->runAction('', Array)
#7 /home/akarelin/www/platform/vendor/yiisoft/yii2/web/Application.php(82): yii\base\Module->runAction('', Array)
#8 /home/akarelin/www/platform/vendor/yiisoft/yii2/base/Application.php(355): yii\web\Application->handleRequest(Object(yii\web\Request))
#9 /home/akarelin/www/platform/frontend/web/index.php(17): yii\base\Application->run()
#10 {main}
Additional Information:
Array
(
[0] => HY000
[1] => 2014
[2] => 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.
)

[mdmunir]

you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.

try add this code before execute your command

\Yii::$app->db->pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY,true);

[aukarelin]

MDMunir, can you explain for what?

I found something, but I can't check it now.
http://www.php.net/manual/en/pdostatement.execute.php#refsect1-pdostatement.execute-notes

Note:
Some drivers require to close cursor before executing next statement.

yii\db\Command::execute doesn't close cursor:

public function execute()
    {
        $sql = $this->getSql();

        $rawSql = $this->getRawSql();

        Yii::info($rawSql, __METHOD__);

        if ($sql == '') {
            return 0;
        }

        $token = $rawSql;
        try {
            Yii::beginProfile($token, __METHOD__);

            $this->prepare();
            $this->pdoStatement->execute();
            $n = $this->pdoStatement->rowCount();

            Yii::endProfile($token, __METHOD__);

            return $n;
        } catch (\Exception $e) {
            Yii::endProfile($token, __METHOD__);
            $this->db->getSchema()->handleException($e, $rawSql);
        }
    }

[mdmunir]

i just read your exception message and try to give you a solution. i dont know, my solution will work or not. if doesnt work, i am sory.

$db = \Yii::$app->getDb();
$db->pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY,true);
$cmd = $db->createCommand('SET @tmp1 = 1; SET @tmp2 = 2;');
$cmd->execute();

$result = $db->createCommand('SELECT @tmp1, @tmp2;')->queryOne();

[aukarelin]

I see what you mean, but to solve the problem we need to understand the reason.
Otherwise it will be a workaround.

[klimov-paul]

I am unable to reproduce this.
Perhaps this issue depends on particular version of PDO or MySQL.
@KarLex, try to adjust Command::execute() code manually adding $this->pdoStatement->closeCursor(). Does it solves your problem?

[klimov-paul]

Closed, since there is no response from the author.