Virtual columns will always have schema changes when checked with `orm:schema-tool:update`

[villermen]

Bug Report

Q	A
BC Break	no
Version	2.11.0

Summary

A virtual column defined via the new capabilities of v2.11 will always have changes when checked with the orm:schema-tool:update console command.

How to reproduce

Consider the following mapping on a MySQL 8 database:

#[Entity]
class Website
{
    #[Column(
        type: 'string',
        insertable: false,
        updatable: false,
        columnDefinition: "VARCHAR(255) GENERATED ALWAYS AS ('foo') VIRTUAL",
        generated: 'ALWAYS'
    )]
    private ?string $url = null;
} 

Running orm:schema-tool:update --dump-sql --force consecutively will always yield the change:

ALTER TABLE website CHANGE url url VARCHAR(255) GENERATED ALWAYS AS ('foo') VIRTUAL;

Expected behavior

Expected behavior is to either compare the column definition from the database with the schema, or ignore it completely and only check column existence when it has a custom definition. Both situations would lead to the changes being generated only once.

[beberlei]

What version of DBAL are you using? Older versions will always cause a change with columnDefinition, DBAL 3.3 I believe is the first version that checks this differently.

[villermen]

I'm currently on v2.13.7 so that might explain it. I'll try (force-)updating it to check if it happens in the latest version.

[villermen]

@beberlei I've managed to update to doctrine/dbal 3.3.0. The result for the "website" example is still the same. In addition, another column manually defined as ENUM that previously existed now exposes the same behavior:

#[Entity]
class Website
{
    #[Column(
        type: 'string',
        insertable: false,
        updatable: false,
        columnDefinition: "VARCHAR(255) GENERATED ALWAYS AS ('foo') VIRTUAL",
        generated: 'ALWAYS'
    )]
    private ?string $url = null;

    #[Column(type: 'string', nullable: true, columnDefinition: "ENUM('preparing', 'pending', 'approved', 'rejected') DEFAULT NULL")]
    private ?string $status = null;
} 

orm:schema-tool:update --dump-sql --force && orm:schema-tool:update --dump-sql --force

ALTER TABLE website CHANGE url url VARCHAR(255) GENERATED ALWAYS AS ('foo') VIRTUAL, CHANGE status status ENUM('foo', 'bar') DEFAULT NULL;

[derrabus]

This sounds like the kind of problem @bcremer tried to tackle with #9410.

[bcremer]

@villermen For your $status column try setting length=0. That fixed the schema diff for us.

[villermen]

Adding length: 0 to the attribute on $status indeed removed it from the schema diff. That doesn't make much sense to me, but I can work with that =)

[bcremer]

@villermen
I would advice to step into \Doctrine\DBAL\Schema\Comparator::diffColumn to find out what property of the column is different.

In case of the enum you will observe that the length property is different so it can be adjusted in the Entity accordingly.
That might help you to find the different property in your generated column case.

On a related note:
It would be super helpful to have a debug/verbose mode for the \Doctrine\DBAL\Schema\Comparator that logs what parts of the table/column is different. One should create a PR for that 🥸

[villermen]

@bcremer Thanks for the pointer!

Looks like it triggered completely unrelated to columnDefinition. Instead it's due to a difference in the notnull property. And fair enough, the column is generated as nullable when using the definition VARCHAR(255) GENERATED ALWAYS AS ('foo') VIRTUAL so it's completely right too!

I've fixed it by changing columnDefinition (and the database schema) to:

VARCHAR(255) GENERATED ALWAYS AS ('foo') VIRTUAL NOT NULL

to bring it in line with the Column attribute, which is not null by default. I could've also fixed it by changing the attribute to nullable: true, but that made less sense considering the column is never NULL.

Having to figure it out like this is pretty cumbersome, so I'm all in favor of the debug/verbose mode suggested by @bcremer. But this issue is definitely invalid because it has nothing to do with the column being virtual.

Thanks for your help!

[bcremer]

Unfortunately the new https://github.com/doctrine/dbal/releases/tag/3.3.1 release and doctrine/dbal#5220 makes it impossible to use columnDefinition to validate enum types that are mapped to varchars like described in https://www.doctrine-project.org/projects/doctrine-orm/en/2.11/cookbook/mysql-enums.html#solution-1-mapping-to-varchars.

[derrabus]

@bcremer: doctrine/dbal#5223