doctrine/archived-orm
1
0
Fork 0
mirror of https://github.com/doctrine/orm.git synced 2026-03-24 06:52:09 +01:00
Code Issues 5.3k Packages Projects Releases 100 Wiki Activity

[Bug] SchemaValidator - Sqlite #5540

New Issue
Closed
opened 2026-01-22 15:10:32 +01:00 by admin · 8 comments
admin commented 2026-01-22 15:10:32 +01:00
Owner
Copy Link

Originally created by @benito103e on GitHub (May 15, 2017).

Originally assigned to: @lcobucci on GitHub.

Hello,

When using pdo_sqlite driver, if the schema contains Foreign keys (ManyToOne join columns on my case) the schema validator fails :

[Database] FAIL - The database schema is not in sync with the current mapping file.

Originally created by @benito103e on GitHub (May 15, 2017). Originally assigned to: @lcobucci on GitHub. Hello, When using pdo_sqlite driver, if the schema contains Foreign keys (ManyToOne join columns on my case) the schema validator fails : > [Database] FAIL - The database schema is not in sync with the current mapping file.
admin added the BugInvalid labels 2026-01-22 15:10:32 +01:00
admin closed this issue 2026-01-22 15:10:32 +01:00
admin commented 2026-01-22 15:10:33 +01:00
Author
Owner
Copy Link

@relte commented on GitHub (Nov 19, 2017):

In my case it's many-to-many relationship and each time I execute doctrine:schema:update --force the output of the dump slightly changes.

Before update:

DROP INDEX IDX_51A228EEBAD26311; -- <--
DROP INDEX IDX_51A228EE166D1F9C; -- <--
CREATE TEMPORARY TABLE __temp__projects_tags AS SELECT project_id, tag_id FROM projects_tags;
DROP TABLE projects_tags;
CREATE TABLE projects_tags (project_id INTEGER NOT NULL, tag_id INTEGER NOT NULL, PRIMARY KEY(project_id, tag_id), CONSTRAINT FK_51A228EE166D1F9C FOREIGN KEY (project_id) REFERENCES project (id) NOT DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT FK_51A228EEBAD26311 FOREIGN KEY (tag_id) REFERENCES tag (id) NOT DEFERRABLE INITIALLY IMMEDIATE);
INSERT INTO projects_tags (project_id, tag_id) SELECT project_id, tag_id FROM __temp__projects_tags;
DROP TABLE __temp__projects_tags;
CREATE INDEX IDX_51A228EEBAD26311 ON projects_tags (tag_id); -- <--
CREATE INDEX IDX_51A228EE166D1F9C ON projects_tags (project_id); -- <--

After update:

DROP INDEX IDX_51A228EE166D1F9C; -- <--
DROP INDEX IDX_51A228EEBAD26311; -- <--
CREATE TEMPORARY TABLE __temp__projects_tags AS SELECT project_id, tag_id FROM projects_tags;
DROP TABLE projects_tags;
CREATE TABLE projects_tags (project_id INTEGER NOT NULL, tag_id INTEGER NOT NULL, PRIMARY KEY(project_id, tag_id), CONSTRAINT FK_51A228EE166D1F9C FOREIGN KEY (project_id) REFERENCES project (id) NOT DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT FK_51A228EEBAD26311 FOREIGN KEY (tag_id) REFERENCES tag (id) NOT DEFERRABLE INITIALLY IMMEDIATE);
INSERT INTO projects_tags (project_id, tag_id) SELECT project_id, tag_id FROM __temp__projects_tags;
DROP TABLE __temp__projects_tags;
CREATE INDEX IDX_51A228EE166D1F9C ON projects_tags (project_id); -- <--
CREATE INDEX IDX_51A228EEBAD26311 ON projects_tags (tag_id); -- <--

As you can see only the order of dropping and creating indexes has changed.
Does anyone have any idea why is that?

If it helps, here is the mapping:

