Truncating table with foreign keys fails #17

Closed
ghost opened this Issue Jun 12, 2011 · 25 comments

Projects

None yet
@ghost
ghost commented Jun 12, 2011

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
Member
stof commented Jun 12, 2011

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

@ghost
ghost commented Jun 13, 2011

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
kbond commented Jun 22, 2011

+1 for reverting 91ff6eb

@leahaense
Contributor

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
Member

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

@ghost
ghost commented Jul 4, 2011

@leahaense

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

@beberlei
Member
beberlei commented Jul 4, 2011

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

@ghost
ghost commented Jul 6, 2011

@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
srosato commented Jul 13, 2011

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.

@dogbrain

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

@dogbrain

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
yakobe commented Jul 22, 2011

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
frastel commented Aug 19, 2011

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

@ghost
ghost commented Aug 29, 2011

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

@beberlei
Member

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

@ghost
ghost commented Aug 29, 2011

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
ghost commented Aug 29, 2011

@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.

@beberlei beberlei closed this in 41ef09a Sep 25, 2011
@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
sergiu-popa commented Oct 27, 2016 edited

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment