Why does Doctrine 2 migration silently fail in Symfony project?

Question

My questions are:

1. Why does the doctrine migration command within Symfony fail to report the SQL error I am listing below
2. How can I get it to report such errors, rather than look like it has succeeded.

To be clear - I know why the SQL itself fails. That's the easy part. The silent nature of the failure is the problem.

I have a doctrine migration in a PHP file, as so:

[... boilerplate ...]

public function up(Schema $schema)
{
    $this->addSql(<<<SQL

    -- This runs fine
    CREATE TABLE [[redacted - this part runs fine]];

    -- This fails with a unique key constraint
    INSERT INTO `users_orgs` (`org_id`, `user_id`, `active`)
    SELECT `oid`, `uid`, 1 as `active` FROM `orgs`;

    -- Never gets here
    ALTER TABLE `orgs` CHANGE `uid` `primary_user_id` int(11) unsigned DEFAULT NULL;

SQL
    );
}

[... boilerplate ...]

Actual Result

The second statement fails because of a unique key constraint (like "Duplicate entry '6-3' for key 'PRIMARY'" - it's a compound key) but the error is not reported. I know it fails because it's clear the final statement never runs, and if I run the offending statement manually, it fails. When I run:

./bin/console doctrine:migrations:migrate

From within my Symfony application, thus calling the doctrine migration code, it reports:

$ ./bin/console doctrine:migrations:migrate

                Application Migrations                    


WARNING! You are about to execute a database migration that could result in schema changes and data lost. Are you sure you wish to continue? (y/n)y
Migrating up to 20160822090333 from 20160820232349

  ++ migrating 20160822090333

     -> CREATE TABLE [[redacted - works fine]];

INSERT INTO `users_orgs` (`org_id`, `user_id`, `active`)
SELECT `oid`, `uid`, 1 as `active` FROM `orgs`;

ALTER TABLE `orgs` CHANGE `uid` `primary_user_id` int(11) unsigned  DEFAULT NULL;


  ++ migrated (0.1s)

  ------------------------

  ++ finished in 0.1s
  ++ 1 migrations executed
  ++ 1 sql queries

Expected Result

I'm expecting it to report an error. As a test, I created a one-column table with a primary key. I tried to insert a record that would generate a unique constraint violation from within a migration. When I executed the migration, doctrine reported this:

Migration 20160908112332 failed during Execution. Error An exception occurred while executing '        insert into FOO values (1)':

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1' for key 'PRIMARY'

My configuration

Here's what I have installed (via composer):

doctrine/annotations                 v1.2.7             Docblock Annotations Parser
doctrine/cache                       v1.6.0             Caching library offering an object-oriented API for many cache backends
doctrine/collections                 v1.3.0             Collections Abstraction library
doctrine/common                      v2.6.1             Common Library for Doctrine projects
doctrine/dbal                        v2.5.4             Database Abstraction Layer
doctrine/doctrine-bundle             1.6.4              Symfony DoctrineBundle
doctrine/doctrine-cache-bundle       1.3.0              Symfony Bundle for Doctrine Cache
doctrine/doctrine-migrations-bundle  v1.2.0             Symfony DoctrineMigrationsBundle
doctrine/inflector                   v1.1.0             Common String Manipulations with regard to casing and singular/plural rules.
doctrine/lexer                       v1.0.1             Base library for a lexer that can be used in Top-Down, Recursive Descent Pa...
doctrine/migrations                  1.4.1              Database Schema migrations using Doctrine DBAL
symfony/symfony                      v3.0.9

MySQL:

mysql Ver 14.14 Distrib 5.6.27, for Linux (x86_64) using  EditLine wrapper

What I've tried

I have searched "silent doctrine failure", "silent doctrine migration failure" on google and here, and I've not seen anything. Any insight would be appreciated.

Answer 1

It will have to do with having multiple statements executed in one query.

Doctrine migrations documentation says:

Internally the addSql call are passed to the dbal executeQuery method.

I tried out executing multiple statements with executeQuery and it behaved the same way as for you. (I got different package versions, but only differs in patch level). When I have multiple statements, it only throws an error if the first statement fails.

Consider this example:

/** @var \Doctrine\DBAL\Connection $connection */
$connection = $this->getDoctrine()->getConnection();

$createTable = "CREATE TABLE test (org_id int not null, user_id int not null, PRIMARY KEY(org_id, user_id));";

$insert = "INSERT INTO test (org_id, user_id) VALUES (1, 1);";

// this will create the table (if doesn't exist) and add a record, the second insert silently fails.
$connection->executeQuery("{$createTable}{$insert}{$insert}"); 

But:

// ... same as above except for executeQuery
$connection->executeQuery("{$createTable}");
$connection->executeQuery("{$insert}");
$connection->executeQuery("{$insert}"); // <- duplicate key error is thrown

Where I'm trying to get to is to use separate addSql() statements for each of your statements.

[... boilerplate ...]

public function up(Schema $schema)
{
    $this->addSql("CREATE TABLE [[redacted - this part runs fine]];");

    $this->addSql("INSERT INTO `users_orgs` (`org_id`, `user_id`, `active`) SELECT `oid`, `uid`, 1 as `active` FROM `orgs`;");

    $this->addSql("ALTER TABLE `orgs` CHANGE `uid` `primary_user_id` int(11) unsigned DEFAULT NULL;");
}

[... boilerplate ...]