How to reduce the number of database connections in tests in PHPUnit and ZF3?

Question

I'm writing integration/database tests for a Zend Framework 3 application by using

• zendframework/zend-test 3.1.0,
• phpunit/phpunit 6.2.2, and
• phpunit/dbunit 3.0.0

My tests are failing due to the

Connect Error: SQLSTATE[HY000] [1040] Too many connections

I set some breakpoints and took a look into the database:

SHOW STATUS WHERE `variable_name` = 'Threads_connected';

And I've actually seen over 100 opened connections.

I've reduced them by disconnecting in the tearDown():

protected function tearDown()
{
    parent::tearDown();
    if ($this->dbAdapter && $this->dbAdapter instanceof Adapter) {
        $this->dbAdapter->getDriver()->getConnection()->disconnect();
    }
}

But I still have over 80 opened connections.

How to decrease the number of the database connections in tests to a possible minimum?

---

more info

(1) I have a lot of tests, where I dispatch a URI. Every such request causes at least one database request, that cause a new database connection. These connections seem not to be closed. This might cause the most connections. (But I haven't yet found a way to make the application close the connections after the request is processed.)

(2) One of the issues might be my testing against the database:

protected function retrieveActualData($table, $idColumn, $idValue)
{
    $sql = new Sql($this->dbAdapter);
    $select = $sql->select($table);
    $select->where([$table . '.' . $idColumn . ' = ?' => $idValue]);
    $statement = $sql->prepareStatementForSqlObject($select);
    $result = $statement->execute();
    $data = $result->current();
    return $data;
}

But the call of the $this->dbAdapter->getDriver()->getConnection()->disconnect() before the return gave nothing.

Example of usage in a test method:

public function testInputDataActionSaving()
{
    // The getFormParams(...) returns an array with the needed input.
    $formParams = $this->getFormParams(self::FORM_CREATE_CLUSTER);

    $createWhateverUrl = '/whatever/create';
    $this->dispatch($createWhateverUrl, Request::METHOD_POST, $formParams);

    $this->assertEquals(
        $formParams['whatever']['some_param'],
        $this->retrieveActualData('whatever', 'id', 2)['some_param']
    );
}

(3) Another issue might be in the PHPUnit (or my configuration of it?). (Striken out, because "PHPUnit does not do anything related to database connections.", see this comment.) Anyway, even if it's not a PHPUnit issue, the fact is, that after the line

$testSuite = $configuration->getTestSuiteConfiguration($this->arguments['testsuite'] ?? null);

in the PHPUnit\TextUI\Command I get 31 new connections.

Answer 1

The clean & proper approach

This seems to be an issue if "your code is written in a way that is hard to test". The DB connection should be either handled by DIC or (in case of some connection pool) some specialize class. Basically, the class, that contains retrieveActualData() should have the Sql instance being passed as a dependency in a constructor.

Instead, it looks like your Sql class is a harmful PDO wrapper, that (most likely) established a DB connection whenever you create an instance. Instead you should be sharing same PDO instance among multiple classes. That way you can both control the amount of the connections established and have a way to test you code in (some) isolation.

So, the primary solution is - your code is bad, but you can clean it up.

Instead of having new snippets sprinkled deep in your execution tree, pass the connection as a dependency and share it.

This way you tests can move towards use of various mocks and stubs, that help you isolate the tested structures.

In case of DB bound logic and gremlins

But there is also a more practical aspect, that you should consider. Use SQLite instead of real database in your integration tests. PDO support that option (you just have to provide a different DSN for your test code).

If you switch to using SQLite as your "testing DB", you will be able to have a well defined DB states (multiple) against which you can test your code.

You have something like file integration-002.db, which contains the prepared database state. In the bootstrap of your integration tests, you just copy over that prepared sqlite database-files from integration-0902.db to live-002.db and run all the tests.

use PHPUnit\Framework\TestCase;

final class CombinedTest extends TestCase
{
    public static function setUpBeforeClass()
    {
        copy(FIXTURE_PATH . '/integration-02.db', FIXTURE_PATH . '/live-02.db');
    }


    // your test go here

}

That way you will gain both better control over your persistence state and your tests will run a lot faster, since there is no network stack involved.

You can also prepare any number of test-databases and add new ones, when a new bug is discovered. This approach will let you recreate more complex scenarios in your DB and even simulate data corruption.

You can see this approach in practice in this project.

---

P.S. from personal experience - using SQLite in the integration tests also improves the general quality of ones SQL code (if you are not using query builders, but instead are writing custom data-mappers). Because it forces you to consider the differences between available functionality in SQLite against MariaDB or PostgreSQL. But it's one of those "your mileage may vary" things.

P.P.S. you can utilize both of the suggested approaches as the same time, since they will only enhance each-other.

Answer 2

You have probably configured your PHP/DB to use persistent connections. This is only way those connections remain there after test ends its execution. It's not that bad.

From manual: Persistent connections are links that do not close when the execution of your script ends. When a persistent connection is requested, PHP checks if there's already an identical persistent connection (that remained open from earlier) - and if it exists, it uses it.

Once you have a connection to username@host:port established, did your thing and disconnect (scipt end execution), afterwards connect again with the same username@host:port, no matter the tables being used you will be connected via the same connection socket.

Four possible reasons for your problem

1. because you're running different users to connect to db server
2. because you're delivering table names into connection
3. because you're running multiple tests at once
4. because you're building multiple connections

and the most possible is the 4-rth one, because its tempting to create a frabric function to create db handle every time you need database, that is creating new connection:

function getConnection() {
    // This is an example to test, that it do leave behind a non closed connection. 
    // Skip "p:", to reduce connections left unless you are configured
    // globally for persistency, eg. by mysqlnd.
    //                      p: forced persistency
    $link = mysqli_connect("p:127.0.0.1", "my_user", "my_password", "my_db");

    if (!$link) return false;

    return $link;
}

Case is, that for every call of exampleous method along same thread, there is going to be opened a whole new connection, because you are really asking for this. Persistent sockets are being reused only if their are not used any more (creator script ends its execution previously). (at least it was the way I was learned to use them a few years ago)

To avoid creating too many connections is to rebuild your connection factory to store all distinct connections and deliver those links you want on demand, without calling connection builder over and over again. This way for a particular user to a partticular server you will finally run once eg. mysqli_connect to retrieve a persistent connection from the server, and keep reusing it all to the end of your script execution.

class  db
{

    static $storage = array();

    public static function getConnection($username = 'username') {

        if (!array_key_exists($username, self::$storage) {
            $link = mysqli_connect("p:127.0.0.1", $username, "my_password", "my_db");

            if (!$link) return false;

            self::$storage[$username] = $link;
        }

        return self::$storage[$username];
    }
}

// ---
$a = db::getConnection();
$b = db::getConnection();

// both $a and $b are the same connection, using the same socket on your server
var_dump($a, $b);

Getting back to your delivered examples, it is probably because of a line:

$sql = new Sql($this->dbAdapter);

beeing executed over and over again along your tests, or by the driver itself doing something extraordinary when being reused frequently. My question would be if the driver is not creating new connection every time on getConnection() being run on it, or if the constructor of Sql() not creating a new connection on every call with new Sql.

edit 1 - after a look into zf3 code:

Try to search for if code is not doing something like in persistent example. But as of using ZF3 I would rather guess that your are using some extension like mysqlnd which makes you not use native mysql driver in favor of Streams with their own timeouts.

edit 2 - db test one after another:

Despite socket persistency - you may not use them at all: SQL server needs time to disconnect user entirely and free a socket for new connection. If you are running tests quickly one after another heres a thing that every test is run and destroyed - what may lead to creating new connection every setUp() call or bootsrap file run. By running a load of tests that are instantiating DB service (anything thst will call Adapter/PDO/Conncetion::connect() you may produce a huge queue of connection to-be-closed on bottom of your one-to-be-opened. That would be where configuring for socket persistency should solve your problem.