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;

Goethe

I have same issue with doctrine/doctrine-migrations-bundle:3.2.2 . Doctrine wants to recreate existing primary key for "user" table, also recreate unique index on same table. Generated SQL of course cannot be executed because both primary key and index already exists. Other tables are fine. Can it be related to escaped table name? – Aleph

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

Penitence