Last Updated: December 06, 2019
·
7.23K
· coma

Workaround for "1701 Cannot truncate a table referenced in a foreign key constraint" using doctrine:fixtures:load --purge-with-truncate

I've been following a BDD approach using Behat to develop a RESTful API on my current project and wanted to fully isolate the data base from feature to feature, so first I tried with:

/**
 * @BeforeFeature
 */
public static function beforeFeature(BeforeFeatureScope $scope)
{
    echo shell_exec('app/console doctrine:schema:drop --env=test --force');
    echo shell_exec('app/console doctrine:schema:create --env=test');
    echo shell_exec('app/console doctrine:fixtures:load --env=test -n');
}

and it was pretty slow, in my case it was taking an average of 1,5s...

Reading the DoctrineFixturesBundle help I found a nice --purge-with-truncate flag, but TA DAN, it was failing with this issue: https://github.com/doctrine/data-fixtures/pull/127

Well, I solved it by replacing the MySqlPlatform::getTruncateTableSQL method like:

config_test.yml

doctrine: dbal: driver_class: Your\OwnBundle\DBAL\Driver ...

src/Your/OwnBundle/DBAL/Driver.php

<?php

namespace Your\OwnBundle\DBAL;

use Doctrine\DBAL\Driver\PDOMySql\Driver as BaseDriver;

class Driver extends BaseDriver
{
    /**
     * {@inheritdoc}
     */
    public function getDatabasePlatform()
    {
        return new Platform();
    }
}

src/Your/OwnBundle/DBAL/Platform.php

<?php

namespace Your\OwnBundle\DBAL;

use Doctrine\DBAL\Platforms\MySqlPlatform;

class Platform extends MySqlPlatform
{
    /**
     * {@inheritdoc}
     */
    public function getTruncateTableSQL($tableName, $cascade = false)
    {
        return sprintf('SET foreign_key_checks = 0;TRUNCATE %s;SET foreign_key_checks = 1;', $tableName);
    }
}

And finally:

/**
 * @BeforeFeature
 */
public static function beforeFeature(BeforeFeatureScope $scope)
{
    echo shell_exec('app/console doctrine:fixtures:load --env=test --purge-with-truncate -n');
}

It's hacky, I know, but it does the job for the tests and cuts down the average time to 0,4s.

4 Responses
Add your response

Thank you, this helped me a lot

over 1 year ago ·

Hey @stormsson, I'm glad to read it!

over 1 year ago ·

Hi You save my whole day of work. Thank you.

over 1 year ago ·

Thanks! Great hacky solution! But for the moment of December 2019 it's not working. Seems that doctrine changed the way how they detect platform. So few steps for Symfony version 4 to make it working again!
1) remove platform in .env.test, e.g. it was:
DATABASETESTURL=mysql://user:pass@host:3306/testdb
must be:
DATABASE
TESTURL=//user:pass@host:3306/testdb
2) you need to override different method inside custom "Driver" class, in article it's "getDatabasePlatform" but it must be "createDatabasePlatformForVersion" from the interface "VersionAwarePlatformDriver"
3) and last but not least, for overridden "Platform" method body should be next (from parent class)
$tableIdentifier = new Identifier($tableName);
return sprintf(
'SET foreignkeychecks = 0;TRUNCATE %s;SET foreignkeychecks = 1;',
$tableIdentifier->getQuotedName($this)
);

over 1 year ago ·