Doctrine DBAL

Incorrect sequence dropping in PostgreSQL

Details

  • Type: Bug Bug
  • Status: Resolved Resolved
  • Priority: Critical Critical
  • Resolution: Fixed
  • Affects Version/s: 2.2.2
  • Fix Version/s: 2.0.0-RC1-RC3
  • Component/s: Platforms
  • Labels:
    None
  • Environment:
    postgresql 8.4.3, Arch Linux 64-bit

Description

Currently, DBAL drops the PostgreSQL sequences using the query:

DROP SEQUENCE sequencename

While it is quite correct at the first look, it fails, if the sequence is actually used by a table. Because Doctrine 2 ORM tries to drop the sequences before the tables, it makes impossible to drop a database schema in PostgreSQL due to the following exception:

$ php53 doctrine.php orm:schema-tool:drop
Dropping database schema...
PDOException
SQLSTATE[2BP01]: Dependent objects still exist: 7 ERROR: cannot drop sequence admins_id_seq because other objects depend on it
DETAIL: default for table admins column id depends on sequence admins_id_seq
HINT: Use DROP ... CASCADE to drop the dependent objects too.
/.../Libs/Doctrine/DBAL/Connection.php
Line 570
Trace:
0. PDO::query on line 570
1. Doctrine\DBAL\Connection::executeQuery on line 484
2. Doctrine\ORM\Tools\SchemaTool::dropSchema on line 78
3. Doctrine\ORM\Tools\Console\Command\SchemaTool\DropCommand::executeSchemaCommand on line 59
4. Doctrine\ORM\Tools\Console\Command\SchemaTool\AbstractCommand::execute on line 159
5. Symfony\Component\Console\Command\Command::run on line 205
6. Symfony\Component\Console\Application::doRun on line 117
7. Symfony\Component\Console\Application::run on line 7

A solution is simply to add the "CASCADE" keyword at the end of the query.

Although I encountered this problem on DBAL 2.0-beta4, I checked the most up-to-date code on Git, and the problem is still present there.

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Benjamin Eberlei added a comment -

Would it help to drop sequences after tables? If then i would just move the code blocks.

Show
Benjamin Eberlei added a comment - Would it help to drop sequences after tables? If then i would just move the code blocks.
Hide
Permalink
Tomasz Jędrzejewski added a comment -

In this particular case - yes, it would help. But consider that different database engines may have different dependencies between schema elements and be more or less restrictive, so they may require different order of code blocks. I'd recommend to make a bit deeper investigation here in order not to cause potential problems with other database engines.

Show
Tomasz Jędrzejewski added a comment - In this particular case - yes, it would help. But consider that different database engines may have different dependencies between schema elements and be more or less restrictive, so they may require different order of code blocks. I'd recommend to make a bit deeper investigation here in order not to cause potential problems with other database engines.
Hide
Permalink
Benjamin Eberlei added a comment -

Fixed

Show
Benjamin Eberlei added a comment - Fixed
Benjamin Eberlei made changes -
Field Original Value New Value
Status Open [ 1 ] Resolved [ 5 ]
Fix Version/s 2.0.0-RC1 [ 10094 ]
Resolution Fixed [ 1 ]
Hide
Permalink
Jon Wadsworth added a comment -

This issue is happening again with Doctrine 2.2.2 on Postgres 9.1.3. when trying to drio a database I get this message even with --full-database

> php doctrine.php orm:schema-tool:drop --force --full-database
Dropping database schema...

[PDOException]
SQLSTATE[2BP01]: Dependent objects still exist: 7 ERROR: cannot drop sequence policycategory_id_seq because other objects depend on it
DETAIL: default for table policycategory column id depends on sequence policycategory_id_seq
HINT: Use DROP ... CASCADE to drop the dependent objects too.

I would love to help diagnose, just let me know what you need and I will be more than happy to help.

Show
Jon Wadsworth added a comment - This issue is happening again with Doctrine 2.2.2 on Postgres 9.1.3. when trying to drio a database I get this message even with --full-database > php doctrine.php orm:schema-tool:drop --force --full-database Dropping database schema... [PDOException] SQLSTATE [2BP01] : Dependent objects still exist: 7 ERROR: cannot drop sequence policycategory_id_seq because other objects depend on it DETAIL: default for table policycategory column id depends on sequence policycategory_id_seq HINT: Use DROP ... CASCADE to drop the dependent objects too. I would love to help diagnose, just let me know what you need and I will be more than happy to help.
Jon Wadsworth made changes -
Affects Version/s 2.2.2 [ 10197 ]
Affects Version/s 2.0.0-BETA4 [ 10071 ]
Benjamin Eberlei made changes -
Workflow jira [ 11936 ] jira-feedback2 [ 17667 ]
Benjamin Eberlei made changes -
Workflow jira-feedback2 [ 17667 ] jira-feedback3 [ 20022 ]
Hide
Permalink
Jon Wadsworth added a comment -

I forgot to update this for anyone else who might have had my problem. The issue was solved for me when I was reviewing some models. The project was originally intended for MySQL and Generated Value Strategy was not set to Auto. Upon changing it to auto, everything worked correctly.

Show
Jon Wadsworth added a comment - I forgot to update this for anyone else who might have had my problem. The issue was solved for me when I was reviewing some models. The project was originally intended for MySQL and Generated Value Strategy was not set to Auto. Upon changing it to auto, everything worked correctly.
This list may be incomplete, as errors occurred whilst retrieving source from linked applications:
  • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DBAL-54, expand=changesets[-21:-1].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

People

  • Assignee:
    Benjamin Eberlei
    Reporter:
    Tomasz Jędrzejewski
Vote (0)
Watch (1)

Dates

  • Created:
    Updated:
    Resolved: