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

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)

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

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.

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.

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');
}

@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

@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.

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.

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.

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

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() { // … });