5

When updating schema, doctrine always drops and add constraints. I think, it is something wrong...

php app/console doctrine:schema:update --force
Updating database schema...
Database schema updated successfully! "112" queries were executed

php app/console doctrine:schema:update --dump-sql
ALTER TABLE table.managers DROP CONSTRAINT FK_677E81B7A76ED395;
ALTER TABLE table.managers ADD CONSTRAINT FK_677E81B7A76ED395 FOREIGN KEY (user_id) REFERENCES table."user" (id) NOT DEFERRABLE INITIALLY IMMEDIATE;
...

php app/console doctrine:schema:validate
[Mapping]  OK - The mapping files are correct.
[Database] FAIL - The database schema is not in sync with the current mapping file.

How can this may be fixed?

sampleNull
  • 126
  • 1
  • 8

3 Answers3

3

After some digging into doctrine update schema methods, I've finally found an issue. The problem was with table names - "table.order" and "table.user". When doctrine makes diff, this names become non equal, because of internal escaping (?). So, "user" != user, and foreign keys to those tables (order, user) always recreating.

Solution #1 - just rename tables to avoid name matching with postgresql keywords, like my_user, my_order. Solution #2 - manually escape table names. This not worked for me, tried many different escaping ways.

I've applied solution #1 and now I see:

Nothing to update - your database is already in sync with the current entity metadata

sampleNull
  • 126
  • 1
  • 8
1

I have had the same issue on Postgres with a uniqueConstraint with a where clause.

* @ORM\Table(name="avatar",
* uniqueConstraints={
* @ORM\UniqueConstraint(name="const_name", columns={"id_user", "status"}, options={"where": "(status = 'pending')"})

Doctrine is comparing schemas from metadata and new generated schema, during indexes comparation where clauses are not matching.

string(34) "((status)::text = 'pending'::text)"
string(20) "(status = 'pending')"

You just have to change you where clause to match by

((avatar)::text = 'pending'::text)

PS: My issue was with Postgres database

I hope this will help someone.

Fred
  • 11
  • 3
0

I have come across this several times and it's because the php object was changed a few times and still does not match the mapping to the database. Basically a reboot will fix the issue but it can be ugly to implement.

If you drop the constraint in the database and php objects (remove the doctrine2 mappings), then update schema and confirm that nothing needs to be updated. Then add the doctrine mapping back to the php, update the schema using --dump-sql and review the changes that are shown. Confirm that this is exactly what you want and execute the update query. Now updating the schema should not show that anything else needs to be updated.

George
  • 1,478
  • 17
  • 28
  • Thanks for your answer, George. I've removed some constraint in one entity, and I got following error when execute update: [Doctrine\DBAL\DBALException] An exception occurred while executing 'DROP INDEX user_email_unique': SQLSTATE[42704]: Undefined object: 7 ERROR: index "user_email_unique" does not exist . But If I manually execute this query (DROP INDEX user_email_unique), all gone well. – sampleNull Apr 08 '14 at 13:14
  • But removing and adding constraints again doesn't reduce amount of ADD\DROP queries. I've also tried to completely DROP schema, and update from "clean" database, but got the same results. Looks like Doctrine2 badly handles postgresql constraints – sampleNull Apr 08 '14 at 13:39
  • I use postgresql and have encountered these problems. My problems usually related to the column type in that doctrine did not match exactly to the postgresql type and I used very basic types that may use more memory than needed. What type are you using for user_id? – George Apr 08 '14 at 15:02
  • /** * @ORM\ManyToOne(targetEntity="Company\UserBundle\Entity\User") * @var User */ protected $user; – sampleNull Apr 08 '14 at 16:02
  • In the user entity, what type is the ID column? Is it simply @ORM\Column(type="integer")? Can you check that all of your fields are one of the basic types in both the user and managers entities? [doctrine mapping types](http://doctrine-orm.readthedocs.org/en/latest/reference/basic-mapping.html) – George Apr 08 '14 at 16:24
  • Yes, both have * @ORM\Column(name="id",type="integer") – sampleNull Apr 08 '14 at 16:46