Dbal: Doctrine ORM tries to recreate partial indexes with the same definition every time.

[skobkin]

I've just encountered strange behavior when Doctrine ORM 2.5.5 tries to recreate some of indexes each time when I'm generating the migration or using doctrine:schema:update in my project.

I have following indexes definition:

/**
 * @ORM\Table(name="telegram_accounts", schema="users", indexes={
 *      @ORM\Index(name="subscriber_notification_idx", columns={"subscriber_notification"}, options={"where": "subscriber_notification = TRUE"}),
 *      @ORM\Index(name="rename_notification_idx", columns={"rename_notification"}, options={"where": "rename_notification = TRUE"}),
 * })
 * @ORM\Entity(repositoryClass="Skobkin\Bundle\PointToolsBundle\Repository\Telegram\AccountRepository")
 * @ORM\HasLifecycleCallbacks()
 */
class Account

Full entity code

Every time I do that it generates following SQL:

-- You can also see that index names are generated without schema name which is other bug (I think it's known)
DROP INDEX rename_notification_idx;
DROP INDEX subscriber_notification_idx;
CREATE INDEX rename_notification_idx ON users.telegram_accounts (rename_notification) WHERE rename_notification = TRUE;
CREATE INDEX subscriber_notification_idx ON users.telegram_accounts (subscriber_notification) WHERE subscriber_notification = TRUE;

Even if I apply this migration or do doctrine:schema:update --force it will do that next time again.

My stack:

• PHP 7.0.14
• PostgreSQL 9.6.1
• symfony/symfony v2.7.21
• doctrine/annotations v1.3.0
• doctrine/cache v1.6.1
• doctrine/collections v1.3.0
• doctrine/common v2.6.2
• doctrine/data-fixtures v1.2.2
• doctrine/dbal v2.5.5
• doctrine/doctrine-bundle 1.6.4
• doctrine/doctrine-cache-bundle 1.3.0
• doctrine/doctrine-fixtures-bundle 2.3.0
• doctrine/doctrine-migrations-bundle v1.2.1
• doctrine/inflector v1.1.0
• doctrine/instantiator 1.0.5
• doctrine/lexer v1.0.1
• doctrine/migrations 1.4.1
• doctrine/orm v2.5.5

My project's config.yml

[Ocramius]

@skobkin does this come up also with 2.5.4?

[skobkin]

@Ocramius Yes. I've just downgraded doctrine/orm to 2.5.4

  - Removing doctrine/orm (v2.5.5)
  - Installing doctrine/orm (v2.5.4)

Then generated new migration with following code:

    public function up(Schema $schema)
    {
        // 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\'.');

        // I've added 'users' schema here manually to prevent migration fail
        $this->addSql('DROP INDEX users.rename_notification_idx');
        $this->addSql('DROP INDEX users.subscriber_notification_idx');
        $this->addSql('CREATE INDEX rename_notification_idx ON users.telegram_accounts (rename_notification) WHERE rename_notification = TRUE');
        $this->addSql('CREATE INDEX subscriber_notification_idx ON users.telegram_accounts (subscriber_notification) WHERE subscriber_notification = TRUE');
    }

Then I've done doctrine:migration:migrate, then doctrine:schema:update --dump-sql and here it is:

$ sf doc:sche:up --dump-sql
DROP INDEX subscriber_notification_idx;
DROP INDEX rename_notification_idx;
CREATE INDEX subscriber_notification_idx ON users.telegram_accounts (subscriber_notification) WHERE subscriber_notification = TRUE;
CREATE INDEX rename_notification_idx ON users.telegram_accounts (rename_notification) WHERE rename_notification = TRUE;

[Ocramius]

@skobkin so ORM version is not causing this? Can you check with different DBAL and ORM versions to see if this is a regression?

[skobkin]

@Ocramius Yes I can. Which versions you think will be more efficient to try?

[Ocramius]

@skobkin I'd try 2.4.latest of both ORM and DBAL, then 2.5.0 of both. If the bug is reproducible in 2.4.0, then this is a new bug, not a regression.

[skobkin]

doctrine/orm 2.4.8, doctrine/dbal 2.4.5 - can't reproduce due to lack of options support in @Index annotation:

$ sf doc:sche:up --dump-sql
...                                                                                                                                                          
  [Doctrine\Common\Annotations\AnnotationException]                                                                                                                                                   
  [Creation Error] The annotation @ORM\Index declared on class Skobkin\Bundle\PointToolsBundle\Entity\Telegram\Account does not have a property named "options". Available properties: name, columns

Changing doctrine/orm and doctrine/dbal both to 2.5.0 forced me to use --with-dependencies:

  - Removing doctrine/annotations (v1.3.0)
  - Installing doctrine/annotations (v1.3.1)
    Downloading: 100%         

  - Removing doctrine/common (v2.6.2)
  - Installing doctrine/common (v2.5.3)
    Loading from cache

  - Removing doctrine/dbal (v2.5.5)
  - Installing doctrine/dbal (v2.5.0)
    Downloading: 100%         

  - Removing doctrine/orm (v2.5.6)
  - Installing doctrine/orm (v2.5.0)
    Downloading: 100%

After that I generated new migration:

// Seems like 2.5.0 in comparison with 2.5.5 have some problems with named indexes (I also generated migration which renamed IDX_SOME_HASH to human readable names after some of composer updates recently)
// https://bitbucket.org/skobkin/point-tools/src/fd3cd2d5171e043ab8011890d89eaf63bd6504d1/app/DoctrineMigrations/Version20170108152129.php?at=master&fileviewer=file-view-default
$this->addSql('ALTER INDEX subscriptions.author_idx RENAME TO IDX_22DA64DDF675F31B');
$this->addSql('ALTER INDEX subscriptions.subscriber_idx RENAME TO IDX_22DA64DD7808B1AD');

// The problem is still here:
$this->addSql('DROP INDEX subscriber_notification_idx');
$this->addSql('DROP INDEX rename_notification_idx');
$this->addSql('CREATE INDEX subscriber_notification_idx ON users.telegram_accounts (subscriber_notification) WHERE subscriber_notification = TRUE');
$this->addSql('CREATE INDEX rename_notification_idx ON users.telegram_accounts (rename_notification) WHERE rename_notification = TRUE');

After execution of this migration doc:sche:up --dump-sql returns this:

DROP INDEX subscriber_notification_idx;
DROP INDEX rename_notification_idx;
CREATE INDEX subscriber_notification_idx ON users.telegram_accounts (subscriber_notification) WHERE subscriber_notification = TRUE;
CREATE INDEX rename_notification_idx ON users.telegram_accounts (rename_notification) WHERE rename_notification = TRUE;

Seems like the problem was introduced with @Index(options={}) support.

[Ocramius]

Hmm, wondering if DBAL is able to extract column options from an existing DB in MySQL...

[andreasferber]

Try putting parentheses around your partial index WHERE clause:

@ORM\Index(name="subscriber_notification_idx", columns={"subscriber_notification"}, options={"where": "(subscriber_notification = TRUE)"}),

The way getListTableIndexesSQL() in Doctrine\DBAL\Platform\PostgreSqlPlatform queries the PostgreSQL system tables leads to those parentheses being added if they aren't already there, which makes the schema tool notice a difference.

[CvekCoding]

Had the same issue.
Solution is to put paretheses around WHERE clause (as andreasferber suggested) + use lower-case "true". So the proper string will be:

@ORM\Index(name="subscriber_notification_idx", columns={"subscriber_notification"}, options={"where": "(subscriber_notification = true)"}),

[elfin-sbreuers]

I observe the same issue without using Index explicitly. I am using doctrine in a symfony project with a sqlite database. The following entities with a OneToMany relationship are created:

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity(repositoryClass="App\Repository\LegRepository")
 */
class Leg
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Dog", inversedBy="leg")
     */
    private $dog;
}

