dbal: Schema validation error (and diff error) when collation is set

Bug Report

Not sure if this belongs here or to ORM / Migrations, but this looks directly related to #5322.

Q	A
Version	3.3.4

Summary

When an entity has a collation set for a string column:

class User
{
    #[ORM\Column(type: 'string', length: 100, options: ['collation' => 'ascii_bin'])]
    public string $email;
}

doctrine:schema:validate always reports:

[ERROR] The database schema is not in sync with the current mapping file.

and doctrine:migrations:diff generates the same migration again and again, even after executing it.

How to reproduce

Small reproduce project: https://github.com/BenMorel/doctrine-bug-2022-03-29

Set DATABASE_URL in .env or .env.local
Run bin/console doctrine:database:create

Run bin/console doctrine:migrations:diff

Generated new migration class to "/home/benjamin/src/roketto/packages/backend/bug/migrations/Version20220329140701.php"

To run just this migration for testing purposes, you can use migrations:execute --up 'DoctrineMigrations\\Version20220329140701'

To revert the migration you can use migrations:execute --down 'DoctrineMigrations\\Version20220329140701'

Execute the migration: bin/console doctrine:migrations:migrate -n

[notice] Migrating up to DoctrineMigrations\Version20220329140701
[notice] finished in 18.6ms, used 14M memory, 1 migrations executed, 1 sql queries

Check the schema: bin/console doctrine:schema:validate

Mapping
-------

[OK] The mapping files are correct.

Database
--------

[ERROR] The database schema is not in sync with the current mapping file.

At this point, running doctrine:migrations:diff again generates the same migration again and again:

ALTER TABLE user CHANGE email email VARCHAR(100) NOT NULL COLLATE `ascii_bin`

About this issue

Original URL
State: closed
Created 2 years ago
Comments: 17 (10 by maintainers)

Most upvoted comments

Here’s a simplified reproducer w/o SQL:

<?php

use Doctrine\DBAL\Schema\Table;
use Doctrine\DBAL\Tools\Console\ConnectionProvider;
use Doctrine\DBAL\Types\Types;

require 'vendor/autoload.php';

$connection = /* get connection */;

$table = new Table('test');
$table->addColumn(
    'name',
    Types::STRING,
    [
        'length' => 255,
        'customSchemaOptions' => [
            'collation' => 'utf8mb4_unicode_ci',
        ]
    ]
);

$connection->executeStatement('DROP TABLE IF EXISTS test');
$schemaManager = $connection->createSchemaManager();
$schemaManager->createTable($table);

$t2 = $schemaManager->listTableDetails('test');
$diff = $schemaManager->createComparator()->diffTable($t2, $table);
var_dump($connection->getDatabasePlatform()->getAlterTableSQL($diff));

// array(1) {
//   [0]=>
//   string(84) "ALTER TABLE test CHANGE name name VARCHAR(255) NOT NULL COLLATE `utf8mb4_unicode_ci`"
// }

[…] column definitions take precedence over table definitions and the charset is still inferred from the collation, even if I don’t specify it. 😃

Yeah, that’s what’s happening. We probably need to auto-detect the charset if the collation is specified but the charset isn’t using something from the documentation. For instance,

SELECT CHARACTER_SET_NAME
FROM information_schema.COLLATIONS
WHERE COLLATION_NAME = 'utf8mb4_unicode_ci';
-- utf8mb4

It’s possible that the same issue will exist if the collation is also omitted but it’s inherited from some defaults as it works on SQL Server.

morozov on Jun 30, 2022