Migrations not ever in sync with the DB

[electricBonfire]

Q	A
Version	1.6.2

Support Question

I am running a symfony 4 project using doctrine and mysql.

I have been running into an issue where the doctrine:migrations:diff continuously creates migrations regardless of whether the mapping data has changed since last diff / migration

For example

If I drop my database, remove all of my migration files, create a new blank db, run ./bin/console doctrine:migrations:diff and then ./bin/console doctrine:migrations:migrate It creates my database structure. Then without modifying code I run ./bin/console doctrine:migrations:diff again and I get a new migration file altering many of the tables.

Here is the initial diff:

public function up(Schema $schema) : void
{
    // this up() migration is auto-generated, please modify it to your needs
    $this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');

    $this->addSql('CREATE TABLE oauth_access_token (id INT AUTO_INCREMENT NOT NULL, client_id INT NOT NULL, user_id INT DEFAULT NULL, token VARCHAR(255) NOT NULL, expires_at INT DEFAULT NULL, scope VARCHAR(255) DEFAULT NULL, UNIQUE INDEX UNIQ_F7FA86A45F37A13B (token), INDEX IDX_F7FA86A419EB6921 (client_id), INDEX IDX_F7FA86A4A76ED395 (user_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE oauth_client (id INT AUTO_INCREMENT NOT NULL, random_id VARCHAR(255) NOT NULL, redirect_uris LONGTEXT NOT NULL COMMENT \'(DC2Type:array)\', secret VARCHAR(255) NOT NULL, allowed_grant_types LONGTEXT NOT NULL COMMENT \'(DC2Type:array)\', PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE fos_user (id INT AUTO_INCREMENT NOT NULL, username VARCHAR(180) NOT NULL, username_canonical VARCHAR(180) NOT NULL, email VARCHAR(180) NOT NULL, email_canonical VARCHAR(180) NOT NULL, enabled TINYINT(1) NOT NULL, salt VARCHAR(255) DEFAULT NULL, password VARCHAR(255) NOT NULL, last_login DATETIME DEFAULT NULL, confirmation_token VARCHAR(180) DEFAULT NULL, password_requested_at DATETIME DEFAULT NULL, roles LONGTEXT NOT NULL COMMENT \'(DC2Type:array)\', name VARCHAR(255) DEFAULT NULL, phone_number VARCHAR(255) DEFAULT NULL, notes LONGTEXT DEFAULT NULL, UNIQUE INDEX UNIQ_957A647992FC23A8 (username_canonical), UNIQUE INDEX UNIQ_957A6479A0D96FBF (email_canonical), UNIQUE INDEX UNIQ_957A6479C05FB297 (confirmation_token), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE oauth_refresh_token (id INT AUTO_INCREMENT NOT NULL, client_id INT NOT NULL, user_id INT DEFAULT NULL, token VARCHAR(255) NOT NULL, expires_at INT DEFAULT NULL, scope VARCHAR(255) DEFAULT NULL, UNIQUE INDEX UNIQ_55DCF7555F37A13B (token), INDEX IDX_55DCF75519EB6921 (client_id), INDEX IDX_55DCF755A76ED395 (user_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE event (id INT AUTO_INCREMENT NOT NULL, program_id INT DEFAULT NULL, scheduled_employee_id INT DEFAULT NULL, location_id INT DEFAULT NULL, date DATE DEFAULT NULL, start_time TIME DEFAULT NULL, end_time TIME DEFAULT NULL, isi_report VARCHAR(255) DEFAULT NULL, INDEX IDX_3BAE0AA73EB8070A (program_id), INDEX IDX_3BAE0AA73E2088FC (scheduled_employee_id), INDEX IDX_3BAE0AA764D218E (location_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE event_user_request (event_id INT NOT NULL, user_id INT NOT NULL, INDEX IDX_8E899B6671F7E88B (event_id), INDEX IDX_8E899B66A76ED395 (user_id), PRIMARY KEY(event_id, user_id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE event_supplier_varietal (event_id INT NOT NULL, supplier_varietal_id INT NOT NULL, INDEX IDX_8FE63B1171F7E88B (event_id), INDEX IDX_8FE63B118EEC9B31 (supplier_varietal_id), PRIMARY KEY(event_id, supplier_varietal_id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE retail_account (id INT AUTO_INCREMENT NOT NULL, account_name VARCHAR(255) NOT NULL, contact_name VARCHAR(255) NOT NULL, contact_email VARCHAR(255) NOT NULL, contact_phone VARCHAR(255) NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE oauth_auth_code (id INT AUTO_INCREMENT NOT NULL, client_id INT NOT NULL, user_id INT DEFAULT NULL, token VARCHAR(255) NOT NULL, redirect_uri LONGTEXT NOT NULL, expires_at INT DEFAULT NULL, scope VARCHAR(255) DEFAULT NULL, UNIQUE INDEX UNIQ_4D12F0E05F37A13B (token), INDEX IDX_4D12F0E019EB6921 (client_id), INDEX IDX_4D12F0E0A76ED395 (user_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE retail_account_location (id INT AUTO_INCREMENT NOT NULL, retail_account_id INT DEFAULT NULL, store_number VARCHAR(255) DEFAULT NULL, store_name VARCHAR(255) DEFAULT NULL, store_address VARCHAR(255) DEFAULT NULL, store_city VARCHAR(255) DEFAULT NULL, store_state VARCHAR(255) DEFAULT NULL, store_zip VARCHAR(12) DEFAULT NULL, manager_name VARCHAR(255) DEFAULT NULL, manager_phone VARCHAR(255) DEFAULT NULL, assistant_manager_name VARCHAR(255) DEFAULT NULL, assistant_manager_phone VARCHAR(255) DEFAULT NULL, INDEX IDX_E8E7CE679014C119 (retail_account_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE event_gallery_image (id INT AUTO_INCREMENT NOT NULL, event_id INT DEFAULT NULL, path VARCHAR(255) NOT NULL, INDEX IDX_5F964D071F7E88B (event_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE supplier (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, contact_name VARCHAR(255) NOT NULL, contact_email VARCHAR(255) NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE supplier_varietal (id INT AUTO_INCREMENT NOT NULL, supplier_id INT DEFAULT NULL, program_id INT DEFAULT NULL, name VARCHAR(255) NOT NULL, INDEX IDX_1A7F61702ADD6D8C (supplier_id), INDEX IDX_1A7F61703EB8070A (program_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE recap_question (id INT AUTO_INCREMENT NOT NULL, program_id INT DEFAULT NULL, parent_id INT DEFAULT NULL, question VARCHAR(255) NOT NULL, is_default TINYINT(1) DEFAULT NULL, INDEX IDX_8F870D7A3EB8070A (program_id), INDEX IDX_8F870D7A727ACA70 (parent_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE program (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, dates LONGTEXT DEFAULT NULL COMMENT \'(DC2Type:array)\', sell_sheet VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE program_retail_account (program_id INT NOT NULL, retail_account_id INT NOT NULL, INDEX IDX_7AF8F3CD3EB8070A (program_id), INDEX IDX_7AF8F3CD9014C119 (retail_account_id), PRIMARY KEY(program_id, retail_account_id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE program_supplier (program_id INT NOT NULL, supplier_id INT NOT NULL, INDEX IDX_1A1F904B3EB8070A (program_id), INDEX IDX_1A1F904B2ADD6D8C (supplier_id), PRIMARY KEY(program_id, supplier_id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('CREATE TABLE recap_answer (id INT AUTO_INCREMENT NOT NULL, event_id INT DEFAULT NULL, question_id INT DEFAULT NULL, varietal_id INT DEFAULT NULL, answer LONGTEXT NOT NULL, INDEX IDX_F13D2AA271F7E88B (event_id), INDEX IDX_F13D2AA21E27F6BF (question_id), INDEX IDX_F13D2AA2575B3020 (varietal_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
    $this->addSql('ALTER TABLE oauth_access_token ADD CONSTRAINT FK_F7FA86A419EB6921 FOREIGN KEY (client_id) REFERENCES oauth_client (id)');
    $this->addSql('ALTER TABLE oauth_access_token ADD CONSTRAINT FK_F7FA86A4A76ED395 FOREIGN KEY (user_id) REFERENCES fos_user (id) ON DELETE CASCADE');
    $this->addSql('ALTER TABLE oauth_refresh_token ADD CONSTRAINT FK_55DCF75519EB6921 FOREIGN KEY (client_id) REFERENCES oauth_client (id)');
    $this->addSql('ALTER TABLE oauth_refresh_token ADD CONSTRAINT FK_55DCF755A76ED395 FOREIGN KEY (user_id) REFERENCES fos_user (id) ON DELETE CASCADE');
    $this->addSql('ALTER TABLE event ADD CONSTRAINT FK_3BAE0AA73EB8070A FOREIGN KEY (program_id) REFERENCES program (id)');
    $this->addSql('ALTER TABLE event ADD CONSTRAINT FK_3BAE0AA73E2088FC FOREIGN KEY (scheduled_employee_id) REFERENCES fos_user (id)');
    $this->addSql('ALTER TABLE event ADD CONSTRAINT FK_3BAE0AA764D218E FOREIGN KEY (location_id) REFERENCES retail_account_location (id)');
    $this->addSql('ALTER TABLE event_user_request ADD CONSTRAINT FK_8E899B6671F7E88B FOREIGN KEY (event_id) REFERENCES event (id) ON DELETE CASCADE');
    $this->addSql('ALTER TABLE event_user_request ADD CONSTRAINT FK_8E899B66A76ED395 FOREIGN KEY (user_id) REFERENCES fos_user (id) ON DELETE CASCADE');
    $this->addSql('ALTER TABLE event_supplier_varietal ADD CONSTRAINT FK_8FE63B1171F7E88B FOREIGN KEY (event_id) REFERENCES event (id) ON DELETE CASCADE');
    $this->addSql('ALTER TABLE event_supplier_varietal ADD CONSTRAINT FK_8FE63B118EEC9B31 FOREIGN KEY (supplier_varietal_id) REFERENCES supplier_varietal (id) ON DELETE CASCADE');
    $this->addSql('ALTER TABLE oauth_auth_code ADD CONSTRAINT FK_4D12F0E019EB6921 FOREIGN KEY (client_id) REFERENCES oauth_client (id)');
    $this->addSql('ALTER TABLE oauth_auth_code ADD CONSTRAINT FK_4D12F0E0A76ED395 FOREIGN KEY (user_id) REFERENCES fos_user (id) ON DELETE CASCADE');
    $this->addSql('ALTER TABLE retail_account_location ADD CONSTRAINT FK_E8E7CE679014C119 FOREIGN KEY (retail_account_id) REFERENCES retail_account (id)');
    $this->addSql('ALTER TABLE event_gallery_image ADD CONSTRAINT FK_5F964D071F7E88B FOREIGN KEY (event_id) REFERENCES event (id)');
    $this->addSql('ALTER TABLE supplier_varietal ADD CONSTRAINT FK_1A7F61702ADD6D8C FOREIGN KEY (supplier_id) REFERENCES supplier (id)');
    $this->addSql('ALTER TABLE supplier_varietal ADD CONSTRAINT FK_1A7F61703EB8070A FOREIGN KEY (program_id) REFERENCES program (id)');
    $this->addSql('ALTER TABLE recap_question ADD CONSTRAINT FK_8F870D7A3EB8070A FOREIGN KEY (program_id) REFERENCES program (id)');
    $this->addSql('ALTER TABLE recap_question ADD CONSTRAINT FK_8F870D7A727ACA70 FOREIGN KEY (parent_id) REFERENCES recap_question (id)');
    $this->addSql('ALTER TABLE program_retail_account ADD CONSTRAINT FK_7AF8F3CD3EB8070A FOREIGN KEY (program_id) REFERENCES program (id) ON DELETE CASCADE');
    $this->addSql('ALTER TABLE program_retail_account ADD CONSTRAINT FK_7AF8F3CD9014C119 FOREIGN KEY (retail_account_id) REFERENCES retail_account (id) ON DELETE CASCADE');
    $this->addSql('ALTER TABLE program_supplier ADD CONSTRAINT FK_1A1F904B3EB8070A FOREIGN KEY (program_id) REFERENCES program (id) ON DELETE CASCADE');
    $this->addSql('ALTER TABLE program_supplier ADD CONSTRAINT FK_1A1F904B2ADD6D8C FOREIGN KEY (supplier_id) REFERENCES supplier (id) ON DELETE CASCADE');
    $this->addSql('ALTER TABLE recap_answer ADD CONSTRAINT FK_F13D2AA271F7E88B FOREIGN KEY (event_id) REFERENCES event (id)');
    $this->addSql('ALTER TABLE recap_answer ADD CONSTRAINT FK_F13D2AA21E27F6BF FOREIGN KEY (question_id) REFERENCES recap_question (id)');
    $this->addSql('ALTER TABLE recap_answer ADD CONSTRAINT FK_F13D2AA2575B3020 FOREIGN KEY (varietal_id) REFERENCES supplier_varietal (id)');
}

and the second diff:

public function up(Schema $schema) : void
{
    // this up() migration is auto-generated, please modify it to your needs
    $this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');

    $this->addSql('ALTER TABLE oauth_access_token CHANGE user_id user_id INT DEFAULT NULL, CHANGE expires_at expires_at INT DEFAULT NULL, CHANGE scope scope VARCHAR(255) DEFAULT NULL');
    $this->addSql('ALTER TABLE fos_user CHANGE salt salt VARCHAR(255) DEFAULT NULL, CHANGE last_login last_login DATETIME DEFAULT NULL, CHANGE confirmation_token confirmation_token VARCHAR(180) DEFAULT NULL, CHANGE password_requested_at password_requested_at DATETIME DEFAULT NULL, CHANGE name name VARCHAR(255) DEFAULT NULL, CHANGE phone_number phone_number VARCHAR(255) DEFAULT NULL');
    $this->addSql('ALTER TABLE oauth_refresh_token CHANGE user_id user_id INT DEFAULT NULL, CHANGE expires_at expires_at INT DEFAULT NULL, CHANGE scope scope VARCHAR(255) DEFAULT NULL');
    $this->addSql('ALTER TABLE event CHANGE program_id program_id INT DEFAULT NULL, CHANGE scheduled_employee_id scheduled_employee_id INT DEFAULT NULL, CHANGE location_id location_id INT DEFAULT NULL, CHANGE date date DATE DEFAULT NULL, CHANGE start_time start_time TIME DEFAULT NULL, CHANGE end_time end_time TIME DEFAULT NULL, CHANGE isi_report isi_report VARCHAR(255) DEFAULT NULL');
    $this->addSql('ALTER TABLE oauth_auth_code CHANGE user_id user_id INT DEFAULT NULL, CHANGE expires_at expires_at INT DEFAULT NULL, CHANGE scope scope VARCHAR(255) DEFAULT NULL');
    $this->addSql('ALTER TABLE retail_account_location CHANGE retail_account_id retail_account_id INT DEFAULT NULL, CHANGE store_number store_number VARCHAR(255) DEFAULT NULL, CHANGE store_name store_name VARCHAR(255) DEFAULT NULL, CHANGE store_address store_address VARCHAR(255) DEFAULT NULL, CHANGE store_city store_city VARCHAR(255) DEFAULT NULL, CHANGE store_state store_state VARCHAR(255) DEFAULT NULL, CHANGE store_zip store_zip VARCHAR(12) DEFAULT NULL, CHANGE manager_name manager_name VARCHAR(255) DEFAULT NULL, CHANGE manager_phone manager_phone VARCHAR(255) DEFAULT NULL, CHANGE assistant_manager_name assistant_manager_name VARCHAR(255) DEFAULT NULL, CHANGE assistant_manager_phone assistant_manager_phone VARCHAR(255) DEFAULT NULL');
    $this->addSql('ALTER TABLE event_gallery_image CHANGE event_id event_id INT DEFAULT NULL');
    $this->addSql('ALTER TABLE supplier_varietal CHANGE supplier_id supplier_id INT DEFAULT NULL, CHANGE program_id program_id INT DEFAULT NULL');
    $this->addSql('ALTER TABLE recap_question CHANGE program_id program_id INT DEFAULT NULL, CHANGE parent_id parent_id INT DEFAULT NULL, CHANGE is_default is_default TINYINT(1) DEFAULT NULL');
    $this->addSql('ALTER TABLE program CHANGE dates dates LONGTEXT DEFAULT NULL COMMENT \'(DC2Type:array)\', CHANGE sell_sheet sell_sheet VARCHAR(255) DEFAULT NULL');
    $this->addSql('ALTER TABLE recap_answer CHANGE event_id event_id INT DEFAULT NULL, CHANGE question_id question_id INT DEFAULT NULL, CHANGE varietal_id varietal_id INT DEFAULT NULL');
}

As you can see the first diff creates the table oauth_access_token and sets the column user_id INT DEFAULT NULL

Then the second diff tries to modify the table ALTER TABLE oauth_access_token CHANGE user_id user_id INT DEFAULT NULL

Also, If I run ./bin/console doctrine:migrations:diff ./bin/console doctrine:migrations:migrate and then ./bin/console doctrine:schema:validate I get:

Mapping

[OK] The mapping files are correct.

Database

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

[jwage]

What database and version are you using?

[Majkl578]

Looks like https://github.com/doctrine/doctrine2/issues/6565.

[dvc]

i have similar issue with PostgreSQL

• PostgreSQL 9.6.8 on x86_64-pc-linux-musl, compiled by gcc (Alpine 6.2.1) 6.2.1 20160822, 64-bit
• doctrine/doctrine-migrations-bundle v1.3.1
• doctrine/migrations v1.8.1

In only one entity! Other entities with absolutely same config looks great :)

final class Version20180809093821 extends AbstractMigration
{
    public function up(Schema $schema) : void
    {
        // 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\'.');

        $this->addSql('ALTER TABLE message ALTER created_at TYPE TIMESTAMP(0) WITHOUT TIME ZONE');
        $this->addSql('ALTER TABLE message ALTER created_at DROP DEFAULT');
    }

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

        $this->addSql('ALTER TABLE message ALTER created_at TYPE TIMESTAMP(0) WITHOUT TIME ZONE');
        $this->addSql('ALTER TABLE message ALTER created_at DROP DEFAULT');
    }
}

# Message.php
...
    /**
     * @ORM\Column(type="datetime_immutable")
     *
     * @var \DateTimeImmutable
     */
    private $createdAt;
...

# message DDL (generated by PHPStorm Database module)
create table message
(
...
    created_at timestamp(0) not null,
...
)

[lcobucci]

@electricBonfire we need to know the exact versions of the database and DBAL. The latter is really important, since it's where the comparison is done.

Knowing what's the DDL registered on the DB would also be useful.

[svycka]

same here

doctrine/migrations:1.8.1
doctrine/dbal:2.8.0
doctrine/orm:2.6.2
PHP 7.1.16
mariadb: mysql  Ver 15.1 Distrib 10.3.10-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

[mhnrm]

Any progress on this issue? We have a similar effect here.

doctrine/migrations: 1.8.1 doctrine/dbal: 2.9.0 doctrine/orm: 2.6.3

MySQL 5.7.24 PHP 7.2.12

[bendavies]

you are all using different database vendors so your issues are not going to be the same thing.

I think @dvc issue may have been fixed by my PR: https://github.com/doctrine/dbal/pull/3158

This is not a migrations issue, it's a dbal issue.

[mhnrm]

In my case - boolean fields were changed to TINYINT(1) NOT NULL again and again - the cause was that - probably - DBAL does not longer accept options = {"default" = false}; the migrations were no longer generated when I changed that to options = {"default" = 0}.

[smertelny]

I've had the same issue with doctrine/dbal 2.9.2. It was generating same DATETIME NOT NULL SQL. Fixed by changing @Column(type="datetimetz") to @Column(type="datetime")

[UBERPHP]

i'm having the same problem for multiple column types with mariadb 10.3.11 and current latest dbal release with php 7.2

[ChristopheZOL]

Hi everyone, I had the same problem as described by @electricBonfire, even after drop and total migration regeneration, I kept having the same diff generated by doctrine:migrations:diff :

-> ALTER TABLE product_departure_price CHANGE product_departure_id product_departure_id INT DEFAULT NULL
-> ALTER TABLE request_job CHANGE status_code status_code INT DEFAULT NULL, CHANGE fpid fpid VARCHAR(255) DEFAULT NULL
-> ALTER TABLE product_image CHANGE product_id product_id INT DEFAULT NULL
-> ALTER TABLE product_step CHANGE product_id product_id INT DEFAULT NULL
-> ALTER TABLE product_departure CHANGE product_id product_id INT DEFAULT NULL
-> ALTER TABLE product_destination CHANGE product_id product_id INT DEFAULT NULL

I started another project a month ago with same vendor versions, which had no problems to generate migrations : doctrine/migrations: 2.0.0 doctrine/dbal: 2.9.2 doctrine/orm: 2.6.3

The main difference was in the database used. Both projects are on docker, for the first one I use image mariadb:10.1 (corresponding to MariaDB 10.1.*) and it worked, for the project having the problem I use image mariadb:10.3 (corresponding to MariaDB 10.3).

After switching the database used on the new project to 10.1 instead of 10.3, the problem disappeared. When reading the changelog for MariaDB, I don't found anything that could be related :/

At least the problem is not a problem anymore for my use case, I hope it can help you in understanding the source.

[fancyweb]

Having the exact same problem than @dvc with doctrine/dbal 2.9.2, doctrine/doctrine-migrations-bundle 2.0.0 and PostgreSQL 11.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 8.3.0) 8.3.0, 64-bit

