TRUNCATE fails on tables which have foreign key constraints

[BRMatt]

As of MySQL 5.5 TRUNCATE fails if the table being truncated is referenced by foreign key constraints in other tables.

A temporary solution appears to be to disable foreign key checks before truncation and then re-enable them afterwards

http://zendframework.com/issues/browse/ZF-11235?focusedCommentId=45644&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-45644

[alecsammon]

Nasty but the following will get your tests running

$query = 'SET foreign_key_checks=0;'.$query.' ;SET foreign_key_checks=1;';

to PHPUnit_Extensions_Database_Operation_Truncate line 77

[MaxEvron]

When using DBUnit with PHPUnit, you need to overload getSetUpOperation().

Example above works with PostgresSQL database containing tables with foreign keys.

class MyTest extends PHPUnit_Extensions_Database_TestCase {

    // IMPORTANT : overload getSetUpOperation and add "TRUE" parameter to CLEAN_INSERT()
    protected function getSetUpOperation() {
        return PHPUnit_Extensions_Database_Operation_Factory::CLEAN_INSERT(TRUE);
        //                                                                 ⬆⬆⬆
    }

    public function getConnection() {
        $objPDO = new PDO(); // use your own DSN here
        return $this->createDefaultDBConnection($objPDO);
    }

    public function getDataSet() {
        return $this->createXMLDataSet('MyConfig.xml');
    }

    public function testFirst() {
        // Your test code
    }

    public function testSecond() {
        // Your test code
    }

    // Etc.

}

[navarr]

I'm kind of astonished that this issue is a year and a half old. +1

Ran into this issue trying to run my stuff and was VERY confused.

[gsouf]

Sad statement, gave me headache, like a lot of persons that you can see question on stackoverflow or on different forums...

[nepda]

The workaround by @MaxEvron is not working for me with MySQL. Is there any clean solution out there?

[gsouf]

@nepda that's the only way i found : https://gist.github.com/gsouf/6168877#file-phpunittestdb-php

[nepda]

Thank you @SneakyBobito! This works. Maybe not the finest solution but not as dirty as hacking inside the framework.

[roninbar]

This solution (based on @MaxEvron's suggestion above) works with MySQL 5.5. It's better than setting foreign_key_checks = 0 because it doesn't sacrifice referential integrity.

class MyTest extends \PHPUnit_Extensions_Database_TestCase {
    // <editor-fold defaultstate="collapsed" desc="PHPUnit_Extensions_Database_TestCase Overrides">

    /**
     * @return \PHPUnit_Extensions_Database_Operation
     */
    protected function getSetUpOperation() {
        return new \PHPUnit_Extensions_Database_Operation_Composite(array(
            \PHPUnit_Extensions_Database_Operation_Factory::DELETE_ALL(),
            \PHPUnit_Extensions_Database_Operation_Factory::INSERT()
        ));
    }

    // getConnection...

    // getDataSet

    // </editor-fold>

    // Test methods
}

[elazar]

@ron-inbar That does indeed look like a much better work-around.

@nepda I'm assuming that @MaxEvron's solution not working on MySQL implies that there's some sort of issue with the TRUNCATE operation and MySQL, possibly involving use of foreign keys. Could you try capturing this query and executing it manually to confirm that it works?

[roninbar]

@elazar, the error you get when you try to execute this operation looks like this:

"COMPOSITE[TRUNCATE] operation failed on query: 
                TRUNCATE `<table>`
             using args: Array
(
)
 [SQLSTATE[42000]: Syntax error or access violation: 1701 Cannot truncate a table referenced in a foreign key constraint (...)]"

[elazar]

@MaxEvron Can you confirm that @ron-inbar's solution also works on PostgreSQL? I'd like to make sure we're not affecting non-MySQL servers by making this change if possible.

[MaxEvron]

Matthew,

I don't have much time to check for the moment but I'll try to create a test database and check this works before the end of January.

Read you. Le 23 déc. 2013 05:05, "Matthew Turland" notifications@github.com a écrit :

@MaxEvron https://github.com/MaxEvron Can you confirm that @ron-inbarhttps://github.com/ron-inbar's solution also works on PostgreSQL? I'd like to make sure we're not affecting non-MySQL servers by making this change if possible.

— Reply to this email directly or view it on GitHubhttps://github.com/sebastianbergmann/dbunit/issues/37#issuecomment-31103550 .

[awladnas]

hi guys ,

i am also facing same issue. I have tried to foreign_key_checks = 0 in setup method it did not work for me. I also tried
return new \PHPUnit_Extensions_Database_Operation_Composite(array( \PHPUnit_Extensions_Database_Operation_Factory::DELETE_ALL(), \PHPUnit_Extensions_Database_Operation_Factory::INSERT() ));

in getSetUpOperation() method it also did not work. I am using mysql database.

Is there any other solution for this issue ?

[gsouf]

@awladnas did you place foreign_key_checks = 0 as follows : https://gist.github.com/gsouf/6168877#file-phpunittestdb-php

[awladnas]

yes i did.Only difference : in my case class is not abstract.

[gsouf]

Yes, abstraction doesnt matter. Idiot question, but did you think to extend this class when you run your test ? Because i still used it yesterday and it worked like a charm

[awladnas]

Yes i extend this class for testing another class. I will add a git repo with my code tomorrow :)

