Trait RefreshDatabase does not always refresh the database between tests

I have a few hundred tests in my project. My test environment is simply a different MySQL schema in the homestead database.

   <php>
        <env name="APP_ENV" value="testing"/>
        <env name="BCRYPT_ROUNDS" value="4"/>
        <env name="CACHE_DRIVER" value="array"/>
        <env name="SESSION_DRIVER" value="array"/>
        <env name="QUEUE_DRIVER" value="sync"/>
        <env name="MAIL_DRIVER" value="array"/>
        <env name="DB_DATABASE" value="homestead_test"/>
    </php>

The trait RefreshDatabase is touted to make sure that each tests do not interfere with one an other, however in roughly 30% of the cases, the database is not completely rollbacked between tests, as the following test throws an error (30% of the times it is ran):

    /**
     * Checks that the seenIndicators() function does not returns the seen indicators of a task sharing the same ID as this VEM.
     * @group seenIndicators
     */
    public function testSeenIndicators_withTaskWithSameId()
    {
        $task = factory(Task::class)->create(['id' => $this->vem->id]);
        factory(SeenIndicator::class, 3)->create(['checkable_type' => Task::class, 'checkable_id' => $task->id]);

        $this->assertEquals($this->vem->seenIndicators->count(), $this->seenIndicators->count());
    }

With an error that there is a duplicate primary key (3/4/5) for the tasks table. This error demonstrates that the RefreshDatabase does not always completely empty the database of data between each tests when using a database that is not in-memory.

Is there a way to sidestep this issue? Note that for me using an in-memory sqlite database could cause issues since some of my policies use a MySQL function to delve into JSON data.

bobbybouwmann

7 years ago

Level 88

So RefreshDatabase doesn't refresh the full database on each test. It will refresh the database on the first run and after that it will use database transactions. These transactions are started and cleared during each test, so your database should be empty if you don't seed anything!

Might be interesting for you to watch this video about the trait: https://laracasts.com/series/whats-new-in-laravel-5-5/episodes/14

Barracuda

7 years ago

Level 1

I have watched that one already. Thing is, if it was things that were being seeded, the error would be consistent (100% of the executions), however not all the executions have this problem. Most don't have duplicate key errors. So I suspect that the tests are sometimes executed before the previous transaction is completely rollbacked.

bobbybouwmann

7 years ago

Level 88

@Barracuda So the problem I see in your code is that you generate the ID of the items as well. You should let your database handle the creation of the id field by using auto increment. You don't really care what ID it has right?

So this

$task = factory(Task::class)->create(['id' => $this->vem->id]);

Should just be this

$task = factory(Task::class)->create();

It doesn't make sense to give the task the same id as another resource right?

tobz.nz

7 years ago

Level 3

I know its an old thread, but just a comment on this(as I only just figured it out myself) - It could be to do with the fact that auto-increment values are not reset when a transaction is rolled back.

So even though a test has rolled back all the data inserted, the auto-increment value is still going up between each insert / test.

So, you either have to hard code your inserted/factory ID's or use variables in your assertions.

... or manually reset the auto-increment to 1 between tests?

5 likes

nezarfadle

7 years ago

Level 2

Thanks @tobz.nz,

For those who still can not figure it out, you can do something similar to this in your TestCase.php file:

public function tearDown()
    {
      
        $sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'YOUR_DATABASE_NAME';";

        DB::statement("SET FOREIGN_KEY_CHECKS = 0;");
        $tables = DB::select($sql);

        array_walk($tables, function($table){
            if ($table->TABLE_NAME != 'migrations') {
                DB::table($table->TABLE_NAME)->truncate();
            }
        });
        
        DB::statement("SET FOREIGN_KEY_CHECKS = 1;");
        parent::tearDown();
    }

1 like

Please or to participate in this conversation.