[Netopolis]

Same problem here with 10.1.37-MariaDB using InnoDB, doctrine/dbal 2.9.2, orm 2.6.3 and migrations 2.1.0 When I run a diff again, doctrine keeps creating the same migration file for exactly 4 indexes that are already there.

[pxr64]

Some issue here only happens on MariaDB, projects that run on Postgres generate clean migrations

[Pixelshaped]

Hi! Got this after upgrading to DBAL 3.3.1 (from DBAL 2) MariaDB 10.5.12 Doctrine Migrations 3.4.1

I've got an empty up() migration generated. And a down migration regenerated no matter what:

public function down(Schema $schema): void
    {
        // this down() migration is auto-generated, please modify it to your needs
        $this->addSql('ALTER TABLE address CHANGE street street VARCHAR(255) NOT NULL COLLATE `utf8mb4_unicode_ci`, CHANGE city city VARCHAR(255) NOT NULL COLLATE `utf8mb4_unicode_ci`, CHANGE post_code post_code VARCHAR(255) NOT NULL COLLATE `utf8mb4_unicode_ci`, CHANGE country country ENUM(\'fr\') NOT NULL COLLATE `utf8mb4_unicode_ci` COMMENT \'(82a9e4d26595c87ab6e442391d8c5bba)(DC2Type:countryenum)\', CHANGE first_name first_name VARCHAR(255) DEFAULT NULL COLLATE `utf8mb4_unicode_ci`, CHANGE last_name last_name VARCHAR(255) DEFAULT NULL COLLATE `utf8mb4_unicode_ci`');
        $this->addSql('ALTER TABLE api_key CHANGE token token VARCHAR(255) NOT NULL COLLATE `utf8mb4_unicode_ci`, CHANGE role role VARCHAR(255) NOT NULL COLLATE `utf8mb4_unicode_ci`, CHANGE type type VARCHAR(255) NOT NULL COLLATE `utf8mb4_unicode_ci`');
        $this->addSql('ALTER TABLE blog_article CHANGE title title VARCHAR(255) NOT NULL COLLATE `utf8mb4_unicode_ci`, CHANGE excerpt excerpt LONGTEXT NOT NULL COLLATE `utf8mb4_unicode_ci`, CHANGE external_link external_link VARCHAR(255) NOT NULL COLLATE `utf8mb4_unicode_ci`, CHANGE slug slug VARCHAR(255) NOT NULL COLLATE `utf8mb4_unicode_ci`, CHANGE image_filename image_filename VARCHAR(255) DEFAULT NULL COLLATE `utf8mb4_unicode_ci`');
        ...

Apparently something is angry at my collation settings... They are defined in doctrine.yaml such as:

doctrine:
    dbal:
        url: '%database_url%'

        server_version: 'mariadb-10.5.12'
        charset: utf8mb4
        default_table_options:
            charset: utf8mb4
            collate: utf8mb4_unicode_ci

When I run doctrine:schema:validate, I've got:

Mapping
-------

 [OK] The mapping files are correct.                                                                                    

Database
--------

 [OK] The database schema is in sync with the mapping files.

So why does it regenerates the same migration ad libitum?

[fdiedler]

Hi,

Same problem for me with MySQL 5.7 version? The function up() of the migration file is empty and the function down() look like :

$this->addSql('ALTER TABLE address CHANGE street street VARCHAR(100) DEFAULT NULL COLLATE utf8mb4_unicode_ci, CHANGE zip zip VARCHAR(10) DEFAULT NULL COLLATE utf8mb4_unicode_ci, CHANGE city city VARCHAR(50) DEFAULT NULL COLLATE utf8mb4_unicode_ci');

Is it a bug ?

[Pixelshaped]

It definitely is, but I'm not sure the problem lies with DBAL directly. I started breakpointing the other day and noticed that in DiffGenerator:95, the $fromSchema var contains tables that contain columns that have a _platformOptions array containing ['charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci'], but the same column in the $toSchema var contains ['version' => false].

Interestingly, this is the case with DBAL 2 and 3. It's just not interpreted in the same way by the SqlGenerator so I think the problem lies with doctrine/migrations bundle. But I haven't had the time to go farther (I'll try this weekend).

Edit: No I was wrong, I can AND can't reproduce the bug with doctrine/migrations-bundle:3.2.1, so it's really in dbal. I'll check from which version it appeared. Currently testing dbal:3.2.0 without issue.

[Pixelshaped]

OK so I tested all the releases of doctrine/dbal between 3.2.0 and 3.3.1... and the problem appears at 3.3.1.

composer require doctrine/dbal:3.3.0
binco do:mi:diff
...
In NoChangesDetected.php line 13:

  No changes detected in your mapping information.

composer require doctrine/dbal:3.3.1
binco do:mi:diff
...
Generated new migration class to "/www/lorem/migrations/Version20220203134734.php"

[Pixelshaped]

I opened a new issue because that one was old and maybe out of sight. #5243

[fdiedler]

Thanks for your issue opened, hope it will be fix soon

[Pixelshaped]

@fdiedler in the meantime you can try locking your dbal version to something that works. I did: composer require doctrine/dbal:3.2.2 Does that work for you?

[fdiedler]

@Pixelshaped It is strange, I do not have any reference to doctrine/dbal nor doctrine/orm in my composer.json

    "require": {
        "php": ">=7.1.3",
        "ext-ctype": "*",
        "ext-iconv": "*",
        "doctrine/doctrine-migrations-bundle": "^3.2",
        "sensio/framework-extra-bundle": "^6.2",
        "symfony/console": "5.4.*",
        "symfony/dotenv": "5.4.*",
        "symfony/flex": "^1.17|^2",
        "symfony/framework-bundle": "5.4.*",
        "symfony/monolog-bundle": "^3.7",
        "symfony/runtime": "5.4.*",
        "symfony/validator": "5.4.*",
        "symfony/yaml": "5.4.*",
    },

Maybe it is "included" by another package like framework-bundle ?

[Pixelshaped]

@Pixelshaped It is strange, I do not have any reference to doctrine/dbal nor doctrine/orm in my composer.json

    "require": {
        "php": ">=7.1.3",
        "ext-ctype": "*",
        "ext-iconv": "*",
        "doctrine/doctrine-migrations-bundle": "^3.2",
        "sensio/framework-extra-bundle": "^6.2",
        "symfony/console": "5.4.*",
        "symfony/dotenv": "5.4.*",
        "symfony/flex": "^1.17|^2",
        "symfony/framework-bundle": "5.4.*",
        "symfony/monolog-bundle": "^3.7",
        "symfony/runtime": "5.4.*",
        "symfony/validator": "5.4.*",
        "symfony/yaml": "5.4.*",
    },

Maybe it is "included" by another package like framework-bundle ?

@fdiedler what happens when you type composer info doctrine/dbal and composer why doctrine/dbal

[fdiedler]

@Pixelshaped I just added the doctrine/dbal and blocks to version 3.2.2 and it works when I do migrations:diff :) It is just strange that the reference is not explicitly in my composer.json. The why and info command works fine and give me some information about doctrine/dbal

[Pixelshaped]

@Pixelshaped I just added the doctrine/dbal and blocks to version 3.2.2 and it works when I do migrations:diff :) It is just strange that the reference is not explicitly in my composer.json. The why and info command works fine and give me some information about doctrine/dbal