<?php

namespace App\Entity;

use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity(repositoryClass="App\Repository\DogRepository")
 */
class Dog
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\OneToMany(targetEntity="App\Entity\Leg", mappedBy="dog")
     */
    private $legs;

    public function __construct()
    {
        $this->legs = new ArrayCollection();
    }
}



md5-b3d0f40b59da51e0c48b4d6213ea0a4d



./bin/console doctrine:schema:update --dump-sql



md5-7021c6217fc302988d15a3bd62af0968



     DROP INDEX IDX_75D0804F634DFEB;
     CREATE TEMPORARY TABLE __temp__leg AS SELECT id, dog_id FROM leg;
     DROP TABLE leg;
     CREATE TABLE leg (id INTEGER NOT NULL, dog_id INTEGER DEFAULT NULL, PRIMARY KEY(id), CONSTRAINT FK_75D0804F634DFEB FOREIGN KEY (dog_id) REFERENCES dog (id) NOT DEFERRABLE INITIALLY IMMEDIATE);
     INSERT INTO leg (id, dog_id) SELECT id, dog_id FROM __temp__leg;
     DROP TABLE __temp__leg;
     CREATE INDEX IDX_75D0804F634DFEB ON leg (dog_id);

independent of the state of the database.
This also spoiles the doctrine:migrations of my symfony project. I observed the issue with doctrine/dbal v2.7.1 and v2.5.13. Symfony in version 3.3.16 and 4.0.9 are both affected.

