Mysql as gone away

[PyRowMan]

Hey guys,

I'm using the API-platform way of using frankenphp to create an API managing a MYSQL database,

My issue is that after a long time of inactivity (in dev environment for example) the mysql server closes the connection.

Since the connection seems to stay opened due to frankenphp. I get a "Mysql has gone away" after the wait_timeout as been reached I don't really now how it would be possible to renew the connection automatically. But I think it would be the way here ?

here is my doctrine configuration file if needed :

doctrine:
    dbal:
        url: '%env(resolve:DATABASE_URL)%'

        # IMPORTANT: You MUST configure your server version,
        # either here or in the DATABASE_URL env var (see .env file)
        #server_version: '15'

        profiling_collect_backtrace: '%kernel.debug%'
    orm:
        auto_generate_proxy_classes: true
        enable_lazy_ghost_objects: true
        report_fields_where_declared: true
        validate_xml_mapping: true
        naming_strategy: doctrine.orm.naming_strategy.underscore_number_aware
        auto_mapping: true
        mappings:
            App:
                is_bundle: false
                dir: '%kernel.project_dir%/src/Entity'
                prefix: 'App\Entity'
                alias: App

when@test:
    doctrine:
        dbal:
            # "TEST_TOKEN" is typically set by ParaTest
            dbname_suffix: '_test%env(default::TEST_TOKEN)%'

when@prod:
    doctrine:
        orm:
            auto_generate_proxy_classes: false
            proxy_dir: '%kernel.build_dir%/doctrine/orm/Proxies'
            query_cache_driver:
                type: pool
                pool: doctrine.system_cache_pool
            result_cache_driver:
                type: pool
                pool: doctrine.result_cache_pool

    framework:
        cache:
            pools:
                doctrine.result_cache_pool:
                    adapter: cache.app
                doctrine.system_cache_pool:
                    adapter: cache.system

I'm new to frankenphp so I don't understand all the basics of how it works, so if my issue is not where it should be I apologize in advance.

Here is the complete log in case :

{
"channel": "request",
"context":
    {
    "exception":
        {
        "class": "Doctrine\\DBAL\\Exception\\ConnectionLost",
        "code": 2006,
        "file": "/app/vendor/doctrine/dbal/src/Driver/API/MySQL/ExceptionConverter.php:104",
        "message": "An exception occurred while executing a query: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away",
        "previous":
            {
            "class": "Doctrine\\DBAL\\Driver\\PDO\\Exception",
            "code": 2006,
            "file": "/app/vendor/doctrine/dbal/src/Driver/PDO/Exception.php:28",
            "message": "SQLSTATE[HY000]: General error: 2006 MySQL server has gone away",
            "previous":
                {
                "class": "PDOException",
                "code": 0,
                "file": "/app/vendor/doctrine/dbal/src/Driver/PDO/Statement.php:130",
                "message": "SQLSTATE[HY000]: General error: 2006 MySQL server has gone away"
                }
            }
        }
    },
"datetime": "2023-11-02T19:24:21.790899+01:00",
"extra":
    {
    },
"level": 500,
"level_name": "CRITICAL",
"message": "Uncaught PHP Exception Doctrine\\DBAL\\Exception\\ConnectionLost: \"An exception occurred while executing a query: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away\" at /app/vendor/doctrine/dbal/src/Driver/API/MySQL/ExceptionConverter.php line 104"
}

[bpolaszek]

Related: #233

[dunglas]

Fixed by symfony/symfony#53214.

[AykutCevik]

Hello @dunglas, I still face the issue when using Symfony 7.1 and frankenphp:1-php8.3 docker image. After a while, I get the message that the "MySQL server went away". Should it be solved and I might be missing something?

[dunglas]

Be sure to use a version of DoctrineBundle that contains the fix. Last time I checked this was the dev version.

[AykutCevik]

Thank you, yes, it seems it will be released in 2.13 soon if I'm not mistaken: doctrine/DoctrineBundle@2.12.x...2.13.x

[riskersen]

Hi there,

we see the same issue, but with https://packagist.org/packages/doctrine/doctrine-bundle#2.13.x-dev
Normally, the application should either reconnect to the database or send kind of keepalives/ping to the database.
We don't see this behavior on vanilla-php-fpm container..

Any ideas? Perhaps @dunglas ? :)

Short update: as workaround, I increased wait_timeout on mysql side, but that it's quite dirty..

[juslintek]

Same issue here, but with doctrine-bundle 2.13 stable. :-) Seems like the problem got reintroduced again after the fix. :-)

[riskersen]

Our workaround is to disable worker mode of frankenphp.. we don't really rely on the speed, so stability and availability is more important to us

[dunglas]

Would you be able to provide a reproducer (a repository with Docker containing minimal code for instance)? I'll take a look.

[juslintek]

Hey @dunglas, try api-platform demo, but instead of postgres, make it with mysql: wait_timeout = 10. Also make sure doctrine-bundle is of v2.13. If that does not help to reproduce it, I will create with my own identical packages simple reproducer, but this will take time to prepare.

[juslintek]

Seems like issue is fixed with php 8.3.11 :-) I was using 8.3.7 it was related to mysql 8.0.36 or percona 8.0.36 pdo bug: php/php-src#13618

[riskersen]

I'm not sure about that, because we are using mariadb and the linked issue seems to be explicitly only for mysql

[juslintek]

Sorry for false positive!

Okay, it actually was not the only fix. The actual fix that did it was upgrade of amqp-messenger to 7.1, because they have it fixed here: https://github.com/symfony/amqp-messenger/blob/7.1/Transport/AmqpReceiver.php#L54 as you see there is attempt to reconnect. And also by applying https://github.com/symfony/symfony/blob/7.1/src/Symfony/Bridge/Doctrine/Messenger/DoctrinePingConnectionMiddleware.php as documented here: https://symfony.com/doc/current/messenger.html#middleware-for-doctrine
Which attempts to create new connection before handler is executed.

Also make sure you have no EntityManagers injected in events which would run before this middleware, checkout Workers lifecycle over here: https://github.com/symfony/symfony/blob/7.2/src/Symfony/Component/Messenger/Worker.php#L162 because middlewares are handled in message bus dispatch method: https://github.com/symfony/symfony/blob/6864dbe5a6f578cb5b9d9a830fe8bb31b504f55d/src/Symfony/Component/Messenger/MessageBus.php#L69

Otherwise worker will load probably expired PDO instance via EntityManager -> MySQL Driver -> Connection -> PDO 😀
And you'll get very same error, that server has gone away.