Worker mode: maintain the same PDO database connection between requests?

[abda11ah]

I'm starting to experiment with worker mode with a PHP application that doesn't use any framework.
I see that in xdebug as soon as I start my docker server, Frankenphp launches 4 requests to my worker.php file

Then, as soon as I reach the frankenphp_handle_request() function, I'm taken to the next request. And then my $pdo object, or even if I name it $GLOBALS['pdo'], is destroyed.

so this code doesn't work:

function connectToDatabase()
{
    $dsn = sprintf(
        "mysql:host=%s;port=%s;dbname=%s;charset=utf8mb4",
        $_ENV['DB_HOST'],
        $_ENV['DB_PORT'],
        $_ENV['MYSQL_DATABASE']
    );

    $options = [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
        PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci"
    ];

    try {
        return new PDO(
            $dsn,
            $_ENV['DB_USER'],
            $_ENV['MYSQL_ROOT_PASSWORD'],
            $options
        );
    } catch (\Exception $e) {
        throw new \Exception('Failed to connect to database: ' . $e->getMessage());
    }
}

$GLOBALS['pdo'] ??= connectToDatabase();

in php error.log I get :

[09-Mar-2025 11:40:15 Europe/Paris] PHP Fatal error : Uncaught Exception : Database connection failed : SQLSTATE[HY000] [2002] Connection refused in /app/html/public/index.php:34

probably because there was already a connection to the database during the previous query.

yet my PDO connection code is outside the $handler :

$handler = static function () use ($router, $container) {
    $container->get('language.middleware')->start();

    try {
        $router->run();
    } catch (\Throwable $e) {
        error_log($e->getMessage());
    }
};

[AlliBalliBaba]

Connection refused is more likely caused by a wrong port/host. Do you see the same error when using $_SERVER instead of $_ENV?

[abda11ah]

it's not a credentials problem because the first connexion is successful

$GLOBALS['pdo'] ??= connectToDatabase();
$successful = $GLOBALS['pdo'] ? 'good' : 'bad';

[abda11ah]

You mean $_SERVER is not destroyed between requests ?

I thought $_SERVER is not for defining vars but only for retrieving server provided vars

[AlliBalliBaba]

$_SERVER is destroyed between requests, it's just currently preferred over $_ENV in worker mode.
Can you also show the code that retrieves something from the database afterwards (the line where the error is thrown)

[abda11ah]

Yay !

I finally solved it. Thanks to Perplexity AI !

Here is my final index.php (worker file) , I had to include almost everything in the $handler
The vars inside the $handler are not lost between requests.

<?php

require '../vendor/autoload.php';

use App\Container\Container;
use App\Container\ServiceProvider;

ignore_user_abort(true);

$handler = static function () {
    $container = new Container();

    ServiceProvider::register($container);
    $container->get('exception')->register();

    $container->get('language.middleware')->start();

    // Get required services in dependency order
    $router = $container->get('router');

    // Public routes
    $router->group('/', function () use ($router, $container) {
        $router->get('', fn() => $container->get('home.controller')->home());
    }, [$container->get('activity.middleware')]);
    //$router->get('/privacy-policy', fn() => $container->get('home.controller')->privacy());
    $router->get('/privacy-policy', fn() => $container->get('home.controller')->privacy());
    $router->get('/login', fn() => $container->get('home.controller')->showLogin());


 //// all the other routes here ...

    // Presence routes (protected)
    $router->group('/presence', function () use ($router, $container) {
        $router->get('', fn() => $container->get('presence.controller')->getPresence());
    }, [$container->get('auth.middleware')]);

    try {
        $router->run();
    } catch (\Throwable $e) {
        error_log($e->getMessage());
    }
};

// Configure worker lifecycle
$maxRequests = (int) ($_SERVER['MAX_REQUESTS'] ?? 0);

// Main worker loop
for ($nbRequests = 0; !$maxRequests || $nbRequests < $maxRequests; ++$nbRequests) {

    $keepRunning = \frankenphp_handle_request($handler);

    // Add periodic cleanup
    //  $container->get('cache')->clearExpired(); 

    gc_collect_cycles();

    if (!$keepRunning) {
        break;
    }
}

// Graceful shutdown
$container->get('database')->closeConnection();

my database connection method :

    public function connectToDatabase()
    {
        if ($this->connected) {
            return;
        }

        try {
            $dsn = sprintf(
                "mysql:host=%s;port=%s;dbname=%s;charset=utf8mb4",
                $_ENV['DB_HOST'],
                $_ENV['DB_PORT'],
                $_ENV['MYSQL_DATABASE']
            );
            $options = [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false,
                PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci"
            ];
            $this->pdo = new PDO(
                $dsn,
                $_ENV['DB_USER'],
                $_ENV['MYSQL_ROOT_PASSWORD'],
                $options
            );
            $this->connected = true;
        } catch (\PDOException $e) {
            throw new \PDOException("Failed to connect to database: " . $e->getMessage());
        }
    }

Now running ab -n 1000 -c 64 https://mydomain gives me 2.5ms per request instead of 4ms !

[abda11ah]

oops , there are still errors on php error log when I run ab -n 1000 -c 64 https://mydomain/

So how to connect many requests in worker mode using always the same PDO object ?

[15-Mar-2025 14:59:07 Europe/Paris] PHP Fatal error:  Uncaught PDOException: Failed to connect to database: SQLSTATE[08004] [1040] Too many connections in /app/html/src/Services/DatabaseService.php:75
Stack trace:
#0 /app/html/src/Services/DatabaseService.php(20): App\Services\DatabaseService->connectToDatabase()
#1 /app/html/src/Container/ServiceProvider.php(62): App\Services\DatabaseService->__construct()
#2 /app/html/src/Container/Container.php(23): App\Container\ServiceProvider::{closure:App\Container\ServiceProvider::register():61}(Object(App\Container\Container))
#3 /app/html/src/Container/ServiceProvider.php(83): App\Container\Container->get('database')
#4 /app/html/src/Container/Container.php(23): App\Container\ServiceProvider::{closure:App\Container\ServiceProvider::register():81}(Object(App\Container\Container))
#5 /app/html/src/Container/ServiceProvider.php(57): App\Container\Container->get('language')
#6 /app/html/src/Container/Container.php(23): App\Container\ServiceProvider::{closure:App\Container\ServiceProvider::register():54}(Object(App\Container\Container))
#7 /app/html/src/Container/ServiceProvider.php(100): App\Container\Container->get('router')
#8 /app/html/src/Container/Container.php(23): App\Container\ServiceProvider::{closure:App\Container\ServiceProvider::register():99}(Object(App\Container\Container))
#9 /app/html/public/index.php(14): App\Container\Container->get('exception')
#10 [internal function]: {closure:/app/html/public/index.php:10}()
#11 /app/html/public/index.php(131): frankenphp_handle_request(Object(Closure))
#12 {main}

[abda11ah]

YAY !

it solved quickly by adding

PDO::ATTR_PERSISTENT => true

in the PDO options

[AlliBalliBaba]

You should probably put the setup logic for the container outside of the handler. That will make it so you only have to set up once (and only create the connection once).