Could you give me a hint of how to address this issue? Since I don't see how the annotation syntax could help in this case.

[elfin-sbreuers]

I withdraw my comment. If somebody else observes this issue:

It is based on the fact that dbal removed foreign key support for sqlite platform.

[hacfi]

I've come across the same issue:
Stack:
PHP 7.2.12
MySQL 5.7.21
doctrine/dbal v2.9.1
doctrine/orm v2.6.3
symfony/symfony v4.2.1

...
 * @ORM\Table(
 *     uniqueConstraints={
 *         @ORM\UniqueConstraint(name="IDX_UNIQ_CUSTADDR_FKCUST_DEFBILLADDR", columns={"fk_customer"}, options={"where": "
 * (is_default_billing_address = true)"}),
 *         @ORM\UniqueConstraint(name="IDX_UNIQ_CUSTADDR_FKCUST_DEFSHIPADDR", columns={"fk_customer"}, options={"where": "
 * (is_default_shipping_address = true)"})
 *     }
 * )
...

[hacfi]

False alarm in my case: I used this on a previous project with Postgresql and trying it with MySQL now which doesn't support partial indexes. The schema tool compares the index coming from the db that doesn't have a where clause with the index generated from the mapping which has a where clause.

[tasselchof]

I have an issue with index:

rate_modifier_id = 3 AND state NOT IN ('new')

doctrine/dbal v2.9.2
doctrine/orm v2.6.3

[trickeyone]

I've been getting this issue for a while (since 2015), can't give a specific version, but whatever the version was in 2015.
It also occurs for foreign keys. It will add drops for FKs that haven't been changed.

Edit: For defined Indexes in @Table, I've seen the issue when not specifying a where condition

[ernestas-s]

Having same problem.

doctrine/annotations                v1.6.1   
doctrine/cache                      v1.8.0   
doctrine/collections                v1.6.1   
doctrine/common                     v2.10.0  
doctrine/data-fixtures              v1.3.1   
doctrine/dbal                       v2.9.2   
doctrine/doctrine-bundle            1.10.2   
doctrine/doctrine-cache-bundle      1.3.5    
doctrine/doctrine-fixtures-bundle   3.1.0    
doctrine/doctrine-migrations-bundle v1.3.2   
doctrine/event-manager              v1.0.0   
doctrine/inflector                  v1.3.0   
doctrine/instantiator               1.2.0    
doctrine/lexer                      v1.0.1   
doctrine/migrations                 v1.8.1   
doctrine/orm                        v2.6.3   
doctrine/persistence                1.1.1    
doctrine/reflection                 v1.0.0

$this->addSql('ALTER TABLE employee_schedule_breaks DROP FOREIGN KEY FK_4F7C782A8C03F15CAA9E377A');
$this->addSql('DROP INDEX IDX_4F7C782AAA9E377A8C03F15C ON employee_schedule_breaks');
$this->addSql('ALTER TABLE 
          employee_schedule_breaks 
        ADD 
          CONSTRAINT FK_4F7C782A8C03F15CAA9E377A FOREIGN KEY (employee_id, date) REFERENCES employee_schedule (employee_id, date) ON DELETE CASCADE');

/**
 * @ORM\Entity
 * @ORM\Table(name="employee_schedule_breaks")
 */
class EmployeeScheduleEntryBreak
{
    /**
     * @ORM\ManyToOne(targetEntity="EmployeeScheduleEntry", inversedBy="breaks")
     * @ORM\JoinColumns({
     *      @ORM\JoinColumn(name="employee_id", referencedColumnName="employee_id", onDelete="CASCADE"),
     *      @ORM\JoinColumn(name="date", referencedColumnName="date", onDelete="CASCADE"),
     * })
     * @var EmployeeScheduleEntry
     */
    private $entry;

    /**
     * @ORM\Id
     * @ORM\ManyToOne(targetEntity="xxx\EmployeeBundle\Entity\Employee")
     * @ORM\JoinColumn(name="employee_id", nullable=false, onDelete="CASCADE")
     * @var Employee
     */
    private $employee;

    /**
     * @ORM\Column(name="date", type="date", nullable=false)
     * @var Date
     */
    private $date;
}

/**
 * @ORM\Entity(repositoryClass="xxx\CalendarBundle\Entity\Repository\EmployeeScheduleEntryRepository")
 * @ORM\Table(name="employee_schedule")
 */
class EmployeeScheduleEntry
{
    /**
     * @ORM\Id
     * @ORM\ManyToOne(targetEntity="xxx\EmployeeBundle\Entity\Employee")
     * @ORM\JoinColumn(name="employee_id", nullable=false, onDelete="CASCADE")
     * @var Employee
     */
    private $employee;

    /**
     * @ORM\Column(name="date", type="date", nullable=false)
     * @ORM\Id
     * @var Date
     */
    private $date;
}

[lyrixx]

Same issue here. With

• doctrine/dbal 2.9.1 => OK
• doctrine/dbal 2.9.2 => KO

[kocur1508]

Had a same issue with droping and creating index. Adding parentheses worked just fine for me.

options={"where": "(current = true)"}

[bmxpapr]

Same issue here with the following stack:

- PHP 7.2.19-1+ubuntu16.04.1+deb.sury.org+1 (cli) (built: May 31 2019 11:16:55) ( NTS )
- mysqld  Ver 5.6.43 for Linux on x86_64 (MySQL Community Server (GPL))
- symfony/symfony                     v4.2.11
- doctrine/annotations                v1.8.0
- doctrine/cache                      v1.8.1
- doctrine/collections                v1.6.2
- doctrine/common                     v2.11.0
- doctrine/dbal                       v2.9.2
- doctrine/doctrine-bundle            1.9.1
- doctrine/doctrine-cache-bundle      1.3.3
- doctrine/doctrine-migrations-bundle v2.0.0
- doctrine/event-manager              v1.0.0
- doctrine/inflector                  v1.3.0
- doctrine/instantiator               1.2.0
- doctrine/lexer                      1.1.0
- doctrine/migrations                 2.1.1
- doctrine/orm                        v2.6.4
- doctrine/persistence                1.1.1
- doctrine/reflection                 v1.0.0

I also confirm that reverting doctrine/dbal to 2.9.1 solves the issue, so this suggets the bug was introduced in 2.9.2. I also tried uprading to 2.9.3 and 2.10.0 but the issue is still there.

[yvoyer]

I made a PR to reproduce the issue in DBAL. https://github.com/doctrine/dbal/pull/3458
I'll check if it is still a WIP or not.

[ostrolucky]

Closing here since it's DBAL and not ORM issue.

[lyrixx]

I guess we can all use https://github.com/doctrine/dbal/issues/2866 to track progression on this issue

[lyrixx]

@ostrolucky Could you move this issue to the DBAL repo ? thanks

[henrypenny]

I had a similar issue with a key.

I debugged in Doctrine\DBAL\Schema\Comparitor::diffTable to find out the difference between $fromSchema and $toSchema

Before:

@ORM\Index(columns={"text"}, options={"lengths": {100}})

After:

@ORM\Index(columns={"text"}, options={"lengths": {"100"}})

Once I added quotations around 100 the from and to schema's matched and the diff no longer dropped and recreated the key everytime.

[PowerKiKi]

In this (three year old) thread different people had different problems and solutions. But AFAIK nobody were able to clearly identify a bug in DBAL. Instead the problems are the result of a misuse of (a slightly tricky part of) the lib.

So I'm in favor to close this issue. And re-open a new issue only if somebody can clearly identify and reproduce a bug.

For the record possible solutions for the end-users are:

• Adding parentheses
• Using lowercase keywords
• Specifying lengths
• Quoting lengths

[henrypenny]

In the case of my issue:
The parentheses should not be required as the value is an integer.
However, making this change would likely be a breaking change.
I would like to see the parenthesis be made optional - or for an error to be thrown if they are omitted.

[leimd]

We are running into the same issue with Postgres 9.6 as well. In our case, we are building partial index like this:

*     @ORM\Index(
*          name="monetaryitem_orgunit_idx",
*          columns={"orgunit_id"},
*          options={
*              "where": "(type IN ('invoice', 'purchaseorder', 'beanpayment'))"
*          }
*     )

And everytime we generate the migration script, it would ask us to drop the old index and create new index. After putting some var_dump statement in lib/Doctrine/DBAL/Schema/Index.php::samePartialIndex, we've found out that the where clause in the annotation and the where clause in the actual database is acutally different, the where clause in database is converted to something like this
CREATE INDEX monetaryitem_orgunit_created_idx ON public.monetaryitem USING btree (created, orgunit_id) WHERE ((type)::text = ANY ((ARRAY['invoice'::character varying, 'importedpayment'::character varying, 'purchaseorder'::character varying])::text[]))

and the result from our var_dumps looks like this:

/var/bean/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/Index.php:213:
string(72) "This index option: (type IN ('invoice', 'purchaseorder', 'beanpayment'))"
/var/bean/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/Index.php:214:
string(158) "Other index option: ((type)::text = ANY ((ARRAY['invoice'::character varying, 'purchaseorder'::character varying, 'beanpayment'::character varying])::text[]))"

This has also been verified by manually create the index, and afterwards the indexdef in pg_indexes table is automatically changed by Postgres.