dbal
dbal copied to clipboard
doctrine
Doctrine ORM tries to recreate partial indexes with the same definition every time.
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
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 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;
@skobkin so ORM version is not causing this? Can you check with different DBAL and ORM versions to see if this is a regression?
@Ocramius Yes I can. Which versions you think will be more efficient to try?
@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.
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.
Hmm, wondering if DBAL is able to extract column options from an existing DB in MySQL...
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.
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)"}),
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();
}
}
Executing
./bin/console doctrine:schema:update --dump-sql
does always create the following output:
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.
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.
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)"})
* }
* )
...
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.
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
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
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;
}
Had a same issue with droping and creating index. Adding parentheses worked just fine for me.
options={"where": "(current = true)"}
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.
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.
I guess we can all use https://github.com/doctrine/dbal/issues/2866 to track progression on this issue
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.
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:
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.
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.
Here are some working examples for nullable datetime columns:
- PostgreSQL 9.6
- doctrine/dbal 2.13.1
- doctrine/orm 2.8.4
@ORM\UniqueConstraint(columns={"email", "user_id"}, options={"where": "(deleted_at IS NULL)"})
Multiple columns need another layer of brackets:
@ORM\UniqueConstraint(columns={"email", "account_id"}, options={"where":"((deleted_at IS NULL) AND (accepted_at IS NULL))"})
Note the uppercase keywords (looks like the lowercase booleans mentioned here act differently).
I had the similar experience with partial index and Postgres (Postgres 12.6, doctrine/dbal 2.13.1, doctrine/orm 2.9.2).
So here is example how I resolved this issue in my case when i had to use predicate on text column:
Annotation:
@ORM\Index(
name="recommended",
columns={"recommendation_till"},
options={"where": "((approved = true) AND ((status)::text = 'active'::text))"}
)
Generated SQL in migration generated with `doctrine:migrations:diff:
CREATE INDEX recommended ON article (recommendation_till)
WHERE ((approved = true) AND ((status)::text = \'active\'::text))
Running doctrine:migrations:diff second time will not try to recreate indexes 👌
Hello, another question, does PGSQL support partial indexing with doctrine?
- According to Doctrine docs https://www.doctrine-project.org/projects/doctrine-orm/en/2.17/reference/annotations-reference.html#uniqueconstraint
- PGSQL https://www.postgresql.org/docs/current/indexes-partial.html#INDEXES-PARTIAL-EX3
- Here is an answer but without sample Stack https://stackoverflow.com/questions/20671917/postgres-partial-unique-indexes-in-symfony
We have the same scenario which seems doesnt work
<entity name="App\Domain\UserPack\Email\Model\UserEmail"
repository-class="App\Infrastructure\Persistence\Repository\DB\DQL\UserEmail\DQLRepositoryUserEmail">
<!-- Indexes -->
<indexes>
<index name="user_email_search_index" columns="email"/>
</indexes>
<unique-constraints>
<unique-constraint
name="unique_email_for_user"
columns="user_uuid,email"
/>
<unique-constraint
name="user_only_one_active_fallback_and_main_email"
columns="user_uuid,is_enabled,is_fallback"
>
<!-- columns="user_uuid,is_enabled,is_fallback"-->
<!-- columns="user_uuid,email,is_enabled,is_fallback"-->
<options>
<option name="user_should_have_only_one_unique_email_option">
(((id IS NOT NULL) AND (name IS NULL)) AND (email IS NULL))
</option>
<!-- WHERE user_uuid AND email-->
<option name="user_should_have_only_one_enabled_main_email_option">
(user_uuid AND is_enabled AND is_fallback)
</option>
<option name="user_should_have_only_one_enabled_main_email_option">
(user_uuid AND is_enabled AND NOT is_fallback)
</option>
<option name="user_only_one_active_fallback_and_main_email_option">
((is_enabled AND is_fallback) AND (is_enabled AND NOT is_fallback))
</option>
</options>
</unique-constraint>
</unique-constraints>
<embedded name="email"
class="App\Domain\Core\ValueObjects\String\Email"
use-column-prefix="false"
/>
<embedded name="isEnabled"
class="App\Domain\Core\ValueObjects\Boolean\IsEnabled"
use-column-prefix="false"
/>
<embedded name="isFallback"
class="App\Domain\Core\ValueObjects\Boolean\IsFallback"
use-column-prefix="false"
/>
tried lots of ways e.g.
<unique-constraints>
<unique-constraint
name="unique_email_for_user"
columns="user_uuid,email"
/>
<unique-constraint
name="user_only_one_active_fallback_and_main_email"
columns="user_uuid,is_enabled,is_fallback"
>
<!-- columns="user_uuid,is_enabled,is_fallback"-->
<!-- columns="user_uuid,email,is_enabled,is_fallback"-->
<options>
<option name="user_should_have_only_one_unique_email_option">
(( (user_uuid IS NOT NULL) AND (is_enabled IS TRUE)) AND (is_fallback IS TRUE) ))
</option>
<option name="user_should_have_only_one_unique_email_option">
(( (user_uuid IS NOT NULL) AND (is_enabled IS true)) AND (is_fallback IS true) ))
</option>
<option name="user_should_have_only_one_unique_email_option">
(((user_uuid IS NOT NULL) AND (is_enabled IS TRUE)) AND (is_fallback IS TRUE))
</option>
<option name="user_should_have_only_one_unique_email_option">
WHERE: (((user_uuid IS NOT NULL) AND (is_enabled IS TRUE)) AND (is_fallback IS TRUE))
</option>
<option name="user_should_have_only_one_unique_email_option">
<option name="WHERE">
(((user_uuid IS NOT NULL) AND (is_enabled IS TRUE)) AND (is_fallback IS TRUE))
</option>
</option>
<option name="WHERE">
WHERE:(((user_uuid IS NOT NULL) AND (is_enabled IS TRUE)) AND (is_fallback IS TRUE))
</option>
<!-- WHERE user_uuid AND email-->
<option name="user_should_have_only_one_enabled_main_email_option">
(user_uuid AND is_enabled AND is_fallback)
</option>
<option name="user_should_have_only_one_enabled_main_email_option">
(user_uuid AND is_enabled AND NOT is_fallback)
</option>
<option name="user_only_one_active_fallback_and_main_email_option">
((is_enabled AND is_fallback) AND (is_enabled AND NOT is_fallback))
</option>
</options>
</unique-constraint>
</unique-constraints>
doctrine does not generate new SQL if option is added or removed. Only default UNIQUE index generated in case its added / removed
$this->addSql('CREATE UNIQUE INDEX user_only_one_active_fallback_and_main_email ON user_email (user_uuid, is_enabled, is_fallback)');
pg_config --version
PostgreSQL 13.13 (Debian 13.13-0+deb11u1)
"beberlei/doctrineextensions": "^v1.2.8",
"doctrine/collections": "^1.6",
"doctrine/common": "^3.3.0",
"doctrine/dbal": "^2.11",
"doctrine/doctrine-bundle": "^2.2",
"doctrine/doctrine-migrations-bundle": "^3.0",
"doctrine/orm": "^2.7.4",
Thanks in advance!