[awladnas]

@SneakyBobito this is my sample test example : http://pastebin.com/1BjVt2DA

i also tried like this: http://pastebin.com/zh69kMh7

[gsouf]

Hard to help you with so few things. It looks like my example execpt for the conn (line 15 of your link). You use 1 conn everytime when i re-init it every time).

My example works on many severs, with many applications, many frameworks.... . Sorry cant help you more :x

[jameswithers]

@awladnas I had the same error. The suggestion by @ron-inbar works for me on MySQL. You also need to ensure the action on your foreign keys is set to CASCADE not RESTRICT (within MySQL).

[karptonite]

@elazar @ron-inbar I actually found that the solution of overloading just getSetUpOperation did not entirely work for me. While it fixed the problem of deleting the tables, it created a new problem related to the table primary keys. I'm not certain, but I believe that DELETE_ALL does not reset the tables primary keys (auto-increment) the way truncate does, leading to unpredictable results when inserting into a table.

[roninbar]

@karptonite, you can reset the auto-increment counter as follows:

ALTER TABLE table_name AUTO_INCREMENT = 1;

See http://viralpatel.net/blogs/reseting-mysql-autoincrement-column/. I didn't have time to try it myself so I'm not completely sure where to place this command in the test class.

[roninbar]

@karptonite On second thought, why do you rely on auto-increment instead of specifying the primary key explicitly in your data files?

[karptonite]

@ron-inbar Thanks, I know I can do that, but I also wasn't sure of the best place to put it. For now I've been turning on and off foreign key checks before and after setUp().

I am specifying primary keys explicitly in my data files. The problem comes when multiple tests test record creation (and reading). Inserts by the systems under test themselves, which do not specify ids, change the autoincrement value for the next insert (by a system under test) and so forth.

[elazar]

I tend to think it's better to be explicit and provide primary key values in your data set so their values are predictable.

[karptonite]

@elazar We may have been typing comments at the same time, but I am providing primary key values in the data set. The problem is that the changing value of auto-increment is causing interacting tests when test write to the database.

[matiasbarone]

I'm using Postgresql 9.4 and have the same problem. I made it works using Cascade on the Truncate clause. To do that I override getSetUpOperation the following way. PHPUnit_Extensions_Database_Operation_Factory::CLEAN_INSERT() support receiving boolean to force the cascade

    /**
     * Returns the database operation executed in test setup.
     *
     * @return PHPUnit_Extensions_Database_Operation_DatabaseOperation
     */
    protected function getSetUpOperation() {
        return PHPUnit_Extensions_Database_Operation_Factory::CLEAN_INSERT ( true );
    }

[stale[bot]]

This issue has been automatically marked as stale because it has not had activity within the last 60 days. It will be closed after 7 days if no further activity occurs. Thank you for your contributions.

[stale[bot]]

This issue has been automatically closed because it has not had activity since it was marked as stale. Thank you for your contributions.