doctrine:migrations:diff always regenerates enum creation

[fkrauthan]

I am using Symfony 3.2 and a mysql database. I have the following problem everytime I run console doctrine:migrations:diff. The output always regenerates the enum field (and the teardown always "reverts" it back to regular varchar fields.

Here is an example output:

> C:\Dev\php\php-7.1\php.exe C:\Users\fkrauthan\PhpstormProjects\play-for-today\flowergame2\bin\console doctrine:migrations:diff
Generated new migration class to "C:\Users\fkrauthan\PhpstormProjects\play-for-today\flowergame2\app/migrations/Version20170107235007.php" from schema differences.
<?php

namespace Application\Migrations;

use Doctrine\DBAL\Migrations\AbstractMigration;
use Doctrine\DBAL\Schema\Schema;

/**
 * Auto-generated Migration: Please modify to your needs!
 */
class Version20170107235007 extends AbstractMigration
{
    /**
     * @param Schema $schema
     */
    public function up(Schema $schema)
    {
        // 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 objects (id INT AUTO_INCREMENT NOT NULL, user_id INT DEFAULT NULL, object_type_id INT DEFAULT NULL, created_at DATETIME DEFAULT NULL, updated_at DATETIME DEFAULT NULL, INDEX IDX_B21ACCF3A76ED395 (user_id), INDEX IDX_B21ACCF3C5020C33 (object_type_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('ALTER TABLE objects ADD CONSTRAINT FK_B21ACCF3A76ED395 FOREIGN KEY (user_id) REFERENCES users (id)');
        $this->addSql('ALTER TABLE objects ADD CONSTRAINT FK_B21ACCF3C5020C33 FOREIGN KEY (object_type_id) REFERENCES object_types (id)');
        $this->addSql('ALTER TABLE compendium_entries CHANGE state state ENUM(\'seed\', \'germ\', \'mature\') NOT NULL');
        $this->addSql('ALTER TABLE flowers CHANGE state state ENUM(\'seed\', \'germ\', \'mature\') NOT NULL');
        $this->addSql('ALTER TABLE flower_graphic_sets CHANGE rarity rarity ENUM(\'common\', \'breed\', \'wild\', \'exclusive\') NOT NULL');
        $this->addSql('ALTER TABLE users CHANGE sort_option sort_option ENUM(\'old\', \'release\', \'new\', \'color\', \'species\') NOT NULL');
    }

    /**
     * @param Schema $schema
     */
    public function down(Schema $schema)
    {
        // this down() 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('DROP TABLE objects');
        $this->addSql('ALTER TABLE compendium_entries CHANGE state state VARCHAR(255) NOT NULL COLLATE utf8_unicode_ci');
        $this->addSql('ALTER TABLE flower_graphic_sets CHANGE rarity rarity VARCHAR(255) NOT NULL COLLATE utf8_unicode_ci');
        $this->addSql('ALTER TABLE flowers CHANGE state state VARCHAR(255) NOT NULL COLLATE utf8_unicode_ci');
        $this->addSql('ALTER TABLE users CHANGE sort_option sort_option VARCHAR(255) NOT NULL COLLATE utf8_unicode_ci');
    }
}


Process finished with exit code 0 at 15:50:07.
Execution time: 962 ms.```

Any idea why this is happening? Right now every time I generate a new diff I have to manual remove the enum alters as reverting a migration would destroy my database schema.

[fre5h]

@fkrauthan

1. What version of MySQL do you use?
2. Can you show your migration where you tables with enums are created the first time. You should have such migration. Because as I understood your new migrations adds already added enums.

[fkrauthan]

I am using latest Mariadb version (a mysql drop in replacement).

And here is the initial migration where some of the enums got added for the first time:

<?php

namespace Application\Migrations;

use Doctrine\DBAL\Migrations\AbstractMigration;
use Doctrine\DBAL\Schema\Schema;

/**
 * Auto-generated Migration: Please modify to your needs!
 */
class Version20161205185813 extends AbstractMigration
{
    /**
     * @param Schema $schema
     */
    public function up(Schema $schema)
    {
        // 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 flowers (id INT AUTO_INCREMENT NOT NULL, user_id INT DEFAULT NULL, flower_type_id INT DEFAULT NULL, flower_graphic_set_id INT DEFAULT NULL, last_breed_at DATETIME DEFAULT NULL, state ENUM(\'seed\', \'germ\', \'mature\') NOT NULL, created_at DATETIME DEFAULT NULL, updated_at DATETIME DEFAULT NULL, INDEX IDX_7DAF2300A76ED395 (user_id), INDEX IDX_7DAF2300818FFC14 (flower_type_id), INDEX IDX_7DAF23004BB6826F (flower_graphic_set_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
    }

    /**
     * @param Schema $schema
     */
    public function down(Schema $schema)
    {
        // this down() 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 flowers DROP FOREIGN KEY FK_7DAF23004BB6826F');
        $this->addSql('ALTER TABLE flowers DROP FOREIGN KEY FK_7DAF2300818FFC14');
        $this->addSql('ALTER TABLE flowers DROP FOREIGN KEY FK_7DAF2300A76ED395');
        $this->addSql('DROP TABLE flowers');
    }
}

[fre5h]

After doctrine generated this migration file for your, have you edited it manually?

[fkrauthan]

The original file no. I just did some changes before posting it here (removed some tables that where part of this migration).

[fre5h]

The reason why you always get ENUMs on migration diff is that in your first migration missed the comment for ENUM column(s). It should look like this

status ENUM ('unsent', 'sending', 'sent', 'canceled', 'error') NOT NULL COMMENT (DC2Type:NotificationStatusType)

If Doctrine finds this comment, then it excludes this field on migration diff.

The question is why Doctrine didn't generate comment for your MariaDB. Maybe there is another syntax for it in MariaDB or something else. Need to investigate. By the way what database driver do you use? E.g.

parameters:
    database_driver: pdo_mysql

[fkrauthan]

I use pdo_mysql as my database_driver. And it looks like the SQL generated never added that COMMENT part to the statement. Therefor the database never executed it. MySQL and MariaDB are syntax equal if it comes to things like that. So it seems like somewhere in the code there is a compatibility check that does not work correctly?

[fkrauthan]

Is there a better way to just detect the type instead of having to use a comment from your bundles point of view?

[fre5h]

Doctrine always use comments for custom type (enums, json, array, etc.). So the only way is to find why comments are not generated in your case. What version of my bundle do you use?

[fre5h]

And also what version of Doctrine?

[fkrauthan]

Here are the versions out of my composer.lock file.

doctrine/doctrine-bundle: 1.6.4 doctrine/orm: 2.5.6

doctrine/doctrine-migrations-bundle: 1.2.1 doctrine/migrations: 1.5.0

fresh/doctrine-enum-bundle: 4.8.1

[fre5h]

OK, thanks https://github.com/fre5h/DoctrineEnumBundle/blob/master/DBAL/Types/AbstractEnumType.php#L72 This a function which adds correct ENUM syntax for specific platform. As you can see there is no adding of comment. Comments to database columns are added by Doctrine. So it is not a problem of my bundle, because its generated correct syntax for platform. Something wrong is with Doctrine in your case.

Can you for example install MySQL and create migration for it to check what happens.

[fkrauthan]

Is this a doctrine orm or a doctrine-bundle issue? I will open a issue in there bug tracker and reference this issue.

[fre5h]

It is a Doctrine DBAL job to create a proper SQL for platform. https://github.com/doctrine/dbal/tree/master/lib/Doctrine/DBAL/Platforms

[fre5h]

You can debug MySqlPlatform in the place where comments should be added. Maybe there you can find something new.

[fre5h]

@fkrauthan Hey, have you fixed it?

[fkrauthan]

@fre5h No it is still an issue. Started debugging with the help from the DBAL people but so far no luck. It seems like it is not detecting that type to be commented.

[smcjones]

This is definitely a migrations issue. I created a custom type and it was having the same problems, with the comments. I looked here to see if this would solve it before going through the effort of conversion.

[adrienlamotte]

Hello guys! I have the same behavior here. Did you found a solution since then ?

[spacetraveller]

Would also love a solution to this. Once this is fixed and out of the way, migrations will be perfect.

[thuijzer]

It this related to a collation check? Every diff all my CHAR fields are altered and I notice that the down function will reset the collation for those fields.

For example:

public function up(Schema $schema) : void
    $this->addSql('ALTER TABLE `my_table` CHANGE my_field my_fied CHAR(32) NOT NULL');
}

public function down(Schema $schema) : void
    $this->addSql('ALTER TABLE `my_table` CHANGE my_field my_fied CHAR(32) NOT NULL COLLATE utf8mb4_unicode_ci');
}

MySQL 5.7.19, default collation: utf8mb4_unicode_ci Doctrine migrations: 1.8.1

[nuil]

Hi, I'm also having this issue. Any news about it?

[codepushr]

Same... any solutions yet?

[pculka]

in the same boat. any ideas?

[Gonzalo1987]

Same problem here, probably the tag of this issue should be bug instead question.

[deansoffer]

anyone solved it ?

[adriankirchner]

@deansoffer didn't solve the root cause but in my case defining a default value stops the ongoing generation of migrations

not working:

@ORM\Column(type="string", length=255, columnDefinition="enum('invoice', 'delivery')")

working:

@ORM\Column(type="string", length=255, columnDefinition="enum('invoice', 'delivery')", options={"default":"invoice"})

doctrine:
    dbal:
        mapping_types:
            enum: string

"doctrine/migrations": 3.0.1

[michnovka]

@adriankirchner well such random behavior screams BUG. GIven how ENUMs are common types in DB, I wonder why this has not been addressed yet? I still have this bug with doctrine/doctrine-migrations-bundle 3.1.1

[kirya-dev]

Problem SOLVED.

doctrine:
    dbal:
        mapping_types:
            enum: string
        types:
            user_status_enum: App\Enum\UserStatusEnum

use Doctrine\DBAL\Types\Type;

class UserStatusEnum extends Type
{
   public function getName(): string
   {
       return 'user_status_enum';
   }

   public function requiresSQLCommentHint(AbstractPlatform $platform): bool
   {
       return true;
   }
}

[IronXtreme]

Thanks @kyria-dev, your solution is working for me

[bertoost]

Hi! I am running into same, although I do not use your bundle... I am working on an automated Enum field, with one EnumType, using the $enumType property of Doctrine's column annotation class. The problem is, that Doctrine compares the existing schema and the defined schema by entities. Therefore the comment is placed in the database, but it does not match since the ENUM values are not yet generated since the $enumType is not loaded then. Quite difficult to explain. But when I debug the EnumType class, it visits twice (once for the db-schema & once for the desired entity schema -> when the $enumType is set).