CODEWITHSUNDEEP
phpsymfonydoctrine-orm
user3551918
user3551918

Reputation: 13

Symfony2&Doctrine SQLSTATE[HY000] [1040] Too many connections

The parent command below calls another child command asynchronously.

Parent Command

foreach ( $values as $value ) {
    // asynchronous call
    shell_exec(sprintf('php app/console {child command} %d > /dev/null &', $value));
}

Child Command

$em = $this->getContainer()->get('doctrine')->getEntityManager();
// some database action

When executing the parent command, the error below has occurred.

[PDOException]
SQLSTATE[HY000] [1040] Too many connections

I think there is the problem about the close of database connections.

So I added 

$em->getConnection()->close();

in the last of the child command, but the result has not changed.

Upvotes: 1

Views: 1508

Answers (1)

ek9
ek9

Reputation: 3442

the close() did not help because you are sending processes to backgruond (because of & at the end of command) which makes the commands execute in parallel. The problem is that you MySQL server has limited amount of maximum simultaneous connections.

What you can do is limit the spawning of processes, so you do not spawn e.g. more than 5 at a time is to wait for 5th process to finish before spawning another 5. This would be effective if those processes take similar amount of time to finish:

$i = 0;
foreach ( $values as $value ) {
    $i++
    // make asynchronous call for first 4 processes
    if ($i < 5) {
        shell_exec(sprintf('php app/console {child command} %d > /dev/null &', $value));
    } else {
          // wait for 5th process to finish before spawning more
          shell_exec(sprintf('php app/console {child command} %d > /dev/null', $value));
          $i = 0;
    }
}

Then basically adjust the number of spawnable processes to what the limit of simultaneous connections on your MySQL server is set.

Upvotes: 1

Related Questions