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
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...
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 doctrine-bundle@2.5.6