zf2 pdo error fatal sorry too many clients already when making successive co

zf2 pdo error fatal sorry too many clients already when making successive co  using -'php,postgresql,zend-framework,pdo,zend-framework2'

I'm porting a class from a non-frameworked php library to Zend Framework 2. This class has methods to run an input query on multiple databases and multiple servers. We have 5 servers, each with anywhere from 5 to over 100 PostgreSQL databases. It works fine with low level postgres calls, but using \Zend\Db\Adapter\Adapter seems to be causing problems because connections aren't being released. Here's the code:

foreach($params['databases'] as $database):
    $db_conn['database'] = trim($database);

    $dsn = "pgsql:dbname=" . $db_conn['database'] .
        ";host=" . $db_conn['hostname'] .
        ";port=" . $db_conn['port'];
    $db = array(
            'driver'         => 'Pdo',
            'dsn'            => $dsn,
            'username'       => $db_conn['username'],
            'password'       => $db_conn['password'],

    $adapter = new \Zend\Db\Adapter\Adapter($db);
    $connection = null;
    $connect_count = 0;
    $connected = false;
    while (!$connected && $connect_count < 5) {
        try {
            $connect_count ++;
            $connection = $adapter->getDriver()->getConnection();
            $connected = true;
        } catch (RuntimeException $e) {
            echo("\n<br>RuntimeException {$connect_count}: {$db_conn['database']} {$e->getCode()} {$e->getMessage()}");
    try {
        if (self::checkTablesExist($args['tables_exist'], $connection)) {
            $results[trim($database)] = @$connection->execute(self::$arguments['query']);    
        } else {
            log::error(__FUNCTION__." Missing tables", $db_conn['hostname']." : ".$db_conn['database']." : ".join(",", $args['tables_exist']));
    } catch (PDOException $e) {
        echo('\n<br>PDOException : ' . $e->getCode() . " " . $e->getMessage());
    } catch (RuntimeException $e) {
        echo("\n<br>RuntimeException : {$db_conn['database']} {$e->getCode()} {$e->getMessage()}");
    } catch (Exception $e) {
        echo('\n<br>Exception : ' . $e->getCode() . " " . $e->getMessage());


Everything works fine until around the 50th database, then I start getting

  "RuntimeException: 7 Connect Error: SQLSTATE[08006] [7] FATAL: sorry, too many clients already"

I added the loop with the sleep() statement, which helped slightly. Now it gets to the point where it throws exceptions and the first few it might only go through 2 or 3 times, but then it goes back to not making any connections for the next several databases.

So it seems like $connection->disconnect() isn't working at all, connections are still not being released, at least not in a timely manner. I could just skip using Zend Framework in that part of the code and it would work, but I wanted to do it "canonically", so it wouldn't just be messy like that.

asked Oct 19, 2015 by pradip.bhoge
0 votes

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
Anti-spam verification:
To avoid this verification in future, please log in or register.