How to close DB connections and reduce their number in functional PHPUnit tests for a ZF3 application?

Question

_{Some years ago I already created a very similar or even nearly the same question on StackOverflow. I got great detailed answers, but they didn't lead to a solution of my problem. Now, the problem became even bigger and I'm starting the second attempt to solve this here. Since the code has been changed, I don't want to update the original question. It would be a too big update and the answers might perhaps not match to the new version of the question. So I'm formulating it as a new one:}

I'm writing functional tests for a Zend Framework 3 application by using

• zendframework/zend-test 3.2.2,
• phpunit/phpunit 6.5.14, and
• phpunit/dbunit 3.0.3

The most of the tests are a kind of controller tests. The test code calls a URI / an action by using the Zend\Test\PHPUnit\Controller\AbstractControllerTestCase#dispatch(...) and analyzes 1. the response / output data and 2. the changes at the database (if it was a writing call like "create foo"), e.g.:

/**
...
* @dataProvider provideDataForShowOrderAccess
*/
public function testShowOrderAccess(string $username, int $responseStatusCode)
{
    ...
    $this->createOrder(...);
    $this->reset();
    $_SERVER['AUTH_USER'] = $username;
    ...
    $this->dispatch($showUrl);
    $this->assertResponseStatusCode($responseStatusCode);
}

/**
...
* @dataProvider provideDataForShowOrder
*/
public function testShowOrder(string $username, bool $isOwner)
{
    ...
    $this->createOrder($connectionType, $endpointSourceType);
    $this->reset();
    $_SERVER['AUTH_USER'] = $username;

    // testing the access by the owner
    $orderId = 1;
    $showUrl = '/order/show/' . $orderId;
    $this->dispatch($showUrl);

    if ($isOwner) {
        $this->assertResponseStatusCode(Response::STATUS_CODE_200);
        $this->assertModuleName('Order');
        $this->assertControllerName('Order\Controller\Process');
        $this->assertControllerClass('ProcessController');
        $this->assertMatchedRouteName('order/show');

        /** @var Foo $foo */
        $foo = $this->getApplication()->getMvcEvent()->getResult()->getVariable('foo', null);

        $fooData = $createParams['foo'];
        $barData = $barData['bar'];

        $this->assertNotNull($bar);
        $this->assertInstanceOf(Foo::class, $foo);
        $this->assertEquals($orderId, $foo->getId());
        $this->assertEquals($fooData['bar'], $foo->getBar());
        ...
    } else {
        $this->assertResponseStatusCode(Response::STATUS_CODE_302);
    }
}

For every single test the database gets reset.