App\Entity\Project:
    type: entity
    table: project
    id:
        id:
            type: integer
            generator: { strategy: AUTO }
    fields:
        title:
            type: string
            length: 100
        description:
            type: text
        source:
            type: string
            length: 200
        demo:
            type: string
            length: 200
    manyToMany:
        tags:
            targetEntity: Tag
            joinTable:
                name: projects_tags
                joinColumns:
                    project_id:
                        referencedColumnName: id
                inverseJoinColumns:
                    tag_id:
                        referencedColumnName: id
@relte commented on GitHub (Nov 19, 2017): In my case it's many-to-many relationship and each time I execute `doctrine:schema:update --force ` the output of the dump slightly changes. Before update: ```sql DROP INDEX IDX_51A228EEBAD26311; -- <-- DROP INDEX IDX_51A228EE166D1F9C; -- <-- CREATE TEMPORARY TABLE __temp__projects_tags AS SELECT project_id, tag_id FROM projects_tags; DROP TABLE projects_tags; CREATE TABLE projects_tags (project_id INTEGER NOT NULL, tag_id INTEGER NOT NULL, PRIMARY KEY(project_id, tag_id), CONSTRAINT FK_51A228EE166D1F9C FOREIGN KEY (project_id) REFERENCES project (id) NOT DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT FK_51A228EEBAD26311 FOREIGN KEY (tag_id) REFERENCES tag (id) NOT DEFERRABLE INITIALLY IMMEDIATE); INSERT INTO projects_tags (project_id, tag_id) SELECT project_id, tag_id FROM __temp__projects_tags; DROP TABLE __temp__projects_tags; CREATE INDEX IDX_51A228EEBAD26311 ON projects_tags (tag_id); -- <-- CREATE INDEX IDX_51A228EE166D1F9C ON projects_tags (project_id); -- <-- ``` After update: ```sql DROP INDEX IDX_51A228EE166D1F9C; -- <-- DROP INDEX IDX_51A228EEBAD26311; -- <-- CREATE TEMPORARY TABLE __temp__projects_tags AS SELECT project_id, tag_id FROM projects_tags; DROP TABLE projects_tags; CREATE TABLE projects_tags (project_id INTEGER NOT NULL, tag_id INTEGER NOT NULL, PRIMARY KEY(project_id, tag_id), CONSTRAINT FK_51A228EE166D1F9C FOREIGN KEY (project_id) REFERENCES project (id) NOT DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT FK_51A228EEBAD26311 FOREIGN KEY (tag_id) REFERENCES tag (id) NOT DEFERRABLE INITIALLY IMMEDIATE); INSERT INTO projects_tags (project_id, tag_id) SELECT project_id, tag_id FROM __temp__projects_tags; DROP TABLE __temp__projects_tags; CREATE INDEX IDX_51A228EE166D1F9C ON projects_tags (project_id); -- <-- CREATE INDEX IDX_51A228EEBAD26311 ON projects_tags (tag_id); -- <-- ``` As you can see only the order of dropping and creating indexes has changed. Does anyone have any idea why is that? If it helps, here is the mapping: ```yaml App\Entity\Project: type: entity table: project id: id: type: integer generator: { strategy: AUTO } fields: title: type: string length: 100 description: type: text source: type: string length: 200 demo: type: string length: 200 manyToMany: tags: targetEntity: Tag joinTable: name: projects_tags joinColumns: project_id: referencedColumnName: id inverseJoinColumns: tag_id: referencedColumnName: id ```
admin commented 2026-01-22 15:10:34 +01:00
Author
Owner
Copy Link

@lcobucci commented on GitHub (Nov 24, 2017):

@benito103e @Tykzz the schema is compared using DBAL, which version of doctrine/dbal do you have installed?

Could you try to send a failing functional test on DBAL so that we can investigate what's happening (or at least create the an issue there)?

