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

[marforon]

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.

[morozov]

@marforon how exactly does one reproduce the issue you're experiencing? The SQL in the description doesn't seem to be enough. What version of MySQL are you using?

FWIW, on MySQL 8, the table created from the DDL in the description is introspected like this:

mysql> 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;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> SELECT t.TABLE_COLLATION,
    ->        ccsa.CHARACTER_SET_NAME
    -> FROM information_schema.TABLES t
    ->     INNER JOIN information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` ccsa
    ->         ON ccsa.COLLATION_NAME = t.TABLE_COLLATION
    -> WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'doctrine' AND TABLE_NAME = 'notification_event';
+-----------------+--------------------+
| TABLE_COLLATION | CHARACTER_SET_NAME |
+-----------------+--------------------+
| utf8_unicode_ci | utf8               |
+-----------------+--------------------+
1 row in set (0.00 sec)

Note that the charset name is utf8, not utf8mb3.

[marforon]

@morozov I think it's fine as you have it. Try to do SHOW CREATE TABLE notification_event; and see if the create table is with charset utf8mb3 or not, I just used SHOW CREATE TABLE query to dump the schema definition. But it was created by migration sql as:

$this->addSql('CREATE TABLE notification_event (name VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL COLLATE `utf8mb4_unicode_ci`) DEFAULT CHARACTER SET utf8 COLLATE `utf8_unicode_ci` ENGINE = InnoDB COMMENT = \'\' ');

My exact version is 8.0.26. (Not sure which version it was when I first run this migration, but it doesn't matter in my case, I can replicate it from the scratch on this version)

So to replicate this:

1 Require doctrine/dbal 3.2.2
2. Create an entity with only name property #[ORM\Column(length: 255)]
3. Run sql:

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;

4. (Clear cache) Check schema diff (for Symfony bin/console doctrine:schema:update --dump-sql) with result

[OK] Nothing to update - your database is already in sync with the current entity metadata.

5. Require doctrine/dbal 3.3.2
6. (Clear cache) Check schema diff (for Symfony bin/console doctrine:schema:update --dump-sql) with result

 The following SQL statements will be executed:

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

[morozov]

Could you try reproducing this issue without the ORM and Symfony?

[marforon]

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

[morozov]

Thanks, @marforon. I can reproduce the issue.

[morozov]

So as far as I understand the code,

1. In the database, you create a table with the following defaults: CHARACTER SET utf8 COLLATE `utf8_unicode_ci`.
2. The table contains a column that overrides the defaults: CHARACTER SET utf8mb4 NOT NULL COLLATE `utf8mb4_unicode_ci`.
3. In the code, you define a column that doesn't override the defaults:

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

So as a result, you get a DDL that removes the overrides from the column in the database:

ALTER TABLE test CHANGE name name VARCHAR(255) NOT NULL

What exactly do you believe is wrong here?

[surikman]

@morozov I create a new issue for this - #5322 (I am not sure if the title is correct, if not feel free to update)

[morozov]

@marforon is your issue still reproducible on 3.3.x-dev with the changes from #5326 in place?

[morozov]

Closing as non-reproducible.

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.