[Parallel testing] PDOException: There is already an active transaction

[ChoKaPeek]

• Laravel Version: 8.33.1
• PHP Version: 8.0.3
• Database Driver & Version: MySQL 5.7.32

Description:

Greetings, we want to test multiple database interactions, so we need isolated tests and a way to create / migrate / seed replicas of our mysql database (disk storage). We are using Sanctum for authentication.

I've been trying to use the new parallel testing functionality and read your different docs and issues about it.

My conclusions are:

• the DatabaseTransactions trait is needed, the others are "replaced" in whole or in part by ParallelTesting
• the new ParallelTesting::setUpTestDatabase() is exactly what I need to seed the databases at creation
• no test db definition required as ParallelTesting will already create copies of the form name_test_1, name_test_2
• without --recreate-databases, seeding won't be called again as it is only called after its test db is created

My seeds are a bunch of factories, DB::unprepared.

Running art migrate:fresh and art db:seed manually then art test without --parallel, tests work fine. However when running with --parallel --recreate-databases, the seeding does not work and I stumble upon this error:

PDOException: There is already an active transaction

EE                                                                  2 / 2 (100%)

Time: 00:14.187, Memory: 20.00 MB

There were 2 errors:

1) Tests\Feature\AuthTest::testAuthLogin
PDOException: There is already an active transaction

[root]/vendor/laravel/framework/src/Illuminate/Database/Concerns/ManagesTransactions.php:137
[root]/vendor/laravel/framework/src/Illuminate/Database/Concerns/ManagesTransactions.php:113
[root]/vendor/laravel/framework/src/Illuminate/Foundation/Testing/DatabaseTransactions.php:21
[root]/vendor/laravel/framework/src/Illuminate/Foundation/Testing/TestCase.php:129
[root]/vendor/laravel/framework/src/Illuminate/Foundation/Testing/TestCase.php:90

2) Tests\Feature\AuthTest::testAuthGetUser
Illuminate\Database\QueryException: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'company_id' cannot be null (SQL: insert into `users` (`company_id`, [cut]))

[root]/vendor/laravel/framework/src/Illuminate/Database/Connection.php:678
[root]/vendor/laravel/framework/src/Illuminate/Database/Connection.php:638
[root]/vendor/laravel/framework/src/Illuminate/Database/Connection.php:472
[root]/vendor/laravel/framework/src/Illuminate/Database/Connection.php:424
[root]/vendor/laravel/framework/src/Illuminate/Database/Query/Processors/Processor.php:32
[root]/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php:2882
[root]/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php:1547
[root]/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Model.php:1052
[root]/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Model.php:1017
[root]/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Model.php:858
[root]/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Factories/Factory.php:275
[root]/vendor/laravel/framework/src/Illuminate/Collections/Traits/EnumeratesValues.php:234
[root]/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Factories/Factory.php:278
[root]/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Factories/Factory.php:236
[root]/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Factories/Factory.php:230
[root]/tests/Feature/AuthTest.php:73

FAILURES!
Tests: 2, Assertions: 0, Errors: 2.

Providers/AppServiceProvider.php

<?php

namespace App\Providers;

use Illuminate\Support\Facades\Artisan;
use Illuminate\Support\Facades\ParallelTesting;
use Illuminate\Support\ServiceProvider;

class AppServiceProvider extends ServiceProvider
{
    /**
     * Bootstrap any application services.
     *
     * @return void
     */
    public function boot()
    {
        // Executed when a test database is created...
        ParallelTesting::setUpTestDatabase(function () {
            Artisan::call('db:seed');
        });
    }
}

tests/CreatesApplication.php

<?php

namespace Tests;

use Illuminate\Contracts\Console\Kernel;

trait CreatesApplication
{
    /**
     * Creates the application.
     *
     * @return \Illuminate\Foundation\Application
     */
    public function createApplication()
    {
        $app = require __DIR__.'/../bootstrap/app.php';

        $app->make(Kernel::class)->bootstrap();

        return $app;
    }
}

tests/TestCase.php

<?php

namespace Tests;

use Illuminate\Foundation\Testing\DatabaseTransactions;
use Illuminate\Foundation\Testing\TestCase as BaseTestCase;

abstract class TestCase extends BaseTestCase
{
    use DatabaseTransactions;
    use CreatesApplication;
}

migrations/*.php

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

class ImportDbTables extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        DB::unprepared(file_get_contents(base_path().'/database/dump.sql'));
    }
}

phpunit.xml

<?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="Feature">
            <directory suffix="Test.php">./tests/Feature</directory>
        </testsuite>
    </testsuites>
    <coverage processUncoveredFiles="true">
        <include>
            <directory suffix=".php">./app</directory>
        </include>
    </coverage>
    <php>
        <server name="APP_ENV" value="testing"/>
        <server name="BCRYPT_ROUNDS" value="4"/>
        <server name="CACHE_DRIVER" value="array"/>
        <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>

Example test

<?php

namespace Tests\Feature;

use App\Models\User;
use Laravel\Sanctum\Sanctum;
use Tests\TestCase;

class AuthTest extends TestCase
{
    public function testAuthLogin()
    {
        $user = User::factory()->create([
            'email' => 'test@login.com',
            'password' => 'test_1',
        ]);

        $response = $this->postJson('/auth/login', [
            'email' => 'test@login.com',
            'password' => 'test_1',
            'device_name' => 'test_1'
        ]);

        $response->assertOk()->assertJsonFragment([
            'status' => 'Success',
            'message' => null
        ]);
        $this->assertArrayHasKey('data', $response);
        $this->assertArrayHasKey('token', $response['data']);
    }
}

Any help would be greatly appreciated!

[driesvints]

Hey there,

Can you first please try one of the support channels below? If you can actually identify this as a bug, feel free to report back and I'll gladly help you out and re-open this issue.

• Laracasts Forums
• Laravel.io Forums
• StackOverflow
• Discord
• Larachat
• IRC

Thanks!

[jszoja]

We are experiencing similar problem in our pipelines.
Running multiple parallel tests, which are using transactions often causes PDO exception.

I've debugged it havely and have suspicion that the cause is shared db session when running transactions from command line.
Parallel tests create separate processes per file, so if one file is smaller than the other the process is finishing when the other process is in transaction mode at the time.

PHP process, when closing clears all open connections by default and it looks like it clears transactions as well.
I still cannot create a single code to have it replicated all the time, but for our app almost every attempt ends with PDO exception for inactive transaction.

Ref: stackoverflow