migrations

migrations copied to clipboard

Hello,

BC Break Report

Since I updated doctrine/migrations from 3.0.1 to 3.0.2, my migrations are not working anymore

Q	A
BC Break	yes
Version	3.0.2

Summary

We use doctrine migration to create and execute migrations on a PostGreSQL database. Since update from 3.0.1 to 3.0.2, it is not working anymore.

Previous behavior

When executing doctrine:migrations:migrate, every migrations are running fine.

Current behavior

When executing doctrine:migrations:migrate, migrations fail like this :

[notice] Migrating up to DoctrineMigrations\Version20210118123225
[error] Migration DoctrineMigrations\Version20210111103326 failed during Execution. Error: "An exception occurred while executing 'INSERT INTO network (id, name, created_at, updated_at) VALUES (nextval('network_id_seq'), 'Métro', NOW(), NOW());
INSERT INTO network (id, name, created_at, updated_at) VALUES (nextval('network_id_seq'), 'RER', NOW(), NOW());':

SQLSTATE[42601]: Syntax error: 7 ERROR:  cannot insert multiple commands into a prepared statement"
[error] Error thrown while running command "doctrine:migrations:migrate". Message: "An exception occurred while executing 'INSERT INTO network (id, name, created_at, updated_at) VALUES (nextval('network_id_seq'), 'Métro', NOW(), NOW());
INSERT INTO network (id, name, created_at, updated_at) VALUES (nextval('network_id_seq'), 'RER', NOW(), NOW());':

SQLSTATE[42601]: Syntax error: 7 ERROR:  cannot insert multiple commands into a prepared statement"


In AbstractPostgreSQLDriver.php line 73:
                                                                                                                                                            
  An exception occurred while executing 'INSERT INTO network (id, name, created_at, updated_at) VALUES (nextval('network_id_seq'), 'Métro', NOW(), NOW());  
  INSERT INTO network (id, name, created_at, updated_at) VALUES (nextval('network_id_seq'), 'RER', NOW(), NOW());':                                         
                                                                                                                                                            
  SQLSTATE[42601]: Syntax error: 7 ERROR:  cannot insert multiple commands into a prepared statement                                                        
                                                                                                                                                            

In Exception.php line 18:
                                                                                                      
  SQLSTATE[42601]: Syntax error: 7 ERROR:  cannot insert multiple commands into a prepared statement  
                                                                                                      

In PDOConnection.php line 125:
                                                                                                      
  SQLSTATE[42601]: Syntax error: 7 ERROR:  cannot insert multiple commands into a prepared statement  

How to reproduce

Well I don't really know, I just did a composer update and it doesn't work anymore. I then did check one by one all my dependencies, downgrading them and so on... and this behavior appears with 3.0.2. 3.0.1 is fine.

I can give you the code for this migration :

    public function up(Schema $schema): void
    {
        $sql = <<<SQL
INSERT INTO network (id, name, created_at, updated_at) VALUES (nextval('network_id_seq'), 'Métro', NOW(), NOW());
INSERT INTO network (id, name, created_at, updated_at) VALUES (nextval('network_id_seq'), 'RER', NOW(), NOW());
SQL;

        $this->addSql($sql);
    }

Thank you

you should be doing 2 addSql calls to add 2 statements. Passing multiple statements in the same addSql was never intentionally supported.

Note btw that the only change that could explain this difference is a revert of a change done in 3.0.0 (that was breaking other things), so this was probably not working in 2.x either.

Allright, I fixed them manually and it worked fine. However this was not really clear and seems to work with MySql (from what my dev told me), so maybe a notice about the changes or when exception is thrown may be a good idea.

Thank you

This issue is related to https://github.com/doctrine/migrations/pull/1071

Ideally https://github.com/doctrine/migrations/pull/1071 should not have been merged, but it causes issues with some database engines when exec is used (addSql uses exec or prepare depending if there are parameters or not...)... so it is hard to decide which one is better.

Does it make sense to add something addUpdateSql that allows to place multiple SQL statements (using alway the underlying exec) PDO function?

ping @greg0ire @stof what do you think about this?

I also found my self forced to split sql instructions just to workaround this issue.

I think @lyrixx 's situation is more exceptional than @omsi668 . If we were to design the API from scratch, it would make more sense to have executeStatement be used by default, and to have an extra method to force the use of PDO::query() by using Connection::executeQuery() with no parameters in the case of REPAIR.

Sadly, we are not designing the API from scratch, which means we should probably deprecate addSQL() in favor of 2 new methods. addStatement() and addQuery() come to mind, and make a lot of sense semantically IMO, so maybe this is for the better.

We have a big application with hundreds of tables and lots of complex database functions and procedures. One way I have managed to adopt doctrine as a database migration tool on a legacy application was to export the current schema and default data as two big SQL files.

The only way to import it is to execute in a sigle query, since the pg_dump script is designed to be executed in batch.

I came to this issue since it also broke in version 3.0.2+ for me, I downgraded to 3.0.1 for now.

The doubt I have now is how can I load and execute these scripts and execute them since versions 3.0.2+ ?

My initial migration method looks like this:

public function up(Schema $schema) : void
{
      // this up() migration is auto-generated, please modify it to your needs
      $this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'postgresql', 'Migration can only be executed safely on \'postgresql\'.');

      $this->addSql(file_get_contents(__DIR__ . "/../../sql/initial-schema.sql"));
}

The SQL has 40 thousand lines and some statements cannot be simply separated by ';' semicolon to be executed individually.

Any advices ?

Thanks !

@Hodes how about using a ~MySQL~ PostgreSQL CLI client to do that initial import, and then use Doctrine migrations for the rest?

@greg0ire Hmmm. If there were no Doctrine/migrations solutions I will need end up using such a custom script solution. Of course in my case the cli client will be a PostgreSQL one. Or I will need to create a script to check if the database was already initialized. I need this to be automated because the application will setup itself including the Cloud structure. I thought of using directly migrations because it already controls the database state and it will know it should run initialization once, then the subsequent pushes to the app respository will trigger only incremental migrations. As you metioned before another method compliant with addSql in <= v3.0.1 would be handy. Thanks.