@lcobucci commented on GitHub (Nov 24, 2017): @benito103e @Tykzz the schema is compared using DBAL, which version of `doctrine/dbal` do you have installed? Could you try to send a failing functional test on DBAL so that we can investigate what's happening (or at least create the an issue there)?
admin commented 2026-01-22 15:10:35 +01:00
Author
Owner
Copy Link

@benito103e commented on GitHub (Nov 27, 2017):

@lcobucci I use "doctrine/dbal" v2.5.12 with a Sqlite database.

Let's see how it goes with any entities that have a oneToMany relation :

<?php

namespace AppBundle\Entity;

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

/**
 * Test1
 *
 * @ORM\Table(name="test1")
 * @ORM\Entity(repositoryClass="AppBundle\Repository\Test1Repository")
 */
class Test1
{
    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var ArrayCollection
     *
     * @ORM\OneToMany(targetEntity="AppBundle\Entity\Test2", mappedBy="test1")
     */
    private $tests2;

    /**
     * UpdateJob constructor.
     */
    public function __construct()
    {
        $this->tests2 = new ArrayCollection();
    }


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


/**
 * Test2
 *
 * @ORM\Table(name="test2")
 * @ORM\Entity(repositoryClass="AppBundle\Repository\Test2Repository")
 */
class Test2
{
    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var UpdateJob
     *
     * @ORM\JoinColumn(name="test_id")
     * @ORM\ManyToOne(targetEntity="AppBundle\Entity\Test1", inversedBy="tests2")
     */
    private $test1;


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

And the result :

$ app/console doctrine:schema:create
$ 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.

$ app/console d:s:u --dump-sql
DROP INDEX IDX_13BB8D581E5D0459;
CREATE TEMPORARY TABLE __temp__test2 AS SELECT id, test_id FROM test2;
DROP TABLE test2;
CREATE TABLE test2 (id INTEGER NOT NULL, test_id INTEGER DEFAULT NULL, PRIMARY KEY(id), CONSTRAINT FK_13BB8D581E5D0459 FOREIGN KEY (test_id) REFERENCES test1 (id) NOT DEFERRABLE INITIALLY IMMEDIATE);
INSERT INTO test2 (id, test_id) SELECT id, test_id FROM __temp__test2;
DROP TABLE __temp__test2;
CREATE INDEX IDX_13BB8D581E5D0459 ON test2 (test_id);

I can force an update, the result stay the same.

@benito103e commented on GitHub (Nov 27, 2017): @lcobucci I use "doctrine/dbal" v2.5.12 with a Sqlite database. Let's see how it goes with any entities that have a oneToMany relation : ```php <?php namespace AppBundle\Entity; use Doctrine\Common\Collections\ArrayCollection; use Doctrine\ORM\Mapping as ORM; /** * Test1 * * @ORM\Table(name="test1") * @ORM\Entity(repositoryClass="AppBundle\Repository\Test1Repository") */ class Test1 { /** * @var int * * @ORM\Column(name="id", type="integer") * @ORM\Id * @ORM\GeneratedValue(strategy="AUTO") */ private $id; /** * @var ArrayCollection * * @ORM\OneToMany(targetEntity="AppBundle\Entity\Test2", mappedBy="test1") */ private $tests2; /** * UpdateJob constructor. */ public function __construct() { $this->tests2 = new ArrayCollection(); } /** * Get id * * @return integer */ public function getId() { return $this->id; } } /** * Test2 * * @ORM\Table(name="test2") * @ORM\Entity(repositoryClass="AppBundle\Repository\Test2Repository") */ class Test2 { /** * @var int * * @ORM\Column(name="id", type="integer") * @ORM\Id * @ORM\GeneratedValue(strategy="AUTO") */ private $id; /** * @var UpdateJob * * @ORM\JoinColumn(name="test_id") * @ORM\ManyToOne(targetEntity="AppBundle\Entity\Test1", inversedBy="tests2") */ private $test1; /** * Get id * * @return integer */ public function getId() { return $this->id; } } ``` And the result : ```bash $ app/console doctrine:schema:create $ 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. $ app/console d:s:u --dump-sql DROP INDEX IDX_13BB8D581E5D0459; CREATE TEMPORARY TABLE __temp__test2 AS SELECT id, test_id FROM test2; DROP TABLE test2; CREATE TABLE test2 (id INTEGER NOT NULL, test_id INTEGER DEFAULT NULL, PRIMARY KEY(id), CONSTRAINT FK_13BB8D581E5D0459 FOREIGN KEY (test_id) REFERENCES test1 (id) NOT DEFERRABLE INITIALLY IMMEDIATE); INSERT INTO test2 (id, test_id) SELECT id, test_id FROM __temp__test2; DROP TABLE __temp__test2; CREATE INDEX IDX_13BB8D581E5D0459 ON test2 (test_id); ``` I can force an update, the result stay the same.
admin commented 2026-01-22 15:10:35 +01:00
Author
Owner
Copy Link

@lcobucci commented on GitHub (Nov 27, 2017):

DBAL 2.5.x is not actively maintained (only security issues gets backported), so try to upgrade to 2.6. If you don't manage to solve it please send the failing functional test on DBAL.

@lcobucci commented on GitHub (Nov 27, 2017): DBAL 2.5.x is not actively maintained (only security issues gets backported), so try to upgrade to 2.6. If you don't manage to solve it please send the failing functional test on DBAL.
admin commented 2026-01-22 15:10:35 +01:00
Author
Owner
Copy Link

@lcobucci commented on GitHub (Nov 27, 2017):

You can use these tests as example: 954ce2e82c/tests/Doctrine/Tests/DBAL/Functional/Schema/SchemaManagerFunctionalTestCase.php (L1206-L1336)

@lcobucci commented on GitHub (Nov 27, 2017): You can use these tests as example: https://github.com/doctrine/dbal/blob/954ce2e82c5892dfbd51c4bf5bd154c85fc3ecf8/tests/Doctrine/Tests/DBAL/Functional/Schema/SchemaManagerFunctionalTestCase.php#L1206-L1336
admin commented 2026-01-22 15:10:35 +01:00
Author
Owner
Copy Link

@lcobucci commented on GitHub (Nov 27, 2017):

I'll close this issue because it seems to be related to DBAL rather than the ORM. Please reopen it if you feel the need of further discussion.

@lcobucci commented on GitHub (Nov 27, 2017): I'll close this issue because it seems to be related to DBAL rather than the ORM. Please reopen it if you feel the need of further discussion.
admin commented 2026-01-22 15:10:36 +01:00
Author
Owner
Copy Link

@benito103e commented on GitHub (Nov 27, 2017):

@lcobucci thanks, dbal 2.6 is only for PHP 7.1 so I won't be able to update.

@benito103e commented on GitHub (Nov 27, 2017): @lcobucci thanks, dbal 2.6 is only for PHP 7.1 so I won't be able to update.
admin commented 2026-01-22 15:10:37 +01:00
Author
Owner
Copy Link

@relte commented on GitHub (Dec 1, 2017):

@lcobucci
I was using 2.6.2, I've updated to 2.6.3 but nothing has changed.
Here is the test, not sure if done right:

