[Bug][Postgres] SQLs generated via schema update changes nothing

[wgorczyca]

Hi!

Recently I updated to symfony 5 and it comes version 2.0.0 of doctrine/doctrine-bundle, it seems that 2.0.0 version generates SQLs changes that that changes nothing.

I have following mapping for product entity

    <entity name="Acme\Entity\Product" table="products">
        <id name="id" column="id" type="product_id">
            <generator strategy="NONE"/>
        </id>
        <many-to-one field="category" target-entity="Acme\Entity\Category">
            <join-column name="category_id" nullable="false"/>
        </many-to-one>
    </entity>

When I run doctrine:schema:update --dump-sql it dumps following sql

     ALTER TABLE products ALTER id TYPE UUID;
     ALTER TABLE products ALTER id DROP DEFAULT;
     ALTER TABLE products ALTER category_id TYPE UUID;
     ALTER TABLE products ALTER category_id DROP DEFAULT;

Columns id and category_id already have type UUID with NOT NULL values. If I run doctrine:schema:update --force to execute those queries and again check for new queries by doctrine:schema:update --dump-sql those queries are still there.

It's quiet annoying behavior because I use doctrine migrations bundle and even if there is no changes in schema it generates migrations that changes nothing. If want to generate migration that should change schema it mixes those those sqls that does nothing with real migration sqls.

Any ideas what changes in 2.0.0 version could cause this issue?

Postgres version: 11.5

[alcaeus]

This is an issue because of some caching changes, which will be fixed in #1073. In the meantime, please clear the metadata cache before generating migrations. Apologies for the inconvenience.

[wgorczyca]

@alcaeus FYI mayby there is another issue than this one you have mentioned because when I try clear cache (doctrine:cache:clear-metadata --em product_catalog) I get following error

 [ERROR] No cache entries were deleted.

I use multiple entity managers so mayby it's related with it

[dmaicher]

New releases 1.12.1 and 2.0.1 are fixing this issue.

[wgorczyca]

I updated to 2.0.2 version, cleated metadata cache, but it didn't help, the issue is still there

[dmaicher]

@wgorczyca which issue exactly? The schema changes are now correctly detected, right?

[wgorczyca]

@dmaicher Nope, it's not detected correctly - generated schema changes id fields (uuid) changes nothing, and even if I execute schema update it geneates same schema changes again and again for all id fields

[dmaicher]

Ok then possibly it was not related to the caching issue 😕

Any chance you can find out which change causes the issue?

[dmaicher]

Ah it seems you are using custom types? Did you check the UPGRADE file?

https://github.com/doctrine/DoctrineBundle/blob/master/UPGRADE-2.0.md#types

Probably has something todo with the requiresSQLCommentHint changes

[wgorczyca]

Yes, I do use custom types, I checked UPGRADE file - I do not mark types as commented in doctrine config, before I used version 1.11.2 and it was working fine. I'll try to downgrade to 1.x and will try to spot what changes cause this issue.

[wgorczyca]

@dmaicher I did some checks and it seems that it issue occur when requiresSQLCommentHint() for custom type returns false. When I set return value to true and run schema update once it doesn't detect schema changes on next run what is correct behavior. Mayby that will help you to guess what changes caused this issue, if it doesn't I'll try to look deeper tomorrow.

[dmaicher]

So it seems you should have had some deprecations on 1.12 then?

https://github.com/doctrine/DoctrineBundle/blob/1.12.x/ConnectionFactory.php#L113

Can you check this?

Making requiresSQLCommentHint return true should remove the deprecations on 1.12 and fix your problem then on 2.x I hope 😊

[alcaeus]

The requiresSQLCommentHint deprecation already happened in 1.11, but due to versioning in the pack it could happen that users jump from 1.6.10 (the minimum version required in the pack) straight to 2.0, bypassing the entire deprecation layer. Please limit the DoctrineBundle dependency to ^1.12 and fix deprecations before allowing to install 2.0.

I've created symfony/orm-pack#18 to discuss how this can be avoided in the future - feedback in that issue would be greatly appreciated.

[wgorczyca]

@dmaicher I downgraded to 1.12 and none of deprecation triggers. The error occurs if commented in configuration is set to false and requiresSQLCommentHint() returns false.

[alcaeus]

Setting commented to true is deprecated, instead update the type to return true.

[wgorczyca]

@alcaeus Commented is set to false, and return type return type returns false, so it's correct config for v2.0 ?

[alcaeus]

Didn’t you say it works when you return true? Just for clarity, you need to return true if you want to treat a built-in database type (e.g. TEXT) as a special type when mapping to your object property (e.g. because you’re storing serialised data or a specific value object). In those cases, you need to return true; it’s just that registering the type through DoctrineBundle implicitly marked custom types as commented.

[wgorczyca]

@alcaeus so you're saying that if I want to use custom type I must set requiresSQLCommentHint() to true and there is no way to disable comment hint?

Edit: If what I wrote up is true there is no issue, it can be closed. I was kinda confused because I was able to configure custom type so it doesn't contain comment hint without any errors. Thank you for explaination.

[stof]

For schema changes to be detected properly, a DBAL type must either be commented, or be registered as a mapping for some native type.
Otherwise, DBAL won't detect things properly, as reading the existing schema from the DB won't recognize your custom type on existing column.

[ostrolucky]

We no longer auto mark types as commented since 2.0, this was deprecated in 1.11. Behaviour is correct.