db: yii\db\command execute() don't handle errors on multiple queries

What steps will reproduce the problem?

Try to import db schema (from mysqldump or PhpMyAdmin schema export) with some error on non-first query. In example, change “int” column type in some table to “intFOO” to cause mysql error.

Schema:

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `auth_assignment`;
CREATE TABLE `auth_assignment` (
  `item_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `user_id` intFOO(10) UNSIGNED NOT NULL,
  `created_at` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Example code (console action):

public function actionReset()
{
    $schemaFile = 'some/path/schema.sql';
    try {
        $command = Yii::$app->db->createCommand(file_get_contents($schemaFile));
        $command->execute();

        Console::output('Schema imported.');
        return self::EXIT_CODE_NORMAL;
    } catch (\Exception $e) {
        Console::error($e->getMessage());
    }
    return self::EXIT_CODE_ERROR;
}

What is the expected result?

Execution ends with proper exception.

What do you get instead?

“Schema imported.” on stdout - no exception thrown, or error reported.

Additional info

Read this question and approved answer: How to check if db execute sql fails?

I believe that Yii should handle this case?

Q	A
Yii version	2.0.12
PHP version	7.1.x
Operating system	Kubuntu 17.04

About this issue

Original URL
State: open
Created 7 years ago
Comments: 17 (9 by maintainers)

Most upvoted comments

I mean rather using PDOStatement::nextRowset() to iterate through all queries passed to PDOStatement::execute() to catch exceptions and get number of rows affected by each of these queries.

See example in linked issue in PHP’s bugzilla:

$pdo->beginTransaction();
try {
	$statement = $pdo->prepare($sql);
	$statement->execute();
	while ($statement->nextRowset()) {/* https://bugs.php.net/bug.php?id=61613 */};
	$pdo->commit();
} catch (\PDOException $e) {
	$pdo->rollBack();
	throw $e;
}

rob006 on Sep 20, 2017

In continue of our discussion I have one more question. Construction:

try {
  Yii::$app->db->pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES, 1);
  $statement = Yii::$app->db->pdo->prepare($sql);
  $statement->execute();
  while ($statement->nextRowset()) {
    //
  }
} catch (\Exception $e) {
  //
}

works fine and throw an exception on first failed sql query, but it is not possible to put MySQL code with ‘DELIMITER’ - the exception will return:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`() BEG' at line 1.

But I if use Yii::$app->db->createCommand($sql)->execute(); - no exceptions with ‘DELIMITER’. PDO doesn`t understand DELIMITER, and what solution in this case?

papalapa on Feb 22, 2018