Schema diff generates duplicative ALTER statements with custom columnDefinition

[pps1]

Bug Report

Q	A
BC Break	n/a
Version	2.11.0
MySQL	8.0.27
DBAL	3.3.0
Migrations	3.4.0

Summary

I need to add an auto-incremented integer column that is not the primary key to a MySQL InnoDB table. My entity is defined as follows:

/**
 * @ORM\Entity(repositoryClass=AddressRepository::class)
 * @ORM\Table(
 *   uniqueConstraints={@ORM\UniqueConstraint(name="id_int",columns={"id_int"})}
 * )
 */
class Address
{
    //{...}
	
    /**
     * @ORM\Column(
     *     type="integer",
     *     nullable=false,
     *     unique=true,
     *     options={"unsigned"=true},
     *     columnDefinition="int unsigned NOT NULL AUTO_INCREMENT UNIQUE"
     * )
     */
    private ?int $idInt = null;
	
    //{...}
}

Running doctrine:schema:diff (Symfony 5.4 binding) results in the correct migration:

final class Version20220119220248 extends AbstractMigration
{
    public function up(Schema $schema): void
    {
        $this->addSql('ALTER TABLE address ADD id_int int unsigned NOT NULL AUTO_INCREMENT UNIQUE');
    }
}

Executing the migration yields the correct schema:

mysql> show create table address \G;
--------------
show create table address
--------------

*************************** 1. row ***************************
       Table: address
