CODEWITHSUNDEEP
mysqlsymfonydoctrine-ormdoctrinedoctrine-migrations
Mateusz K.
Mateusz K.

Reputation: 121

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

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

Upvotes: 9

Views: 1803

Answers (3)

Sebastijan Sakač
Sebastijan Sakač

Reputation: 1

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=""

Upvotes: 0

michnovka
michnovka

Reputation: 3389

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]

Upvotes: 4

Rafael Martinelli
Rafael Martinelli

Reputation: 103

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...

Upvotes: 0

Related Questions