Version 3.4.1 & the ORM SchemaTool

[HypeMC]

Bug Report

Q	A
Version	3.4.1
Platform	PostgreSQL

Summary

Since DBAL version 3.4.1, the ORM SchemaTool doesn't work as expected. I have found two problems with the SchemaTool::dropDatabase() functionality & PostgreSQL.

https://github.com/doctrine/orm/blob/5283e1441cc020d526d4842c6bfa21c1a500886d/lib/Doctrine/ORM/Tools/SchemaTool.php#L842-L850

This is for example used by the LiipTestFixturesBundle.

https://github.com/liip/LiipTestFixturesBundle/blob/fac42aff90023c698e4fa44101c406e6a9208b9e/src/Services/DatabaseTools/ORMDatabaseTool.php#L111

1. It tries to drop sequences that don't exist:

   public function testDropWithSequence(): void
   {
       $platform = $this->connection->getDatabasePlatform();
   
       if (! $platform instanceof PostgreSQLPlatform) {
           self::markTestSkipped('Test is for PostgreSQL.');
       }
   
       $schema = new Schema();
       $table  = $schema->createTable('t3');
       $table->addColumn('id', 'integer', ['notnull' => true])->setAutoincrement(true);
       $table->setPrimaryKey(['id']);
   
       $schemaManager = $this->connection->createSchemaManager();
       $schemaManager->createSchemaObjects($schema);
   
       // Emulate the SchemaTool
       $schema        = $schemaManager->createSchema();
       $dropSchemaSql = $schema->toDropSql($this->connection->getDatabasePlatform());
   
       foreach ($dropSchemaSql as $sql) {
           echo $sql, PHP_EOL;
           $this->connection->executeQuery($sql);
       }
   
       self::assertFalse($schemaManager->tablesExist(['t3']));
       self::assertCount(0, array_filter($schemaManager->listSequences(), static function ($sequence): bool {
           return strtolower($sequence->getName()) === 't3_id_seq';
       }));
   }

   The following SQL is generated:

   DROP TABLE t3
   DROP SEQUENCE t3_id_seq CASCADE

   The sequence t3_id_seq does exist, but it is removed with the drop table statement, so the drop sequence statement fails:

   There was 1 error:
   
   1) Doctrine\DBAL\Tests\SQL\Builder\CreateAndDropSchemaObjectsSQLBuilderTest::testDropWithSequence
   Doctrine\DBAL\Exception\TableNotFoundException: An exception occurred while executing a query: SQLSTATE[42P01]: Undefined table: 7 ERROR:  sequence "t3_id_seq" does not exist
   
   ~/Projects/Doctrine/dbal/src/Driver/API/PostgreSQL/ExceptionConverter.php:75
   ~/Projects/Doctrine/dbal/src/Connection.php:1822
   ~/Projects/Doctrine/dbal/src/Connection.php:1763
   ~/Projects/Doctrine/dbal/src/Connection.php:1034
   ~/Projects/Doctrine/dbal/tests/Functional/SQL/Builder/CreateAndDropSchemaObjectsSQLBuilderTest.php:77
   
   Caused by
   Doctrine\DBAL\Driver\PDO\Exception: SQLSTATE[42P01]: Undefined table: 7 ERROR:  sequence "t3_id_seq" does not exist
   
   ~/Projects/Doctrine/dbal/src/Driver/PDO/Exception.php:28
   ~/Projects/Doctrine/dbal/src/Driver/PDO/Connection.php:76
   ~/Projects/Doctrine/dbal/src/Connection.php:1029
   ~/Projects/Doctrine/dbal/tests/Functional/SQL/Builder/CreateAndDropSchemaObjectsSQLBuilderTest.php:77
   
   Caused by
   PDOException: SQLSTATE[42P01]: Undefined table: 7 ERROR:  sequence "t3_id_seq" does not exist
   
   ~/Projects/Doctrine/dbal/src/Driver/PDO/Connection.php:71
   ~/Projects/Doctrine/dbal/src/Connection.php:1029
   ~/Projects/Doctrine/dbal/tests/Functional/SQL/Builder/CreateAndDropSchemaObjectsSQLBuilderTest.php:77
   

