PHPUnit and MySQL truncation error

Question

I am getting a headache with PHPUnit's behavior of always running TRUNCATE prior to inserting fixtures without first setting foreign key checks off:

Syntax error or access violation: 1701 Cannot truncate a table referenced in a foreign key constraint

Basically, PHPUnit tries to truncate a table before it inserts fixtures. How do I tell it to SET FOREIGN_KEY_CHECKS=0;?

Underact

This happens even when the table referencing the table you are truncating is empty. Pretty sure this is a MySQL bug, regardless of the comments here: bugs.mysql.com/bug.php?id=54678 @Underact solution is sadly the only option. – Vernalize

Answer 1

I found the answer it seems. I ended up overriding some methods by extending a class.

<?php

/**
 * Disables foreign key checks temporarily.
 */
class TruncateOperation extends \PHPUnit_Extensions_Database_Operation_Truncate
{
    public function execute(\PHPUnit_Extensions_Database_DB_IDatabaseConnection $connection, \PHPUnit_Extensions_Database_DataSet_IDataSet $dataSet)
    {
        $connection->getConnection()->query("SET foreign_key_checks = 0");
        parent::execute($connection, $dataSet);
        $connection->getConnection()->query("SET foreign_key_checks = 1");
    }
}

Then example usage:

class FooTest extends \PHPUnit_Extensions_Database_TestCase
{
    public function getSetUpOperation()
    {
        $cascadeTruncates = true; // If you want cascading truncates, false otherwise. If unsure choose false.

        return new \PHPUnit_Extensions_Database_Operation_Composite(array(
            new TruncateOperation($cascadeTruncates),
            \PHPUnit_Extensions_Database_Operation_Factory::INSERT()
        ));
    }
}

So I'm effectively disabling foreign key checks and setting them back if they were ever set. Obviously you should make a base class that has this functionality and you extend it rather than PHPUnit's TestCase.

Underact

Where are you setting back foreign key checks? – Codex

@Codex it is reset in TruncateOperation->execute, third line, where it goes: "SET foreign_key_checks = 1". Found this issue on git, which basically does the same, with some added explanation: DbUnit GIT – Powe

Nice solution. I was tempted to use this, but as a control freak, I'd rather ensure that data is entered correctly vs relaxing FK constraints, so I just added some custom delete queries in the setUp method, before I called parent::setUp, and worked fine. – Ivetteivetts

Answer 2

Alternatively, you could simulate truncation with a combination of deleting all records and then re-setting the auto-increment counter.

First, you would have to create new database operation class that will handle resetting the table's auto-increment value.

/**
 * Resets all AUTO_INCREMENT counters on all tables in a dataset.
 * @see PHPUnit_Extensions_Database_Operation_IDatabaseOperation
 */
class ResetAutoincrementOperation implements PHPUnit_Extensions_Database_Operation_IDatabaseOperation
{

    /* 
     * @see PHPUnit_Extensions_Database_Operation_IDatabaseOperation::execute()
     */
    public function execute(PHPUnit_Extensions_Database_DB_IDatabaseConnection $connection, 
            PHPUnit_Extensions_Database_DataSet_IDataSet $dataSet)
    {
        foreach ($dataSet->getReverseIterator() as $table) {
            $query = "ALTER TABLE {$connection->quoteSchemaObject($table->getTableMetaData()->getTableName())}"
                . " AUTO_INCREMENT = 1
            ";

            try {
                $connection->getConnection()->query($query);
            } catch (PDOException $e) {
                throw new PHPUnit_Extensions_Database_Operation_Exception('RESET_AUTOINCREMENT', 
                        $query, array(), $table, $e->getMessage());
            }
        }
    }
}

now overwrite the getSetUpOperation()and getTearDownOperation() methods as described above.

class FooTest extends PHPUnit_Extensions_Database_TestCase
{
    /**
     * @see PHPUnit_Extensions_Database_TestCase::getTearDownOperation()
     */
    protected function getTearDownOperation ()
    {
        // Clean up after ourselves
        return new PHPUnit_Extensions_Database_Operation_Composite(array(
            PHPUnit_Extensions_Database_Operation_Factory::DELETE_ALL(), // 1. delete all records from table
            new ResetAutoincrementOperation() // 2. reset auto increment value
        ));
    }

    /**
     * @see PHPUnit_Extensions_Database_TestCase::getSetUpOperation ()
     */
    protected function getSetUpOperation ()
    {
        return new PHPUnit_Extensions_Database_Operation_Composite(array(
                PHPUnit_Extensions_Database_Operation_Factory::DELETE_ALL(), // 1. delete all records from table
                new ResetAutoincrementOperation(), // 2. reset auto increment value
                PHPUnit_Extensions_Database_Operation_Factory::INSERT() // 3. insert new records
        ));
    }
}

Worked for me with MySQL 5.5.24 and PHPUnit 3.6.10

Demmer

Answer 3

\DB::statement('SET FOREIGN_KEY_CHECKS=0;');

Pieper