Doctrine migrations:diff regenerates same unique index constraint

Question

I have a weird problem. When I run doctrine-migrations migrations:diff it regenerates the index that already is set in an earlier migration.

Earlier migration (the uniq index is also present in my database):

$this->addSql('CREATE TABLE my_table (id INT AUTO_INCREMENT NOT NULL, email VARCHAR(255) NOT NULL, UNIQUE INDEX UNIQ_4VBV083VA6917B55 (email), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE `utf8_unicode_ci` ENGINE = InnoDB');

Regenerated after running doctrine-migrations migrations:diff:

$this->addSql('DROP INDEX uniq_4vbv083va6917b55 ON my_table');
$this->addSql('CREATE UNIQUE INDEX UNIQ_J43107ECE6416I64 ON contact_company (email)');

Notice the lowercase to uppercase. Maybe that means something. In my earlier migration I have it uppercase and it's also appear upper in my database, so I don't know why it is lowercase here.

I've defined the unique constraint like this in my entity:

/**
 * @ORM\Column(type="string", unique=true)
 */
private string $email;

Answer 1

I also ran into this issue. I fixed this by changing my email column length to 191 characters, which is the max allowed for indexing by MySQL (utf8mb4).

Index Sizes: MySQL varchar index length

/**
 * @ORM\Column(type="string", length=191, unique=true)
 */
private string $email;

I have a composite index for my field and noticed while inspecting the index in the database that it reads "email(191),xxxx_id,xxxx_id". I'm not sure how doctrine is doing things but I think it sees that 191 != 255 here and tries to drop and recreate the index over and over.

I am using these package versions:
doctrine/orm -> 2.12.1
doctrine/migrations -> 3.5.0