Issue when trying to reload the fixtures

Question

When I try to reload my fixtures using

php app/console doctrine:fixtures:load

I'm getting this error:

SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (foo_db.Book, CONSTRAINT FK_C29271DD816C6140 FOREIGN KEY (author_id) REFERENCES Author (id))

The error is showed when the status "> purging database" is showed.

This is my code:

class Book{
...

/**
 * @ORM\ManyToOne(targetEntity="Author", inversedBy="books")
 */
private $author;

... 
}

class Author{
    ...
    /**
     * @ORM\OneToMany(targetEntity="Book", mappedBy="author")
     */
    private $books;

}

More: my boss has the same code and it doesn't have that error.

Any idea?

sf 2.0.1 (just updated)/ubuntu 10.10.

Answer 1

If I'm guessing correctly, you are using a MySQL database. If yes, then you are facing a bug/problem with the current version of the doctrine-fixtures library for Doctrine2. The problem is that they are using the TRUNCATE command to purge the current database values but this command has problem deleting foreign associations in MySQL.

See this issue and this one on the GitHub repository of the library for more information and workarounds.

In my particular case, I run this command from a script, so to make the command work correctly, I do:

php app/console doctrine:database:drop --force
php app/console doctrine:database:create

php app/console doctrine:schema:update --force

php app/console doctrine:fixtures:load --append

This way, the purging is done by the drop command and appending has the same effect as not appending since the database is empty when the fixtures are loaded.

I must admit I don't know why your boss doesn't have the problem, maybe there is no book associated with an author in his database.

Hope this help.

Regards,
Matt

Answer 2

I've created a simple Event Subscriber class for Symfony 4. All you need to fix the self-referencing foreign keys issue is to add the below class somewhere to your Symfony 4 project.

This subscriber fires before each Symfony CLI command. In case if the command's name is doctrine:fixtures:load, it performs database purge, but doing SET FOREIGN_KEY_CHECKS = 0 first.

This solves the issue without any other modification.

use Doctrine\Common\DataFixtures\Purger\ORMPurger;
use Doctrine\ORM\EntityManagerInterface;
use Symfony\Component\Console\ConsoleEvents;
use Symfony\Component\Console\Event\ConsoleCommandEvent;
use Symfony\Component\EventDispatcher\EventSubscriberInterface;

class ConsoleCommandEventSubscriber implements EventSubscriberInterface
{
    /**
    * @var EntityManagerInterface
    */
    private $entityManager;

    public function __construct(EntityManagerInterface $entityManager)
    {
        $this->entityManager = $entityManager;
    }

    public static function getSubscribedEvents()
    {
        return [
            ConsoleEvents::COMMAND => 'onCommand',
        ];
    }

    public function onCommand(ConsoleCommandEvent $event)
    {
        if ($event->getCommand()->getName() === 'doctrine:fixtures:load') {
            $this->runCustomTruncate();
        }
    }

    private function runCustomTruncate()
    {
        $connection = $this->entityManager->getConnection();

        $connection->exec('SET FOREIGN_KEY_CHECKS = 0');

        $purger = new ORMPurger($this->entityManager);
        $purger->setPurgeMode(ORMPurger::PURGE_MODE_DELETE);
        $purger->purge();

        $connection->exec('SET FOREIGN_KEY_CHECKS = 1');
    }
}