doctrine:fixtures:load --purge-with-truncate fails

[fkrauthan]

If I run app/console doctrine:fixtures:load --purge-with-truncate I get the error SQLSTATE[42000]: Syntax error or access violation: 1701 Cannot truncate a table referenced in a foreign key constraint

[stof]

This is a restriction in MySQL 5.5: it cannot truncate when there is a foreign key. This is exactly the reason why using TRUNCATE is not the default behavior anymore.

[fkrauthan]

But can't you delete all data then TRUNCATE all tables and then importing all new datas?

[deeky666]

This issue could be fixed by disabling the foreign key checks before truncating (SET foreign_key_checks = 0). Would that be an option worth thinking about? I don't know how other RDMS manage this issue...

[stof]

@deeky666 this has already been suggested, and @beberlei rejected it.

[rjmunro]

doctrine:fixtures:load fails with foreign key errors for me either with or without the --purge-with-truncate option. Is this the same issue, or something deeper?

[deeky666]

Foreign keys are also checked when truncating a table, thus it's the same problem...

[scuben]

Run into the same problem today. I would go for another option to disable foreign key checks for truncate.

[cryptiklemur]

Have we found a solution for this yet?

[deeky666]

According to @guilhermeblanco this will be fixed in 2.0

[luishdez]

Any news on this? why "SET foreign_key_checks = 0;" proposal has been rejected ?

I think SET foreign_key_checks = 0; it's the only possible way to solve this problem. That's why it exists. Because if you have a relation with foreign keys between each other, there is no other way.

So this command doesn't make sense at all to me. :(

[deeky666]

Please see also the discussion here: doctrine/data-fixtures#127

[mnapoli]

So there is still no solution available for this problem? Even a hacky one?

[stormsson]

Maybe I'm late, but I found this to be a workding workaround:

https://coderwall.com/p/staybw/workaround-for-1701-cannot-truncate-a-table-referenced-in-a-foreign-key-constraint-using-doctrine-fixtures-load-purge-with-truncate

[afilina]

Can we please add something like --ignore-foreign-keys so that I don't have to edit the core?

[deeky666]

@afilina "ignoring" foreign keys is a MySQL specific option which is not in that way available for other database vendors. Adding a MySQL specific option to data fixtures is not a good solution and won't happen.
Currently working on a solution in DBAL that uses some kind of abstraction to solve the problem for all platforms. Stay tuned.

[lukaszwit]

Fall into same issue today. Simply wasted time to resolve this manually. Mentioned "ignoring" flag could be abstraction for other rdbms and native for mysql.

[lavoiesl]

See doctrine/data-fixtures#127

[rudak]

'Hi guys' ^^ is there anything new ? ty

[edrush]

Hi, same question as rudak - thank you!

[deeky666]

This still is an issue that is not easy to solve across vendors. At least with the current development stage of DBAL. I had been working on something but haven't gotton to finish it. So "no" there is nothing new yet.

[NathanVss]

You can actually execute this command :

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

[CyrilCharlier]

Same problem, i run a
$ php bin/console doctrine:database:drop --force
$ php bin/console doctrine:database:create
$ php bin/console doctrine:schema:update --force

to solve (if it is a solution)...

[carlowens]

This seems to be doing the job for me as a runtests.sh script:

php bin/console doctrine:database:drop --force --env=test
php bin/console doctrine:database:create --env=test
php bin/console doctrine:schema:update --force --env=test
php bin/console doctrine:fixtures:load --env=test --no-interaction
#test etc
php bin/phpspec run
php bin/behat

[KarolosLykos]

I created a custom command for doing that , although updating schema every time it is a little bit frustrating.

Creating directory Command inside AppBundle

<?php
namespace AppBundle\Command;

use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;


class fixturesReloadCommand extends Command
{
    protected function configure()
    {
        $this
            // the name of the command (the part after "bin/console")
            ->setName('app:fixturesReload')

            // the short description shown while running "php bin/console list"
            ->setDescription('Drop/Create Database and load Fixtures ....')

            // the full command description shown when running the command with
            // the "--help" option
            ->setHelp('This command allows you to load dummy data by recreating database and loading fixtures...');
    }

    protected function execute(InputInterface $input, OutputInterface $output)
    {
        $application = $this->getApplication();
        $application->setAutoExit(false);

        $output->writeln([
            '===================================================',
            '*********        Dropping DataBase        *********',
            '===================================================',
            '',
        ]);

        $options = array('command' => 'doctrine:database:drop',"--force" => true);
        $application->run(new \Symfony\Component\Console\Input\ArrayInput($options));


        $output->writeln([
            '===================================================',
            '*********        Creating DataBase        *********',
            '===================================================',
            '',
        ]);

        $options = array('command' => 'doctrine:database:create',"--if-not-exists" => true);
        $application->run(new \Symfony\Component\Console\Input\ArrayInput($options));

        $output->writeln([
            '===================================================',
            '*********         Updating Schema         *********',
            '===================================================',
            '',
        ]);

        //Create de Schema
        $options = array('command' => 'doctrine:schema:update',"--force" => true);
        $application->run(new \Symfony\Component\Console\Input\ArrayInput($options));

        $output->writeln([
            '===================================================',
            '*********          Load Fixtures          *********',
            '===================================================',
            '',
        ]);

        //Loading Fixtures
        $options = array('command' => 'doctrine:fixtures:load',"--no-interaction" => true);
        $application->run(new \Symfony\Component\Console\Input\ArrayInput($options));

    }
}

And run the command from terminal

php bin/console app:fixturesReload

[karser]

This seems to be shorter

bin/console doctrine:schema:drop --force \
&& bin/console doctrine:schema:update --force \
&& bin/console doctrine:fixtures:load -n