    /**
     * @test
     */
    public function testComparatorShouldReturnFalseWhenAddingAssociativeTable() : void
    {
        $table = new Table('project_tags');
        $table->addColumn('project_id', 'integer');
        $table->addColumn('tag_id', 'integer');
        $table->setPrimaryKey(['project_id', 'tag_id']);
        $table->addIndex(['project_id'], 'IDX_51A228EE166D1F9C');
        $table->addIndex(['tag_id'], 'IDX_51A228EEBAD26311');

        $this->_conn->executeQuery('CREATE TABLE project (id INTEGER NOT NULL, title VARCHAR(100) NOT NULL, description CLOB NOT NULL, source VARCHAR(200) NOT NULL, demo VARCHAR(200) NOT NULL, PRIMARY KEY(id))');
        $this->_conn->executeQuery('CREATE TABLE projects_tags (project_id INTEGER NOT NULL, tag_id INTEGER NOT NULL, PRIMARY KEY(project_id, tag_id))');
        $this->_conn->executeQuery('CREATE INDEX IDX_51A228EE166D1F9C ON projects_tags (project_id)');
        $this->_conn->executeQuery('CREATE INDEX IDX_51A228EEBAD26311 ON projects_tags (tag_id)');
        $this->_conn->executeQuery('CREATE TABLE tag (id INTEGER NOT NULL, name VARCHAR(100) NOT NULL, importance INTEGER NOT NULL, PRIMARY KEY(id))');
        $this->_conn->executeQuery('CREATE TABLE info (id VARCHAR(255) NOT NULL, value CLOB NOT NULL, PRIMARY KEY(id))');
        $this->_conn->executeQuery('INSERT INTO info (id, value) VALUES ("intro", "")');

        $comparator = new Comparator();
        $tableDiff  = $comparator->diffTable($this->_sm->listTableDetails('project_tags'), $table);

        self::assertFalse($tableDiff);
    }

Now I'm wondering, shouldn't the ORM mapping cause creating foreign keys on the projects_tags table?

The version of doctrine/orm that I use is 2.5.12.

@relte commented on GitHub (Dec 1, 2017): @lcobucci I was using 2.6.2, I've updated to 2.6.3 but nothing has changed. Here is the test, not sure if done right: ```php /** * @test */ public function testComparatorShouldReturnFalseWhenAddingAssociativeTable() : void { $table = new Table('project_tags'); $table->addColumn('project_id', 'integer'); $table->addColumn('tag_id', 'integer'); $table->setPrimaryKey(['project_id', 'tag_id']); $table->addIndex(['project_id'], 'IDX_51A228EE166D1F9C'); $table->addIndex(['tag_id'], 'IDX_51A228EEBAD26311'); $this->_conn->executeQuery('CREATE TABLE project (id INTEGER NOT NULL, title VARCHAR(100) NOT NULL, description CLOB NOT NULL, source VARCHAR(200) NOT NULL, demo VARCHAR(200) NOT NULL, PRIMARY KEY(id))'); $this->_conn->executeQuery('CREATE TABLE projects_tags (project_id INTEGER NOT NULL, tag_id INTEGER NOT NULL, PRIMARY KEY(project_id, tag_id))'); $this->_conn->executeQuery('CREATE INDEX IDX_51A228EE166D1F9C ON projects_tags (project_id)'); $this->_conn->executeQuery('CREATE INDEX IDX_51A228EEBAD26311 ON projects_tags (tag_id)'); $this->_conn->executeQuery('CREATE TABLE tag (id INTEGER NOT NULL, name VARCHAR(100) NOT NULL, importance INTEGER NOT NULL, PRIMARY KEY(id))'); $this->_conn->executeQuery('CREATE TABLE info (id VARCHAR(255) NOT NULL, value CLOB NOT NULL, PRIMARY KEY(id))'); $this->_conn->executeQuery('INSERT INTO info (id, value) VALUES ("intro", "")'); $comparator = new Comparator(); $tableDiff = $comparator->diffTable($this->_sm->listTableDetails('project_tags'), $table); self::assertFalse($tableDiff); } ``` Now I'm wondering, shouldn't the ORM mapping cause creating foreign keys on the `projects_tags` table? The version of `doctrine/orm` that I use is 2.5.12.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
BC Break
Bug
Can't Fix
Deprecation
Documentation
DQL
Duplicate
Failing Test
Hacktoberfest
Improvement
Improvement
Incomplete
Invalid
Missing Tests
New Feature
PR Created
pull-request
Mirrored from GitHub Pull Request
 Question
Regression
Status: Waiting feedback
Waiting feedback
WIP
Won't Fix
No Label Bug Invalid
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
admin
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: doctrine/archived-orm#5540
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?