Yii2: yii/db/Command::execute() does not throw errors on multiple commands

[mikehaertl]

If you have a SQL statement with several commands, then yii\db\Command::execute() will not throw an exception, if the first command is successful:

// Throws exception:
Yii::$app->db->createCommand("FOO; BAR; BAZ;")->execute();

// No exception:
Yii::$app->db->createCommand("SELECT 1; FOO; BAR; BAZ;")->execute();

This is a problem for me, because often in migrations I want to execute lengthy auto-generated SQL-statements (e.g. from MySQL Workbench). I had the situation, where some of the statements where not executed due to a syntax error. Still the migrations succeeded without any error message.

[nineinchnick]

You should avoid executing multiple queries in a single command. Some drivers explicitly disallow that. Try this:

$queries = "SELECT 1; FOO; BAR; BAZ;";
foreach (array_filter(array_map('trim', explode(';', $queries))) as $query) {
    Yii::$app->db->createCommand($query)->execute();
}

[SamMousa]

Seems to be a PDO problem:

http://stackoverflow.com/questions/23247553/how-can-i-get-an-error-when-running-multiple-queries-with-pdo

It does however mention a way to workaround it.

@nineinchnick The exploding will work, but it is pretty naive though, any query which has ';' in it will make this break in bad ways! (Naive is meant for the explode statement not for you ;-))

If the queries are autogenerated it is very likely that they are all on a single line each which could offer safer explode options; like:

    $queries = "SELECT 1; FOO; BAR; BAZ;";
    foreach (array_filter(array_map('trim', explode(";\n", $queries))) as $query) {
        Yii::$app->db->createCommand($query)->execute();
    }

[mikehaertl]

I can't see, why this should be disallowed (please provide reference if you disagree).

Also splitting at ; is no solution. The executed SQL may be complex and even contain semicolons e.g. in comment blocks. I don't want to parse SQL to fix this problem.

[samdark]

https://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd/6461110#6461110

[samdark]

But overall I don't think we're able to do anything about it since it's deep issue in PDO.

[mikehaertl]

Right. I think, the alternative is to use PDO::exec() directly instead:

Yii::$app->db->open();
Yii::$app->db->pdo->exec($sql);

It's important to note, that no query statement should be executed this way (SELECT ..., SHOW ...) or you will se a DB exception on the next query, because of PDO::MYSQL_ATTR_USE_BUFFERED_QUERY:

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.

[kidol]

Comment in PHP bug report:

Indeed, just tested it on a PDO configured to throw exceptions, and the PDOException is thrown as soon as nextRowSet() is called!

Yii is always configured to throw, so in case of execute(), all has to be done is to add:

while ($this->pdoStatement->nextRowSet()) {}

This will throw exception if any of the queries fails. In case multi-query is part of transaction, it can then be rolled-back.

Where is problem?

[restyler]

We've encountered this issue a few days ago. We have a bunch of SQL queries (each on new line) in each execute() statement in our migrations because it is very convenient to copy&paste raw SQL to migration from db GUI client. We spent a lot of hours debugging the mysterious errors when some tables had no columns. I suggest to have a helper explodeAndExecuteMultiple(";", $sql) function in yii/db/Migration. It feels hacky, it can break on wrong semicolon, but it is less hacky then wrapping each statement into $sql[] = "sql"; and having foreach() in the end of each migration.

[restyler]

Oops, I see https://github.com/yiisoft/yii2/pull/9896 now.

[mikehaertl]

For the record: The workaround with Yii::$app->db->pdo->exec() is not reliable either. It does not always report errors in later statements. For now I've modified my migrations like this:

public function up()
{
    $command = Yii::$app->db->createCommand($this->sql);
    $command->execute();
    while ($command->pdoStatement->nextRowSet()) {}
}

[016]

For the record: The workaround with Yii::$app->db->pdo->exec() is not reliable either. It does not always report errors in later statements. For now I've modified my migrations like this:

public function up()
{
    $command = Yii::$app->db->createCommand($this->sql);
    $command->execute();
    while ($command->pdoStatement->nextRowSet()) {}
}

that's really help when exec > 1 sql commands with execute() in migrate, thx.

the problem still exist with version 2.0.17, I guess we can add it to the while to execute() ?

[samdark]

@016 are the any side effects?

[SamMousa]

https://php.net/manual/en/pdostatement.nextrowset.php

According to the comments there are issues with it.