why gives you the info about which other package required it beforehand. I guess it was doctrine-migrations as none of your other packages seem related to doctrine. By using composer require doctrine/dbal:3.2.2 you're effectively locking the version of dbal to one that works instead of using the most recent that is tolerated by doctrine-migrations.

But given your requirements, your project seems simple enough that this issue is probably widespread and will be fixed very soon. (in which case you'll be able to cease to require dbal explicitly)

[fdiedler]

@Pixelshaped Thanks for the explanation. There are many dependencies with dbal :

$ composer why doctrine/dbal root dev-master requires doctrine/dbal (3.2.2) doctrine/data-fixtures 1.5.2 conflicts doctrine/dbal (<2.13) doctrine/doctrine-bundle 2.5.5 requires doctrine/dbal (^2.13.1|^3.1) doctrine/migrations 3.4.1 requires doctrine/dbal (^2.11 || ^3.0) doctrine/orm 2.11.1 requires doctrine/dbal (^2.13.1 || ^3.2) symfony/cache v5.4.3 conflicts doctrine/dbal (<2.13.1) symfony/doctrine-bridge v5.4.3 conflicts doctrine/dbal (<2.13.1)

My project is big but I removed a lot of things in my composer.json before posting it here.

Hope this bug will be fixed soon. And thanks for the trick, now it works well (but with a lock version and I really do not like that ^^)

[rela589n]

Could you check if this solves the problem:

    app_doctrine.dbal.schema_filter:
        class: Doctrine\Bundle\DoctrineBundle\Dbal\RegexSchemaAssetFilter
        arguments: [ '#^(?!(doctrine_migration_versions)$).*#' ]
        tags:
            - { name: doctrine.dbal.schema_filter }