Truncating table with foreign keys fails

[ghost]

When I am calling symfony CLI command:

php app/console doctrine:fixtures:load

I get

[PDOException] 

SQLSTATE[42000]: Syntax error or access violation: 1701 Cannot truncate a table referenced in a foreign key constraint (symfony.param_product, CONSTRAINT param_product_ibfk_1 FOREIGN KEY (param_val_id) REFERENCES symfony.param_value (id))

Table symfony.param_product is created by @Orm\JoinTable annotation (with @Orm\ManyToMany). I am using MySQL

This worked fine before this change: 91ff6eb

I thought it might be bug on my side (e.g. no onDelete options) but everything I tried failed.

As far as I learned e.g. from http://forums.asp.net/t/1283840.aspx/1?How+can+I+truncate+the+table+which+have+foreign+key+

 ON DELETE CASCADE is true only for deleting records and not for truncating tables.

 You have to drop the foreign key constraint from Child Table that references the Master Table to be truncated, then after only you are able to truncate the Master Table. 

So it seems to me the only solution is to use DELETE or drop the foregin keys first.

Can we revert the 91ff6eb change to fix this problem? @beberlei?

[stof]

The issue with using DELETE is that it does not reset the auto increment value

[ghost]

Ok, but the issue with TRUNCATE is that the doctrine:fixtures:load command just does NOT work.

If it's really needed, starting auto increment from 1 can be always done for example by dropping and recreating tables, right?

[theinterned]

Yep - I've got this issue too. Rolling back to 91ff6eb for now.

[kbond]

+1 for reverting 91ff6eb

[leabaertschi]

Why not just add 'CASCADE' to the TRUNCATE? $platform->getTruncateTableSQL($tbl) has a second parameter that would return something like 'TRUNCATE table CASCADE' (at least for postgres it does) and with that it works.

[beberlei]

How about adding a pull request? I can merge it then.

[ghost]

@leahaense

The issue still exists when using MySQL (even after 4a8464e).

[beberlei]

In that case your foreign keys are bi-directional or something?

[ghost]

@beberlei yes I think so. I am not 100% sure as SQL is generated by Doctrine from one-to-many/many-to-many annotations (which yes are bi-directional).

Anyone else is having this issue on MySQL? Or is it just me?

[codecowboy]

Can you do foreign_key_checks=0 before TRUNCATE ?

http://bugs.mysql.com/bug.php?id=58788

I'm also hitting this issue

[srosato]

I've ran with the exact same issue with my partner, this does happen on MySQL 5.5 while 5.1 is fine with truncating tables with foreign keys. Reverting back to DELETE wouldn't be an elegant solution as stated before with the auto_increment keys not resetting.

[mikael0hlsson]

Would be nice if there could come a fix like foreign_key_checks=0 for mysql 5.5

http://bugs.mysql.com/bug.php?id=54678

[mikael0hlsson]

Here is a quick dirty hack for getting it to work with MySQL 5.5

diff --git a/lib/Doctrine/Common/DataFixtures/Purger/ORMPurger.php b/lib/Doctrine/Common/DataFixtures/Purger/ORMPurger.php
index a580c1f..ff758c1 100644
--- a/lib/Doctrine/Common/DataFixtures/Purger/ORMPurger.php
+++ b/lib/Doctrine/Common/DataFixtures/Purger/ORMPurger.php
@@ -82,10 +82,12 @@ class ORMPurger implements PurgerInterface
             $orderedTables[] = $class->getTableName();
         }

+       $this->em->getConnection()->executeUpdate("SET foreign_key_checks = 0;");
         $platform = $this->em->getConnection()->getDatabasePlatform();
         foreach($orderedTables as $tbl) {
             $this->em->getConnection()->executeUpdate($platform->getTruncateTableSQL($tbl, true));
         }
+       $this->em->getConnection()->executeUpdate("SET foreign_key_checks = 1;");
     }

     private function getCommitOrder(EntityManager $em, array $classes)

[yakobe]

The above fix works great for me. Any ideas when a fix could be introduced to repo so i dont have to change it manually all the time?

[adrienbrault]

I also had to add this fix to use the bundle.

[mattsnowboard]

The above fixed it for me (as far as I can tell, still learning Doctrine/Symfony2)

[komputerwiz]

I made the foreign_key_checks changes to MySqlPlatform on the DBAL project itself just minutes after @mdarse submitted his pull request:

doctrine/dbal#42

[frastel]

Same problem on my machine with MySQL 5.5.9, however dogbrain's hack helped.

[ghost]

Can we get this fix integrated please? Otherwise everyone always has to add it manually to get it working.

[beberlei]

Its not a "fix" its a hack. I wont merge a hack.

[ghost]

Ok... fair enough. However, it's a hack that allows the fixtures to work and it would be nice if they did ;)
If this cant be merged, is there a fix that can be? Otherwise there is a bug that makes this bundle unusable for many people (without constantly manually hacking it's code every time an update is made)

[ghost]

@beberlei?

Can we revert the 91ff6eb change to fix this problem?

This way it won't be a hack. It was suggested in the first post in this issue.

[mconti80]

I'm not sure I'm doing it right, however I have the same problem. I can't figure out what version I'm using, I've downloaded with composer with

"require": {
    "doctrine/doctrine-fixtures-bundle": "dev-master"
},

Anyway, in order to obtain an index reset, I need to run "doctrine:schema:drop --force" and then "doctrine:schema:update --force" before reloading. Otherwise I have the same error. Can someone pls tell me what I'm doing wrong?
Thanks in advance.

[orourkedd]

I'm having the same issue running the latest version of doctrine fixtures and Mysql 5.5. The "hack" worked, but that's not a portable solution. I have to drop the schema and recreate it every time.

[sergiu-popa]

Do yourself a favor and create a script inside Symfony folder, like load_fixtures:

bin/console doc:sc:drop --force
bin/console doc:sc:cr
bin/console doc:fix:lo --no-interaction

Run chmod 755 load_fixtures and then ./load_fixtures.