Charset and Collation leads to down-Migration

[reinfi]

Bug Report

Q	A
BC Break	no, I think not
Version	3.4.1

Summary

Diff-Command always generates a down migration which all fields with varchar columns have a change in the collation and charset.

Current behavior

mySQL-Version is 5.7.13 but I have the same issues on a mysql 8 platform.

<?php

declare(strict_types=1);

namespace Employee\Entity;

use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity]
#[ORM\Table]
class EmployeeFunction
{
    /**
     * @var integer
     */
    #[ORM\Id]
    #[ORM\Column(type: 'integer', nullable: false)]
    #[ORM\GeneratedValue(strategy: 'IDENTITY')]
    private $id;

    /**
     * @var string
     */
    #[ORM\Column(type: 'string', length: 30, nullable: false)]
    private $name;
}

Given that entity, which has the following sql in the mySQL database

CREATE TABLE EmployeeFunction (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(30) CHARACTER SET utf8 NOT NULL COLLATE `utf8_unicode_ci`, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE `utf8_unicode_ci` ENGINE = InnoDB COMMENT = '' 

This always generates a down migration with the following sql statement.

$this->addSql('ALTER TABLE EmployeeFunction CHANGE name name VARCHAR(30) CHARACTER SET utf8 NOT NULL COLLATE "utf8_unicode_ci"');

The up migration part is always empty.

I could fix this problem by adding the following the my property.

    /**
     * @var string
     */
    #[ORM\Column(type: 'string', length: 30, nullable: false, options: [ 'charset' => 'utf8', 'collation' => 'utf8_unicode_ci' ])]
    private $name;

So maybe it is expected but it has changed between versions because this happens only since a few versions.

How to reproduce

Create entity above and execute diff-command will always lead to changes in the down migration part.

Expected behavior

I'm a bit unsure what I would expect. May be it is correct but then I would expect a up migration to fix the issue and remove charset and collation so diff is empty.

[1ed]

When I changed collate to collation it fixed my issue, see

doctrine/DoctrineBundle#1471 and
#1241 (comment)

[BenMorel]

I have the same issue since we upgraded to DBAL 3. In addition to generating a down migration for all text fields, it also generates an up migration for fields that have an explicit collation:

class Invoice
{
    #[ORM\Column(type: 'string', length: 50, unique: true)]
    protected string $reference;
    
    #[ORM\Column(type: 'string', length: 30)]
    protected string $identifier;
    
    #[ORM\Column(name: 'sequence', type: 'string', length: 20, options: ['collation' => 'ascii_bin'])]
    protected string $sequence;
}

Typical doctrine:migrations:diff output (formatted):

final class Version20220311164758 extends AbstractMigration
{
    public function up(Schema $schema): void
    {
        $this->addSql('
            ALTER TABLE Invoice
            CHANGE sequence sequence VARCHAR(20) NOT NULL COLLATE `ascii_bin`
        ');
    }
    
    public function down(Schema $schema): void
    {
        $this->addSql('
            ALTER TABLE Invoice
            CHANGE reference reference VARCHAR(50) NOT NULL COLLATE `utf8mb4_unicode_ci`,
            CHANGE identifier identifier VARCHAR(30) NOT NULL COLLATE `utf8mb4_unicode_ci`,
            CHANGE sequence sequence VARCHAR(20) CHARACTER SET ascii NOT NULL COLLATE `ascii_bin`
        ');
    }
}

What's really annoying is that executing the migration then running doctrine:migrations:diff generates the same migration again and again.

Stack:

• doctrine/dbal 3.3.3
• doctrine/orm 2.11.2
• doctrine/migrations 3.4.1
• MariaDB 10.5.13

Is there a workaround, while waiting for a fix?

[surikman]

I have same issue, but I meant that issue is in doctrine/dbal -> I explained at here: doctrine/dbal#5322

[surikman]

@BenMorel yes, there is a workaround for this - require version 3.3.2 for doctrine/migrations instead of 3.4.1 (because this bug starts in #1229 )

[greg0ire]

@reinfi @BenMorel please check if doctrine/dbal#5326 fixes your issues.

[BenMorel]

@greg0ire @morozov Thanks, but I'm still having an issue using 3.3.x-dev 83f779b with columns declared with an explicit collation:

class Invoice
{
    #[ORM\Column(name: 'sequence', type: 'string', length: 20, options: ['collation' => 'ascii_bin'])]
    protected string $sequence;
}

Those still generate diffs.

[morozov]

@BenMorel could you try reproducing your issue in a form similar to doctrine/dbal#5322?

[greg0ire]

Closing, let me know if you still have the issue, folks.

[reinfi]

Fixed thank you!

[BenMorel]

@morozov I still have the issue when collation is set on a string column.

I created an issue here: doctrine/dbal#5338 (with reproduce project) but is it the right place?

Please let me know if you want me to transfer the issue to doctrine/migrations or doctrine/orm.