dbal: A wrong charset migration is generated everytime with doctrine/dbal 3.3.1

Bug Report

There is a regression caused by doctrine/dbal 3.3.1 in migrations generation.

Q	A
Version	3.3.1

Summary

Using doctrine/migrations-bundle:3.2.1:

With doctrine/dbal 3.2.0 to 3.3.0 (I tested them all). The command doctrine:migrations:diff produces no migration if there is no change in the schema.
With doctrine/dbal 3.3.1 . The command doctrine:migrations:diff produces an empty up() migration and a wrong down() migration even if there is no change in the schema (and produces it everytime it’s ran).

How to reproduce

Not sure about it. I’m using MariaDB 10.5.12, but another user had this issue and reportedly uses MySQL 5.7. At first I posted in #3391 thinking it was maybe older than that.

In Symfony, Doctrine is configured such as:

doctrine:
    dbal:
        url: '%database_url%'

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

And the wrong down migration produced is full of charset changes:

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`');
        ...

About this issue

Original URL
State: closed
Created 2 years ago
Reactions: 2
Comments: 25 (7 by maintainers)

Most upvoted comments

@Pixelshaped @morozov @natewiebe13 I always have the same problem with doctrine/dbal 3.3.2 Wrong charset migrations are generated at each call to php bin/console doctrine:migrations:diff

fdiedler on Mar 2, 2022

@Pixelshaped

Thanks for reporting a solution, unfortunately all my columns are standard utf8mb4_unicode_ci and this bug still happens. I’ll create another issue if not exists.

Same problem for me, all my colums are in utf8mb4_unicode_ci But good new if the bug is gone on the 3.3.x-dev branch but maybe it worths opening a new issue ?

@greg0ire @Seldaek Hope a fix will be released soon 😃

The bug is gone for me in 3.3.4. @greg0ire pushed a new release yesterday. Check it before opening a new issue.

Pixelshaped on Mar 25, 2022

OK, I’m on it.

greg0ire on Mar 24, 2022

@Pixelshaped could be fixed by https://github.com/doctrine/dbal/pull/5326

Seldaek on Mar 24, 2022

Hi,

I have this issue on 3.3.2 too.

ioniks on Feb 8, 2022

@morozov let’s do a release?

greg0ire on Mar 24, 2022

If you are still seeing the issue (like I was), also check the charset matches the collation. I have collation set to utf8mb4_unicode_ci by default, and some DB fields were set to ascii_general_ci.

Hinted by https://github.com/doctrine/dbal/pull/5224/files I added this to AbstractPlatform::columnsEqual:
if ($column1Array['name'] === 'hash') {
    var_dump($this->getColumnDeclarationSQL('', $column1Array), $this->getColumnDeclarationSQL('', $column2Array));
}
Then ran the schema update command and saw:
string(65) " CHAR(32) CHARACTER SET ascii NOT NULL COLLATE `ascii_general_ci`"
string(45) " CHAR(32) NOT NULL COLLATE `ascii_general_ci`"
Which I could fix by specifying the charset as well on the property’s annotation:
     * @ORM\Column(type="string", length=32, nullable=false, options={"fixed": true, "collation":"ascii_general_ci", "charset": "ascii"})
Simply specifying the collation there was not enough to fix it.

Hopefully this helps someone else.

Thanks for reporting a solution, unfortunately all my columns are standard utf8mb4_unicode_ci and this bug still happens. I’ll create another issue if not exists.

Pixelshaped on Mar 24, 2022

If you are still seeing the issue (like I was), also check the charset matches the collation. I have collation set to utf8mb4_unicode_ci by default, and some DB fields were set to ascii_general_ci.

Hinted by https://github.com/doctrine/dbal/pull/5224/files I added this to AbstractPlatform::columnsEqual:

if ($column1Array['name'] === 'hash') {
    var_dump($this->getColumnDeclarationSQL('', $column1Array), $this->getColumnDeclarationSQL('', $column2Array));
}

Then ran the schema update command and saw:

string(65) " CHAR(32) CHARACTER SET ascii NOT NULL COLLATE `ascii_general_ci`"
string(45) " CHAR(32) NOT NULL COLLATE `ascii_general_ci`"

Which I could fix by specifying the charset as well on the property’s annotation:

     * @ORM\Column(type="string", length=32, nullable=false, options={"fixed": true, "collation":"ascii_general_ci", "charset": "ascii"})

Simply specifying the collation there was not enough to fix it.

Hopefully this helps someone else.

Seldaek on Mar 24, 2022

I created a minimal project here: https://github.com/natewiebe13/doctrine-dbal-bug

Since it might be dependent on which database/env setup, I’ve included a docker environment to hopefully help with replication.

natewiebe13 on Feb 4, 2022