Create Table: CREATE TABLE `address` (
  `id` char(36) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '(DC2Type:guid)',
  `id_int` int unsigned NOT NULL AUTO_INCREMENT,
//{...}
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_int` (`id_int`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Current behavior

The problem comes when running doctrine:migrations:diff again. Without any changes to the field definitions, diff yields the following migration:

final class Version20220119220422 extends AbstractMigration
{
    public function up(Schema $schema): void
    {
        $this->addSql('ALTER TABLE address CHANGE id_int id_int int unsigned NOT NULL AUTO_INCREMENT UNIQUE');
    }
}

How to reproduce

1. Create an auto_incremented integer that is a non-primary key on an entity
2. Run Schema Diff
3. Migrate up
4. Run Schema Diff again

Expected behavior

Running Schema Diff again should not create a duplicative migration statement.

[beberlei]

As Schema diffing is a DBAL feature, i am transfering the issue.

With DBAL 3.3 this should be covered by our new comparison code, but maybe there is a problem somewhere that casues this, your column definition case is really complicated.

[morozov]

@pps1 what version of Doctrine Migrations are you using? Up until 3.4.0 (doctrine/migrations#1227, released a week ago), it used the old schema comparison API that is prone to the issues of comparing schemas like yours. What version of Migrations are you using.

In order for it to be considered a DBAL issue, please reproduce it by using only the DBAL API.

[pps1]

@morozov thanks for your feedback. We're using doctrine/migrations 3.4.0 and as you can see are having the issue even with the latest version. I'll work on reproducing it with the DBAL API and will report back.

[beberlei]

Maybe when you pass in a column definitoin, since that is used in its given order, but the database returns that slightly different, then it always missmatches?

[pps1]

I believe I'm able to reproduce this behavior using the DBAL API.

Here's the Doctrine\DBAL\Schema\Schema for the current, or FROM schema:

Doctrine\DBAL\Schema\Schema Object
(
    [namespaces:Doctrine\DBAL\Schema\Schema:private] => Array
        (
        )

    [_tables:protected] => Array
        (
            [app.address] => Doctrine\DBAL\Schema\Table Object
                (
                    [_columns:protected] => Array
                        (
                            //{...}

                            [id_int] => Doctrine\DBAL\Schema\Column Object
                                (
                                    [_type:protected] => Doctrine\DBAL\Types\IntegerType Object
                                        (
                                        )

                                    [_length:protected] =>
                                    [_precision:protected] => 10
                                    [_scale:protected] => 0
                                    [_unsigned:protected] => 1
                                    [_fixed:protected] =>
                                    [_notnull:protected] => 1
                                    [_default:protected] =>
                                    [_autoincrement:protected] => 1
                                    [_platformOptions:protected] => Array
                                        (
                                        )

                                    [_columnDefinition:protected] =>
                                    [_comment:protected] =>
                                    [_customSchemaOptions:protected] => Array
                                        (
                                        )

                                    [_name:protected] => id_int
                                    [_namespace:protected] =>
                                    [_quoted:protected] =>
                                )

                        )

                    [_indexes:protected] => Array
                        (
                            [id_int] => Doctrine\DBAL\Schema\Index Object
                                (
                                    [_columns:protected] => Array
                                        (
                                            [id_int] => Doctrine\DBAL\Schema\Identifier Object
                                                (
                                                    [_name:protected] => id_int
                                                    [_namespace:protected] =>
                                                    [_quoted:protected] =>
                                                )

                                        )

                                    [_isUnique:protected] => 1
                                    [_isPrimary:protected] =>
                                    [_flags:protected] => Array
                                        (
                                        )

                                    [options:Doctrine\DBAL\Schema\Index:private] => Array
                                        (
                                            [lengths] => Array
                                                (
                                                    [0] =>
                                                )

                                        )

                                    [_name:protected] => id_int
                                    [_namespace:protected] =>
                                    [_quoted:protected] =>
                                )

                            [primary] => Doctrine\DBAL\Schema\Index Object
                                (
                                    [_columns:protected] => Array
                                        (
                                            [id] => Doctrine\DBAL\Schema\Identifier Object
                                                (
                                                    [_name:protected] => id
                                                    [_namespace:protected] =>
                                                    [_quoted:protected] =>
                                                )

                                        )

                                    [_isUnique:protected] => 1
                                    [_isPrimary:protected] => 1
                                    [_flags:protected] => Array
                                        (
                                        )

                                    [options:Doctrine\DBAL\Schema\Index:private] => Array
                                        (
                                            [lengths] => Array
                                                (
                                                    [0] =>
                                                )

                                        )

                                    [_name:protected] => PRIMARY
                                    [_namespace:protected] =>
                                    [_quoted:protected] =>
                                )
                        )

                    [_primaryKeyName:protected] => primary
                    [uniqueConstraints:protected] => Array
                        (
                        )

                    [_fkConstraints:protected] => Array
                        (
                        )

                    [_options:protected] => Array
                        (
                            [create_options] => Array
                                (
                                )

                            [engine] => InnoDB
                            [collation] => utf8mb4_unicode_ci
                            [charset] => utf8mb4
                            [comment] =>
                        )

                    [_schemaConfig:protected] => Doctrine\DBAL\Schema\SchemaConfig Object
                        (
                            [hasExplicitForeignKeyIndexes:protected] =>
                            [maxIdentifierLength:protected] => 63
                            [name:protected] => app
                            [defaultTableOptions:protected] => Array
                                (
                                    [collate] => utf8mb4_unicode_ci
                                    [charset] => utf8mb4
                                )

                        )

                    [implicitIndexes:Doctrine\DBAL\Schema\Table:private] => Array
                        (
                        )

                    [_name:protected] => address
                    [_namespace:protected] =>
                    [_quoted:protected] =>
                )

        	//{...}

        )

    [_sequences:protected] => Array
        (
        )

    [_schemaConfig:protected] => Doctrine\DBAL\Schema\SchemaConfig Object
        (
            [hasExplicitForeignKeyIndexes:protected] =>
            [maxIdentifierLength:protected] => 63
            [name:protected] => app
            [defaultTableOptions:protected] => Array
                (
                    [collate] => utf8mb4_unicode_ci
                    [charset] => utf8mb4
                )

        )

    [_name:protected] => app
    [_namespace:protected] =>
    [_quoted:protected] =>
)

And here is the computed Doctrine\DBAL\Schema\Schema Object for the NEW schema:

Doctrine\DBAL\Schema\Schema Object
(
    [namespaces:Doctrine\DBAL\Schema\Schema:private] => Array
        (
        )

    [_tables:protected] => Array
        (
            [app.address] => Doctrine\DBAL\Schema\Table Object
                (
                    [_columns:protected] => Array
                        (
                            //{...}

                            [id_int] => Doctrine\DBAL\Schema\Column Object
                                (
                                    [_type:protected] => Doctrine\DBAL\Types\IntegerType Object
                                        (
                                        )

                                    [_length:protected] =>
                                    [_precision:protected] => 10
                                    [_scale:protected] => 0
                                    [_unsigned:protected] => 1
                                    [_fixed:protected] =>
                                    [_notnull:protected] => 1
                                    [_default:protected] =>
                                    [_autoincrement:protected] =>
                                    [_platformOptions:protected] => Array
                                        (
                                            [version] =>
                                        )

                                    [_columnDefinition:protected] => int unsigned NOT NULL AUTO_INCREMENT UNIQUE
                                    [_comment:protected] =>
                                    [_customSchemaOptions:protected] => Array
                                        (
                                        )

                                    [_name:protected] => id_int
                                    [_namespace:protected] =>
                                    [_quoted:protected] =>
                                )

                        )

                    [_indexes:protected] => Array
                        (
                            [primary] => Doctrine\DBAL\Schema\Index Object
                                (
                                    [_columns:protected] => Array
                                        (
                                            [id] => Doctrine\DBAL\Schema\Identifier Object
                                                (
                                                    [_name:protected] => id
                                                    [_namespace:protected] =>
                                                    [_quoted:protected] =>
                                                )

                                        )

                                    [_isUnique:protected] => 1
                                    [_isPrimary:protected] => 1
                                    [_flags:protected] => Array
                                        (
                                        )

                                    [options:Doctrine\DBAL\Schema\Index:private] => Array
                                        (
                                        )

                                    [_name:protected] => primary
                                    [_namespace:protected] =>
                                    [_quoted:protected] =>
                                )

                            [id_int] => Doctrine\DBAL\Schema\Index Object
                                (
                                    [_columns:protected] => Array
                                        (
                                            [id_int] => Doctrine\DBAL\Schema\Identifier Object
                                                (
                                                    [_name:protected] => id_int
                                                    [_namespace:protected] =>
                                                    [_quoted:protected] =>
                                                )

                                        )

                                    [_isUnique:protected] => 1
                                    [_isPrimary:protected] =>
                                    [_flags:protected] => Array
                                        (
                                        )

                                    [options:Doctrine\DBAL\Schema\Index:private] => Array
                                        (
                                        )

                                    [_name:protected] => id_int
                                    [_namespace:protected] =>
                                    [_quoted:protected] =>
                                )

                        )

                    [_primaryKeyName:protected] => primary
                    [uniqueConstraints:protected] => Array
                        (
                        )

                    [_fkConstraints:protected] => Array
                        (
                        )

                    [_options:protected] => Array
                        (
                            [create_options] => Array
                                (
                                )

                            [collate] => utf8mb4_unicode_ci
                            [charset] => utf8mb4
                        )

                    [_schemaConfig:protected] => Doctrine\DBAL\Schema\SchemaConfig Object
                        (
                            [hasExplicitForeignKeyIndexes:protected] =>
                            [maxIdentifierLength:protected] => 63
                            [name:protected] => app
                            [defaultTableOptions:protected] => Array
                                (
                                    [collate] => utf8mb4_unicode_ci
                                    [charset] => utf8mb4
                                )

                        )

                    [implicitIndexes:Doctrine\DBAL\Schema\Table:private] => Array
                        (
                        )

                    [_name:protected] => address
                    [_namespace:protected] =>
                    [_quoted:protected] =>
                )

        )

    [_sequences:protected] => Array
        (
        )

    [_schemaConfig:protected] => Doctrine\DBAL\Schema\SchemaConfig Object
        (
            [hasExplicitForeignKeyIndexes:protected] =>
            [maxIdentifierLength:protected] => 63
            [name:protected] => app
            [defaultTableOptions:protected] => Array
                (
                    [collate] => utf8mb4_unicode_ci
                    [charset] => utf8mb4
                )

        )

    [_name:protected] => app
    [_namespace:protected] =>
    [_quoted:protected] =>
)

Could the mismatch on [_autoincrement:protected] be the cause?

[pps1]

@morozov is the above diff helpful? Can I help produce additional artifacts?

[morozov]

@pps1 could you reproduce your issue as a code snippet? E.g. like #2663 (comment) or #2566 (comment).

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