This is something me and my colleagues encounter from time to time.
The Problem
One of the problems of adding unique indexes to existing data, like adding the sluggable Doctrine behaviour using the StofDoctrineExtensionsBundle, is that the generated migration will end up throwing an error:
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '' for key 'UNIQ_BDAFD8C8989D9B62'
Causes
Since the default values for the new MySQL column are not unique, adding the unique index is not possible – which is what the error above is telling us. So we will need to change the migration to also generate the unique values before adding the index.
Solution
In order to do so, we will have to split the generated migration into 2 different migrations, one for adding the new column, which could be a slug, and the other to add the unique index. After running the first migration, we need to execute the code that generates the unique values needed for the index. We can use the postUp method in a Doctrine migration to execute code after the “up” migration finished. We will also need to instantiate and boot the kernel in order to gain access to the Symfony framework and build our functionality like we would do in a controller:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 |
<?php namespace Application\Migrations; use Doctrine\DBAL\Migrations\AbstractMigration; use Doctrine\DBAL\Schema\Schema; /** * Auto-generated Migration: Please modify to your needs! */ class Version20151021133750 extends AbstractMigration { protected static $class = 'AppKernel'; protected static $kernel; /** * Creates a Kernel. * * Available options: * * * environment * * debug * * @param array $options An array of options * * @return HttpKernelInterface A HttpKernelInterface instance */ protected static function createKernel(array $options = array()) { if (null === static::$class) { static::$class = static::getKernelClass(); } return new static::$class( isset($options['environment']) ? $options['environment'] : 'test', isset($options['debug']) ? $options['debug'] : true ); } /** * Creates a Client. * * @param array $options An array of options to pass to the createKernel class * @param array $server An array of server parameters * * @return Client A Client instance */ protected static function createClient(array $options = array(), array $server = array()) { if (null !== static::$kernel) { static::$kernel->shutdown(); } static::$kernel = static::createKernel($options); static::$kernel->boot(); $client = static::$kernel->getContainer()->get('test.client'); $client->setServerParameters($server); return $client; } /** * @param Schema $schema */ public function up(Schema $schema) { // this up() migration is auto-generated, please modify it to your needs $this->abortIf($this->connection->getDatabasePlatform()->getName() != 'mysql', 'Migration can only be executed safely on \'mysql\'.'); $this->addSql('ALTER TABLE book ADD slug VARCHAR(128) DEFAULT ""'); //$this->addSql('CREATE UNIQUE INDEX UNIQ_CBE5A331989D9B62 ON book (slug)'); } public function postUp(Schema $schema) { $this->client = self::createClient(); $this->em = $this->client->getKernel()->getContainer()->get('doctrine')->getEntityManager(); $books = $this->em->getRepository('AppBundle:Book')->findAll(); foreach($books as $book){ // need this so we force the generation of a new slug $book->setSlug(null); $this->em->persist($book); } $this->em->flush(); } /** * @param Schema $schema */ public function down(Schema $schema) { // this down() migration is auto-generated, please modify it to your needs $this->abortIf($this->connection->getDatabasePlatform()->getName() != 'mysql', 'Migration can only be executed safely on \'mysql\'.'); //$this->addSql('DROP INDEX UNIQ_CBE5A331989D9B62 ON book'); $this->addSql('ALTER TABLE book DROP slug'); } } |
For the second migration file we only add the code necessary to add/remove the unique indexes:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
<?php namespace Application\Migrations; use Doctrine\DBAL\Migrations\AbstractMigration; use Doctrine\DBAL\Schema\Schema; /** * Auto-generated Migration: Please modify to your needs! */ class Version20151021141028 extends AbstractMigration { /** * @param Schema $schema */ public function up(Schema $schema) { // this up() migration is auto-generated, please modify it to your needs $this->abortIf($this->connection->getDatabasePlatform()->getName() != 'mysql', 'Migration can only be executed safely on \'mysql\'.'); $this->addSql('ALTER TABLE book CHANGE slug slug VARCHAR(128) NOT NULL'); $this->addSql('CREATE UNIQUE INDEX UNIQ_CBE5A331989D9B62 ON book (slug)'); } /** * @param Schema $schema */ public function down(Schema $schema) { // this down() migration is auto-generated, please modify it to your needs $this->abortIf($this->connection->getDatabasePlatform()->getName() != 'mysql', 'Migration can only be executed safely on \'mysql\'.'); $this->addSql('DROP INDEX UNIQ_CBE5A331989D9B62 ON book'); $this->addSql('ALTER TABLE book CHANGE slug slug VARCHAR(128) DEFAULT \'\' COLLATE utf8_unicode_ci'); } } |
You can generate an empty migration file using the doctrine:migratios:generate command.
If you now run the doctrine:migrations:migrate command everything should be fine and the database should be populated with the unique values we needed in the first place.
Conclusion
Luckily I solved this issue before an important deadline. Let me know if you found any other way around it, or a quicker solution to this issue.
If you need to request a service there is no need to build the kernel. Just implement ContainerAwareInterface http://symfony.com/doc/current/bundles/DoctrineMigrationsBundle/index.html#container-aware-migrations
And you don’t need a second migration file as you can add your indexes in the postUp method too. This way you’ll have less files to maintain.