Organizational Research By

Surprising Reserch Topic

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();
            $connection->connect();
            $connected = true;
        } catch (RuntimeException $e) {
            sleep(2);
            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());
    }

    $connection->disconnect();
endforeach;


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
25 views



Related Hot Questions



Government Jobs Opening


...