framework: PDO Too many connections when running test suite

What exactly do you mean reused? Connection is closed after each test, and until its closed new one opens up…

I tested this using Innotop on my local machine (installed from homebrew), I have 69 test total, and each test class uses DatabaseMigrations trait and Innotop showed 69 connections. I guess PHPUnit is fast so connections are not closed ASAP.

I found this post about this issue.

public function tearDown()
{
    $this->beforeApplicationDestroyed(function () {
        DB::disconnect();
    });

    parent::tearDown();
}

After that max_connection stats were ±3.

I believe the problem is that the connection should be reused (or a few of them pooled), not that the database has a too low max_connections value.

Adding this option in the testing database solve the problem in Laravel 5.5.2

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

The best way we find to fix this issue was to move the test to sqlite driver:

'testing' => [
     'driver' => 'sqlite',
    'database' => storage_path('testing/phpunit.sqlite'),
    'prefix'   => '',
 ]

and my phpunit.xml

    <php>
        <env name="APP_ENV" value="testing"/>
        <env name="DB_CONNECTION" value="testing"/>
        <env name="CACHE_DRIVER" value="array"/>
        <env name="SESSION_DRIVER" value="array"/>
        <env name="QUEUE_DRIVER" value="sync"/>
    </php>

With this we saved around 90% of time running our test cases.

Its problem with max_connections.

Naive solution is to increase max_connections using (run query)

set global max_connections = 200;

To check current max_connections

show variables like "max_connections";

I tested max_connections = 10 and run 60 tests and it failed with

Caused by
PDOException: SQLSTATE[HY000] [1040] Too many connections

@GuidoHendriks was right, I’ve just replaced:

$database->connection($name)->rollBack();

by:

$connection = $database->connection($name);

$connection->rollBack();
$connection->disconnect();

in the RefreshDatabase trait, and now it works like a charm! I wrote a PR (#22569) to fix that.

I just noticed that both RefreshDatabase and DatabaseTransactions use similar methods called beginDatabaseTransaction. They do exactly the same, except for 1 thing: DatabaseTransactions not only rolls back, but disconnects as well. Where RefreshDatabase only rolls back.

https://github.com/laravel/framework/blob/5.5/src/Illuminate/Foundation/Testing/RefreshDatabase.php https://github.com/laravel/framework/blob/5.5/src/Illuminate/Foundation/Testing/DatabaseTransactions.php

I can’t test it at this moment. But I’m pretty this is the problem. The solution by the issue starter fixes it, and that just adds the missing disconnect.

@jycr753 There is always possibility to use sqlite driver with :memory: database, which is perfect for testing.

'testing' => [
    'driver' => 'sqlite',
    'database' => ':memory:',
    'prefix' => '',
],

But remember to enable constrains checks https://sqlite.org/foreignkeys.html

I had a similar problem not too long ago in Lumen, and solved it with:

    public function tearDown()
    {
        \DB::connection()->setPdo(null);
    }

I’m currently experiencing this problem on 5.5.24.

Adding this to my config solved it:

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

We are using Doctrine via Connection::getDoctrineSchemaManager(), and it seems like the Doctrine connection (and hence the PDO) doesn’t get cleaned up at the end of each of our unit tests, despite going \DB::purge(...). If we do the following instead, it solves this issue for us:

$connection->getDoctrineConnection()->close();
\DB::purge($connectionName ?: null);

Is this perhaps the issue you’re facing as well?

And is it intentional to have to clean up a Doctrine connection like this?

(PHP 7.1, laravel/framework 5.4.27, doctrine/dbal 2.5.12, Postgres 9.5.7)