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

[tomaszkane]

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

[rob006]

Are you sure that second and third query are executed?

[cebe]

dependent on the PDO driver settings it may only support one query. Passing multiple queries to createCommand() works in some cases but that is not documented to work and may not work.

[rob006]

This is expected PDO behavior: https://bugs.php.net/bug.php?id=61613

[rob006]

Maybe we should introduce something like executeMultiple()?

[cebe]

Maybe we should introduce something like executeMultiple()?

that would require parsing of SQL to separate statements. That is not easy to do as we need to fully understand SQL syntax of the corresponding dbms. Need to consider things like MySQL magic comments.

[rob006]

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;
}

[cebe]

Is the query only executed on calling nextRowset()? or is just the error not reported back before calling it?

[tomaszkane]

@rob006 executed is all queries before the broken one. Thats why it's tricky - if broken sql is on of the last query (example: some alter table), then when you check database (with some tool/console) and see all tables, you think: "My schema import is success". BTW, in my case transaction is worthless because MYSQL don't support it on tables create/drop/alter.

@cebe queries are executed, nextRowset() just iterate on results and throw PDOException when first result is fail.

[cebe]

@cebe queries are executed, nextRowset() just iterate on results and throw PDOException when first result is fail.

if all queries are executed, we might also want to check further queries for failure, e.g. having the last two queries fail, we'd want an exception reporting all of these.

[papalapa]

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?

[tomaszkane]

BTW, does delimiter is really need? See: https://stackoverflow.com/a/9053823/1829368 I have schema dump in my project - after remove delimiters near the triggers all queries was successful and triggers was created.

[papalapa]

@TomaszKane, yes, thanks, I'm looking for solution to remove delimiters from queriy now.

[tomaszkane]

My schema with delimiters:

-- some queries here

DELIMITER $$
CREATE TRIGGER `fooCounterCacheAfterDelete` AFTER DELETE ON `foo` FOR EACH ROW BEGIN
    UPDATE user SET foo_count = foo_count - 1 WHERE id = OLD.user_id;
END
$$
DELIMITER ;

and import fail. Schema without delimiter:

-- some queries here

CREATE TRIGGER `fooCounterCacheAfterDelete` AFTER DELETE ON `foo` FOR EACH ROW BEGIN
  UPDATE user SET foo_count = foo_count - 1 WHERE id = OLD.user_id;
END;

works fine.

[FantomX1]

Delimiters in MySQL https://stackoverflow.com/a/10259528/3419535

Attempting to use DELIMITER with a client that doesn't support it will cause it to be sent to the server, which will report a syntax error. For example, using PHP and MySQLi:

$mysqli = new mysqli('localhost', 'user', 'pass', 'test'); $result = $mysqli->query('DELIMITER $$'); echo $mysqli->error; Errors with:

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 $$' at line 1

[thomaswruss]

I had the same problem if I execute a procedure that calls more than one procedure. If there is an error, the error will not be raised in Yii. But if I execute just that one procedure, the error will be raised. So this seems to be a bug of Yii. Any suggestion how to detect the errors in a multiple procedure call?

[samdark]

Any suggestion how to detect the errors in a multiple procedure call?

Yes. See https://github.com/yiisoft/db/issues/79#issuecomment-330821059

[tomaszkane]

I also link a solution in Additional info in this issue: https://stackoverflow.com/a/46280130/1829368 @thomaswruss read topic before ask.