Framework: Database Transactions in tests do not roll back, for non-default database connection

[tomturton]

If I have two MySQL databases setup up, and I run tests that affect the non-default database, the DatabaseTransactions trait seems to have no effect.

I double-checked this by starting a new Laravel 5.1.11 project and created a Route::resource with just @show, @edit and @update controller methods. I created two databases, configured my project with the db credentials and added a posts table manually to the non-default database. I also created a 'Post' model that specifies the non-default database as its connection.

When using this site, it works as expected. When running an automated test with the DatabaseTransactions trait, the database change will stick.

use Illuminate\Foundation\Testing\DatabaseTransactions; was included at the top of the test, and use DatabaseTransactions; was included as the first line inside the test class.

[GrahamCampbell]

Not really a bug I'm afraid. You need to start transactions on other connections yourself I'm afraid.

[tomturton]

Not sure what you're trying to suggest. Perhaps you could give an example?

I need to be able to use database transactions when integration testing. I'm unable to test a large proportion of my application because I can only run transactions on the default database. I don't see why Laravel would deliberately not let me run database transitions on more than one database.

In my case, I have multiple websites (Laravel instances) that use a common user account database.

[kmuenkel]

I'm a little new to this myself, so take this with a grain of salt. But after looking at the content of that trait, it looks like if you add a property to your test class called "connectionsToTransact", and assign it to an array of connection names, the trait will handle transactions on whatever databases you like.

Edit: It works. Also, why does this site let you add reactions to your own post? I didn't mean to do that.

[fongx]

I have the same issue and google here, I write down my solution and hope helpful:
If you have multiple databases, for the transaction on the non-default database,, you need to specify the
connection like this:
DB::connection("your_connection_name")->transaction(function() {
// ...
});

[tomschlick]

FYI, fixing this is as easy as adding a $connectionsToTransact property to your TestCase.php (or whatever base class you want in your tests)...

(shared/tenant are both database connection names in my config)

[Enethion]

I had the same problem today. If anyone will have similar problem and happens to use MySQL, check engine used by tables having problem with transactions. If they're using MyISAM, changing it (and default if possible) to InnoDB might help.

[GTCrais]

Similar problem today (Laravel 5.5). Solved it this way:

try {
    \DB::setDefaultConnection('non_default_connection');
    \DB::beginTransaction();

    // your database interaction here

    \DB::commit();
    \DB::setDefaultConnection('default_connection');
} catch (\Exception $e) {
    \DB::rollBack();
    \DB::setDefaultConnection('default_connection');
}

[HirtLocker128]

@GTCrais I'm looking to do this except with both my default and a non default connection. I want both queries to execute successfully or neither. How can I do this using this method?

[GTCrais]

You can't roll queries for different connections in the same transaction.

[HirtLocker128]

@GTCrais Bummer... Thanks!

[denitsa-md]

I tried following @tomschlick's solution but for some reason no tests are running with the standard mysql connections and the variable

protected $connectionsToTransact = ['main', 'tenant'];

I can see the migrations are run though.

On the other hand, removing 'main' and leaving it as protected $connectionsToTransact = ['tenant']; allows tests to run...but data is not cleared on the 'main' database connection.

Also, if I try running all my tests (147 in total), I start getting the error Too many connections after some point (after 88 tests). I assume I'm getting all those connections because my 'main' database is not in the $connectionsToTransact, so it doesn't end up in this line

https://github.com/laravel/framework/blob/f97e57bcb4269811f499a742883dc295fe1cce34/src/Illuminate/Foundation/Testing/RefreshDatabase.php#L81

However, adding it to $connectionsToTransact causes the tests to stop running...viscous circle.

Tests seem to be running okay with an sqlite database.

Has anyone experienced this or might have an idea on how to make it work?

Did a lot of dump()s around the core. The only thing that struck me as odd is that the DatabaseManager->connection() function, called in RefreshDatabase->beginDatabaseTransaction() (the loop looping connectionsToTransact ) is called multiple times during a single test (like 40-50)..not sure if that is normal. I guess it is and I just don't understand why it does it.

[dericlima]

@denitsa-cm I had a similar problem running tests with Laravel and Postgres, also getting the Too many connections message.
My solution was to set the PDO to use persistent connections:

'options' => [ PDO::ATTR_PERSISTENT => env('DB_PERSISTENT', true) ]

Just to clarify this was used only to run tests in Codeship CI, not in production.

[denitsa-md]

@dericlima Thanks for the tip. Will try it :)

[hshahdoost]

@denitsa-cm keep in mind that transactions are not supported by default in MySam engine, so you should make sure you are using InnoDb.

https://dev.mysql.com/doc/refman/5.6/en/myisam-storage-engine.html

[agelios765]

@dericlima Its help with too many connections, but now its PDOException: There is already an active transaction

[Soulriser]

I was struggling with this issue for a while (Laravel 5.8). I tried many variations on the provided traits, setting connections, initiating transactions (statically or on the connection object) and nothing was working.

I then found that calls to DB::statement() and DB::unprepared() were canceling my transactions. Essentially, I had in setUp():

if ($this->testDatabase == 'mysql') {
    DB::setDefaultConnection('mysql_tests');
    DB::beginTransaction();

    $this->beforeApplicationDestroyed(function () {
        DB::rollBack();
    });

    DB::statement('some SQL prep');
    DB::unprepared('a long SQL string');
}

Once I placed the call to beginTransaction() _after_ the statement/unprepared code, then transactions worked.

My guess is that statement() and unprepared() call some lower level code that reinitializes the connection, thereby losing the transaction session. But I don't really know, I just hope this discovery helps some folks.

[jurekhernandez]

Solution " $table->engine = 'InnoDB';"

public function up()
    {
        Schema::create('password_resets', function (Blueprint $table) {
            $table->string('email')->index();
            $table->string('token')->index();
            $table->timestamp('created_at')->nullable();
            $table->engine = 'InnoDB';
        });
    }