Migrations always generated for custom type with DBAL 4

[michnovka]

Bug Report

Q	A
BC Break	no
Migrations version	3.8.0
Migrations bundle	3.3.1
ORM	3.2.1
DBAL	4.0.4
Symfony	7.1.2

Summary

I have issue with DBAL 4 and custom type, the migrations keep getting generated again and again. Furthermore, the down migration looks totally bogus. This is possibly related to #1435

I know that DBAL 4 dropped requiresSqlHint (in doctrine/dbal#5107 , afterwards some issues were found and fixed - doctrine/dbal#6257 )

So when I am using custom type, I expect the first migration diff to drop the DC2Type comments. However my tables have these fields already dropped and yet the migration is being generated.

enum ActionType: string
{
    case SUBMIT = 'submit';
    case CANCEL = 'cancel';
}

class ActionTypeType extends Type
{
    public function getSQLDeclaration(array $column, AbstractPlatform $platform): string
    {
        $class = ActionType::class;

        $values = [];

        foreach ($class::cases() as $val) {
            $values[] = "'{$val->value}'";
        }

        return "enum(" . implode(", ", $values) . ")";
    }

    public function convertToDatabaseValue($value, AbstractPlatform $platform): mixed
    {
        $class = ActionType::class;
        if ($value !== null && !($value instanceof BackedEnum)) {
            $value = $class::tryFrom($value);
        }else{
            return null;
        }

        return $value->value;
    }

    public function convertToPHPValue($value, AbstractPlatform $platform): ?BackedEnum
    {
        if ((!is_int($value)) && !is_string($value)) {
            return null;
        }

        return $class::tryFrom($value);
    }
}

I then have my entity as

#[ORM\Entity(repositoryClass: ActionRepository::class)]
#[ORM\Table(name: 'actions')]
class Action
{
    #[ORM\Id]
    private string $name;

    #[ORM\Column(type: ActionType::class, nullable: false)]
    private ActionType $type;
}

and in Kernel.php I set up type mapping inside Kernel::process():

$typesDefinition[ActionType::class] = ['class' => ActionTypeType::class];
$container->setParameter('doctrine.dbal.connection_factory.types', $typesDefinition);

Now I know that the types are assigned correctly, as migrations generate up like this:

    public function up(Schema $schema): void
    {
        $this->addSql('ALTER TABLE actions CHANGE type type enum(\'submit\', \'cancel\') NOT NULL');
    }

but it is generated ALWAYS.

and the down() migration looks even weirder:

    public function down(Schema $schema): void
    {
        $this->addSql('ALTER TABLE actions CHANGE type type VARCHAR(0) NOT NULL');
    }

Everything works fine with DBAL 3, which uses SQL comments

[michnovka]

@greg0ire I am tagging you as you authored some of the relevant changes and I know you have deep insight into removing comments in DBAL 4.

I was digging into the code, the issue lies here:

So Doctrine\Migrations\Generator\DiffGenerator::generate() calls createFromSchema to do comparison of old vs new schema. This calls Doctrine\DBAL\Schema\MySQLSchemaManager (which inherits AbstractSchemaManager) and specifically it fails on function _getPortableTableColumnDefinition(). Where the array $tableColumn which this function gets is

 array:11 [
  "table_name" => "actions"
  "field" => "type"
  "type" => "enum('submit','cancel')"
  "null" => "NO"
  "key" => ""
  "default" => null
  "extra" => ""
  "comment" => ""
  "characterset" => "utf8mb4"
  "collation" => "utf8mb4_general_ci"
  "name" => ""
]

This function then calls $type = $this->platform->getDoctrineTypeMapping($dbType) and since enum, or whatever custom type is not recognized, it defaults to StringType.

Now check DBAL 3 code:
https://github.com/doctrine/dbal/blob/893417fee2bc5a94a10a2010ae83cab927e21df3/src/Schema/MySQLSchemaManager.php#L189-L192

This uses the comment to deduce the proper type and override whatever type is in DB. Without this now, DBAL considers any unknown DB type to be StringType and I have no way how to tell it its not a string type. Hence the migration will always consider old schema column to be string.

Now the new schema uses the DiffGenerator::createToSchema() which does not use Doctrine\DBAL\Schema\AbstractSchemaManager but instead Doctrine\Migrations\Provider\OrmSchemaProvider and this uses under the hood EntityManager which of course identifies the column type properly as ActionTypeType or whatever custom type you use.

So, since we removed comments in DBAL 4, how can the fromSchema be generated properly? This uses only DB values, it does not care about type definition in Entity code (thats relevant for toSchema only).

[greg0ire]

Please upgrade the ORM to 3.8.5: doctrine/dbal#6423

EDIT: I shouldn't answer so hastily

[michnovka]

@greg0ire I am using DBAL 4.0.4 which is based on 3.8.6 that includes the linked fix. ORM is at 3.2.1 and there is no newer version. And the issue is ONLY with DBAL 4 not DBAL 3

[berkut1]

@michnovka It seems that when Doctrine reads the schema of your database, the getSQLDeclaration function is not being called, which is why it cannot recognize the type. There might be a bug in https://github.com/doctrine/dbal/blob/4.0.x/src/Platforms/AbstractMySQLPlatform.php

Try debugging this function to determine at which stage the type is being lost:
https://github.com/doctrine/dbal/blob/90424473eb144659a89fb1b5c9bca37297085351/src/Platforms/AbstractPlatform.php#L2190
Probably, this is a mistaken assumption. Here, two schemas columns are being compared. If initially it was read as StringType, then the error is somewhere earlier.

[michnovka]

@berkut1 this is indeed a mistaken assumption imo, as getSQLDeclaration not being called is a consequence of not having the proper type. The type has to be known in order for that function to be called. And it is called, just for the new schema.

The issue is that oldSchema is created based on database ONLY. It ignores PHP code. And thats good, as the php code defines the new schema, if it took the PHP code as a hint of the type, then migration has no point, as it would be the same type always.

I honestly see no other way than to somehow store in DB the name of the custom type which was used. Which is exactly what the DB comment was used for. WDYT?

[berkut1]

@michnovka
No, DC2Type comments have been useless since around DBAL 3.x. Doctrine can determine custom types on its own if they are properly registered. You need to find out why, in your case, this is not happening for Enum and where exactly the error is in the code.

[michnovka]

@berkut1 how are they useless when even in 3.9.x there is this code:

https://github.com/doctrine/dbal/blob/893417fee2bc5a94a10a2010ae83cab927e21df3/src/Schema/MySQLSchemaManager.php#L189-L192 ?

And this is where it fails, this is where in 3.8.x version it assigned properly custom type, but in 4.0.x it assigns StringType (as the code assigning type based on comment is missing)

And again, think about what I observed above - the oldSchema is created based on DB data ONLY. not PHP. Only DB. So if DB has no indication about the type, how can it be properly determined for nonstandard types?
newSchema uses PHP code to determine types. And thats good.

Doctrine can determine custom types on its own if they are properly registered.

This has no sense for migrations. If I had customType1 field in table, and I changed it to customType2 field, then did migration, how would doctrine deduce it was before customType1? It is no longer defined and registered. PHP code always reflects only the newSchema.

[berkut1]

DBAL 3.x should have backward compatibility, that why it can works with DC2Types and without. there was added this https://github.com/doctrine/dbal/blob/893417fee2bc5a94a10a2010ae83cab927e21df3/src/Platforms/AbstractPlatform.php#L108
that people can smoothly migrate from DBAL3 to DBAL4

[michnovka]

With DBAL 3.8.X and doctrine.dbal.disable_type_comments: true the issue is also present.

And of course it is. Read again where and why it happens, I explained exactly where the issue lies. oldSchema is created based on DB details only. If we dont use comments (either as they are removed in DBAL 4, or if we disable them manually in DBAL 3) then oldSchema will have no idea what type was used before.

Thanks for your interest and will to help.

[berkut1]

Here,
https://github.com/doctrine/dbal/blob/90424473eb144659a89fb1b5c9bca37297085351/src/Schema/MySQLSchemaManager.php#L135
and
https://github.com/doctrine/dbal/blob/90424473eb144659a89fb1b5c9bca37297085351/src/Schema/MySQLSchemaManager.php#L218
Doctrine is trying to map your registered custom types to your database. What happens when it tries to map the Enum type in your case?

UPD:
You also can check if your type registered, here:

[michnovka]

https://github.com/doctrine/dbal/blob/90424473eb144659a89fb1b5c9bca37297085351/src/Schema/MySQLSchemaManager.php#L135

dump($type, $dbType);

gives

1 "string"
2 "enum"

and

https://github.com/doctrine/dbal/blob/90424473eb144659a89fb1b5c9bca37297085351/src/Schema/MySQLSchemaManager.php#L218

dump($column);

gives

Doctrine\DBAL\Schema\Column {#926
  #_name: "type"
  #_namespace: null
  #_quoted: false
  #_type: Doctrine\DBAL\Types\StringType {#636}
  #_length: 0
  #_precision: null
  #_scale: 0
  #_unsigned: false
  #_fixed: false
  #_notnull: true
  #_default: null
  #_autoincrement: false
  #_platformOptions: []
  #_columnDefinition: null
  #_comment: ""
}

UPD:
I checked and the typeRegistry does contain my custom type ActionTypeType

[greg0ire]

@michnovka it's unclear to me whether you know what platform-aware comparison is. In case you don't, since DBAL 3.2.0, platform-aware comparison allows to compare the generated SQL instead of comparing schemas. This means that 2 different schemas could result in an empty diff, if they result in the same SQL being generated. More on this here: https://www.doctrine-project.org/2021/11/26/dbal-3.2.0.html

Sorry if you already know this, it's genuinely hard to tell for me.

[michnovka]

@greg0ire yes, I get this.

The problem is that when the comparator tries to compare the schemas, it is working with oldSchema and newSchema. oldSchema is Schema instance which is created and populated with data using Doctrine\DBAL\Schema\MySQLSchemaManager and it considers my column to be StringType (cos DB column definition is enum(...) which it doesnt understand, so it says, its StringType - this can be seen in my debug output in comment #1441 (comment)).

This means that when it tries to compare SQL generated between old and new schemas, it calls StringType::getSQLDeclaration output instead of the ActionTypeType::getSQLDeclaration for the old schema.

And this is expected. As if in DB there is no info about which custom type the column is, how can the MySQLSchemaManager assume its ActionTypeType and then gets its SQL from ActionTypeType::getSQLDeclaration to compare with new schema.

Now the new schema is another Schema instance, but this one is created using Doctrine\Migrations\Provider\OrmSchemaProvider and this calls internally EntityManager and that uses PHP code and properly considers the column ActionTypeType. Then new schema's SQL is the proper one, while the old ones is nonsense, as it thinks its a StringType.

[greg0ire]

Ah I get it now, thanks for explaining it again. I think this means the issue is that the DBAL does not understand enum(…), and cannot find a type that will produce the same DDL. I don't see a way around that. I now better get the point of doctrine/dbal#6444, which I now realize was about the same thing.

[michnovka]

yes, it all comes to a simple problem - the oldSchema is generated using ONLY DB introspection. And if DB contains no info about which type was used, then it cannot call proper getSQLDeclaration function.

And no, the problem does not simply limit to enum. I can make an example with another custom type which will have the same issue without enums. ALL custom types have this issue in fact, i.e. all custom types with custom getSQLDeclaration which differs from those provided by DBAL package.