Doctrine migrations always generates empty up() and down() migration with collate change to 'utf8mb4_unicode_ci'

Question

When I run php bin/console doctrine:migrations:diff I always get the following newly generated migration:

<?php

declare(strict_types=1);

namespace DoctrineMigrations;

use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;

final class Version20220221174647 extends AbstractMigration
{
    public function getDescription(): string
    {
        return '';
    }

    public function up(Schema $schema): void
    {
        
    }

    public function down(Schema $schema): void
    {
        // example with one table but migration generates this for all varchar column, in all tables
        $this->addSql('ALTER TABLE address CHANGE company_name company_name VARCHAR(100) DEFAULT NULL COLLATE `utf8mb4_unicode_ci`, CHANGE address_line1 address_line1 VARCHAR(100) DEFAULT NULL COLLATE `utf8mb4_unicode_ci`, CHANGE zip_code zip_code VARCHAR(10) DEFAULT NULL COLLATE `utf8mb4_unicode_ci`, CHANGE city city VARCHAR(50) DEFAULT NULL COLLATE `utf8mb4_unicode_ci`, CHANGE country country VARCHAR(50) DEFAULT NULL COLLATE `utf8mb4_unicode_ci`, CHANGE tax_identifier tax_identifier VARCHAR(255) DEFAULT NULL COLLATE `utf8mb4_unicode_ci`');
    }
}

My SHOW CREATE TABLE address

CREATE TABLE `address` (
  `id` int NOT NULL AUTO_INCREMENT,
  `company_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address_line1` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `zip_code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `city` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `country` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `updated_at` datetime NOT NULL,
  `created_at` datetime NOT NULL,
  `tax_identifier` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

SHOW VARIABLES LIKE 'character\_set\_%'; result:

Variable_name	Value
character_set_client	utf8mb4
character_set_connection	utf8mb4
character_set_database	utf8mb4
character_set_filesystem	binary
character_set_results	utf8mb3
character_set_server	utf8mb4
character_set_system	utf8mb3

And SELECT @@character_set_database, @@collation_database; result:

@@character_set_database: utf8mb4
@@collation_database: utf8mb4_unicode_ci

I also set doctrine config to:

doctrine:
    dbal:
        url: '%env(resolve:DATABASE_URL)%'

        charset: utf8mb4
        default_table_options:
            charset: utf8mb4
            collate: utf8mb4_unicode_ci

Osteoclasis

Are you sure this is related to the charset ? Are you sure your database is at the state before the migration ? If your Adress entity is well mapped, and your database does not contains address table yet, the migration should detect the difference between entity and actual schema – Yoke

As I wrote, method up() of the migration is empty. That suggest, that my database is up to date with my entities definitions. I'm not sure that problem is related to the charset, but that change is auto-generated in down() method of the migration, for all varchar fields. – Osteoclasis

I have the same issue with Symfony 5.4 and doctrine-migrations-bundle 3.2.2 – Lucianaluciano

Im have the same issue. The only way for up method creare the diff, it's using make:migration because migrations:diff doesn't work – Abscind

Answer 1

This is a bug. It will be fixed. As a workaround, you can use solution proposed in the github issue DoctrineBundle/1468

doctrine:
    dbal:
        url: '%env(resolve:DATABASE_URL)%'

        charset: utf8mb4
        default_table_options:
            charset: utf8mb4
            collate: utf8mb4_unicode_ci
            collation: utf8mb4_unicode_ci

Notice the additional collation

P.S. there seems to be a PR to address this already, so likely in few months this fix will not be necessary with up-to-date version. However I confirm this happens with [email protected]

Lucianaluciano

Answer 2

It is for sure a bug. I am coding an app using Symfony and the same thing started to happen between migrations with versions 2022.02.08 and 2022.02.09.

Edit: Checking my composer.json, between these two versions, I updated PHP from 8.0 to 8.1, and Symfony from 5.4.* to 6.0.*. I still believe it's a bug on Symfony...

Tonsillotomy

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Gretagretal

Can you verify composer.lock in your repository and check which component do you have updated between these dates? – Osteoclasis

Answer 3

I dont know if is too late to answer yuour question, but maybe someone will find this helpfull. I had the same issue, and found out that in my .env file I had space in front of DATABASE_URL. So, I had

 DATABASE_URL=""

instead of

DATABASE_URL=""

Distichous