MySQL tables are never up to date!

[JanMikes]

Hi, i have weird problem with doctrine entities and the database diff. It always says database is not up to date.

Running bin/console orm:schema-tool:update --dump-sql results in:

 The following SQL statements will be executed:

     ALTER TABLE oauth2_auth_code CHANGE expiry_date_time expiry_date_time DATETIME NOT NULL;
     ALTER TABLE oauth2_refresh_token CHANGE expiry_date_time expiry_date_time DATETIME NOT NULL;
     ALTER TABLE oauth2_access_token CHANGE expiry_date_time expiry_date_time DATETIME NOT NULL;

I checked database and the columns are exactly like this.

It would not be such a big problem, but we use a CI check if migrations are up to date.

What is weird as well is that generated migration has exactly same SQL query for up() and down() versioning (which confirms that the columns in database are exactly same as they should be according to doctrine mapping):

public function up(Schema $schema) : void
    {
        $this->addSql('ALTER TABLE oauth2_auth_code CHANGE expiry_date_time expiry_date_time DATETIME NOT NULL');
        $this->addSql('ALTER TABLE oauth2_refresh_token CHANGE expiry_date_time expiry_date_time DATETIME NOT NULL');
        $this->addSql('ALTER TABLE oauth2_access_token CHANGE expiry_date_time expiry_date_time DATETIME NOT NULL');
    }

    public function down(Schema $schema) : void
    {
        $this->addSql('ALTER TABLE oauth2_access_token CHANGE expiry_date_time expiry_date_time DATETIME NOT NULL');
        $this->addSql('ALTER TABLE oauth2_auth_code CHANGE expiry_date_time expiry_date_time DATETIME NOT NULL');
        $this->addSql('ALTER TABLE oauth2_refresh_token CHANGE expiry_date_time expiry_date_time DATETIME NOT NULL');
    }

Do you please have any tips where to start my findings? I can ofc send a PR, but any tip will fasten up my research drastically.

Thank you!

[JanMikes]

I am running on MariaDB 10.3.7, as well tested on MySQL 5.5.55 with same results.

Versions:
doctrine/orm 2.6.1
doctrine/dbal 2.7.1
kdyby/doctrine 3.3.0

[lookyman]

I have never seen that before in my life.. 😊 Could you investigate a bit?

[JanMikes]

Ok, i investigated a bit :smile:

Still do not have a solution but at least i know what is causing it.

Problem is with doctrine type datetimetz, we are overriding ddoctrine datetime types:

        Type::overrideType(Type::DATE, ImmutableDateType::class);
        Type::overrideType(Type::DATETIME, ImmutableDateTimeType::class);
        Type::overrideType(Type::DATETIMETZ, ImmutableDateTimeTzType::class);
        Type::overrideType(Type::TIME, ImmutableTimeType::class);
        Type::overrideType(Type::DATE_IMMUTABLE, ImmutableDateType::class);
        Type::overrideType(Type::DATETIME_IMMUTABLE, ImmutableDateTimeType::class);
        Type::overrideType(Type::DATETIMETZ_IMMUTABLE, ImmutableDateTimeTzType::class);
        Type::overrideType(Type::TIME_IMMUTABLE, ImmutableTimeType::class);

Problem is that it evaluates column as datetime type instead of datetimetz - they both have same sql definition and there is absolutely no difference and doctrine just evaluates it to datetime.

[JanMikes]

Ok, next investigation showed up that the problem is not caused by overriding the default types and does not disappear.

Adding a commet (DC2Type:datetimetz) for each affected column helped and solved out this issue. Unfortunately this has to be done manually (either by sql query and straight in database admin).

ALTER TABLE `oauth2_auth_code` CHANGE `expiry_date_time` `expiry_date_time` DATETIME  NOT NULL  COMMENT '(DC2Type:datetimetz)';