'"There is no active transaction" when refreshing database in Laravel 8.0 test

Using php 8.0.2 and Laravel 8.37.0, I am running tests where for every test the database data should be refreshed, since there is conflicting data per test (due to unique constraints).
using the in-memory database with SQLite, this works, but when I switch to MySQL (v8.0.23) I get the next error:

1) Tests\Feature\Controllers\AuthControllerTest::testSuccessLogin
PDOException: There is no active transaction

and the tests after this one fail due to data already inserted and not cleared after the test.

The the test that I am trying to do is:

<?php

namespace Tests\Feature\Controllers;

use App\Models\User;
use App\Models\User\Company;
use App\Repositories\UserRepository;
use Illuminate\Foundation\Testing\RefreshDatabase;
use Tests\TestCase;

class AuthControllerTest extends TestCase
{
    use RefreshDatabase;

    protected array $connectionsToTransact = ['mysql'];

    public function testSuccessLogin(): void
    {
        $this->artisan('migrate-data');

        /** @var User $user */
        $user = User::factory()->create([
            'email' => '[email protected]'
        ]);

        $this->app->bind(UserRepository::class, function() use ($user) {
            return new UserRepository($user, new Company());
        });

        $loginResponse = $this->post('/api/login', [
            'email' => '[email protected]',
            'password' => 'password'
        ]);

        $loginResponse->assertStatus(200);
        $loginResponse->assertJsonStructure([
            'data' => [
                'user' => [
                    'name',
                    'surname',
                    'email',
                    'abilities'
                ],
                'token',
            ]
       ]);
    }
}

and after executing this test and checking in the database, the data still exists. With and without the line protected array $connectionsToTransact = ['mysql']; gives me the same result.

My phpunit.xml-file look like:

<?xml version="1.0" encoding="UTF-8"?>
<phpunit xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xsi:noNamespaceSchemaLocation="./vendor/phpunit/phpunit/phpunit.xsd"
     bootstrap="vendor/autoload.php"
     colors="true"
>
    <testsuites>
        <testsuite name="Unit">
            <directory suffix="Test.php">./tests/Unit</directory>
        </testsuite>
        <testsuite name="Feature">
            <directory suffix="Test.php">./tests/Feature</directory>
        </testsuite>
    </testsuites>
    <coverage processUncoveredFiles="true">
        <include>
            <directory suffix=".php">./app</directory>
        </include>
        <report>
            <html outputDirectory="reports/coverage"/>
        </report>
    </coverage>
    <php>
        <server name="APP_ENV" value="testing"/>
        <server name="BCRYPT_ROUNDS" value="4"/>
        <server name="CACHE_DRIVER" value="array"/>
        <server name="DB_CONNECTION" value="mysql"/>
        <server name="DB_HOST" value="localhost"/>
        <server name="DB_DATABASE" value="mysql_test"/>
        <server name="DB_USERNAME" value="root"/>
        <server name="MAIL_MAILER" value="array"/>
        <server name="QUEUE_CONNECTION" value="sync"/>
        <server name="SESSION_DRIVER" value="array"/>
        <server name="TELESCOPE_ENABLED" value="false"/>
    </php>
</phpunit>

Is this a known issue? Or am I missing someting?

phpmysqllaravelphpunit


Solution 1:[1]

The issue is that your test contains implicit commits and so ends the active transaction.

Re -

"As soon as I do a CREATE statement I get the transaction error." -

CREATE TABLE etc are statements that cause an implicit commit.

This in turn means the RefreshDatabase trait will not work because a rollback is not possible when the transaction is closed.

Hence PDOException: There is no active transaction

It seems to be a known issue/thrown error with php 8.0 - https://github.com/laravel/framework/issues/35380

Solution 2:[2]

This is how I solved it:

Step 1: Extend Connection class and override transaction method:

use Illuminate\Database\Connection as DBConnection;
use Closure;

class Connection extends DBConnection
{

    public function transaction(Closure $callback, $attempts = 1)
    {
        $callback($this);
    }
}

This override basically disables transactions internally, keeping your current code intact.

Step 2: Bind that concrete class to the abstract ConnectionInterface via a ServiceProvider (app/Providers/AppServiceProvider.php would be a good place):

if(config('app.env')==='testing') {
   $this->app->bind(ConnectionInterface::class, Connection::class);
}

Notice that the binding is inside a condition against the environment. We want to apply this binding only when running tests.

If you're not using a .env.testing file for Unit Testing, I really recommend you to do it. It keeps everything much more cleaner. You can just copy .env to .env.testing and only update the DB_CONNECTION and DB_DATABASE constants, pointing to your test database.

Then define the .env.testing environment in phpinit.xml:

<php>
   <env name="APP_ENV" value="testing"/>
   <!-- <env name="DB_CONNECTION" value="memory_testing"/> -->
   <!-- <env name="DB_DATABASE" value=":memory:"/> -->
</php>

Notice that I commented out DB_CONNECTION AND DB_DATABASE since those parameters will be fetched from .env.testing

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 John Smith

Related Questions