Dbal: Doctrine (DBAL) Error Handling while Executing Multiple Queries

[feridmovsumov]

I have an schema sql file (with syntax error) including multiple queries for settings database

example.sql

CREATE TABLE IF NOT EXISTS `example` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;


CREATExxxxxxxxxx TABLE IF NOT EXISTS `example2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

example.php

$sqlContent = file_get_contents("example.sql");
$stmt = $conn->prepare($sqlContent);
$result = $stmt->execute();

execute method doesn't throw any exception even that my sql is incorrect. it documentation says it returns false on failure but it returns true.

How should I do exception hanling here? How can I check if my query has an error?

[deeky666]

@feridmovsumov this is an interesting edge case. PHP database drivers do not handle multi queries very well. Tested this on pdo_mysql and it indeed doesn't error if the second query fails. But it does fail with SyntaxErrorException if the first one is invalid. mysqli does error regardless, whether the first or the second one is invalid. Unfortunately PDO seems to execute one by one, so if the first is valid and the second not, the first one will still have been executed (resulting in the table being created). mysqli seems to validate syntax of all queries first before actually executing anything (resulting in no table being created at all).
This is a tricky one and I am not sure how DBAL should behave here (or if we even can make behaviour consistent between drivers).

[ovcharenkovv]

and what if you wrap both qery in transaction ?

[geekwright]

MySQL does an Implicit Commit on each CREATE. That stops the transaction after the first statement.
http://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html

[tomasfejfar]

We hit this after 4 years from the original issue in Doctrine migrations:

$this->addSql("
CREATE TABLE test (id INT);
ALTER TABLE test DROP COLUMN myName;
");

Super hard to debug!

It would be best if multiple queries in one go would result in error or at least warning.

[tomasfejfar]

Also https://stackoverflow.com/questions/39395262/why-does-doctrine-2-migration-silently-fail-in-symfony-project