Support truncating tables with foreign keys

[Seldaek]

I had to override the loadFixtures method of the LiipFunctionalTestBundle with this stuff to avoid blowing up mysql:

    protected function loadFixtures(array $classNames, $omName = null, $registryName = 'doctrine', $purgeMode = null)
    {
        $conn = $this->getContainer()->get($registryName)->getManager()->getConnection();
        $db = $conn->getDriver()->getDatabasePlatform()->getName();
        if ($db === 'mysql') {
            $conn->exec('SET FOREIGN_KEY_CHECKS=0');
        }
        $res = parent::loadFixtures($classNames, $omName, $registryName, $purgeMode);
        if ($db === 'mysql') {
            $conn->exec('SET FOREIGN_KEY_CHECKS=1');
        }

        return $res;
    }

Now obviously it could be integrated in the bundle (cc @lsmith77) but ideally it would be part of this library itself so everyone benefits. Not sure how/where to best do this though, so I'm dropping this here hoping someone will pick it up :)

[Ocramius]

@Seldaek turning off FK checks in fixtures? Sounds seriously broken... What about the other engines? Is it just a MySQL bug?

[beberlei]

we need to somehow generalize this, its a MySQL problem AFAIK, not sure about other databases. We need to reproduce this in DBAL for sure.

[lsmith77]

the big issue in MySQL is that they do FK checks after every statement rather than at the end of the transaction. so yeah most other RDBMS support so called deferred FKs and therefore do not suffer from this.

[Seldaek]

Sorry I should have clarified. This is not about disabling FK checks for the whole loading of fixtures, the only thing we need is to disable them while running the ORMPurger because it does TRUNCATE and that is not supported with FK checks enabled.

[tPl0ch]

This really is a poking issue. Almost all my CI projects are now failing after an update to MySQL >= 5.5.7 (the version where this behavior of TRUNCATE was introduced). For reference here are the discussions on mysql: http://bugs.mysql.com/bug.php?id=58788 and http://bugs.mysql.com/bug.php?id=58788

I hope that you can find a solution in a timely manner since more and more people will be upgrading mysql for sure.

[tiger-seo]

+1 this need to be resolved

[theofidry]

+1

[fschaeffer]

The only way to fiddle with this issue (as both MySQL won't fix it nor doctrine DBAL willing to add another vendor specific rule) for me was to create a special DBAL driver and use this during fixture loading.

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 gives some hints.

In Symfony I ended up using a config_fixtures.yml, passing the doctrine:fixtures:load env=fixtures where this special Driver is used

config_fixtures.yml

...
doctrine: 
    dbal: 
        driver_class: AppBundle\DBAL\Driver

Driver.php

<?php

namespace AppBundle\DBAL;

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

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

Platform.php

<?php

namespace AppBundle\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);
    }
}

[soullivaneuh]

@fschaeffer With your workaround, I get the following error:

 [Symfony\Component\Config\Definition\Exception\InvalidConfigurationException]  
  Unrecognized option "driver_class" under "doctrine.dbal"                       

My doctrine dbal config:

doctrine:
    dbal:
        driver_class: AppBundle\DBAL\Driver
        default_connection: default
        connections:
            default:
                driver:   pdo_mysql
                host:     "%database_host%"
                port:     "%database_port%"
                dbname:   "%database_name%"
                user:     "%database_user%"
                password: "%database_password%"
                charset:  UTF8

            powerdns:
                driver:   pdo_mysql
                host:     "%powerdns_host%"
                port:     "%powerdns_port%"
                dbname:   "%powerdns_name%"
                user:     "%powerdns_user%"
                password: "%powerdns_password%"
                charset:  UTF8
        types:
            json: Sonata\Doctrine\Types\JsonType

Any idea?

[soullivaneuh]

Ok I get it, it's because I have multiple connections definitions. I have to specify it on each connection:

doctrine:
    dbal:
        default_connection: default
        connections:
            default:
                driver_class: AppBundle\DBAL\Driver
                driver:   pdo_mysql
                host:     "%database_host%"
                port:     "%database_port%"
                dbname:   "%database_name%"
                user:     "%database_user%"
                password: "%database_password%"
                charset:  UTF8