Sqlite schema update keeps on recreating index on join table

[ThePeterMick]

Bug Report

Q	A
BC Break	no
Version	2.8.0

Summary

When using SQLite with indices on join table with indices the validate command will always state that the mapping is OK but database is not, as below. This may or may not be only related to join tables. Using latest stable symfony website skeleton.

Current behaviour

As in the summary above.

How to reproduce

SQLite version: 3.15.2

Create schema: php bin/console doctrine:schema:create [OK] Database schema created successfully!

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

Update: php bin/console doctrine:schema:update Updating database schema... 9 queries were executed [OK] Database schema updated successfully!

And forever after when you run the update command it will keep on executing the 9 queries, as below: php bin/console doctrine:schema:update --dump-sql The following SQL statements will be executed: DROP INDEX IDX_2DE8C6A3D60322AC; DROP INDEX IDX_2DE8C6A3A76ED395; CREATE TEMPORARY TABLE __temp__user_role AS SELECT user_id, role_id FROM user_role; DROP TABLE user_role; CREATE TABLE user_role (user_id INTEGER NOT NULL, role_id INTEGER NOT NULL, PRIMARY KEY(user_id, role_id), CONSTRAINT FK_2DE8C6A3A76ED395 FOREIGN KEY (user_id) REFERENCES users (id) NOT DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT FK_2DE8C6A3D60322AC FOREIGN KEY (role_id) REFERENCES roles (id) NOT DEFERRABLE INITIALLY IMMEDIATE); INSERT INTO user_role (user_id, role_id) SELECT user_id, role_id FROM __temp__user_role; DROP TABLE __temp__user_role; CREATE INDEX IDX_2DE8C6A3D60322AC ON user_role (role_id); CREATE INDEX IDX_2DE8C6A3A76ED395 ON user_role (user_id);

Entities User (shortened for brevity)

class User
{
    /**
     * @var int
     *
     * @ORM\Id
     * @ORM\GeneratedValue
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @var array
     *
     * @ORM\ManyToMany(
     *      targetEntity="Role",
     *      inversedBy="users"
     * )
     * @ORM\JoinTable(name="user_role",
     *      joinColumns={
     *          @ORM\JoinColumn(name="user_id", referencedColumnName="id")},
     *      inverseJoinColumns={
     *          @ORM\JoinColumn(name="role_id", referencedColumnName="id")}
     * )
     */
    private $roles;
    
    // constructors, getters, setters etc.
}

Role

<?php

namespace App\Entity;

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

/**
 * @ORM\Table(name="roles")
 * @ORM\Entity()
 */
class Role
{
    /**
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id()
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\Column(name="name", type="string", length=30)
     */
    private $name;

    /**
     * @ORM\Column(name="role", type="string", length=20, unique=true)
     */
    private $role;

    /**
     * @ORM\ManyToMany(targetEntity="User", mappedBy="roles")
     */
    private $users;

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

    /**
     * @see RoleInterface
     */
    public function getRole()
    {
        return $this->role;
    }

    // ... getters and setters for each property

    /**
     * Get id
     *
     * @return integer
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * Set name
     *
     * @param string $name
     * @return Role
     */
    public function setName($name)
    {
        $this->name = $name;

        return $this;
    }

    /**
     * Get name
     *
     * @return string
     */
    public function getName()
    {
        return $this->name;
    }

    /**
     * Set role
     *
     * @param string $role
     * @return Role
     */
    public function setRole($role)
    {
        $this->role = $role;

        return $this;
    }

    /**
     * Add users
     *
     * @param \App\Entity\User $users
     * @return Role
     */
    public function addUser(\App\Entity\User $users)
    {
        $this->users[] = $users;

        return $this;
    }

    /**
     * Remove users
     *
     * @param \App\Entity\User $users
     */
    public function removeUser(\App\Entity\User $users)
    {
        $this->users->removeElement($users);
    }

    /**
     * Get users
     *
     * @return \Doctrine\Common\Collections\Collection
     */
    public function getUsers()
    {
        return $this->users;
    }
}

Expected behaviour

Do not re-create the indices that are already present and valid in the database. This issue does not happen when using MySQL.

If you need more info please let me know, thanks! :o)

[gogaz]

Same issue here, is there any workaround ?

Apprently, this does not affect anything apart from doctrine:schema

[Seb33300]

I am facing the same issue.

Every time I am doing a doctrine:schema:validate:

[ERROR] The database schema is not in sync with the current mapping file.

doctrine:schema:update returns always the sames queries continuously...

CREATE TEMPORARY TABLE __temp__... AS ...;
DROP TABLE ...;
CREATE TABLE ...;
INSERT INTO ... FROM __temp__...;
DROP TABLE __temp__

Not sure if this is important but I am on Windows

[braisdesousa]

Same Problem here.

[d5c4b3]

I'm having the same issue. I'm not using JoinTable though.