The problem is, that the number of database connections is growing and growing and growing -- with every next test. Currently there are about 350 (SHOW GLOBAL STATUS LIKE 'max_used_connections';) connections for 102 tests. (As a workaround I have to increase the MySQL's max_connections more and more.)

I tried to decrease the number of connections by putting logic like $this->dbAdapter->getDriver()->getConnection()->disconnect(); or/and $this->entityManager->getConnection()->close(); to the tearDown() of my super-class for controller tests. This way I got the number of the connections reduced by about 90. But the most of the connections still don't get killed.

How to close DB connections and significantly reduce the number of concurrently open connections in functional / controller PHPUnit tests for a ZF3 application?

---

Additional information: the most relevant parts of my code

AbstractControllerTest

namespace Base\Test;

use Doctrine\ORM\EntityManager;
use PDO;
use PHPUnit\DbUnit\Database\DefaultConnection;
use Zend\Db\Adapter\Adapter;
use Zend\Db\Sql\Sql;
use Zend\Test\PHPUnit\Controller\AbstractHttpControllerTestCase;

/**
 * Class AbstractControllerTest
 *
 * @package Base\Test
 */
abstract class AbstractControllerTest extends AbstractHttpControllerTestCase
{

    use DatabaseConnectionTrait;

    /**
     * @var string
     */
    static private $applicationConfigPath;

    /** @var Adapter */
    protected $dbAdapter;

    /** @var EntityManager */
    protected $entityManager;

    public function __construct($name = null, array $data = [], $dataName = '')
    {
        parent::__construct($name, $data, $dataName);
        $this->setApplicationConfig(include self::$applicationConfigPath);
    }

    public static function setApplicationConfigPath(string $applicationConfigPath)
    {
        self::$applicationConfigPath = $applicationConfigPath;
    }

    protected function tearDown()
    {
        // Connections: 354
        // Time: 5.7 minutes, Memory: 622.00MB
        // OK (102 tests, 367 assertions)
        // no optimization

        // Connections: 326 (26 connections less)
        // Time: 5.86 minutes, Memory: 620.00MB
        // OK (102 tests, 367 assertions)
        // if ($this->dbAdapter && $this->dbAdapter instanceof Adapter) {
        //     $this->dbAdapter->getDriver()->getConnection()->disconnect();
        // }

        // Connections: 354
        // Time: 5.67 minutes, Memory: 620.00MB
        // OK (102 tests, 367 assertions)
        // $this->entityManager->close();

        // Connections: 291 (63 connections less)
        // Time: 5.63 minutes, Memory: 622.00MB
        // OK (102 tests, 367 assertions)
        // $this->entityManager->getConnection()->close();

        // Connections: 264 (90 connections less)
        // Time: 5.7 minutes, Memory: 620.00MB
        // OK (102 tests, 367 assertions)
        // if ($this->dbAdapter && $this->dbAdapter instanceof Adapter) {
        //     $this->dbAdapter->getDriver()->getConnection()->disconnect();
        // }
        // $this->entityManager->getConnection()->close();

        // Connections: 251
        // Time: 4.71 minutes, Memory: 574.00MB
        // OK (102 tests, 367 assertions)
        // After removing initialization of the EntityManager and the DbAdapter in the constructor and the setUp().

        // closing DB connections
        if ($this->dbAdapter && $this->dbAdapter instanceof Adapter) {
            $this->dbAdapter->getDriver()->getConnection()->disconnect();
        }
        if ($this->entityManager && $this->entityManager instanceof EntityManager) {
            $this->entityManager->getConnection()->close();
        }
        $reflectionObject = new \ReflectionObject($this);
        foreach ($reflectionObject->getProperties() as $prop) {
            if (!$prop->isStatic() && 0 !== strpos($prop->getDeclaringClass()->getName(), 'PHPUnit_')) {
                $prop->setAccessible(true);
                $prop->setValue($this, null);
            }
        }

        $this->reset();
        $this->application = null;
        gc_collect_cycles();

        unset($_SERVER['AUTH_USER']);

        parent::tearDown();
    }

    protected function retrieveActualData($table, $idColumn, $idValue)
    {
        $sql = new Sql($this->getDbAdapter());
        $select = $sql->select($table);
        $select->where([$table . '.' . $idColumn . ' = ?' => $idValue]);
        $statement = $sql->prepareStatementForSqlObject($select);
        $result = $statement->execute();
        $data = $result->current();
        // Decreases the total number of the connections by 1 less.
        // $this->dbAdapter->getDriver()->getConnection()->disconnect();
        return $data;
    }

    protected function getEntityManager()
    {
        $this->entityManager = $this->entityManager
            ?: $this->getApplicationServiceLocator()->get('doctrine.entitymanager.orm_default')
        ;
        return $this->entityManager;
    }

    protected function getDbAdapter()
    {
        $this->dbAdapter = $this->dbAdapter
            ?: $this->getApplicationServiceLocator()->get('Zend\Db\Adapter\Adapter')
        ;
        return $this->dbAdapter;
    }

}

DatabaseConnectionTrait

namespace Base\Test;

use PDO;
use PHPUnit\DbUnit\Database\Connection;
use PHPUnit\DbUnit\Database\DefaultConnection;
use PHPUnit\DbUnit\InvalidArgumentException;

trait DatabaseConnectionTrait
{

    /**
     * @var array
     */
    static private $dbConfigs;
    /**
     * @var PDO
     */
    static private $pdo;
    /**
     * @var Connection
     */
    private $connection;

    public function __construct($name = null, array $data = [], $dataName = '')
    {
        parent::__construct($name, $data, $dataName);
    }

    /**
     * @return Connection
     */
    public function getConnection()
    {
        if (! $this->connection) {
            if (! self::$dbConfigs) {
                throw new InvalidArgumentException(
                    'Set the database configuration first.'
                    . ' '. 'Use the ' . self::class . '::setDbConfigs(...).'
                );
            }
            if (! self::$pdo) {
                self::$pdo = new PDO(
                    self::$dbConfigs['dsn'],
                    self::$dbConfigs['username'],
                    self::$dbConfigs['password'],
                    [PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\'']
                );
            }
            $this->connection = $this->createDefaultDBConnection(self::$pdo, self::$dbConfigs['database']);
        }
        return $this->connection;
    }

    public static function setDbConfigs(array $dbConfigs)
    {
        self::$dbConfigs = $dbConfigs;
    }

    /**
     * Creates a new DefaultDatabaseConnection using the given PDO connection
     * and database schema name.
     *
     * @see The original PHPUnit\DbUnit\TestCaseTrait#createDefaultDBConnection(...).
     *
     * @param PDO    $connection
     * @param string $schema
     *
     * @return DefaultConnection
     */
    protected function createDefaultDBConnection(PDO $connection, $schema = '')
    {
        return new DefaultConnection($connection, $schema);
    }

}

DatabaseInitializer

namespace Base\Test;

/**
 * Class DatabaseInitializer
 *
 * @package Base\Test
 */
class DatabaseInitializer
{

    use DatabaseConnectionTrait;

    /**
     * @var string
     */
    private $database;

    public function __construct(array $dbConfigs)
    {
        $this->database = $dbConfigs['database'];
        self::$dbConfigs = $dbConfigs;
    }

    public function setUp()
    {
        $schemaSql = file_get_contents(self::$dbConfigs['scripts']['schema']);
        $storedProceduresSql = file_get_contents(self::$dbConfigs['scripts']['stored-procedures']);
        $basicDataSql = file_get_contents(self::$dbConfigs['scripts']['basic-data']);
        $testDataSqlSet = array_map(function ($sqlFile) {
            return file_get_contents($sqlFile);
        }, self::$dbConfigs['scripts']['test-data']);

        $this->dropDatabase();
        $this->createDatabase();
        $this->useDatabase();
        $this->createSchema($schemaSql);
        $this->createStoredProcedures($storedProceduresSql);
        $this->createBasicData($basicDataSql);
        $this->createTestData($testDataSqlSet);
    }

    public function tearDown()
    {
        self::$pdo = null;
    }

    protected function createDatabase()
    {
        $this->getDatabaseConnection()->exec('CREATE DATABASE IF NOT EXISTS ' . $this->database . ';');
    }

    protected function useDatabase()
    {
        $this->getDatabaseConnection()->exec('USE ' . $this->database . ';');
    }

    protected function createSchema(string $sql)
    {
        $this->getDatabaseConnection()->exec($sql);
    }

    protected function createBasicData(string $sql)
    {
        $this->getDatabaseConnection()->exec($sql);
    }

    protected function createTestData(array $sqlSet = [])
    {
        foreach ($sqlSet as $sql) {
            $this->getDatabaseConnection()->exec($sql);
        }
    }

    protected function createStoredProcedures(string $sql)
    {
        $statement = $this->getDatabaseConnection()->prepare($sql);
        $statement->execute();
    }

    protected function dropDatabase()
    {
        $this->getDatabaseConnection()->exec('DROP DATABASE IF EXISTS ' . $this->database . ';');
    }

    protected function getDatabaseConnection()
    {
        return $this->getConnection()->getConnection();
    }
}

Bootstrap

namespace Test;

use Base\Test\AbstractControllerTest;
use Base\Test\AbstractDbTest;
use Base\Test\DatabaseInitializer;
use Doctrine\ORM\EntityManager;
use RuntimeException;
use Zend\Loader\AutoloaderFactory;
use Zend\Mvc\Service\ServiceManagerConfig;
use Zend\ServiceManager\ServiceManager;

error_reporting(E_ALL | E_STRICT);
ini_set('memory_limit', '2048M');
chdir(__DIR__);

/**
 * Sets up the MVC (application, service manager, autoloading) and the database.
 */
class Bootstrap
{

    /** @var ServiceManager */
    protected $serviceManager;

    protected $applicationConfigPath;

    /** @var EntityManager */
    protected $entityManager;

    public function __construct()
    {
        $this->applicationConfigPath = __DIR__ . '/../config/application.config.php';
    }

    /**
     * Sets up the
     */
    public function init()
    {
        // autoloading setup
        static::initAutoloader();
        // application configuration & setup
        $applicationConfig = require_once $this->applicationConfigPath;
        $this->prepareApplication($applicationConfig);
        // database configuration & setup
        $dbConfigs = $this->serviceManager->get('Config')['db'];
        $this->setUpDatabase($dbConfigs);
        // listeners & application bootstrap
        $listeners = $this->prepareListeners();
        $this->bootstrapApplication($listeners);
    }

    public function chroot()
    {
        $rootPath = dirname(static::findParentPath('module'));
        chdir($rootPath);
    }

    protected function prepareApplication($config)
    {
        $serviceManagerConfig = isset($config['service_manager']) ? $config['service_manager'] : [];
        $serviceManagerConfigObject = new ServiceManagerConfig($serviceManagerConfig);
        $this->serviceManager = new ServiceManager();
        $serviceManagerConfigObject->configureServiceManager($this->serviceManager);
        $this->serviceManager->setService('ApplicationConfig', $config);
        $this->serviceManager->get('ModuleManager')->loadModules();
    }

    protected function prepareListeners()
    {
        $listenersFromAppConfig     = [];
        $config                     = $this->serviceManager->get('config');
        $listenersFromConfigService = isset($config['listeners']) ? $config['listeners'] : [];
        $listeners = array_unique(array_merge($listenersFromConfigService, $listenersFromAppConfig));
        return $listeners;
    }

    protected function bootstrapApplication($listeners)
    {
        $application = $this->serviceManager->get('Application');
        $application->bootstrap($listeners);
    }

    protected function setUpDatabase(array $dbConfigs)
    {
        $databaseInitializer = new DatabaseInitializer($dbConfigs);
        $databaseInitializer->setUp();
        AbstractDbTest::setDbConfigs($dbConfigs);
        AbstractControllerTest::setApplicationConfigPath($this->applicationConfigPath);
        AbstractControllerTest::setDbConfigs($dbConfigs);
    }

    protected function initAutoloader()
    {
        $vendorPath = static::findParentPath('vendor');

        if (file_exists($vendorPath.'/autoload.php')) {
            include $vendorPath.'/autoload.php';
        }

        if (! class_exists('Zend\Loader\AutoloaderFactory')) {
            throw new RuntimeException(
                'Unable to load ZF2. Run `php composer.phar install`'
            );
        }

        AutoloaderFactory::factory(array(
            'Zend\Loader\StandardAutoloader' => array(
                'autoregister_zf' => true,
                'namespaces' => array(
                    __NAMESPACE__ => __DIR__,
                ),
            ),
        ));
    }

    protected function findParentPath($path)
    {
        $dir = __DIR__;
        $previousDir = '.';
        while (!is_dir($dir . '/' . $path)) {
            $dir = dirname($dir);
            if ($previousDir === $dir) {
                return false;
            }
            $previousDir = $dir;
        }
        return $dir . '/' . $path;
    }

}

$bootstrap = new Bootstrap();
$bootstrap->init();
$bootstrap->chroot();

Answer 1

Since we don't see your controller, your repositories etc. we can't say you "there' mistake". Normally, you should make your connection for database test on setUp method, and destroy it on tearDown method. And you shouldn't start connection for application. Application should start that connection when it needs. Since you're using Doctrine, it does that. Why you're creating/calling EntityManager before tests?

What happens if you run only one test instead whole testcase? How many connections happens? Maybe you're making a mistake in a controller? In a repository maybe?

Here what i see when i look that code;

• I couldn't see where initializer is using. Where do you using it? I didn't like that methods in it.
• Your both DatabaseConnectionTrait and AbstractControllerTest classes have _construct method. If a class overwrites a method which is defined in trait, class' method works. Why trait has that method?
• Are you sure you're not doing same thing over and over again?getDatabaseConnection, getConnection, $this->getConnection()->getConnection();
• Where you define static $em property?
• unset that reflection object when you done with it. I know it's easy and faster way to desctruct all properties, but you should consider to do it yourself. It'll be better way to manage your ram.
• Why you trying to destruct all properties of class in tearDown method with reflection? Destroy connection and let php manage garbage.
• Why you destroy database on tearDown method? If you provide your test-data to phpunit correctly, you don't need that. Check tearDownOperation of DbUnit.

I suggest you run that tests one by one instead whole test-case and check connection counts. Also you should test your Repositories with DbUnit to be sure they're working right. Since Repositories responsible on database, maybe that connection count increasing on them.