Doctrinebundle: TEST environment only: The database schema is not in sync with the current mapping file

[BentCoder]

There are some similar questions regarding to this issue but I haven't seen anything in particular for TEST environment and sqlite. I cannot validate schema for TEST environment which uses sqlite however I can validate schemas for DEV environment which uses mysql. Does anyone know what could be the reason and the possible solution? Whole application works fine in both DEV and TEST environments though. Only my phing and CI build is broken because of this issue.

php app/console doctrine:schema:update --force --env=test
Updating database schema...
Database schema updated successfully! "9" queries were executed
php app/console doctrine:schema:validate --env=test
[Mapping]  OK - The mapping files are correct.
[Database] FAIL - The database schema is not in sync with the current mapping file.

confing_test.yml

imports:
    - { resource: config_dev.yml }

framework:
    test: ~
    session:
        storage_id: session.storage.mock_file
    profiler:
        collect: false

web_profiler:
    toolbar: false
    intercept_redirects: false

swiftmailer:
    disable_delivery: true

doctrine:
    dbal:
        connections:
            default:
                driver: pdo_sqlite
                path: %kernel.cache_dir%/default.db
                charset: UTF8

[BentCoder]

Is there anyone there!

[stof]

Try running app/console doctrine:schema:update --dump-sql --env=test to see which queries would be executed to update it.
There may be a bug in Doctrine leading to no-op updates being triggered in some cases

[BentCoder]

@stof I've run it as you said and got output below:

DROP INDEX UNIQ_5373C966D1F4EB9A;
DROP INDEX UNIQ_5373C9665E237E06;
CREATE TEMPORARY TABLE __temp__country AS SELECT id, name, flag FROM country;
DROP TABLE country;
CREATE TABLE country (id INTEGER NOT NULL, name VARCHAR(100) NOT NULL, flag VARCHAR(50) NOT NULL, PRIMARY KEY(id));
INSERT INTO country (id, name, flag) SELECT id, name, flag FROM __temp__country;
DROP TABLE __temp__country;
CREATE UNIQUE INDEX UNIQ_5373C966D1F4EB9A ON country (flag);
CREATE UNIQUE INDEX UNIQ_5373C9665E237E06 ON country (name);
CREATE TEMPORARY TABLE __temp__user AS SELECT id, name, surname, middle_name, lucky_number, favorite_colour FROM user;
DROP TABLE user;
CREATE TABLE user (id INTEGER NOT NULL, name VARCHAR(50) NOT NULL, surname VARCHAR(50) NOT NULL, middle_name VARCHAR(50) DEFAULT NULL, lucky_number CHAR(2) NOT NULL, favorite_colour VARCHAR(50) DEFAULT NULL, PRIMARY KEY(id));
INSERT INTO user (id, name, surname, middle_name, lucky_number, favorite_colour) SELECT id, name, surname, middle_name, lucky_number, favorite_colour FROM __temp__user;
DROP TABLE __temp__user;

If I run all these queries one by one manually in sql editor, it works fine however, if I run all in one go which is what doctrine:schema:validate does then I get errors below:

[19:36:46] Error while executing SQL query on database 'default': no such table: __temp__user
[19:37:14] Error while executing SQL query on database 'default': table __temp__user already exists

[BentCoder]

@stof Would you know why this would happen or a possible solution? Thanks

[Acerbic]

Have the same issue. Update command executes, then immediately following validate command says "not in sync". Only happens, when column type is "json_array" and db type is SQLite.

The actual field is created just like its supposed to - mapped to the proper SQLite CLOB type, read-write of data is fine too, the validator is the only one who is having a problem.

[BentCoder]

Manually naming indexes/unique constraints in entities seems to be solving the problem in my case.

[benito103e]

@BentCoder I encounter the same problem when I map a schema for a SQLITE database.
The problem does not exist in MySQL.
I see how to manually name the indexes but not the foreign keys.

How did you do that ?

Manually naming indexes/unique constraints in entities seems to be solving the problem in my case.

[BentCoder]

@benito103e

@Index(name="whatever_idx".....
http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/annotations-reference.html#annref-index

@UniqueConstraint(name="whatever_idx"......
http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/annotations-reference.html#uniqueconstraint

[benito103e]

@BentCoder
Thanks, my problem comes from another part :
https://github.com/doctrine/doctrine2/issues/6445

[FlickerBean]

Why is this closed? It appears to be a bug to me?