dbal: Regression on charset/collate from v3.2.2 to v3.3.2

Bug Report

Q	A
Version	3.3.*

Summary / Current behaviour / How to reproduce

I’m having issue with schema diff after upgrade from 3.2.2 to 3.3.2 (MySQL v8, not defined default_table_options or charset on dbal url connection string). Historically some of my tables were created with utf8 charset as a default and columns had a charset and collate defined as utf8mb4 somehow:

CREATE TABLE `notification_event` (
   `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_unicode_ci;

After upgrade to 3.3.2 Doctrine Migrations wants to change columns and remove charset and collate on column definition. It was fine before.

ALTER TABLE notification_event CHANGE name name VARCHAR(255) NOT NULL;

Expected behaviour

Tables created before with utf8mb3 should see columns with charset and collate as before. Only tables with utf8mb4 shouldn’t declare custom charset for columns.

About this issue

Original URL
State: closed
Created 2 years ago
Reactions: 3
Comments: 16 (11 by maintainers)

Most upvoted comments

@morozov sure, hope it helps:

$connection = DriverManager::getConnection(['url' => 'mysql://root:root@127.0.0.1:3306/test?serverVersion=8.0']);
$connection->executeQuery('CREATE TABLE IF NOT EXISTS `test` (`name` VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL COLLATE `utf8mb4_unicode_ci`) DEFAULT CHARACTER SET utf8 COLLATE `utf8_unicode_ci` ENGINE = InnoDB COMMENT = \'\';');
$schemaManager = $connection->createSchemaManager();
$fromSchema = $schemaManager->createSchema();

$toSchema = new Schema([], [], $schemaManager->createSchemaConfig());
$toSchema->createTable('test')->addColumn(
    'name',
    Types::STRING,
    ['length' => 255, 'notnull' => true, 'platformOptions' => ['version' => false]]
);

$diff = $schemaManager->createComparator()->compareSchemas($fromSchema, $toSchema);
$diffSql = $diff->toSql($connection->getDatabasePlatform());

Try to run it with dbal 3.2.2 and then with dbal 3.3.3, see you the difference in $diffSql? With 3.2.2 it should be empty and with 3.3.3 it’s not.

marforon on Mar 16, 2022

When I worked on the schema-aware comparison, I attempted to rename the arguments from using the “from and to” terminology to something like “actual and desired”. But it wasn’t straightforward, so I decided not to proceed.

Regarding the naming, I believe it’s no longer about the comparison (i.e. the diff), it’s about patching in the context of the current connection (i.e. the default SQL Server collation is sourced from the connection). Before brainstorming, we really need to look through the codebase and agree on the semantics of the API (i.e. the exact meaning of the methods and their parameters).

morozov on Mar 20, 2022