Schema is always out of sync when using custom charset/collation on table relation

[ruudk]

Bug Report

Q	A
Version	3.6.1

Summary

I'm still experiencing issues related to custom charset/collations that break schema diffing. Already reported this some time ago but then I couldn't create a reproducer for DBAL. But now I did create a reproducer 🎉

I removed all columns that are not related to the problem. Therefore, you might be tempted to respond: "why do you set a charset/collation on the table, while immediately overriding it on the only column". We have a very large (10 year old) application that has some tables in older charset/collations. We synced up all those production values in our entities so that we don't get any surprises while creating migrations.

Current behaviour

With the reproducer, I keep on getting the following diff:

ALTER TABLE child CHANGE parent_id parent_id CHAR(36) DEFAULT NULL COLLATE `utf8mb4_unicode_ci` COMMENT '(DC2Type:uuid)';

How to reproduce

Please have a look at the reproducer repository: https://github.com/ruudk/doctrine-bug5984-reproducer

$dsn = sprintf("mysql://%s:%s@%s:%d/%s?server_version=8.0&charset=utf8mb4&defaultTableOptions[charset]=utf8mb4&defaultTableOptions[collation]=utf8mb4_unicode_ci&defaultTableOptions[engine]=InnoDB", $username, $password, $host, $port, $database);

$defaultTableOptions = [
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'engine' => 'InnoDB',
];

Type::addType('uuid', UuidType::class);

$connection = DriverManager::getConnection(['url' => $dsn]);
$connection->executeQuery(
    <<<SQL
DROP DATABASE $database;
CREATE DATABASE $database;
SQL
);

$connection = DriverManager::getConnection(['url' => $dsn]);
$connection->executeQuery(
    <<<SQL
CREATE TABLE `parent` (
  `id` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '(DC2Type:uuid)',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
CREATE TABLE `child` (
  `id` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '(DC2Type:uuid)',
  `parent_id` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '(DC2Type:uuid)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_896AFCDB2FFECF6A` (`parent_id`),
  CONSTRAINT `FK_896AFCDB2FFECF6A` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
SQL
);

$schemaManager = $connection->createSchemaManager();
$schemaConfig = $schemaManager->createSchemaConfig()->setDefaultTableOptions($defaultTableOptions);
$fromSchema = $schemaManager->introspectSchema();

$toSchema = new Schema([], [], $schemaConfig);
$table = $toSchema->createTable('parent')
    ->addOption('charset', 'utf8mb4')
    ->addOption('collation', 'utf8mb4_bin');
$table->addColumn(
    'id',
    'uuid',
    [
        'length' => 36,
        'notnull' => true,
        'customSchemaOptions' => [
            'collation' => 'utf8mb4_unicode_ci',
        ],
    ]
);
$table->setPrimaryKey(['id']);

$table = $toSchema->createTable('child')
    ->addOption('charset', 'utf8mb4')
    ->addOption('collation', 'utf8mb4_unicode_ci');
$table->addColumn(
    'id',
    'uuid',
    [
        'length' => 36,
        'notnull' => true,
    ]
);
$table->addColumn(
    'parent_id',
    'uuid',
    [
        'length' => 36,
        'notnull' => true,
        'customSchemaOptions' => [
            'collation' => 'utf8mb4_unicode_ci',
        ],
    ]
);
$table->setPrimaryKey(['id']);
$table->addForeignKeyConstraint(
    'parent',
    ['parent_id'],
    ['id'],
    ['onDelete' => 'CASCADE'],
    'FK_896AFCDB2FFECF6A'
);
$table->addUniqueIndex(
    ['parent_id'],
    'UNIQ_896AFCDB2FFECF6A'
);
$up = $schemaManager->createComparator()->compareSchemas($fromSchema, $toSchema);
$diffSql = $up->toSql($connection->getDatabasePlatform());
if ($diffSql !== []) {
    echo "The schema should be sync, but the diff keeps on reporting changes:\n";
    var_dump($diffSql);
    exit(1);
}

$down = $schemaManager->createComparator()->compareSchemas($toSchema, $fromSchema);
$diffSql = $down->toSql($connection->getDatabasePlatform());
if ($diffSql !== []) {
    echo "The schema should be sync, but the diff keeps on reporting changes:\n";
    var_dump($diffSql);
    exit(1);
}

Expected behaviour

No changes

/cc @morozov Since you asked me for the DBAL reproducer, I hope you have some time to help me investigate this problem further. 💙

[paranoiq]

probably encountered the same issue (3.5.x)

definition with only collation does not work (produces an alter to change charset again and again): #[Column(type: Types::STRING, nullable: false, options: ['charset' => 'ascii'])]

definition with botch charset and collation works fine: #[Column(type: Types::STRING, nullable: false, options: ['charset' => 'ascii', 'collation' => 'ascii_bin'])]