2. It tries to remove the public schema:

   public function testDropPublic(): void
   {
       $platform = $this->connection->getDatabasePlatform();
   
       if (! $platform instanceof PostgreSQLPlatform) {
           self::markTestSkipped('Test is for PostgreSQL.');
       }
   
       $this->connection->executeStatement('CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public');
   
       $schema = new Schema();
       $table  = $schema->createTable('t4');
       $table->addColumn('id', 'integer', ['notnull' => true]);
       $table->setPrimaryKey(['id']);
   
       $schemaManager = $this->connection->createSchemaManager();
       $schemaManager->createSchemaObjects($schema);
   
       // Emulate the SchemaTool
       $schema        = $schemaManager->createSchema();
       $dropSchemaSql = $schema->toDropSql($this->connection->getDatabasePlatform());
   
       foreach ($dropSchemaSql as $sql) {
           echo $sql, PHP_EOL;
           $this->connection->executeQuery($sql);
       }
   
       self::assertFalse($schemaManager->tablesExist(['t4']));
   }

   The following SQL is generated:

   DROP TABLE t4
   DROP SCHEMA public

   Since the public schema is used by the pg_trgm extension the drop schema statement fails:

   There was 1 error:
   
   1) Doctrine\DBAL\Tests\SQL\Builder\CreateAndDropSchemaObjectsSQLBuilderTest::testDropPublic
   Doctrine\DBAL\Exception\DriverException: An exception occurred while executing a query: SQLSTATE[2BP01]: Dependent objects still exist: 7 ERROR:  cannot drop schema public because other objects depend on it
   DETAIL:  extension pg_trgm depends on schema public
   HINT:  Use DROP ... CASCADE to drop the dependent objects too.
   
   ~/Projects/Doctrine/dbal/src/Driver/API/PostgreSQL/ExceptionConverter.php:91
   ~/Projects/Doctrine/dbal/src/Connection.php:1822
   ~/Projects/Doctrine/dbal/src/Connection.php:1763
   ~/Projects/Doctrine/dbal/src/Connection.php:1034
   ~/Projects/Doctrine/dbal/tests/Functional/SQL/Builder/CreateAndDropSchemaObjectsSQLBuilderTest.php:109
   
   Caused by
   Doctrine\DBAL\Driver\PDO\Exception: SQLSTATE[2BP01]: Dependent objects still exist: 7 ERROR:  cannot drop schema public because other objects depend on it
   DETAIL:  extension pg_trgm depends on schema public
   HINT:  Use DROP ... CASCADE to drop the dependent objects too.
   
   ~/Projects/Doctrine/dbal/src/Driver/PDO/Exception.php:28
   ~/Projects/Doctrine/dbal/src/Driver/PDO/Connection.php:76
   ~/Projects/Doctrine/dbal/src/Connection.php:1029
   ~/Projects/Doctrine/dbal/tests/Functional/SQL/Builder/CreateAndDropSchemaObjectsSQLBuilderTest.php:109
   
   Caused by
   PDOException: SQLSTATE[2BP01]: Dependent objects still exist: 7 ERROR:  cannot drop schema public because other objects depend on it
   DETAIL:  extension pg_trgm depends on schema public
   HINT:  Use DROP ... CASCADE to drop the dependent objects too.
   
   ~/Projects/Doctrine/dbal/src/Driver/PDO/Connection.php:71
   ~/Projects/Doctrine/dbal/src/Connection.php:1029
   ~/Projects/Doctrine/dbal/tests/Functional/SQL/Builder/CreateAndDropSchemaObjectsSQLBuilderTest.php:109
   

Possible solutions

1. Changing the order of items passed to array_merge() in DropSchemaObjectsSQLBuilder::buildSQL() seems to fix the sequence problem:

   dbal/src/SQL/Builder/DropSchemaObjectsSQLBuilder.php

   Lines 29 to 32 in 94e0164

   	return array_merge(
   	$this->buildTableStatements($schema->getTables()),
   	$this->buildSequenceStatements($schema->getSequences()),
   	$this->buildNamespaceStatements($schema->getNamespaces()),

       public function buildSQL(Schema $schema): array
       {
           return array_merge(
   -           $this->buildTableStatements($schema->getTables()),
               $this->buildSequenceStatements($schema->getSequences()),
   +           $this->buildTableStatements($schema->getTables()),
               $this->buildNamespaceStatements($schema->getNamespaces()),
           );
       }

2. The DropSchemaSqlCollector did not generate drop statements for sequences schemas, but the DropSchemaObjectsSQLBuilder does, so maybe remove that from the DropSchemaObjectsSQLBuilder (probably not the right solution).

[morozov]

Thanks for the reproducers, @HypeMC. I've updated a few lines in the first one to make it compatible with both 3.3.8 and 3.4.x.

Changing the order of DROP statements for tables and sequences as you proposed seems reasonable, this is how DropSchemaSqlCollector does it in 3.3.8:

dbal/src/Schema/Visitor/DropSchemaSqlCollector.php

Lines 87 to 95 in f18aee0

	foreach ($this->sequences as $sequence) {
	assert($sequence instanceof Sequence);
	$sql[] = $this->platform->getDropSequenceSQL($sequence);
	}
	foreach ($this->tables as $table) {
	assert($table instanceof Table);
	$sql[] = $this->platform->getDropTableSQL($table);
	}

Strictly speaking, the DBAL shouldn't even try to drop the sequences that are owned by some tables since they get dropped with the tables but we could address this separately.

The DropSchemaSqlCollector did not generate drop statements for sequences, but the DropSchemaObjectsSQLBuilder does, so maybe remove that from the DropSchemaObjectsSQLBuilder (probably not the right solution).

This sounds right to me as well.

[morozov]

@HypeMC please see if #5604 addresses these issues.

[HypeMC]

@morozov Thank you for working on a fix for this issue, I can confirm that everything now works as expected.

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.